Skip to content

Commit dc93229

Browse files
mafredrimafredri
committed
feat(coderd/database): rewrite GetUserActivityInsights to use template_usage_stats 
1 parent 7750bb6 commit dc93229

8 files changed

+100
-146
lines changed

‎coderd/database/querier.go

Lines changed: 5 additions & 5 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: 46 additions & 69 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: 44 additions & 67 deletions
Original file line numberDiff line numberDiff line change
@@ -27,80 +27,57 @@ ORDER BY
2727

2828
-- name: GetUserActivityInsights :many
2929
-- GetUserActivityInsights returns the ranking with top active users.
30-
-- The result can be filtered on template_ids, meaning only user data from workspaces
31-
-- based on those templates will be included.
32-
-- Note: When selecting data from multiple templates or the entire deployment,
33-
-- be aware that it may lead to an increase in "usage" numbers (cumulative). In such cases,
34-
-- users may be counted multiple times for the same time interval if they have used multiple templates
30+
-- The result can be filtered on template_ids, meaning only user data
31+
-- from workspaces based on those templates will be included.
32+
-- Note: The usage_seconds and usage_seconds_cumulative differ only when
33+
-- requesting deployment-wide (or multiple template) data. Cumulative
34+
-- produces a bloated value if a user has used multiple templates
3535
-- simultaneously.
36-
WITH app_stats AS (
37-
SELECT
38-
s.start_time,
39-
was.user_id,
40-
w.template_id,
41-
60 as seconds
42-
FROM workspace_app_stats was
43-
JOIN workspaces w ON (
44-
w.id = was.workspace_id
45-
AND CASE WHEN COALESCE(array_length(@template_ids::uuid[], 1), 0) > 0 THEN w.template_id = ANY(@template_ids::uuid[]) ELSE TRUE END
46-
)
47-
-- This table contains both 1 minute entries and >1 minute entries,
48-
-- to calculate this with our uniqueness constraints, we generate series
49-
-- for the longer intervals.
50-
CROSS JOIN LATERAL generate_series(
51-
date_trunc('minute', was.session_started_at),
52-
-- Subtract 1 microsecond to avoid creating an extra series.
53-
date_trunc('minute', was.session_ended_at - '1 microsecond'::interval),
54-
'1 minute'::interval
55-
) s(start_time)
56-
WHERE
57-
s.start_time >= @start_time::timestamptz
58-
-- Subtract one minute because the series only contains the start time.
59-
AND s.start_time < (@end_time::timestamptz) - '1 minute'::interval
60-
GROUP BY s.start_time, w.template_id, was.user_id
61-
), session_stats AS (
62-
SELECT
63-
date_trunc('minute', was.created_at) as start_time,
64-
was.user_id,
65-
was.template_id,
66-
CASE WHEN
67-
SUM(was.session_count_vscode) > 0 OR
68-
SUM(was.session_count_jetbrains) > 0 OR
69-
SUM(was.session_count_reconnecting_pty) > 0 OR
70-
SUM(was.session_count_ssh) > 0
71-
THEN 60 ELSE 0 END as seconds
72-
FROM workspace_agent_stats was
73-
WHERE
74-
was.created_at >= @start_time::timestamptz
75-
AND was.created_at < @end_time::timestamptz
76-
AND was.connection_count > 0
77-
AND CASE WHEN COALESCE(array_length(@template_ids::uuid[], 1), 0) > 0 THEN was.template_id = ANY(@template_ids::uuid[]) ELSE TRUE END
78-
GROUP BY date_trunc('minute', was.created_at), was.user_id, was.template_id
79-
), combined_stats AS (
36+
WITH deployment_stats AS (
8037
SELECT
81-
user_id,
82-
template_id,
8338
start_time,
84-
seconds
85-
FROM app_stats
86-
UNION
39+
user_id,
40+
array_agg(template_id) AS template_ids,
41+
-- See motivation in GetTemplateInsights for LEAST(SUM(n), 30).
42+
LEAST(SUM(usage_mins), 30) AS usage_mins
43+
FROM
44+
template_usage_stats
45+
WHERE
46+
start_time >= @start_time::timestamptz
47+
AND end_time <= @end_time::timestamptz
48+
AND CASE WHEN COALESCE(array_length(@template_ids::uuid[], 1), 0) > 0 THEN template_id = ANY(@template_ids::uuid[]) ELSE TRUE END
49+
GROUP BY
50+
start_time, user_id
51+
), template_ids AS (
8752
SELECT
8853
user_id,
89-
template_id,
90-
start_time,
91-
seconds
92-
FROM session_stats
54+
array_agg(DISTINCT template_id) AS ids
55+
FROM
56+
deployment_stats, unnest(template_ids) template_id
57+
GROUP BY
58+
user_id
9359
)
60+
9461
SELECT
95-
users.id as user_id,
96-
users.username,
97-
users.avatar_url,
98-
array_agg(DISTINCT template_id)::uuid[] AS template_ids,
99-
SUM(seconds) AS usage_seconds
100-
FROM combined_stats
101-
JOIN users ON (users.id = combined_stats.user_id)
102-
GROUP BY users.id, username, avatar_url
103-
ORDER BY user_id ASC;
62+
ds.user_id,
63+
u.username,
64+
u.avatar_url,
65+
t.ids::uuid[] AS template_ids,
66+
(SUM(ds.usage_mins) * 60)::bigint AS usage_seconds
67+
FROM
68+
deployment_stats ds
69+
JOIN
70+
users u
71+
ON
72+
u.id = ds.user_id
73+
JOIN
74+
template_ids t
75+
ON
76+
ds.user_id = t.user_id
77+
GROUP BY
78+
ds.user_id, u.username, u.avatar_url, t.ids
79+
ORDER BY
80+
ds.user_id ASC;
10481

10582
-- name: GetTemplateInsights :one
10683
-- GetTemplateInsights returns the aggregate user-produced usage of all

‎coderd/testdata/insights/user-activity/multiple_users_and_workspaces_week_all_templates.json.golden

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -16,7 +16,7 @@
1616
"user_id": "00000000-0000-0000-0000-000000000004",
1717
"username": "user1",
1818
"avatar_url": "",
19-
"seconds": 30540
19+
"seconds": 26820
2020
},
2121
{
2222
"template_ids": [

‎coderd/testdata/insights/user-activity/multiple_users_and_workspaces_week_deployment_wide.json.golden

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -16,7 +16,7 @@
1616
"user_id": "00000000-0000-0000-0000-000000000004",
1717
"username": "user1",
1818
"avatar_url": "",
19-
"seconds": 30540
19+
"seconds": 26820
2020
},
2121
{
2222
"template_ids": [

‎coderd/testdata/insights/user-activity/multiple_users_and_workspaces_week_other_timezone_(São_Paulo).json.golden

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -16,7 +16,7 @@
1616
"user_id": "00000000-0000-0000-0000-000000000004",
1717
"username": "user1",
1818
"avatar_url": "",
19-
"seconds": 23280
19+
"seconds": 23160
2020
},
2121
{
2222
"template_ids": [

‎coderd/testdata/insights/user-activity/multiple_users_and_workspaces_weekly_aggregated_deployment_wide.json.golden

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -16,7 +16,7 @@
1616
"user_id": "00000000-0000-0000-0000-000000000004",
1717
"username": "user1",
1818
"avatar_url": "",
19-
"seconds": 29820
19+
"seconds": 26100
2020
},
2121
{
2222
"template_ids": [

‎coderd/testdata/insights/user-activity/multiple_users_and_workspaces_weekly_aggregated_templates.json.golden

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -16,7 +16,7 @@
1616
"user_id": "00000000-0000-0000-0000-000000000004",
1717
"username": "user1",
1818
"avatar_url": "",
19-
"seconds": 29820
19+
"seconds": 26100
2020
},
2121
{
2222
"template_ids": [

0 commit comments

Comments
 (0)