Skip to content

Commit a0fce36

Browse files
mafredrimafredri
authored
feat(coderd): add times_used to coder_apps in insights API (coder#13292)
For now, only applied to `coder_app`s, same logic can be implemented for VS Code, SSH, etc. Part of coder#13099
1 parent 63e0685 commit a0fce36

24 files changed

+346
-102
lines changed

‎coderd/apidoc/docs.go

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

‎coderd/apidoc/swagger.json

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

‎coderd/database/dbmem/dbmem.go

Lines changed: 72 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3149,6 +3149,30 @@ func (q *FakeQuerier) GetTemplateAppInsights(ctx context.Context, arg database.G
31493149
GROUP BY
31503150
start_time, user_id, slug, display_name, icon
31513151
),
3152+
-- Analyze the users unique app usage across all templates. Count
3153+
-- usage across consecutive intervals as continuous usage.
3154+
times_used AS (
3155+
SELECT DISTINCT ON (user_id, slug, display_name, icon, uniq)
3156+
slug,
3157+
display_name,
3158+
icon,
3159+
-- Turn start_time into a unique identifier that identifies a users
3160+
-- continuous app usage. The value of uniq is otherwise garbage.
3161+
--
3162+
-- Since we're aggregating per user app usage across templates,
3163+
-- there can be duplicate start_times. To handle this, we use the
3164+
-- dense_rank() function, otherwise row_number() would suffice.
3165+
start_time - (
3166+
dense_rank() OVER (
3167+
PARTITION BY
3168+
user_id, slug, display_name, icon
3169+
ORDER BY
3170+
start_time
3171+
) * '30 minutes'::interval
3172+
) AS uniq
3173+
FROM
3174+
template_usage_stats_with_apps
3175+
),
31523176
*/
31533177

31543178
// Due to query optimizations, this logic is somewhat inverted from
@@ -3160,12 +3184,19 @@ func (q *FakeQuerier) GetTemplateAppInsights(ctx context.Context, arg database.G
31603184
DisplayName string
31613185
Icon string
31623186
}
3187+
type appTimesUsedGroupBy struct {
3188+
UserID uuid.UUID
3189+
Slug string
3190+
DisplayName string
3191+
Icon string
3192+
}
31633193
type appInsightsRow struct {
31643194
appInsightsGroupBy
31653195
TemplateIDs []uuid.UUID
31663196
AppUsageMins int64
31673197
}
31683198
appInsightRows := make(map[appInsightsGroupBy]appInsightsRow)
3199+
appTimesUsedRows := make(map[appTimesUsedGroupBy]map[time.Time]struct{})
31693200
// FROM
31703201
for _, stat := range q.templateUsageStats {
31713202
// WHERE
@@ -3201,9 +3232,42 @@ func (q *FakeQuerier) GetTemplateAppInsights(ctx context.Context, arg database.G
32013232
row.TemplateIDs = append(row.TemplateIDs, stat.TemplateID)
32023233
row.AppUsageMins = least(row.AppUsageMins+appUsage, 30)
32033234
appInsightRows[key] = row
3235+
3236+
// Prepare to do times_used calculation, distinct start times.
3237+
timesUsedKey := appTimesUsedGroupBy{
3238+
UserID: stat.UserID,
3239+
Slug: slug,
3240+
DisplayName: app.DisplayName,
3241+
Icon: app.Icon,
3242+
}
3243+
if appTimesUsedRows[timesUsedKey] == nil {
3244+
appTimesUsedRows[timesUsedKey] = make(map[time.Time]struct{})
3245+
}
3246+
// This assigns a distinct time, so we don't need to
3247+
// dense_rank() later on, we can simply do row_number().
3248+
appTimesUsedRows[timesUsedKey][stat.StartTime] = struct{}{}
32043249
}
32053250
}
32063251

3252+
appTimesUsedTempRows := make(map[appTimesUsedGroupBy][]time.Time)
3253+
for key, times := range appTimesUsedRows {
3254+
for t := range times {
3255+
appTimesUsedTempRows[key] = append(appTimesUsedTempRows[key], t)
3256+
}
3257+
}
3258+
for _, times := range appTimesUsedTempRows {
3259+
slices.SortFunc(times, func(a, b time.Time) int {
3260+
return int(a.Sub(b))
3261+
})
3262+
}
3263+
for key, times := range appTimesUsedTempRows {
3264+
uniq := make(map[time.Time]struct{})
3265+
for i, t := range times {
3266+
uniq[t.Add(-(30 * time.Minute * time.Duration(i)))] = struct{}{}
3267+
}
3268+
appTimesUsedRows[key] = uniq
3269+
}
3270+
32073271
/*
32083272
-- Even though we allow identical apps to be aggregated across
32093273
-- templates, we still want to be able to report which templates
@@ -3288,14 +3352,20 @@ func (q *FakeQuerier) GetTemplateAppInsights(ctx context.Context, arg database.G
32883352

32893353
var rows []database.GetTemplateAppInsightsRow
32903354
for key, gr := range groupedRows {
3291-
rows = append(rows, database.GetTemplateAppInsightsRow{
3355+
row := database.GetTemplateAppInsightsRow{
32923356
TemplateIDs: templateRows[key].TemplateIDs,
32933357
ActiveUsers: int64(len(uniqueSortedUUIDs(gr.ActiveUserIDs))),
32943358
Slug: key.Slug,
32953359
DisplayName: key.DisplayName,
32963360
Icon: key.Icon,
32973361
UsageSeconds: gr.UsageSeconds,
3298-
})
3362+
}
3363+
for tuk, uniq := range appTimesUsedRows {
3364+
if key.Slug == tuk.Slug && key.DisplayName == tuk.DisplayName && key.Icon == tuk.Icon {
3365+
row.TimesUsed += int64(len(uniq))
3366+
}
3367+
}
3368+
rows = append(rows, row)
32993369
}
33003370

33013371
// NOTE(mafredri): Add sorting if we decide on how to handle PostgreSQL collations.

‎coderd/database/queries.sql.go

Lines changed: 40 additions & 6 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: 38 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -249,7 +249,7 @@ WITH
249249
apps.slug,
250250
apps.display_name,
251251
apps.icon,
252-
tus.app_usage_mins
252+
(tus.app_usage_mins -> apps.slug)::smallint AS usage_mins
253253
FROM
254254
apps
255255
JOIN
@@ -273,14 +273,36 @@ WITH
273273
display_name,
274274
icon,
275275
-- See motivation in GetTemplateInsights for LEAST(SUM(n), 30).
276-
LEAST(SUM(app_usage.value::smallint), 30) AS usage_mins
276+
LEAST(SUM(usage_mins), 30) AS usage_mins
277277
FROM
278-
template_usage_stats_with_apps, jsonb_each(app_usage_mins) AS app_usage
279-
WHERE
280-
app_usage.key = slug
278+
template_usage_stats_with_apps
281279
GROUP BY
282280
start_time, user_id, slug, display_name, icon
283281
),
282+
-- Analyze the users unique app usage across all templates. Count
283+
-- usage across consecutive intervals as continuous usage.
284+
times_used AS (
285+
SELECT DISTINCT ON (user_id, slug, display_name, icon, uniq)
286+
slug,
287+
display_name,
288+
icon,
289+
-- Turn start_time into a unique identifier that identifies a users
290+
-- continuous app usage. The value of uniq is otherwise garbage.
291+
--
292+
-- Since we're aggregating per user app usage across templates,
293+
-- there can be duplicate start_times. To handle this, we use the
294+
-- dense_rank() function, otherwise row_number() would suffice.
295+
start_time - (
296+
dense_rank() OVER (
297+
PARTITION BY
298+
user_id, slug, display_name, icon
299+
ORDER BY
300+
start_time
301+
) * '30 minutes'::interval
302+
) AS uniq
303+
FROM
304+
template_usage_stats_with_apps
305+
),
284306
-- Even though we allow identical apps to be aggregated across
285307
-- templates, we still want to be able to report which templates
286308
-- the data comes from.
@@ -302,7 +324,17 @@ SELECT
302324
ai.slug,
303325
ai.display_name,
304326
ai.icon,
305-
(SUM(ai.usage_mins) * 60)::bigint AS usage_seconds
327+
(SUM(ai.usage_mins) * 60)::bigint AS usage_seconds,
328+
COALESCE((
329+
SELECT
330+
COUNT(*)
331+
FROM
332+
times_used
333+
WHERE
334+
times_used.slug = ai.slug
335+
AND times_used.display_name = ai.display_name
336+
AND times_used.icon = ai.icon
337+
), 0)::bigint AS times_used
306338
FROM
307339
app_insights AS ai
308340
JOIN

‎coderd/insights.go

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -543,6 +543,7 @@ func convertTemplateInsightsApps(usage database.GetTemplateInsightsRow, appUsage
543543
Slug: app.Slug,
544544
Icon: app.Icon,
545545
Seconds: app.UsageSeconds,
546+
TimesUsed: app.TimesUsed,
546547
})
547548
}
548549

‎coderd/testdata/insights/template/multiple_users_and_workspaces_three_weeks_second_template.json.golden

Lines changed: 12 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -15,15 +15,17 @@
1515
"display_name": "Visual Studio Code",
1616
"slug": "vscode",
1717
"icon": "/icon/code.svg",
18-
"seconds": 3600
18+
"seconds": 3600,
19+
"times_used": 0
1920
},
2021
{
2122
"template_ids": [],
2223
"type": "builtin",
2324
"display_name": "JetBrains",
2425
"slug": "jetbrains",
2526
"icon": "/icon/intellij.svg",
26-
"seconds": 0
27+
"seconds": 0,
28+
"times_used": 0
2729
},
2830
{
2931
"template_ids": [
@@ -33,7 +35,8 @@
3335
"display_name": "Web Terminal",
3436
"slug": "reconnecting-pty",
3537
"icon": "/icon/terminal.svg",
36-
"seconds": 7200
38+
"seconds": 7200,
39+
"times_used": 0
3740
},
3841
{
3942
"template_ids": [
@@ -43,15 +46,17 @@
4346
"display_name": "SSH",
4447
"slug": "ssh",
4548
"icon": "/icon/terminal.svg",
46-
"seconds": 10800
49+
"seconds": 10800,
50+
"times_used": 0
4751
},
4852
{
4953
"template_ids": [],
5054
"type": "builtin",
5155
"display_name": "SFTP",
5256
"slug": "sftp",
5357
"icon": "/icon/terminal.svg",
54-
"seconds": 0
58+
"seconds": 0,
59+
"times_used": 0
5560
},
5661
{
5762
"template_ids": [
@@ -61,7 +66,8 @@
6166
"display_name": "app1",
6267
"slug": "app1",
6368
"icon": "/icon1.png",
64-
"seconds": 25200
69+
"seconds": 25200,
70+
"times_used": 2
6571
}
6672
],
6773
"parameters_usage": []

0 commit comments

Comments
 (0)