add GetUserStatusCountsByDay

coder · SasSwart · Jan 13, 2025 · Dec 14, 2024 · Dec 14, 2024 · Dec 14, 2024
commit ec16728f3677328a069f007b2145d0f48820fcf4
diff --git a/coderd/database/dbauthz/dbauthz.go b/coderd/database/dbauthz/dbauthz.go
@@ -2421,6 +2421,13 @@ func (q *querier) GetUserNotificationPreferences(ctx context.Context, userID uui
 	return q.db.GetUserNotificationPreferences(ctx, userID)
 }
 
+func (q *querier) GetUserStatusCountsByDay(ctx context.Context, arg database.GetUserStatusCountsByDayParams) ([]database.GetUserStatusCountsByDayRow, error) {
+	if err := q.authorizeContext(ctx, policy.ActionRead, rbac.ResourceSystem); err != nil {
+		return nil, err
+	}
+	return q.db.GetUserStatusCountsByDay(ctx, arg)
+}
+
 func (q *querier) GetUserWorkspaceBuildParameters(ctx context.Context, params database.GetUserWorkspaceBuildParametersParams) ([]database.GetUserWorkspaceBuildParametersRow, error) {
 	u, err := q.db.GetUserByID(ctx, params.OwnerID)
 	if err != nil {

diff --git a/coderd/database/dbmem/dbmem.go b/coderd/database/dbmem/dbmem.go
@@ -5669,6 +5669,15 @@ func (q *FakeQuerier) GetUserNotificationPreferences(_ context.Context, userID u
 	return out, nil
 }
 
+func (q *FakeQuerier) GetUserStatusCountsByDay(ctx context.Context, arg database.GetUserStatusCountsByDayParams) ([]database.GetUserStatusCountsByDayRow, error) {
+	err := validateDatabaseType(arg)
+	if err != nil {
+		return nil, err
+	}
+
+	panic("not implemented")
+}
+
 func (q *FakeQuerier) GetUserWorkspaceBuildParameters(_ context.Context, params database.GetUserWorkspaceBuildParametersParams) ([]database.GetUserWorkspaceBuildParametersRow, error) {
 	q.mutex.RLock()
 	defer q.mutex.RUnlock()

diff --git a/coderd/database/dbmetrics/querymetrics.go b/coderd/database/dbmetrics/querymetrics.go
diff --git a/coderd/database/dbmock/dbmock.go b/coderd/database/dbmock/dbmock.go
diff --git a/coderd/database/querier.go b/coderd/database/querier.go
diff --git a/coderd/database/queries.sql.go b/coderd/database/queries.sql.go
diff --git a/coderd/database/queries/insights.sql b/coderd/database/queries/insights.sql
@@ -771,3 +771,71 @@ SELECT
 FROM unique_template_params utp
 JOIN workspace_build_parameters wbp ON (utp.workspace_build_ids @> ARRAY[wbp.workspace_build_id] AND utp.name = wbp.name)
 GROUP BY utp.num, utp.template_ids, utp.name, utp.type, utp.display_name, utp.description, utp.options, wbp.value;
+
+-- name: GetUserStatusCountsByDay :many
+WITH dates AS (
+    -- Generate a series of dates between start and end
+    SELECT
+        day::date
+    FROM
+        generate_series(
+            date_trunc('day', @start_time::timestamptz),
+            date_trunc('day', @end_time::timestamptz),
+            '1 day'::interval
+        ) AS day
+),
+initial_statuses AS (
+    -- Get the status of each user right before the start date
+    SELECT DISTINCT ON (user_id)
+        user_id,
+        new_status as status
+    FROM
+        user_status_changes
+    WHERE
+        changed_at < @start_time::timestamptz
+    ORDER BY
+        user_id,
+        changed_at DESC
+),
+relevant_changes AS (
+    -- Get only the status changes within our date range
+    SELECT
+        date_trunc('day', changed_at)::date AS day,
+        user_id,
+        new_status as status
+    FROM
+        user_status_changes
+    WHERE
+        changed_at >= @start_time::timestamptz
+        AND changed_at <= @end_time::timestamptz
+),
+daily_status AS (
+    -- Combine initial statuses with changes
+    SELECT
+        d.day,
+        COALESCE(rc.status, i.status) as status,
+        COALESCE(rc.user_id, i.user_id) as user_id
+    FROM
+        dates d
+    CROSS JOIN
+        initial_statuses i
+    LEFT JOIN
+        relevant_changes rc
+    ON
+        rc.day = d.day
+        AND rc.user_id = i.user_id
+)
+SELECT
+    day,
+    status,
+    COUNT(*) AS count
+FROM
+    daily_status
+WHERE
+    status IS NOT NULL
+GROUP BY
+    day,
+    status
+ORDER BY
+    day ASC,
+    status ASC;