Skip to content

Commit 77c3115

Browse files
mafredrimafredri
committed
feat(coderd/database): rewrite GetUserActivityInsights to use template_usage_stats 
1 parent 1793acb commit 77c3115

File tree

2 files changed

+90
-136
lines changed

2 files changed

+90
-136
lines changed

‎coderd/database/queries.sql.go

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

‎coderd/database/queries/insights.sql

+44-67
Original file line numberDiff line numberDiff line change
@@ -28,80 +28,57 @@ ORDER BY
2828

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

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

0 commit comments

Comments
 (0)