@@ -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'
55- ' 1 minute'
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'
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+
9562SELECT
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