insights;

WITH

insightsAS (

template_id,

user_id,

LEAST(SUM(usage_mins),30)AS usage_mins,

LEAST(SUM(ssh_mins),30)AS ssh_mins,

LEAST(SUM(sftp_mins),30)AS sftp_mins,

LEAST(SUM(reconnecting_pty_mins),30)AS reconnecting_pty_mins,

LEAST(SUM(vscode_mins),30)AS vscode_mins,

LEAST(SUM(jetbrains_mins),30)AS jetbrains_mins

template_usage_stats

start_time>= @start_time::timestamptz

AND end_time<= @end_time::timestamptz

start_time, template_id, user_id

)

WITH agent_stats_by_interval_and_userAS (

date_trunc('minute',was.created_at)AS created_at_trunc,

was.template_id,

was.user_id,

CASE WHENSUM(was.session_count_vscode)>0 THEN60 ELSE0 ENDAS usage_vscode_seconds,

CASE WHENSUM(was.session_count_jetbrains)>0 THEN60 ELSE0 ENDAS usage_jetbrains_seconds,

CASE WHENSUM(was.session_count_reconnecting_pty)>0 THEN60 ELSE0 ENDAS usage_reconnecting_pty_seconds,

CASE WHENSUM(was.session_count_ssh)>0 THEN60 ELSE0 ENDAS usage_ssh_seconds

FROM workspace_agent_stats was

was.created_at>= @start_time::timestamptz

ANDwas.created_at< @end_time::timestamptz

ANDwas.connection_count>0

GROUP BY created_at_trunc,was.template_id,was.user_id

)

template_id,

COUNT(DISTINCT user_id)AS active_users,

(SUM(usage_mins)*60)::bigintAS usage_total_seconds,-- Includes app usage.

(SUM(ssh_mins)*60)::bigintAS usage_ssh_seconds,

(SUM(sftp_mins)*60)::bigintAS usage_sftp_seconds,

(SUM(reconnecting_pty_mins)*60)::bigintAS usage_reconnecting_pty_seconds,

(SUM(vscode_mins)*60)::bigintAS usage_vscode_seconds,

(SUM(jetbrains_mins)*60)::bigintAS usage_jetbrains_seconds

insights

COALESCE(COUNT(DISTINCT user_id))::bigintAS active_users,

COALESCE(SUM(usage_vscode_seconds),0)::bigintAS usage_vscode_seconds,

COALESCE(SUM(usage_jetbrains_seconds),0)::bigintAS usage_jetbrains_seconds,

COALESCE(SUM(usage_reconnecting_pty_seconds),0)::bigintAS usage_reconnecting_pty_seconds,

COALESCE(SUM(usage_ssh_seconds),0)::bigintAS usage_ssh_seconds

FROM agent_stats_by_interval_and_user

GROUP BY template_id;