Queries · q197 · Support Operations Manager · week 1, night 1
“Which agent groups miss first-response and resolution SLA targets most often, and by how much when they do?”
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 1, request 2 of 3
Night 1: establish the baseline Mia and Daniel both asked for first — where volume sits, whether staffing matches it, and the headline SLA miss numbers. I'm starting at the agent-group grain (ties load to staffing and lets us see which queues run slow) and the priority grain (the number Daniel wants for the review, with an 'All' rollup row so we have one true headline rate plus the breakdown). I'm holding macros/KB, root-cause-of-slowness, and CSAT for later nights once I've seen where the pain concentrates — no point drilling into 'why slow' before I know 'where slow'. All three reports use 2025-01-01 to 2025-12-31 (last full year) as the baseline window so later weekly/monthly cuts have something to compare against.
The engineer · 1 dry run on the empty schema, then the submission
WITH base AS ( SELECT t.id, t.agent_group_id, COALESCE(ag.name, 'Unassigned queue') AS agent_group_name, t.created_at, t.first_response_at, t.resolved_at FROM tickets t LEFT JOIN agent_groups ag ON ag.id = t.agent_group_id WHERE t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01' AND t.sla_policy_id IS NOT NULL ), ticket_stats AS ( SELECT agent_group_id, agent_group_name, COUNT(*) AS ticket_count_with_sla, 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 base GROUP BY agent_group_id, agent_group_name ), breach_join AS ( SELECT base.agent_group_id, base.agent_group_name, sb.ticket_id, sb.breach_kind, sb.minutes_over FROM base JOIN sla_breaches sb ON sb.ticket_id = base.id ), breach_stats AS ( SELECT agent_group_id, agent_group_name, COUNT(DISTINCT ticket_id) FILTER (WHERE breach_kind = 'first_response') AS fr_breach_tickets, COUNT(DISTINCT ticket_id) FILTER (WHERE breach_kind = 'resolution') AS res_breach_tickets, AVG(minutes_over) FILTER (WHERE breach_kind = 'first_response') AS avg_minutes_over_first_response, AVG(minutes_over) FILTER (WHERE breach_kind = 'resolution') AS avg_minutes_over_resolution FROM breach_join GROUP BY agent_group_id, agent_group_name ) SELECT ts.agent_group_name, ts.ticket_count_with_sla, ROUND(CAST(100.0 * COALESCE(bs.fr_breach_tickets, 0) / ts.ticket_count_with_sla AS numeric), 1) AS first_response_breach_rate, ROUND(CAST(100.0 * COALESCE(bs.res_breach_tickets, 0) / ts.ticket_count_with_sla AS numeric), 1) AS resolution_breach_rate, ROUND(CAST(bs.avg_minutes_over_first_response AS numeric), 0) AS avg_minutes_over_first_response, ROUND(CAST(bs.avg_minutes_over_resolution AS numeric), 0) AS avg_minutes_over_resolution, ROUND(CAST(ts.avg_first_response_minutes AS numeric), 0) AS avg_first_response_minutes, ROUND(CAST(ts.avg_resolution_minutes AS numeric), 0) AS avg_resolution_minutes FROM ticket_stats ts LEFT JOIN breach_stats bs ON bs.agent_group_id IS NOT DISTINCT FROM ts.agent_group_id ORDER BY (ts.agent_group_name = 'Unassigned queue') ASC, resolution_breach_rate DESC NULLS LAST, ts.agent_group_name ASC
“Query compiles and returns the expected columns. Submitting.”
“Submitted. Summary of what the report does:
- Grain: one row per agent group (by group identity, not just name, so same-named groups in different tenants stay separate), plus a single collapsed 'Unassigned queue' row for tickets with no agent_group_id.
- Filter: tickets created in 2025 with a non-null sla_policy_id.
- Breach rates: % of those tickets that have a matching sla_breaches row, split by breach_kind (first_response / resolution).
- Avg minutes over: average minutes_over from the breach rows themselves.
- Avg actual minutes: average of first_response_at - created_at and resolved_at - created_at, computed only over tickets where that timestamp is actually set (noted in the submit note since pending tickets have no value to average).
- Ordering: resolution breach rate descending, with the Unassigned queue row forced to the bottom regardless of its rate.”
As Ben Carter saw it, from the small dataset the agents worked on
agent_group_name | ticket_count_with_sla | first_response_breach_rate | resolution_breach_rate | avg_minutes_over_first_response | avg_minutes_over_resolution | avg_first_response_minutes | avg_resolution_minutes ------------------+-----------------------+----------------------------+------------------------+---------------------------------+-----------------------------+----------------------------+----------------------- Customer Care | 2 | 50.0 | 100.0 | 49 | 1215 | 2331 | 3957 Customer Care | 1 | 100.0 | 100.0 | 340 | 1327 | 1060 | 2468 Customer Care | 3 | 0.0 | 66.7 | NULL | 800 | 568 | 1601 Returns & Refunds | 82 | 73.2 | 48.8 | 4675 | 10530 | 3733 | 6162 Returns & Refunds | 20 | 55.0 | 45.0 | 830 | 1668 | 738 | 2171 Delivery Issues | 31 | 58.1 | 41.9 | 3736 | 8691 | 2496 | 4943 Customer Care | 81 | 56.8 | 40.7 | 6648 | 15001 | 4163 | 7811 Customer Care | 45 | 53.3 | 40.0 | 4455 | 8071 | 2727 | 4936 Returns & Refunds | 41 | 61.0 | 36.6 | 4708 | 11181 | 3212 | 5592 Delivery Issues | 44 | 47.7 | 36.4 | 5602 | 19789 | 2989 | 8746 Customer Care | 106 | 54.7 | 35.8 | 4003 | 9697 | 2464 | 4264 Returns & Refunds | 38 | 57.9 | 34.2 | 2328 | 2184 | 1631 | 2138 Customer Care | 3 | 33.3 | 33.3 | 89 | 372 | 1504 | 3592 Key Accounts | 6 | 16.7 | 33.3 | 70 | 796 | 213 | 1389 Returns & Refunds | 3 | 0.0 | 33.3 | NULL | 119 | 758 | 1031 Returns & Refunds | 144 | 63.9 | 32.6 | 1602 | 3278 | 1458 | 2629 Returns & Refunds | 47 | 36.2 | 31.9 | 4003 | 6705 | 1786 | 3234 Delivery Issues | 16 | 31.3 | 31.3 | 2420 | 2278 | 1081 | 2306 Delivery Issues | 43 | 46.5 | 30.2 | 3662 | 13353 | 2071 | 5153 Delivery Issues | 107 | 51.4 | 29.9 | 1915 | 4126 | 1327 | 2413 Customer Care | 36 | 44.4 | 27.8 | 1977 | 4589 | 1181 | 2662 Customer Care | 149 | 54.4 | 26.8 | 1184 | 2869 | 1010 | 2034 Delivery Issues | 30 | 43.3 | 26.7 | 3751 | 7421 | 1943 | 3162 Customer Care | 50 | 50.0 | 26.0 | 2751 | 3659 | 1693 | 2262 Customer Care | 4 | 50.0 | 25.0 | 54 | 368 | 956 | 1201 Customer Care | 4 | 0.0 | 25.0 | NULL | 3576 | 173 | 1189 Customer Care | 28 | 35.7 | 25.0 | 1698 | 4470 | 865 | 2182 Customer Care | 4 | 25.0 | 25.0 | 1017 | 190 | 1632 | 2473 Delivery Issues | 4 | 0.0 | 25.0 | NULL | 182 | 154 | 456 Key Accounts | 4 | 0.0 | 25.0 | NULL | 168 | 128 | 1083 Customer Care | 5 | 0.0 | 20.0 | NULL | 109 | 1172 | 1673 Customer Care | 10 | 20.0 | 20.0 | 157 | 112 | 212 | 818 Delivery Issues | 15 | 46.7 | 20.0 | 1028 | 2219 | 770 | 1562 Delivery Issues | 10 | 20.0 | 20.0 | 509 | 694 | 333 | 905 Customer Care | 6 | 33.3 | 16.7 | 688 | 2127 | 879 | 1797 Customer Care | 6 | 0.0 | 16.7 | NULL | 723 | 1166 | 1942 Customer Service | 6 | 0.0 | 16.7 | NULL | 1629 | 213 | 1040 Customer Care | 74 | 32.4 | 16.2 | 341 | 1152 | 374 | 1083 Delivery Issues | 25 | 36.0 | 16.0 | 2092 | 7658 | 1101 | 2643 Returns & Refunds | 13 | 30.8 | 15.4 | 561 | 1015 | 1052 | 1625 Key Accounts | 7 | 14.3 | 14.3 | 82 | 2533 | 169 | 1529 Returns & Refunds | 21 | 23.8 | 14.3 | 1492 | 1920 | 585 | 1141 Delivery Issues | 22 | 22.7 | 13.6 | 397 | 589 | 331 | 957 Customer Care | 16 | 18.8 | 12.5 | 236 | 1003 | 225 | 766 Returns & Refunds | 24 | 25.0 | 12.5 | 848 | 1058 | 449 | 1082 Customer Care | 17 | 23.5 | 11.8 | 1630 | 4500 | 1487 | 2454 Returns & Refunds | 17 | 17.6 | 11.8 | 376 | 447 | 1224 | 2021 Customer Care | 9 | 33.3 | 11.1 | 217 | 138 | 302 | 891 Delivery Issues | 9 | 33.3 | 11.1 | 233 | 6610 | 323 | 1865 Delivery Issues | 9 | 22.2 | 11.1 | 604 | 837 | 1486 | 2366 (171 rows, first 50 printed)
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
Changed only where the benchmark needs the same result on every system
WITH base AS ( SELECT t.id, t.agent_group_id, COALESCE(ag.name, 'Unassigned queue') AS agent_group_name, t.created_at, t.first_response_at, t.resolved_at SELECT t.id AS id, t.agent_group_id AS agent_group_id, COALESCE(ag.name, 'Unassigned queue') AS agent_group_name, t.created_at AS created_at, t.first_response_at AS first_response_at, t.resolved_at AS resolved_at FROM tickets t LEFT JOIN agent_groups ag ON ag.id = t.agent_group_id WHERE t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01' AND t.sla_policy_id IS NOT NULL),ticket_stats AS ( SELECT agent_group_id, agent_group_name, COUNT(*) AS ticket_count_with_sla, 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 base GROUP BY agent_group_id, agent_group_name),breach_join AS ( SELECT base.agent_group_id, base.agent_group_name, sb.ticket_id, sb.breach_kind, sb.minutes_over SELECT base.agent_group_id AS agent_group_id, base.agent_group_name AS agent_group_name, sb.ticket_id AS ticket_id, sb.breach_kind AS breach_kind, sb.minutes_over AS minutes_over FROM base JOIN sla_breaches sb ON sb.ticket_id = base.id),breach_stats AS ( SELECT agent_group_id, agent_group_name, COUNT(DISTINCT ticket_id) FILTER (WHERE breach_kind = 'first_response') AS fr_breach_tickets, COUNT(DISTINCT ticket_id) FILTER (WHERE breach_kind = 'resolution') AS res_breach_tickets, AVG(minutes_over) FILTER (WHERE breach_kind = 'first_response') AS avg_minutes_over_first_response, AVG(minutes_over) FILTER (WHERE breach_kind = 'resolution') AS avg_minutes_over_resolution AVG(CAST(minutes_over AS NUMERIC(18,6))) FILTER (WHERE breach_kind = 'first_response') AS avg_minutes_over_first_response, AVG(CAST(minutes_over AS NUMERIC(18,6))) FILTER (WHERE breach_kind = 'resolution') AS avg_minutes_over_resolution FROM breach_join GROUP BY agent_group_id, agent_group_name)SELECT ts.agent_group_name, ts.ticket_count_with_sla, ROUND(CAST(100.0 * COALESCE(bs.fr_breach_tickets, 0) / ts.ticket_count_with_sla AS numeric), 1) AS first_response_breach_rate, ROUND(CAST(100.0 * COALESCE(bs.res_breach_tickets, 0) / ts.ticket_count_with_sla AS numeric), 1) AS resolution_breach_rate, ROUND(CAST(bs.avg_minutes_over_first_response AS numeric), 0) AS avg_minutes_over_first_response, ROUND(CAST(bs.avg_minutes_over_resolution AS numeric), 0) AS avg_minutes_over_resolution, ROUND(CAST(ts.avg_first_response_minutes AS numeric), 0) AS avg_first_response_minutes, ROUND(CAST(ts.avg_resolution_minutes AS numeric), 0) AS avg_resolution_minutes ts.agent_group_name AS agent_group_name, ts.ticket_count_with_sla AS ticket_count_with_sla, 100.0 * COALESCE(bs.fr_breach_tickets, 0) / ts.ticket_count_with_sla AS first_response_breach_rate, 100.0 * COALESCE(bs.res_breach_tickets, 0) / ts.ticket_count_with_sla AS resolution_breach_rate, bs.avg_minutes_over_first_response AS avg_minutes_over_first_response, bs.avg_minutes_over_resolution AS avg_minutes_over_resolution, ts.avg_first_response_minutes AS avg_first_response_minutes, ts.avg_resolution_minutes AS avg_resolution_minutesFROM ticket_stats tsLEFT JOIN breach_stats bs ON bs.agent_group_id IS NOT DISTINCT FROM ts.agent_group_idORDER BY (ts.agent_group_name = 'Unassigned queue') ASC, (ts.agent_group_name = 'Unassigned queue') ASC NULLS LAST, resolution_breach_rate DESC NULLS LAST, ts.agent_group_name ASC ts.agent_group_name ASC NULLS LAST, ts.agent_group_id NULLS LAST
WITH base AS ( SELECT t.id AS id, t.agent_group_id AS agent_group_id, COALESCE(ag.name, 'Unassigned queue') AS agent_group_name, t.created_at AS created_at, t.first_response_at AS first_response_at, t.resolved_at AS resolved_at FROM tickets t LEFT JOIN agent_groups ag ON ag.id = t.agent_group_id WHERE t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01' AND t.sla_policy_id IS NOT NULL ), ticket_stats AS ( SELECT agent_group_id, agent_group_name, COUNT(*) AS ticket_count_with_sla, 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 base GROUP BY agent_group_id, agent_group_name ), breach_join AS ( SELECT base.agent_group_id AS agent_group_id, base.agent_group_name AS agent_group_name, sb.ticket_id AS ticket_id, sb.breach_kind AS breach_kind, sb.minutes_over AS minutes_over FROM base JOIN sla_breaches sb ON sb.ticket_id = base.id ), breach_stats AS ( SELECT agent_group_id, agent_group_name, COUNT(DISTINCT ticket_id) FILTER (WHERE breach_kind = 'first_response') AS fr_breach_tickets, COUNT(DISTINCT ticket_id) FILTER (WHERE breach_kind = 'resolution') AS res_breach_tickets, AVG(CAST(minutes_over AS NUMERIC(18,6))) FILTER (WHERE breach_kind = 'first_response') AS avg_minutes_over_first_response, AVG(CAST(minutes_over AS NUMERIC(18,6))) FILTER (WHERE breach_kind = 'resolution') AS avg_minutes_over_resolution FROM breach_join GROUP BY agent_group_id, agent_group_name ) SELECT ts.agent_group_name AS agent_group_name, ts.ticket_count_with_sla AS ticket_count_with_sla, 100.0 * COALESCE(bs.fr_breach_tickets, 0) / ts.ticket_count_with_sla AS first_response_breach_rate, 100.0 * COALESCE(bs.res_breach_tickets, 0) / ts.ticket_count_with_sla AS resolution_breach_rate, bs.avg_minutes_over_first_response AS avg_minutes_over_first_response, bs.avg_minutes_over_resolution AS avg_minutes_over_resolution, ts.avg_first_response_minutes AS avg_first_response_minutes, ts.avg_resolution_minutes AS avg_resolution_minutes FROM ticket_stats ts LEFT JOIN breach_stats bs ON bs.agent_group_id IS NOT DISTINCT FROM ts.agent_group_id ORDER BY (ts.agent_group_name = 'Unassigned queue') ASC NULLS LAST, resolution_breach_rate DESC NULLS LAST, ts.agent_group_name ASC NULLS LAST, ts.agent_group_id NULLS LAST
WITH base AS ( SELECT t.id AS id, t.agent_group_id AS agent_group_id, COALESCE(ag.name, 'Unassigned queue') AS agent_group_name, t.created_at AS created_at, t.first_response_at AS first_response_at, t.resolved_at AS resolved_at FROM tickets t LEFT JOIN agent_groups ag ON ag.id = t.agent_group_id WHERE t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01' AND t.sla_policy_id IS NOT NULL ), ticket_stats AS ( SELECT agent_group_id, agent_group_name, COUNT(*) AS ticket_count_with_sla, 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 base GROUP BY agent_group_id, agent_group_name ), breach_join AS ( SELECT base.agent_group_id AS agent_group_id, base.agent_group_name AS agent_group_name, sb.ticket_id AS ticket_id, sb.breach_kind AS breach_kind, sb.minutes_over AS minutes_over FROM base JOIN sla_breaches sb ON sb.ticket_id = base.id ), breach_stats AS ( SELECT agent_group_id, agent_group_name, COUNT(DISTINCT ticket_id) FILTER (WHERE breach_kind = 'first_response') AS fr_breach_tickets, COUNT(DISTINCT ticket_id) FILTER (WHERE breach_kind = 'resolution') AS res_breach_tickets, AVG(CAST(minutes_over AS NUMERIC(18,6))) FILTER (WHERE breach_kind = 'first_response') AS avg_minutes_over_first_response, AVG(CAST(minutes_over AS NUMERIC(18,6))) FILTER (WHERE breach_kind = 'resolution') AS avg_minutes_over_resolution FROM breach_join GROUP BY agent_group_id, agent_group_name ) SELECT ts.agent_group_name AS agent_group_name, ts.ticket_count_with_sla AS ticket_count_with_sla, 100.0 * COALESCE(bs.fr_breach_tickets, 0) / ts.ticket_count_with_sla AS first_response_breach_rate, 100.0 * COALESCE(bs.res_breach_tickets, 0) / ts.ticket_count_with_sla AS resolution_breach_rate, bs.avg_minutes_over_first_response AS avg_minutes_over_first_response, bs.avg_minutes_over_resolution AS avg_minutes_over_resolution, ts.avg_first_response_minutes AS avg_first_response_minutes, ts.avg_resolution_minutes AS avg_resolution_minutes FROM ticket_stats ts LEFT JOIN breach_stats bs ON bs.agent_group_id IS NOT DISTINCT FROM ts.agent_group_id ORDER BY (ts.agent_group_name = 'Unassigned queue') ASC NULLS LAST, resolution_breach_rate DESC NULLS LAST, ts.agent_group_name ASC NULLS LAST, ts.agent_group_id NULLS LAST
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: SLA breach rates by agent group Question: Which agent groups miss first-response and resolution SLA targets most often, and by how much when they do? Grain: one row per agent group (plus one row for tickets with no agent_group_id, labeled 'Unassigned queue') Columns: - agent_group_name - ticket_count_with_sla (tickets created in 2025 with a non-null sla_policy_id) - first_response_breach_rate (% of those tickets with a sla_breaches row of breach_kind = first_response) - resolution_breach_rate (% with a sla_breaches row of breach_kind = resolution) - avg_minutes_over_first_response (avg minutes_over for first_response breaches) - avg_minutes_over_resolution (avg minutes_over for resolution breaches) - avg_first_response_minutes (actual first_response_at - created_at) - avg_resolution_minutes (actual resolved_at - created_at) Details: Window: tickets.created_at between 2025-01-01 and 2025-12-31 inclusive, restricted to tickets with sla_policy_id not null (tickets actually under an SLA). Source tables: tickets, sla_breaches, agent_groups. Join sla_breaches on ticket_id, split by breach_kind. Order by resolution_breach_rate descending, 'Unassigned queue' row last. Round rates to 1 decimal percent, minutes to whole numbers. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runWITH base AS ( SELECT t.id, t.agent_group_id, COALESCE(ag.name, 'Unassigned queue') AS agent_group_name, t.created_at, t.first_response_at, t.resolved_at FROM tickets t LEFT JOIN agent_groups ag ON ag.id = t.agent_group_id WHERE t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01' AND t.sla_policy_id IS NOT NULL ), ticket_stats AS ( SELECT agent_group_id, agent_group_name, COUNT(*) AS ticket_count_with_sla, 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 base GROUP BY agent_group_id, agent_group_name ), breach_join AS ( SELECT base.agent_group_id, base.agent_group_name, sb.ticket_id, sb.breach_kind, sb.minutes_over FROM base JOIN sla_breaches sb ON sb.ticket_id = base.id ), breach_stats AS ( SELECT agent_group_id, agent_group_name, COUNT(DISTINCT ticket_id) FILTER (WHERE breach_kind = 'first_response') AS fr_breach_tickets, COUNT(DISTINCT ticket_id) FILTER (WHERE breach_kind = 'resolution') AS res_breach_tickets, AVG(minutes_over) FILTER (WHERE breach_kind = 'first_response') AS avg_minutes_over_first_response, AVG(minutes_over) FILTER (WHERE breach_kind = 'resolution') AS avg_minutes_over_resolution FROM breach_join GROUP BY agent_group_id, agent_group_name ) SELECT ts.agent_group_name, ts.ticket_count_with_sla, ROUND(CAST(100.0 * COALESCE(bs.fr_breach_tickets, 0) / ts.ticket_count_with_sla AS numeric), 1) AS first_response_breach_rate, ROUND(CAST(100.0 * COALESCE(bs.res_breach_tickets, 0) / ts.ticket_count_with_sla AS numeric), 1) AS resolution_breach_rate, ROUND(CAST(bs.avg_minutes_over_first_response AS numeric), 0) AS avg_minutes_over_first_response, ROUND(CAST(bs.avg_minutes_over_resolution AS numeric), 0) AS avg_minutes_over_resolution, ROUND(CAST(ts.avg_first_response_minutes AS numeric), 0) AS avg_first_response_minutes, ROUND(CAST(ts.avg_resolution_minutes AS numeric), 0) AS avg_resolution_minutes FROM ticket_stats ts LEFT JOIN breach_stats bs ON bs.agent_group_id IS NOT DISTINCT FROM ts.agent_group_id ORDER BY (ts.agent_group_name = 'Unassigned queue') ASC, resolution_breach_rate DESC NULLS LAST, ts.agent_group_name ASC
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: agent_group_name, ticket_count_with_sla, first_response_breach_rate, resolution_breach_rate, avg_minutes_over_first_response, avg_minutes_over_resolution, avg_first_response_minutes, avg_resolution_minutes
submitWITH base AS ( SELECT t.id, t.agent_group_id, COALESCE(ag.name, 'Unassigned queue') AS agent_group_name, t.created_at, t.first_response_at, t.resolved_at FROM tickets t LEFT JOIN agent_groups ag ON ag.id = t.agent_group_id WHERE t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01' AND t.sla_policy_id IS NOT NULL ), ticket_stats AS ( SELECT agent_group_id, agent_group_name, COUNT(*) AS ticket_count_with_sla, 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 base GROUP BY agent_group_id, agent_group_name ), breach_join AS ( SELECT base.agent_group_id, base.agent_group_name, sb.ticket_id, sb.breach_kind, sb.minutes_over FROM base JOIN sla_breaches sb ON sb.ticket_id = base.id ), breach_stats AS ( SELECT agent_group_id, agent_group_name, COUNT(DISTINCT ticket_id) FILTER (WHERE breach_kind = 'first_response') AS fr_breach_tickets, COUNT(DISTINCT ticket_id) FILTER (WHERE breach_kind = 'resolution') AS res_breach_tickets, AVG(minutes_over) FILTER (WHERE breach_kind = 'first_response') AS avg_minutes_over_first_response, AVG(minutes_over) FILTER (WHERE breach_kind = 'resolution') AS avg_minutes_over_resolution FROM breach_join GROUP BY agent_group_id, agent_group_name ) SELECT ts.agent_group_name, ts.ticket_count_with_sla, ROUND(CAST(100.0 * COALESCE(bs.fr_breach_tickets, 0) / ts.ticket_count_with_sla AS numeric), 1) AS first_response_breach_rate, ROUND(CAST(100.0 * COALESCE(bs.res_breach_tickets, 0) / ts.ticket_count_with_sla AS numeric), 1) AS resolution_breach_rate, ROUND(CAST(bs.avg_minutes_over_first_response AS numeric), 0) AS avg_minutes_over_first_response, ROUND(CAST(bs.avg_minutes_over_resolution AS numeric), 0) AS avg_minutes_over_resolution, ROUND(CAST(ts.avg_first_response_minutes AS numeric), 0) AS avg_first_response_minutes, ROUND(CAST(ts.avg_resolution_minutes AS numeric), 0) AS avg_resolution_minutes FROM ticket_stats ts LEFT JOIN breach_stats bs ON bs.agent_group_id IS NOT DISTINCT FROM ts.agent_group_id ORDER BY (ts.agent_group_name = 'Unassigned queue') ASC, resolution_breach_rate DESC NULLS LAST, ts.agent_group_name ASC
Submitted to tonight's batch. Your work on this request is done.