move aggregation logic for GetUserStatusChanges into the SQL

coder · SasSwart · Dec 14, 2024 · Dec 14, 2024 · Dec 14, 2024 · Dec 17, 2024
commit ce1bda5f8b8ab3f3a362f51a1e5eec396cf83ecd
diff --git a/coderd/database/dbauthz/dbauthz.go b/coderd/database/dbauthz/dbauthz.go
@@ -2413,7 +2413,7 @@ func (q *querier) GetUserNotificationPreferences(ctx context.Context, userID uui
 	return q.db.GetUserNotificationPreferences(ctx, userID)
 }
 
-func (q *querier) GetUserStatusChanges(ctx context.Context, arg database.GetUserStatusChangesParams) ([]database.UserStatusChange, error) {
+func (q *querier) GetUserStatusChanges(ctx context.Context, arg database.GetUserStatusChangesParams) ([]database.GetUserStatusChangesRow, error) {
 	if err := q.authorizeContext(ctx, policy.ActionRead, rbac.ResourceUser); err != nil {
 		return nil, err
 	}

diff --git a/coderd/database/dbmem/dbmem.go b/coderd/database/dbmem/dbmem.go
@@ -5666,7 +5666,7 @@ func (q *FakeQuerier) GetUserNotificationPreferences(_ context.Context, userID u
 	return out, nil
 }
 
-func (q *FakeQuerier) GetUserStatusChanges(_ context.Context, arg database.GetUserStatusChangesParams) ([]database.UserStatusChange, error) {
+func (q *FakeQuerier) GetUserStatusChanges(_ context.Context, arg database.GetUserStatusChangesParams) ([]database.GetUserStatusChangesRow, error) {
 	q.mutex.RLock()
 	defer q.mutex.RUnlock()
 
@@ -5675,12 +5675,27 @@ func (q *FakeQuerier) GetUserStatusChanges(_ context.Context, arg database.GetUs
 		return nil, err
 	}
 
-	result := make([]database.UserStatusChange, 0)
+	result := make([]database.GetUserStatusChangesRow, 0)
 	for _, change := range q.userStatusChanges {
 		if change.ChangedAt.Before(arg.StartTime) || change.ChangedAt.After(arg.EndTime) {
 			continue
 		}
-		result = append(result, change)
+		if !slices.ContainsFunc(result, func(r database.GetUserStatusChangesRow) bool {
+			return r.ChangedAt.Equal(change.ChangedAt) && r.NewStatus == change.NewStatus
+		}) {
+			result = append(result, database.GetUserStatusChangesRow{
+				NewStatus: change.NewStatus,
+				ChangedAt: change.ChangedAt,
+				Count:     1,
+			})
+		} else {
+			for i, r := range result {
+				if r.ChangedAt.Equal(change.ChangedAt) && r.NewStatus == change.NewStatus {
+					result[i].Count++
+					break
+				}
+			}
+		}
 	}
 
 	return result, nil

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
@@ -773,9 +773,36 @@ JOIN workspace_build_parameters wbp ON (utp.workspace_build_ids @> ARRAY[wbp.wor
 GROUP BY utp.num, utp.template_ids, utp.name, utp.type, utp.display_name, utp.description, utp.options, wbp.value;
 
 -- name: GetUserStatusChanges :many
+WITH last_status_per_day AS (
+    -- First get the last status change for each user for each day
+    SELECT DISTINCT ON (date_trunc('day', changed_at), user_id)
+        date_trunc('day', changed_at)::timestamptz AS date,
+        new_status,
+        user_id
+    FROM user_status_changes
+    WHERE changed_at >= @start_time::timestamptz
+        AND changed_at < @end_time::timestamptz
+    ORDER BY
+        date_trunc('day', changed_at),
+        user_id,
+        changed_at DESC  -- This ensures we get the last status for each day
+),
+daily_counts AS (
+    -- Then count unique users per status per day
+    SELECT
+        date,
+        new_status,
+        COUNT(*) AS count
+    FROM last_status_per_day
+    GROUP BY
+        date,
+        new_status
+)
 SELECT
-	*
-FROM user_status_changes
-WHERE changed_at >= @start_time::timestamptz
-	AND changed_at < @end_time::timestamptz
-ORDER BY changed_at;
+    date::timestamptz AS changed_at,
+    new_status,
+    count::bigint
+FROM daily_counts
+ORDER BY
+    new_status ASC,
+    date ASC;
diff --git a/coderd/insights.go b/coderd/insights.go
@@ -340,19 +340,14 @@ func (api *API) insightsUserStatusCountsOverTime(rw http.ResponseWriter, r *http
 		StatusCounts: make(map[codersdk.UserStatus][]codersdk.UserStatusChangeCount),
 	}
 
-	slices.SortFunc(rows, func(a, b database.UserStatusChange) int {
-		return a.ChangedAt.Compare(b.ChangedAt)
-	})
-
 	for _, row := range rows {
-		date := row.ChangedAt.Truncate(24 * time.Hour)
 		status := codersdk.UserStatus(row.NewStatus)
 		if _, ok := resp.StatusCounts[status]; !ok {
 			resp.StatusCounts[status] = make([]codersdk.UserStatusChangeCount, 0)
 		}
 		resp.StatusCounts[status] = append(resp.StatusCounts[status], codersdk.UserStatusChangeCount{
-			Date:  date,
-			Count: 1,
+			Date:  row.ChangedAt,
+			Count: row.Count,
 		})
 	}