10000 add GetUserStatusCountsByDay · coder/coder@61b27bf · GitHub
[go: up one dir, main page]
Skip to content

Commit 61b27bf

Browse files
SasSwartSasSwart
committed
add GetUserStatusCountsByDay
1 parent 4cd66af commit 61b27bf

File tree

7 files changed

+210
-0
lines changed

7 files changed

+210
-0
lines changed

‎coderd/database/dbauthz/dbauthz.go

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2413,6 +2413,13 @@ func (q *querier) GetUserNotificationPreferences(ctx context.Context, userID uui
24132413
return q.db.GetUserNotificationPreferences(ctx, userID)
24142414
}
24152415

2416+
func (q *querier) GetUserStatusCountsByDay(ctx context.Context, arg database.GetUserStatusCountsByDayParams) ([]database.GetUserStatusCountsByDayRow, error) {
2417+
if err := q.authorizeContext(ctx, policy.ActionRead, rbac.ResourceSystem); err != nil {
2418+
return nil, err
2419+
}
2420+
return q.db.GetUserStatusCountsByDay(ctx, arg)
2421+
}
2422+
24162423
func (q *querier) GetUserWorkspaceBuildParameters(ctx context.Context, params database.GetUserWorkspaceBuildParametersParams) ([]database.GetUserWorkspaceBuildParametersRow, error) {
24172424
u, err := q.db.GetUserByID(ctx, params.OwnerID)
24182425
if err != nil {

‎coderd/database/dbmem/dbmem.go

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5664,6 +5664,15 @@ func (q *FakeQuerier) GetUserNotificationPreferences(_ context.Context, userID u
56645664
return out, nil
56655665
}
56665666

5667+
func (q *FakeQuerier) GetUserStatusCountsByDay(ctx context.Context, arg database.GetUserStatusCountsByDayParams) ([]database.GetUserStatusCountsByDayRow, error) {
5668+
err := validateDatabaseType(arg)
5669+
if err != nil {
5670+
return nil, err
5671+
}
5672+
5673+
panic("not implemented")
5674+
}
5675+
56675676
func (q *FakeQuerier) GetUserWorkspaceBuildParameters(_ context.Context, params database.GetUserWorkspaceBuildParametersParams) ([]database.GetUserWorkspaceBuildParametersRow, error) {
56685677
q.mutex.RLock()
56695678
defer q.mutex.RUnlock()

‎coderd/database/dbmetrics/querymetrics.go

Lines changed: 7 additions & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/dbmock/dbmock.go

Lines changed: 15 additions & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/querier.go

Lines changed: 1 addition & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/queries.sql.go

Lines changed: 103 additions & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/queries/insights.sql

Lines changed: 68 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -771,3 +771,71 @@ SELECT
771771
FROM unique_template_params utp
772772
JOIN workspace_build_parameters wbp ON (utp.workspace_build_ids @> ARRAY[wbp.workspace_build_id] AND utp.name = wbp.name)
773773
GROUP BY utp.num, utp.template_ids, utp.name, utp.type, utp.display_name, utp.description, utp.options, wbp.value;
774+
775+
-- name: GetUserStatusCountsByDay :many
776+
WITH dates AS (
777+
-- Generate a series of dates between start and end
778+
SELECT
779+
day::date
780+
FROM
781+
generate_series(
782+
date_trunc('day', @start_time::timestamptz),
783+
date_trunc('day', @end_time::timestamptz),
784+
'1 day'::interval
785+
) AS day
786+
),
787+
initial_statuses AS (
788+
-- Get the status of each user right before the start date
789+
SELECT DISTINCT ON (user_id)
790+
user_id,
791+
new_status as status
792+
FROM
793+
user_status_changes
794+
WHERE
795+
changed_at < @start_time::timestamptz
796+
ORDER BY
797+
user_id,
798+
changed_at DESC
799+
),
800+
relevant_changes AS (
801+
-- Get only the status changes within our date range
802+
SELECT
803+
date_trunc('day', changed_at)::date AS day,
804+
user_id,
805+
new_status as status
806+
FROM
807+
user_status_changes
808+
WHERE
809+
changed_at >= @start_time::timestamptz
810+
AND changed_at <= @end_time::timestamptz
811+
),
812+
daily_status AS (
813+
-- Combine initial statuses with changes
814+
SELECT
815+
d.day,
816+
COALESCE(rc.status, i.status) as status,
817+
COALESCE(rc.user_id, i.user_id) as user_id
818+
FROM
819+
dates d
820+
CROSS JOIN
821+
initial_statuses i
822+
LEFT JOIN
823+
relevant_changes rc
824+
ON
825+
rc.day = d.day
826+
AND rc.user_id = i.user_id
827+
)
828+
SELECT
829+
day,
830+
status,
831+
COUNT(*) AS count
832+
FROM
833+
daily_status
834+
WHERE
835+
status IS NOT NULL
836+
GROUP BY
837+
day,
838+
status
839+
ORDER BY
840+
day ASC,
841+
status ASC;

0 commit comments

Comments
 (0)
0