WITH

-- This CTE is used to truncate agent usage into minute buckets, then

-- flatten the users agent usage within the template so that usage in

-- multiple workspaces under one template is only counted once for

-- every minute (per user).

insights AS (

SELECT

template_id,

user_id,

COUNT(DISTINCT CASE WHEN session_count_ssh > 0 THEN date_trunc('minute', created_at) ELSE NULL END) AS ssh_mins,

-- TODO(mafredri): Enable when we have the column.

-- COUNT(DISTINCT CASE WHEN session_count_sftp > 0 THEN date_trunc('minute', created_at) ELSE NULL END) AS sftp_mins,

COUNT(DISTINCT CASE WHEN session_count_reconnecting_pty > 0 THEN date_trunc('minute', created_at) ELSE NULL END) AS reconnecting_pty_mins,

COUNT(DISTINCT CASE WHEN session_count_vscode > 0 THEN date_trunc('minute', created_at) ELSE NULL END) AS vscode_mins,

COUNT(DISTINCT CASE WHEN session_count_jetbrains > 0 THEN date_trunc('minute', created_at) ELSE NULL END) AS jetbrains_mins,

-- NOTE(mafredri): The agent stats are currently very unreliable, and

-- sometimes the connections are missing, even during active sessions.

-- Since we can't fully rely on this, we check for "any connection

-- within this bucket". A better solution here would be preferable.

MAX(connection_count) > 0 AS has_connection

FROM

workspace_agent_stats

WHERE

created_at >= @start_time::timestamptz

AND created_at < @end_time::timestamptz

-- Inclusion criteria to filter out empty results.

AND (

session_count_ssh > 0

-- TODO(mafredri): Enable when we have the column.

-- OR session_count_sftp > 0

OR session_count_reconnecting_pty > 0

OR session_count_vscode > 0

OR session_count_jetbrains > 0

)

GROUP BY

template_id, user_id

)

COUNT(DISTINCT user_id)::bigint AS active_users,

(SUM(vscode_mins) * 60)::bigint AS usage_vscode_seconds,

(SUM(jetbrains_mins) * 60)::bigint AS usage_jetbrains_seconds,

(SUM(reconnecting_pty_mins) * 60)::bigint AS usage_reconnecting_pty_seconds,

(SUM(ssh_mins) * 60)::bigint AS usage_ssh_seconds

insights

has_connection

template_id;