Queries · q200 · Support Operations Manager · week 1, night 2
“At the real operational unit (one merchant's one agent group), where is ticket load heaviest, and does staffing (agent count) look right for that load?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
Week 1; shared with Daniel Achterberg, VP Customer Experience
Review how the helpdesk runs: queues and agent groups, use of macros and knowledge articles, SLA targets and breaches, and what makes tickets slow.
The manager, at the start of week 1
Subject: Helpdesk operating review — what I need this week
Hi Ben,
Daniel and I need a baseline read on how the helpdesk actually runs, so we can walk into our first leadership review with a clear picture instead of impressions. This feeds our decisions on how we staff/structure agent groups, where we invest in macros and knowledge base content, and whether our current SLA targets are realistic. Given you only get a handful of reports a night, here's what matters most, roughly in priority order:
No need to go deep on all five — pick what fits in the nightly slots and flag anything surprising even if I didn't ask directly. Keep it at a level Daniel and I can act on quickly; we'll dig deeper live once we see the numbers.
Thanks, Mia
Subject: A few things I'd like your review to cover
Hi Ben,
As you dig into the helpdesk review this week, a few questions from my side:
If you find anything that points to a specific merchant or category driving a disproportionate share of the pain, flag it even if it's outside these three.
Thanks, Daniel
The analyst · week 1, night 2, request 2 of 3
Night 1 surfaced a methodology problem before any business insight: Grace's agent-group report rolled up every tenant's same-named group (e.g. all 120 merchants' 'Customer Care') into one row, while Omar's SLA-breach-by-group report kept each tenant's group separate (171 rows). The two reports use different grains under the same label, so they can't be compared, and neither tells us whether load/staffing is balanced within a real operational unit (a tenant's own group). Also, the company briefing says a handful of merchants drive most volume — I haven't checked that at all yet, and Daniel explicitly asked to flag any single merchant driving disproportionate pain. So tonight I'm fixing the grain: first, ticket volume and SLA performance by merchant to find concentration and any outlier tenant; second, the highest-volume tenant+group combinations (the real staffing units) to redo the staffing-balance read properly; third, a day-of-week/time-of-day cut of SLA breaches since Daniel specifically asked about time patterns and I have a slot free for it. Headline SLA numbers from report 3 (37.7% first-response breach, 20.3% resolution breach, urgent tickets breaching resolution over half the time) are worth noting to Mia/Daniel now even before drill-down.
The engineer · 1 dry run on the empty schema, then the submission
WITH ticket_agg AS ( SELECT tenant_id, agent_group_id, COUNT(*) AS ticket_count, COUNT(*) FILTER (WHERE assignee_agent_id IS NULL) AS unassigned_count, AVG(EXTRACT(EPOCH FROM (first_response_at - created_at))/60.0) FILTER (WHERE first_response_at IS NOT NULL) AS avg_first_response_minutes, AVG(EXTRACT(EPOCH FROM (resolved_at - created_at))/60.0) FILTER (WHERE resolved_at IS NOT NULL) AS avg_resolution_minutes FROM tickets WHERE agent_group_id IS NOT NULL AND created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01' GROUP BY tenant_id, agent_group_id ), breach_agg AS ( SELECT t.tenant_id, t.agent_group_id, COUNT(DISTINCT sb.ticket_id) AS resolution_breach_count FROM tickets t JOIN sla_breaches sb ON sb.ticket_id = t.id AND sb.tenant_id = t.tenant_id WHERE t.agent_group_id IS NOT NULL AND t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01' AND sb.breach_kind = 'resolution' GROUP BY t.tenant_id, t.agent_group_id ), agent_counts AS ( SELECT tenant_id, agent_group_id, COUNT(DISTINCT support_agent_id) AS agent_count FROM agent_group_members GROUP BY tenant_id, agent_group_id ) SELECT ten.name AS tenant_name, ag.name AS agent_group_name, ta.ticket_count, COALESCE(ac.agent_count,0) AS agent_count, ROUND(ta.ticket_count::numeric / NULLIF(ac.agent_count,0), 1) AS tickets_per_agent, ROUND(100.0 * ta.unassigned_count / NULLIF(ta.ticket_count,0), 1) AS pct_unassigned, ROUND(ta.avg_first_response_minutes::numeric, 1) AS avg_first_response_minutes, ROUND(ta.avg_resolution_minutes::numeric, 1) AS avg_resolution_minutes, ROUND(100.0 * COALESCE(ba.resolution_breach_count,0) / NULLIF(ta.ticket_count,0), 1) AS resolution_breach_rate FROM ticket_agg ta JOIN tenants ten ON ten.id = ta.tenant_id JOIN agent_groups ag ON ag.id = ta.agent_group_id LEFT JOIN agent_counts ac ON ac.tenant_id = ta.tenant_id AND ac.agent_group_id = ta.agent_group_id LEFT JOIN breach_agg ba ON ba.tenant_id = ta.tenant_id AND ba.agent_group_id = ta.agent_group_id ORDER BY ta.ticket_count DESC, ten.name, ag.name LIMIT 20
“Query compiles cleanly with the expected 9 columns. Submitting.”
“Submitted. Summary of what the report delivers:
- Grain: one row per (tenant, agent_group), top 20 by ticket_count in 2025.
- Measures: ticket_count, agent_count (distinct agents via agent_group_members), tickets_per_agent, pct_unassigned, avg_first_response_minutes, avg_resolution_minutes, resolution_breach_rate — all from tickets, agent_groups, agent_group_members, support_agents, tenants, and sla_breaches, windowed on tickets.created_at in 2025 with agent_group_id not null, as requested.
- Caveat flagged to the analyst: sla_breaches.breach_kind isn't an enumerated column in the catalog, so I assumed the value 'resolution' identifies resolution-target breaches — worth a quick sanity check on their end. I also noted that a separate total-combination-count figure can be requested if needed, since it doesn't fit naturally as a column in a top-20 row-grain report.”
As Ben Carter saw it, from the small dataset the agents worked on
tenant_name | agent_group_name | ticket_count | agent_count | tickets_per_agent | pct_unassigned | avg_first_response_minutes | avg_resolution_minutes | resolution_breach_rate ------------------------------+-------------------+--------------+-------------+-------------------+----------------+----------------------------+------------------------+----------------------- Cragsley Outfitters | Customer Care | 149 | 3 | 49.7 | 0.7 | 1010.0 | 2034.4 | 26.8 Cragsley Outfitters | Returns & Refunds | 144 | 3 | 48.0 | 0.7 | 1458.0 | 2629.5 | 32.6 Cragsley Outfitters | Delivery Issues | 107 | 2 | 53.5 | 0.0 | 1326.7 | 2413.2 | 29.9 Marrowby & Vane | Customer Care | 106 | 2 | 53.0 | 2.8 | 2463.5 | 4264.2 | 35.8 Marrowby & Vane | Returns & Refunds | 82 | 2 | 41.0 | 3.7 | 3733.1 | 6162.4 | 48.8 Studio Vrij | Customer Care | 81 | 2 | 40.5 | 0.0 | 4162.9 | 7811.4 | 40.7 Brennquell Kaffee | Customer Care | 74 | 2 | 37.0 | 0.0 | 374.4 | 1082.6 | 16.2 Petalcourt Beauty | Customer Care | 50 | 2 | 25.0 | 0.0 | 1693.3 | 2261.6 | 26.0 Grind Collective | Returns & Refunds | 47 | 1 | 47.0 | 0.0 | 1785.7 | 3234.1 | 31.9 Grind Collective | Customer Care | 45 | 1 | 45.0 | 0.0 | 2727.1 | 4935.8 | 40.0 Studio Vrij | Delivery Issues | 44 | 1 | 44.0 | 0.0 | 2989.4 | 8746.0 | 36.4 Marrowby & Vane | Delivery Issues | 43 | 1 | 43.0 | 0.0 | 2070.5 | 5152.5 | 30.2 Studio Vrij | Returns & Refunds | 41 | 1 | 41.0 | 0.0 | 3212.3 | 5592.4 | 36.6 Petalcourt Beauty | Returns & Refunds | 38 | 1 | 38.0 | 0.0 | 1630.7 | 2138.2 | 34.2 Kibblewick | Customer Care | 36 | 2 | 18.0 | 0.0 | 1180.5 | 2661.5 | 27.8 Brennquell Kaffee | Delivery Issues | 34 | 1 | 34.0 | 0.0 | 353.2 | 874.6 | 5.9 Ballycorran Industrial Supply | Customer Service | 31 | 1 | 31.0 | 0.0 | 534.6 | 1517.9 | 6.5 Petalcourt Beauty | Delivery Issues | 31 | 1 | 31.0 | 0.0 | 2496.0 | 4943.1 | 41.9 Grind Collective | Delivery Issues | 30 | 1 | 30.0 | 0.0 | 1943.3 | 3161.7 | 26.7 Brennquell Kaffee | Returns & Refunds | 29 | 1 | 29.0 | 3.4 | 214.3 | 721.7 | 6.9 (20 rows)
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
It builds on:
Changed only where the benchmark needs the same result on every system
WITH ticket_agg AS ( SELECT tenant_id, agent_group_id, COUNT(*) AS ticket_count, COUNT(*) FILTER (WHERE assignee_agent_id IS NULL) AS unassigned_count, AVG(EXTRACT(EPOCH FROM (first_response_at - created_at))/60.0) FILTER (WHERE first_response_at IS NOT NULL) AS avg_first_response_minutes, AVG(EXTRACT(EPOCH FROM (resolved_at - created_at))/60.0) FILTER (WHERE resolved_at IS NOT NULL) AS avg_resolution_minutes AVG({{seconds_between(created_at, first_response_at)}}/60.0) FILTER (WHERE first_response_at IS NOT NULL) AS avg_first_response_minutes, AVG({{seconds_between(created_at, resolved_at)}}/60.0) FILTER (WHERE resolved_at IS NOT NULL) AS avg_resolution_minutes FROM tickets WHERE agent_group_id IS NOT NULL AND created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01' GROUP BY tenant_id, agent_group_id),breach_agg AS ( SELECT t.tenant_id, t.agent_group_id, SELECT t.tenant_id AS tenant_id, t.agent_group_id AS agent_group_id, COUNT(DISTINCT sb.ticket_id) AS resolution_breach_count FROM tickets t JOIN sla_breaches sb ON sb.ticket_id = t.id AND sb.tenant_id = t.tenant_id WHERE t.agent_group_id IS NOT NULL AND t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01' AND sb.breach_kind = 'resolution' GROUP BY t.tenant_id, t.agent_group_id),agent_counts AS ( SELECT tenant_id, agent_group_id, COUNT(DISTINCT support_agent_id) AS agent_count FROM agent_group_members GROUP BY tenant_id, agent_group_id)SELECT ten.name AS tenant_name, ag.name AS agent_group_name, ta.ticket_count, ta.ticket_count AS ticket_count, COALESCE(ac.agent_count,0) AS agent_count, ROUND(ta.ticket_count::numeric / NULLIF(ac.agent_count,0), 1) AS tickets_per_agent, ROUND(100.0 * ta.unassigned_count / NULLIF(ta.ticket_count,0), 1) AS pct_unassigned, ROUND(ta.avg_first_response_minutes::numeric, 1) AS avg_first_response_minutes, ROUND(ta.avg_resolution_minutes::numeric, 1) AS avg_resolution_minutes, ROUND(100.0 * COALESCE(ba.resolution_breach_count,0) / NULLIF(ta.ticket_count,0), 1) AS resolution_breach_rate CAST(ta.ticket_count AS numeric(18,6)) / NULLIF(ac.agent_count,0) AS tickets_per_agent, 100.0 * ta.unassigned_count / NULLIF(ta.ticket_count,0) AS pct_unassigned, ta.avg_first_response_minutes AS avg_first_response_minutes, ta.avg_resolution_minutes AS avg_resolution_minutes, 100.0 * COALESCE(ba.resolution_breach_count,0) / NULLIF(ta.ticket_count,0) AS resolution_breach_rateFROM ticket_agg taJOIN tenants ten ON ten.id = ta.tenant_idJOIN agent_groups ag ON ag.id = ta.agent_group_idLEFT JOIN agent_counts ac ON ac.tenant_id = ta.tenant_id AND ac.agent_group_id = ta.agent_group_idLEFT JOIN breach_agg ba ON ba.tenant_id = ta.tenant_id AND ba.agent_group_id = ta.agent_group_idORDER BY ta.ticket_count DESC, ten.name, ag.nameLIMIT 20ORDER BY ta.ticket_count DESC NULLS FIRST, ten.name NULLS LAST, ag.name NULLS LAST, ta.tenant_id NULLS LAST, ta.agent_group_id NULLS LAST{{fetch_first(20)}}
WITH ticket_agg AS ( SELECT tenant_id, agent_group_id, COUNT(*) AS ticket_count, COUNT(*) FILTER (WHERE assignee_agent_id IS NULL) AS unassigned_count, AVG(CAST(EXTRACT(EPOCH FROM (first_response_at - created_at)) AS NUMERIC(18,6))/60.0) FILTER (WHERE first_response_at IS NOT NULL) AS avg_first_response_minutes, AVG(CAST(EXTRACT(EPOCH FROM (resolved_at - created_at)) AS NUMERIC(18,6))/60.0) FILTER (WHERE resolved_at IS NOT NULL) AS avg_resolution_minutes FROM tickets WHERE agent_group_id IS NOT NULL AND created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01' GROUP BY tenant_id, agent_group_id ), breach_agg AS ( SELECT t.tenant_id AS tenant_id, t.agent_group_id AS agent_group_id, COUNT(DISTINCT sb.ticket_id) AS resolution_breach_count FROM tickets t JOIN sla_breaches sb ON sb.ticket_id = t.id AND sb.tenant_id = t.tenant_id WHERE t.agent_group_id IS NOT NULL AND t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01' AND sb.breach_kind = 'resolution' GROUP BY t.tenant_id, t.agent_group_id ), agent_counts AS ( SELECT tenant_id, agent_group_id, COUNT(DISTINCT support_agent_id) AS agent_count FROM agent_group_members GROUP BY tenant_id, agent_group_id ) SELECT ten.name AS tenant_name, ag.name AS agent_group_name, ta.ticket_count AS ticket_count, COALESCE(ac.agent_count,0) AS agent_count, CAST(ta.ticket_count AS numeric(18,6)) / NULLIF(ac.agent_count,0) AS tickets_per_agent, 100.0 * ta.unassigned_count / NULLIF(ta.ticket_count,0) AS pct_unassigned, ta.avg_first_response_minutes AS avg_first_response_minutes, ta.avg_resolution_minutes AS avg_resolution_minutes, 100.0 * COALESCE(ba.resolution_breach_count,0) / NULLIF(ta.ticket_count,0) AS resolution_breach_rate FROM ticket_agg ta JOIN tenants ten ON ten.id = ta.tenant_id JOIN agent_groups ag ON ag.id = ta.agent_group_id LEFT JOIN agent_counts ac ON ac.tenant_id = ta.tenant_id AND ac.agent_group_id = ta.agent_group_id LEFT JOIN breach_agg ba ON ba.tenant_id = ta.tenant_id AND ba.agent_group_id = ta.agent_group_id ORDER BY ta.ticket_count DESC NULLS FIRST, ten.name NULLS LAST, ag.name NULLS LAST, ta.tenant_id NULLS LAST, ta.agent_group_id NULLS LAST FETCH FIRST 20 ROWS ONLY
WITH ticket_agg AS ( SELECT tenant_id, agent_group_id, COUNT(*) AS ticket_count, COUNT(*) FILTER (WHERE assignee_agent_id IS NULL) AS unassigned_count, AVG(CAST(EXTRACT(EPOCH FROM (first_response_at - created_at)) AS NUMERIC(18,6))/60.0) FILTER (WHERE first_response_at IS NOT NULL) AS avg_first_response_minutes, AVG(CAST(EXTRACT(EPOCH FROM (resolved_at - created_at)) AS NUMERIC(18,6))/60.0) FILTER (WHERE resolved_at IS NOT NULL) AS avg_resolution_minutes FROM tickets WHERE agent_group_id IS NOT NULL AND created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01' GROUP BY tenant_id, agent_group_id ), breach_agg AS ( SELECT t.tenant_id AS tenant_id, t.agent_group_id AS agent_group_id, COUNT(DISTINCT sb.ticket_id) AS resolution_breach_count FROM tickets t JOIN sla_breaches sb ON sb.ticket_id = t.id AND sb.tenant_id = t.tenant_id WHERE t.agent_group_id IS NOT NULL AND t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01' AND sb.breach_kind = 'resolution' GROUP BY t.tenant_id, t.agent_group_id ), agent_counts AS ( SELECT tenant_id, agent_group_id, COUNT(DISTINCT support_agent_id) AS agent_count FROM agent_group_members GROUP BY tenant_id, agent_group_id ) SELECT ten.name AS tenant_name, ag.name AS agent_group_name, ta.ticket_count AS ticket_count, COALESCE(ac.agent_count,0) AS agent_count, CAST(ta.ticket_count AS numeric(18,6)) / NULLIF(ac.agent_count,0) AS tickets_per_agent, 100.0 * ta.unassigned_count / NULLIF(ta.ticket_count,0) AS pct_unassigned, ta.avg_first_response_minutes AS avg_first_response_minutes, ta.avg_resolution_minutes AS avg_resolution_minutes, 100.0 * COALESCE(ba.resolution_breach_count,0) / NULLIF(ta.ticket_count,0) AS resolution_breach_rate FROM ticket_agg ta JOIN tenants ten ON ten.id = ta.tenant_id JOIN agent_groups ag ON ag.id = ta.agent_group_id LEFT JOIN agent_counts ac ON ac.tenant_id = ta.tenant_id AND ac.agent_group_id = ta.agent_group_id LEFT JOIN breach_agg ba ON ba.tenant_id = ta.tenant_id AND ba.agent_group_id = ta.agent_group_id ORDER BY ta.ticket_count DESC NULLS FIRST, ten.name NULLS LAST, ag.name NULLS LAST, ta.tenant_id NULLS LAST, ta.agent_group_id NULLS LAST LIMIT 20
WITH ticket_agg AS ( SELECT tenant_id, agent_group_id, COUNT(*) AS ticket_count, COUNT(*) FILTER (WHERE assignee_agent_id IS NULL) AS unassigned_count, AVG(CAST(divideDecimal(toDecimal128(dateDiff('microsecond', created_at, first_response_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6)))/60.0) FILTER (WHERE first_response_at IS NOT NULL) AS avg_first_response_minutes, AVG(CAST(divideDecimal(toDecimal128(dateDiff('microsecond', created_at, resolved_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6)))/60.0) FILTER (WHERE resolved_at IS NOT NULL) AS avg_resolution_minutes FROM tickets WHERE agent_group_id IS NOT NULL AND created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01' GROUP BY tenant_id, agent_group_id ), breach_agg AS ( SELECT t.tenant_id AS tenant_id, t.agent_group_id AS agent_group_id, COUNT(DISTINCT sb.ticket_id) AS resolution_breach_count FROM tickets t JOIN sla_breaches sb ON sb.ticket_id = t.id AND sb.tenant_id = t.tenant_id WHERE t.agent_group_id IS NOT NULL AND t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01' AND sb.breach_kind = 'resolution' GROUP BY t.tenant_id, t.agent_group_id ), agent_counts AS ( SELECT tenant_id, agent_group_id, COUNT(DISTINCT support_agent_id) AS agent_count FROM agent_group_members GROUP BY tenant_id, agent_group_id ) SELECT ten.name AS tenant_name, ag.name AS agent_group_name, ta.ticket_count AS ticket_count, COALESCE(ac.agent_count,0) AS agent_count, CAST(ta.ticket_count AS numeric(18,6)) / NULLIF(ac.agent_count,0) AS tickets_per_agent, 100.0 * ta.unassigned_count / NULLIF(ta.ticket_count,0) AS pct_unassigned, ta.avg_first_response_minutes AS avg_first_response_minutes, ta.avg_resolution_minutes AS avg_resolution_minutes, 100.0 * COALESCE(ba.resolution_breach_count,0) / NULLIF(ta.ticket_count,0) AS resolution_breach_rate FROM ticket_agg ta JOIN tenants ten ON ten.id = ta.tenant_id JOIN agent_groups ag ON ag.id = ta.agent_group_id LEFT JOIN agent_counts ac ON ac.tenant_id = ta.tenant_id AND ac.agent_group_id = ta.agent_group_id LEFT JOIN breach_agg ba ON ba.tenant_id = ta.tenant_id AND ba.agent_group_id = ta.agent_group_id ORDER BY ta.ticket_count DESC NULLS FIRST, ten.name NULLS LAST, ag.name NULLS LAST, ta.tenant_id NULLS LAST, ta.agent_group_id NULLS LAST FETCH FIRST 20 ROWS ONLY
The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted
Report request from Ben Carter (analyst for the Support Operations Manager): Title: Highest-volume merchant + agent-group units Question: At the real operational unit (one merchant's one agent group), where is ticket load heaviest, and does staffing (agent count) look right for that load? Grain: one row per (tenant, agent_group) pair Columns: - tenant_name - agent_group_name - ticket_count (created 2025) - agent_count (support_agents in that tenant's group via agent_group_members) - tickets_per_agent - pct_unassigned - avg_first_response_minutes - avg_resolution_minutes - resolution_breach_rate Details: Window: tickets.created_at between 2025-01-01 and 2025-12-31, tickets.agent_group_id not null. Source tables: tickets, agent_groups, agent_group_members, support_agents, tenants, sla_breaches. Group by tenant_id + agent_group_id (do not roll up by group name across tenants). Order by ticket_count descending, show the top 20 rows, and note how many (tenant, agent_group) combinations exist in total in 2025. Round tickets_per_agent to 1 decimal, rates to 1 decimal percent. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runWITH ticket_agg AS ( SELECT tenant_id, agent_group_id, COUNT(*) AS ticket_count, COUNT(*) FILTER (WHERE assignee_agent_id IS NULL) AS unassigned_count, AVG(EXTRACT(EPOCH FROM (first_response_at - created_at))/60.0) FILTER (WHERE first_response_at IS NOT NULL) AS avg_first_response_minutes, AVG(EXTRACT(EPOCH FROM (resolved_at - created_at))/60.0) FILTER (WHERE resolved_at IS NOT NULL) AS avg_resolution_minutes FROM tickets WHERE agent_group_id IS NOT NULL AND created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01' GROUP BY tenant_id, agent_group_id ), breach_agg AS ( SELECT t.tenant_id, t.agent_group_id, COUNT(DISTINCT sb.ticket_id) AS resolution_breach_count FROM tickets t JOIN sla_breaches sb ON sb.ticket_id = t.id AND sb.tenant_id = t.tenant_id WHERE t.agent_group_id IS NOT NULL AND t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01' AND sb.breach_kind = 'resolution' GROUP BY t.tenant_id, t.agent_group_id ), agent_counts AS ( SELECT tenant_id, agent_group_id, COUNT(DISTINCT support_agent_id) AS agent_count FROM agent_group_members GROUP BY tenant_id, agent_group_id ) SELECT ten.name AS tenant_name, ag.name AS agent_group_name, ta.ticket_count, COALESCE(ac.agent_count,0) AS agent_count, ROUND(ta.ticket_count::numeric / NULLIF(ac.agent_count,0), 1) AS tickets_per_agent, ROUND(100.0 * ta.unassigned_count / NULLIF(ta.ticket_count,0), 1) AS pct_unassigned, ROUND(ta.avg_first_response_minutes::numeric, 1) AS avg_first_response_minutes, ROUND(ta.avg_resolution_minutes::numeric, 1) AS avg_resolution_minutes, ROUND(100.0 * COALESCE(ba.resolution_breach_count,0) / NULLIF(ta.ticket_count,0), 1) AS resolution_breach_rate FROM ticket_agg ta JOIN tenants ten ON ten.id = ta.tenant_id JOIN agent_groups ag ON ag.id = ta.agent_group_id LEFT JOIN agent_counts ac ON ac.tenant_id = ta.tenant_id AND ac.agent_group_id = ta.agent_group_id LEFT JOIN breach_agg ba ON ba.tenant_id = ta.tenant_id AND ba.agent_group_id = ta.agent_group_id ORDER BY ta.ticket_count DESC, ten.name, ag.name LIMIT 20
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: tenant_name, agent_group_name, ticket_count, agent_count, tickets_per_agent, pct_unassigned, avg_first_response_minutes, avg_resolution_minutes, resolution_breach_rate
submitWITH ticket_agg AS ( SELECT tenant_id, agent_group_id, COUNT(*) AS ticket_count, COUNT(*) FILTER (WHERE assignee_agent_id IS NULL) AS unassigned_count, AVG(EXTRACT(EPOCH FROM (first_response_at - created_at))/60.0) FILTER (WHERE first_response_at IS NOT NULL) AS avg_first_response_minutes, AVG(EXTRACT(EPOCH FROM (resolved_at - created_at))/60.0) FILTER (WHERE resolved_at IS NOT NULL) AS avg_resolution_minutes FROM tickets WHERE agent_group_id IS NOT NULL AND created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01' GROUP BY tenant_id, agent_group_id ), breach_agg AS ( SELECT t.tenant_id, t.agent_group_id, COUNT(DISTINCT sb.ticket_id) AS resolution_breach_count FROM tickets t JOIN sla_breaches sb ON sb.ticket_id = t.id AND sb.tenant_id = t.tenant_id WHERE t.agent_group_id IS NOT NULL AND t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01' AND sb.breach_kind = 'resolution' GROUP BY t.tenant_id, t.agent_group_id ), agent_counts AS ( SELECT tenant_id, agent_group_id, COUNT(DISTINCT support_agent_id) AS agent_count FROM agent_group_members GROUP BY tenant_id, agent_group_id ) SELECT ten.name AS tenant_name, ag.name AS agent_group_name, ta.ticket_count, COALESCE(ac.agent_count,0) AS agent_count, ROUND(ta.ticket_count::numeric / NULLIF(ac.agent_count,0), 1) AS tickets_per_agent, ROUND(100.0 * ta.unassigned_count / NULLIF(ta.ticket_count,0), 1) AS pct_unassigned, ROUND(ta.avg_first_response_minutes::numeric, 1) AS avg_first_response_minutes, ROUND(ta.avg_resolution_minutes::numeric, 1) AS avg_resolution_minutes, ROUND(100.0 * COALESCE(ba.resolution_breach_count,0) / NULLIF(ta.ticket_count,0), 1) AS resolution_breach_rate FROM ticket_agg ta JOIN tenants ten ON ten.id = ta.tenant_id JOIN agent_groups ag ON ag.id = ta.agent_group_id LEFT JOIN agent_counts ac ON ac.tenant_id = ta.tenant_id AND ac.agent_group_id = ta.agent_group_id LEFT JOIN breach_agg ba ON ba.tenant_id = ta.tenant_id AND ba.agent_group_id = ta.agent_group_id ORDER BY ta.ticket_count DESC, ten.name, ag.name LIMIT 20
Submitted to tonight's batch. Your work on this request is done.