Simplified thegenerate_series a bit via bettersqlc workaround
Query indentation was fixed (there was some tab spacing)
Changed usage granularity from 5 minutes to 1 minute, I feel 5 minutes may skew the numbers too much for short lived sessions

Notes:

Unfortunately I couldn't combineGetTemplateInsights andGetTemplateAppInsights into a single query because ofsqlc limitations, and I did not want to trade type-safety for performance

github-actionsbot assignedmafredri

Aug 16, 2023

mafredri changed the title~~feat(coderd): report template app usage insights~~feat(coderd): add template app usage to insights

Aug 16, 2023

Copy link

MemberAuthor

mafredri commentedAug 16, 2023

Demo:

mtojek reviewed

Aug 17, 2023

View reviewed changes

coderd/database/queries/insights.sql OutdatedShow resolvedHide resolved

coderd/database/queries/insights.sqlShow resolvedHide resolved

coderd/insights.go

		appUsage, err = tx.GetTemplateAppInsights(ctx, database.GetTemplateAppInsightsParams{
		StartTime: startTime,
		EndTime: endTime,
		TemplateIDs: templateIDs,

Copy link

Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others.Learn more.

Hint: while you are writing unit tests, please make sure that cover paths withTemplateIDs and without them (ALL).

Copy link

MemberAuthor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others.Learn more.

I will create an improved test-suite as a follow-up to this PR which will perform tests for multiple templates as well.

mafredri added5 commits

August 18, 2023 14:38

feat(coderd): report template app usage insights

cece437

Fixes#8658

use 1 minute granularity instead of 5

0db240a

fix coalesce and cleanup with array remove

91dce4f

Revert "use 1 minute granularity instead of 5"

98a835c

This reverts commit77e8382.

fix query joins, add basic test for app usage

cacebb9

mafredri force-pushed themafredri/feat-add-app-usage-to-template-insights-2 branch from7707313 tocacebb9Compare

August 18, 2023 14:38

mafredri added5 commits

August 18, 2023 14:40

nolint

6fd3d93

test produce another active user via app usage

3bc91b5

implement fakedb

ae20a46

strict

6b8fe1a

fix gen

1904fbe

Copy link

MemberAuthor

mafredri commentedAug 18, 2023

I've added basic tests and will improve upon them in a follow up PR. I've also manually confirmed that the query changes produces the same data ondev.coder.com when disregarding the new data.

A funny side-effect of using the 5 minute windows is that when testing, I:

Opened code-server
Refreshed insights, there was immediately 5 minutes code-server usage
Waited 1 minute
Refreshed insights, there was 10 minutes code-server usage because the clock went from :39 to :40

More users will skew nr. 4 even more, so I'm convinced we need to reduce the window whilst maintaining or improving query performance, and perhaps not use one at all for template apps since it's not strictly required.