fix(coderd): rewrite template insights query for speed and fix intervals

mafredri · mafredri · commit 82a899b60b34 · 2023-08-24T11:01:58.000Z
diff --git a/coderd/database/queries.sql.go b/coderd/database/queries.sql.go
diff --git a/coderd/database/queries/insights.sql b/coderd/database/queries/insights.sql
@@ -25,32 +25,22 @@ ORDER BY user_id ASC;
 -- GetTemplateInsights has a granularity of 5 minutes where if a session/app was
 -- in use during a minute, we will add 5 minutes to the total usage for that
 -- session/app (per user).
-WITH ts AS (
-	SELECT
-		d::timestamptz AS from_,
-		(d::timestamptz + '5 minute'::interval) AS to_,
-		EXTRACT(epoch FROM '5 minute'::interval) AS seconds
-	FROM
-		-- Subtract 1 second from end_time to avoid including the next interval in the results.
-		generate_series(@start_time::timestamptz, (@end_time::timestamptz) - '1 second'::interval, '5 minute'::interval) d
-), agent_stats_by_interval_and_user AS (
+WITH agent_stats_by_interval_and_user AS (
 	SELECT
-		ts.from_,
-		ts.to_,
+		date_trunc('minute', was.created_at),
 		was.user_id,
 		array_agg(was.template_id) AS template_ids,
-		CASE WHEN SUM(was.session_count_vscode) > 0 THEN ts.seconds ELSE 0 END AS usage_vscode_seconds,
-		CASE WHEN SUM(was.session_count_jetbrains) > 0 THEN ts.seconds ELSE 0 END AS usage_jetbrains_seconds,
-		CASE WHEN SUM(was.session_count_reconnecting_pty) > 0 THEN ts.seconds ELSE 0 END AS usage_reconnecting_pty_seconds,
-		CASE WHEN SUM(was.session_count_ssh) > 0 THEN ts.seconds ELSE 0 END AS usage_ssh_seconds
-	FROM ts
-	JOIN workspace_agent_stats was ON (
-		was.created_at >= ts.from_
-		AND was.created_at < ts.to_
+		CASE WHEN SUM(was.session_count_vscode) > 0 THEN 60 ELSE 0 END AS usage_vscode_seconds,
+		CASE WHEN SUM(was.session_count_jetbrains) > 0 THEN 60 ELSE 0 END AS usage_jetbrains_seconds,
+		CASE WHEN SUM(was.session_count_reconnecting_pty) > 0 THEN 60 ELSE 0 END AS usage_reconnecting_pty_seconds,
+		CASE WHEN SUM(was.session_count_ssh) > 0 THEN 60 ELSE 0 END AS usage_ssh_seconds
+	FROM workspace_agent_stats was
+	WHERE
+		was.created_at >= @start_time::timestamptz
+		AND was.created_at < @end_time::timestamptz
 		AND was.connection_count > 0
 		AND CASE WHEN COALESCE(array_length(@template_ids::uuid[], 1), 0) > 0 THEN was.template_id = ANY(@template_ids::uuid[]) ELSE TRUE END
-	)
-	GROUP BY ts.from_, ts.to_, ts.seconds, was.user_id
+	GROUP BY date_trunc('minute', was.created_at), was.user_id
 ), template_ids AS (
 	SELECT array_agg(DISTINCT template_id) AS ids
 	FROM agent_stats_by_interval_and_user, unnest(template_ids) template_id
diff --git a/coderd/insights_test.go b/coderd/insights_test.go
@@ -760,13 +760,13 @@ func TestTemplateInsights_Golden(t *testing.T) {
 						sessionCountVSCode: 1,
 						sessionCountSSH:    1,
 					},
-					{ // 12 minutes of usage -> 15 minutes.
+					{ // 12 minutes of usage.
 						startedAt:       frozenWeekAgo.AddDate(0, 0, 1),
 						endedAt:         frozenWeekAgo.AddDate(0, 0, 1).Add(12 * time.Minute),
 						sessionCountSSH: 1,
 					},
-					{ // 2 minutes of usage -> 10 minutes because it crosses the 5 minute interval boundary.
-						startedAt:             frozenWeekAgo.AddDate(0, 0, 2).Add(4 * time.Minute),
+					{ // 1m30s of usage -> 2m rounded.
+						startedAt:             frozenWeekAgo.AddDate(0, 0, 2).Add(4*time.Minute + 30*time.Second),
 						endedAt:               frozenWeekAgo.AddDate(0, 0, 2).Add(6 * time.Minute),
 						sessionCountJetBrains: 1,
 					},
diff --git a/coderd/testdata/insights/multiple_users_and_workspaces_week_all_templates.json.golden b/coderd/testdata/insights/multiple_users_and_workspaces_week_all_templates.json.golden
@@ -31,7 +31,7 @@
         "display_name": "JetBrains",
         "slug": "jetbrains",
         "icon": "/icon/intellij.svg",
-        "seconds": 600
+        "seconds": 120
       },
       {
         "template_ids": [
@@ -55,7 +55,7 @@
         "display_name": "SSH",
         "slug": "ssh",
         "icon": "/icon/terminal.svg",
-        "seconds": 11700
+        "seconds": 11520
       },
       {
         "template_ids": [
diff --git a/coderd/testdata/insights/multiple_users_and_workspaces_week_deployment_wide.json.golden b/coderd/testdata/insights/multiple_users_and_workspaces_week_deployment_wide.json.golden
@@ -31,7 +31,7 @@
         "display_name": "JetBrains",
         "slug": "jetbrains",
         "icon": "/icon/intellij.svg",
-        "seconds": 600
+        "seconds": 120
       },
       {
         "template_ids": [
@@ -55,7 +55,7 @@
         "display_name": "SSH",
         "slug": "ssh",
         "icon": "/icon/terminal.svg",
-        "seconds": 11700
+        "seconds": 11520
       },
       {
         "template_ids": [
diff --git a/coderd/testdata/insights/multiple_users_and_workspaces_week_first_template.json.golden b/coderd/testdata/insights/multiple_users_and_workspaces_week_first_template.json.golden
@@ -25,7 +25,7 @@
         "display_name": "JetBrains",
         "slug": "jetbrains",
         "icon": "/icon/intellij.svg",
-        "seconds": 600
+        "seconds": 120
       },
       {
         "template_ids": [
@@ -45,7 +45,7 @@
         "display_name": "SSH",
         "slug": "ssh",
         "icon": "/icon/terminal.svg",
-        "seconds": 8100
+        "seconds": 7920
       },
       {
         "template_ids": [
diff --git a/coderd/testdata/insights/multiple_users_and_workspaces_week_other_timezone_(São_Paulo).json.golden b/coderd/testdata/insights/multiple_users_and_workspaces_week_other_timezone_(São_Paulo).json.golden
@@ -27,7 +27,7 @@
         "display_name": "JetBrains",
         "slug": "jetbrains",
         "icon": "/icon/intellij.svg",
-        "seconds": 600
+        "seconds": 120
       },
       {
         "template_ids": [
@@ -47,7 +47,7 @@
         "display_name": "SSH",
         "slug": "ssh",
         "icon": "/icon/terminal.svg",
-        "seconds": 4500
+        "seconds": 4320
       },
       {
         "template_ids": [
