Nov 4, 2025 · Nov 4, 2025
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
 -- GetTemplateAppInsightsByTemplate is used for Prometheus metrics. Keep
 -- in sync with GetTemplateAppInsights and UpsertTemplateUsageStats.
 WITH
 filtered_stats AS (
 SELECT
 was.workspace_id,
 was.user_id,
 was.agent_id,
 was.access_method,
 was.slug_or_port,
 was.session_started_at,
 was.session_ended_at
 FROM
 workspace_app_stats AS was
 WHERE
 was.session_ended_at >= @start_time::timestamptz
 AND was.session_started_at <  @end_time::timestamptz
 ),
 -- This CTE is used to explode app usage into minute buckets, then
 -- flatten the users app usage within the template so that usage in
 -- multiple workspaces under one template is only counted once for
 -- every minute.
 app_insights AS (
 SELECT
 w.template_id,
 was.user_id,
 fs.user_id,
 -- Both app stats and agent stats track web terminal usage, but
 -- by different means. The app stats value should be more
 -- accurate so we don't want to discard it just yet.
 CASE
 WHENwas.access_method = 'terminal'
 WHENfs.access_method = 'terminal'
 THEN '[terminal]' -- Unique name, app names can't contain brackets.
 ELSEwas.slug_or_port
 ELSEfs.slug_or_port
 END::text AS app_name,
 COALESCE(wa.display_name, '') AS display_name,
 (wa.slug IS NOT NULL)::boolean AS is_app,
 COUNT(DISTINCT s.minute_bucket) AS app_minutes
 FROM
 workspace_app_stats ASwas
 filtered_stats ASfs
 JOIN
 workspaces AS w
 ON
 w.id =was.workspace_id
 w.id =fs.workspace_id
 -- We do a left join here because we want to include user IDs that have used
 -- e.g. ports when counting active users.
 LEFT JOIN
 workspace_apps wa
 ON
 wa.agent_id =was.agent_id
 AND wa.slug =was.slug_or_port
 wa.agent_id =fs.agent_id
 AND wa.slug =fs.slug_or_port
 -- Generate a series of minute buckets for each session for computing the
 -- mintes/bucket.
 CROSS JOIN
 generate_series(
 date_trunc('minute',was.session_started_at),
 date_trunc('minute',fs.session_started_at),
 -- Subtract 1 μs to avoid creating an extra series.
 date_trunc('minute',was.session_ended_at - '1 microsecond'::interval),
 date_trunc('minute',fs.session_ended_at - '1 microsecond'::interval),
 '1 minute'::interval
 ) AS s(minute_bucket)
 WHERE
 s.minute_bucket >= @start_time::timestamptz
 AND s.minute_bucket < @end_time::timestamptz
 GROUP BY
 w.template_id,was.user_id,was.access_method,was.slug_or_port, wa.display_name, wa.slug
 w.template_id,fs.user_id,fs.access_method,fs.slug_or_port, wa.display_name, wa.slug
 )

 SELECT
Original file line number	Diff line number	Diff line change
Expand Up		@@ -350,52 +350,67 @@ GROUP BY
		-- GetTemplateAppInsightsByTemplate is used for Prometheus metrics. Keep
		-- in sync with GetTemplateAppInsights and UpsertTemplateUsageStats.
		WITH
		filtered_stats AS (
		SELECT
		was.workspace_id,
		was.user_id,
		was.agent_id,
		was.access_method,
		was.slug_or_port,
		was.session_started_at,
		was.session_ended_at
		FROM
		workspace_app_stats AS was
		WHERE
		was.session_ended_at >= @start_time::timestamptz
		AND was.session_started_at < @end_time::timestamptz
		),
		-- This CTE is used to explode app usage into minute buckets, then
		-- flatten the users app usage within the template so that usage in
		-- multiple workspaces under one template is only counted once for
		-- every minute.
		app_insights AS (
		SELECT
		w.template_id,
		was.user_id,
		fs.user_id,
		-- Both app stats and agent stats track web terminal usage, but
		-- by different means. The app stats value should be more
		-- accurate so we don't want to discard it just yet.
		CASE
		WHENwas.access_method = 'terminal'
		WHENfs.access_method = 'terminal'
		THEN '[terminal]' -- Unique name, app names can't contain brackets.
		ELSEwas.slug_or_port
		ELSEfs.slug_or_port
		END::text AS app_name,
		COALESCE(wa.display_name, '') AS display_name,
		(wa.slug IS NOT NULL)::boolean AS is_app,
		COUNT(DISTINCT s.minute_bucket) AS app_minutes
		FROM
		workspace_app_stats ASwas
		filtered_stats ASfs
		JOIN
		workspaces AS w
		ON
		w.id =was.workspace_id
		w.id =fs.workspace_id
		-- We do a left join here because we want to include user IDs that have used
		-- e.g. ports when counting active users.
		LEFT JOIN
		workspace_apps wa
		ON
		wa.agent_id =was.agent_id
		AND wa.slug =was.slug_or_port
		wa.agent_id =fs.agent_id
		AND wa.slug =fs.slug_or_port
		-- Generate a series of minute buckets for each session for computing the
		-- mintes/bucket.
		CROSS JOIN
		generate_series(
		date_trunc('minute',was.session_started_at),
		date_trunc('minute',fs.session_started_at),
		-- Subtract 1 μs to avoid creating an extra series.
		date_trunc('minute',was.session_ended_at - '1 microsecond'::interval),
		date_trunc('minute',fs.session_ended_at - '1 microsecond'::interval),
		'1 minute'::interval
		) AS s(minute_bucket)
		WHERE
		s.minute_bucket >= @start_time::timestamptz
		AND s.minute_bucket < @end_time::timestamptz
		GROUP BY
		w.template_id,was.user_id,was.access_method,was.slug_or_port, wa.display_name, wa.slug
		w.template_id,fs.user_id,fs.access_method,fs.slug_or_port, wa.display_name, wa.slug
		)

		SELECT
Expand Down