Skip to content

Commit ce1bda5

Browse files
SasSwartSasSwart
committed
move aggregation logic for GetUserStatusChanges into the SQL
1 parent b204783 commit ce1bda5

File tree

8 files changed

+108
-43
lines changed

8 files changed

+108
-43
lines changed

‎coderd/database/dbauthz/dbauthz.go

+1-1
Original file line numberDiff line numberDiff line change
@@ -2413,7 +2413,7 @@ func (q *querier) GetUserNotificationPreferences(ctx context.Context, userID uui
24132413
return q.db.GetUserNotificationPreferences(ctx, userID)
24142414
}
24152415

2416-
func (q *querier) GetUserStatusChanges(ctx context.Context, arg database.GetUserStatusChangesParams) ([]database.UserStatusChange, error) {
2416+
func (q *querier) GetUserStatusChanges(ctx context.Context, arg database.GetUserStatusChangesParams) ([]database.GetUserStatusChangesRow, error) {
24172417
if err := q.authorizeContext(ctx, policy.ActionRead, rbac.ResourceUser); err != nil {
24182418
return nil, err
24192419
}

‎coderd/database/dbmem/dbmem.go

+18-3
Original file line numberDiff line numberDiff line change
@@ -5666,7 +5666,7 @@ func (q *FakeQuerier) GetUserNotificationPreferences(_ context.Context, userID u
56665666
return out, nil
56675667
}
56685668

5669-
func (q *FakeQuerier) GetUserStatusChanges(_ context.Context, arg database.GetUserStatusChangesParams) ([]database.UserStatusChange, error) {
5669+
func (q *FakeQuerier) GetUserStatusChanges(_ context.Context, arg database.GetUserStatusChangesParams) ([]database.GetUserStatusChangesRow, error) {
56705670
q.mutex.RLock()
56715671
defer q.mutex.RUnlock()
56725672

@@ -5675,12 +5675,27 @@ func (q *FakeQuerier) GetUserStatusChanges(_ context.Context, arg database.GetUs
56755675
return nil, err
56765676
}
56775677

5678-
result := make([]database.UserStatusChange, 0)
5678+
result := make([]database.GetUserStatusChangesRow, 0)
56795679
for _, change := range q.userStatusChanges {
56805680
if change.ChangedAt.Before(arg.StartTime) || change.ChangedAt.After(arg.EndTime) {
56815681
continue
56825682
}
5683-
result = append(result, change)
5683+
if !slices.ContainsFunc(result, func(r database.GetUserStatusChangesRow) bool {
5684+
return r.ChangedAt.Equal(change.ChangedAt) && r.NewStatus == change.NewStatus
5685+
}) {
5686+
result = append(result, database.GetUserStatusChangesRow{
5687+
NewStatus: change.NewStatus,
5688+
ChangedAt: change.ChangedAt,
5689+
Count: 1,
5690+
})
5691+
} else {
5692+
for i, r := range result {
5693+
if r.ChangedAt.Equal(change.ChangedAt) && r.NewStatus == change.NewStatus {
5694+
result[i].Count++
5695+
break
5696+
}
5697+
}
5698+
}
56845699
}
56855700

56865701
return result, nil

‎coderd/database/dbmetrics/querymetrics.go

+1-1
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/dbmock/dbmock.go

+2-2
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/querier.go

+1-1
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/queries.sql.go

+51-23
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/queries/insights.sql

+32-5
Original file line numberDiff line numberDiff line change
@@ -773,9 +773,36 @@ JOIN workspace_build_parameters wbp ON (utp.workspace_build_ids @> ARRAY[wbp.wor
773773
GROUP BY utp.num, utp.template_ids, utp.name, utp.type, utp.display_name, utp.description, utp.options, wbp.value;
774774

775775
-- name: GetUserStatusChanges :many
776+
WITH last_status_per_day AS (
777+
-- First get the last status change for each user for each day
778+
SELECT DISTINCT ON (date_trunc('day', changed_at), user_id)
779+
date_trunc('day', changed_at)::timestamptz AS date,
780+
new_status,
781+
user_id
782+
FROM user_status_changes
783+
WHERE changed_at >= @start_time::timestamptz
784+
AND changed_at < @end_time::timestamptz
785+
ORDER BY
786+
date_trunc('day', changed_at),
787+
user_id,
788+
changed_at DESC -- This ensures we get the last status for each day
789+
),
790+
daily_counts AS (
791+
-- Then count unique users per status per day
792+
SELECT
793+
date,
794+
new_status,
795+
COUNT(*) AS count
796+
FROM last_status_per_day
797+
GROUP BY
798+
date,
799+
new_status
800+
)
776801
SELECT
777-
*
778-
FROM user_status_changes
779-
WHERE changed_at >= @start_time::timestamptz
780-
AND changed_at < @end_time::timestamptz
781-
ORDER BY changed_at;
802+
date::timestamptz AS changed_at,
803+
new_status,
804+
count::bigint
805+
FROM daily_counts
806+
ORDER BY
807+
new_status ASC,
808+
date ASC;

‎coderd/insights.go

+2-7
Original file line numberDiff line numberDiff line change
@@ -340,19 +340,14 @@ func (api *API) insightsUserStatusCountsOverTime(rw http.ResponseWriter, r *http
340340
StatusCounts: make(map[codersdk.UserStatus][]codersdk.UserStatusChangeCount),
341341
}
342342

343-
slices.SortFunc(rows, func(a, b database.UserStatusChange) int {
344-
return a.ChangedAt.Compare(b.ChangedAt)
345-
})
346-
347343
for _, row := range rows {
348-
date := row.ChangedAt.Truncate(24 * time.Hour)
349344
status := codersdk.UserStatus(row.NewStatus)
350345
if _, ok := resp.StatusCounts[status]; !ok {
351346
resp.StatusCounts[status] = make([]codersdk.UserStatusChangeCount, 0)
352347
}
353348
resp.StatusCounts[status] = append(resp.StatusCounts[status], codersdk.UserStatusChangeCount{
354-
Date: date,
355-
Count: 1,
349+
Date: row.ChangedAt,
350+
Count: row.Count,
356351
})
357352
}
358353

0 commit comments

Comments
 (0)