@@ -297,64 +297,35 @@ GROUP BY template_id, display_name, slug_or_port;
297297-- time, if end time is a partial interval, it will be included in the results and
298298-- that interval will be shorter than a full one. If there is no data for a selected
299299-- interval/template, it will be included in the results with 0 active users.
300- WITH tsAS (
301- SELECT
302- d::timestamptz AS from_,
303- CASE
304- WHEN (d::timestamptz + (@interval_days::int || ' day' <= @end_time::timestamptz
305- THEN (d::timestamptz + (@interval_days::int || ' day'
306- ELSE @end_time::timestamptz
307- ENDAS to_
308- FROM
309- -- Subtract 1 microsecond from end_time to avoid including the next interval in the results.
310- generate_series(@start_time::timestamptz , (@end_time::timestamptz )- ' 1 microsecond' int || ' day' AS d
311- ), unflattened_usage_by_intervalAS (
312- -- We select data from both workspace agent stats and workspace app stats to
313- -- get a complete picture of usage. This matches how usage is calculated by
314- -- the combination of GetTemplateInsights and GetTemplateAppInsights. We use
315- -- a union all to avoid a costly distinct operation.
316- --
317- -- Note that one query must perform a left join so that all intervals are
318- -- present at least once.
319- SELECT
320- ts.* ,
321- was .template_id ,
322- was .user_id
323- FROM ts
324- LEFT JOIN workspace_agent_stats wasON (
325- was .created_at >= ts .from_
326- AND was .created_at < ts .to_
327- AND was .connection_count > 0
328- AND CASE WHEN COALESCE(array_length(@template_ids::uuid[],1 ),0 )> 0 THENwas .template_id = ANY(@template_ids::uuid[]) ELSE TRUE END
329- )
330- GROUP BY ts .from_ ,ts .to_ ,was .template_id ,was .user_id
331-
332- UNION ALL
333-
334- SELECT
335- ts.* ,
336- w .template_id ,
337- was .user_id
338- FROM ts
339- JOIN workspace_app_stats wasON (
340- (was .session_started_at >= ts .from_ AND was .session_started_at < ts .to_ )
341- OR (was .session_ended_at > ts .from_ AND was .session_ended_at < ts .to_ )
342- OR (was .session_started_at < ts .from_ AND was .session_ended_at >= ts .to_ )
343- )
344- JOIN workspaces wON (
345- w .id = was .workspace_id
346- AND CASE WHEN COALESCE(array_length(@template_ids::uuid[],1 ),0 )> 0 THENw .template_id = ANY(@template_ids::uuid[]) ELSE TRUE END
300+ WITH
301+ tsAS (
302+ SELECT
303+ d::timestamptz AS from_,
304+ CASE
305+ WHEN (d::timestamptz + (@interval_days::int || ' day' <= @end_time::timestamptz
306+ THEN (d::timestamptz + (@interval_days::int || ' day'
307+ ELSE @end_time::timestamptz
308+ ENDAS to_
309+ FROM
310+ -- Subtract 1 microsecond from end_time to avoid including the next interval in the results.
311+ generate_series(@start_time::timestamptz , (@end_time::timestamptz )- ' 1 microsecond' int || ' day' AS d
347312)
348- GROUP BY ts .from_ ,ts .to_ ,w .template_id ,was .user_id
349- )
350313
351314SELECT
352- from_AS start_time,
353- to_AS end_time,
354- array_remove(array_agg(DISTINCT template_id),NULL )::uuid[]AS template_ids,
355- COUNT (DISTINCT user_id)AS active_users
356- FROM unflattened_usage_by_interval
357- GROUP BY from_, to_;
315+ ts .from_ AS start_time,
316+ ts .to_ AS end_time,
317+ array_remove(array_agg(DISTINCTtus .template_id ),NULL )::uuid[]AS template_ids,
318+ COUNT (DISTINCTtus .user_id )AS active_users
319+ FROM
320+ ts
321+ LEFT JOIN
322+ template_usage_statsAS tus
323+ ON
324+ tus .start_time >= ts .from_
325+ AND tus .end_time <= ts .to_
326+ AND CASE WHEN COALESCE(array_length(@template_ids::uuid[],1 ),0 )> 0 THENtus .template_id = ANY(@template_ids::uuid[]) ELSE TRUE END
327+ GROUP BY
328+ ts .from_ ,ts .to_ ;
358329
359330-- name: GetTemplateUsageStats :many
360331SELECT