Queries · q198 · Support Operations Manager · week 1, night 1
“What is our overall SLA breach rate for 2025, and which priority levels drive the misses?”
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 3 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 as ticket_id, t.priority, sp.first_response_target_minutes, sp.resolution_target_minutes from tickets t join sla_policies sp on sp.id = t.sla_policy_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 ), breach_fr as ( select ticket_id, avg(minutes_over) as minutes_over from sla_breaches where breach_kind = 'first_response' group by ticket_id ), breach_res as ( select ticket_id, avg(minutes_over) as minutes_over from sla_breaches where breach_kind = 'resolution' group by ticket_id ), per_priority as ( select b.priority::text as priority, count(*) as ticket_count_with_sla, round(avg(b.first_response_target_minutes)) as typical_first_response_target_minutes, round(avg(b.resolution_target_minutes)) as typical_resolution_target_minutes, round(100.0 * count(fr.ticket_id) / count(*), 1) as first_response_breach_rate, round(100.0 * count(res.ticket_id) / count(*), 1) as resolution_breach_rate, round(avg(fr.minutes_over)) as avg_minutes_over_first_response, round(avg(res.minutes_over)) as avg_minutes_over_resolution from base b left join breach_fr fr on fr.ticket_id = b.ticket_id left join breach_res res on res.ticket_id = b.ticket_id group by b.priority ), overall as ( select 'All priorities'::text as priority, count(*) as ticket_count_with_sla, null::numeric as typical_first_response_target_minutes, null::numeric as typical_resolution_target_minutes, round(100.0 * count(fr.ticket_id) / count(*), 1) as first_response_breach_rate, round(100.0 * count(res.ticket_id) / count(*), 1) as resolution_breach_rate, round(avg(fr.minutes_over)) as avg_minutes_over_first_response, round(avg(res.minutes_over)) as avg_minutes_over_resolution from base b left join breach_fr fr on fr.ticket_id = b.ticket_id left join breach_res res on res.ticket_id = b.ticket_id ), combined as ( select 0 as sort_rank, * from overall union all select 1 as sort_rank, * from per_priority ) select priority, ticket_count_with_sla, typical_first_response_target_minutes, typical_resolution_target_minutes, first_response_breach_rate, resolution_breach_rate, avg_minutes_over_first_response, avg_minutes_over_resolution from combined order by sort_rank, resolution_breach_rate desc nulls last, priority
“Query compiles and output columns match the request. Submitting.”
“Submitted. The report computes, per priority (plus an 'All priorities' summary row first), the SLA-eligible ticket count, ticket-weighted average first-response/resolution targets, breach rates (% of tickets with a matching sla_breaches row by kind), and average minutes over target — ordered with the headline row first, then priorities by resolution breach rate descending.”
As Ben Carter saw it, from the small dataset the agents worked on
priority | ticket_count_with_sla | typical_first_response_target_minutes | typical_resolution_target_minutes | first_response_breach_rate | resolution_breach_rate | avg_minutes_over_first_response | avg_minutes_over_resolution ---------------+-----------------------+---------------------------------------+-----------------------------------+----------------------------+------------------------+---------------------------------+---------------------------- All priorities | 2320 | NULL | NULL | 37.7 | 20.3 | 2493.0 | 6480.0 urgent | 241 | 60.0 | 480.0 | 35.3 | 50.6 | 2053.0 | 2617.0 high | 338 | 120.0 | 1440.0 | 43.5 | 21.3 | 1282.0 | 3476.0 normal | 1518 | 480.0 | 2880.0 | 39.3 | 17.7 | 2864.0 | 9022.0 low | 223 | 1440.0 | 7200.0 | 20.6 | 3.6 | 2314.0 | 7413.0 (5 rows)
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 as ticket_id, t.priority, sp.first_response_target_minutes, sp.resolution_target_minutes select t.id as ticket_id, t.priority AS priority, sp.first_response_target_minutes AS first_response_target_minutes, sp.resolution_target_minutes AS resolution_target_minutes from tickets t join sla_policies sp on sp.id = t.sla_policy_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),breach_fr as ( select ticket_id, avg(minutes_over) as minutes_over select ticket_id, avg(cast(minutes_over as numeric(18,6))) as minutes_over from sla_breaches where breach_kind = 'first_response' group by ticket_id),breach_res as ( select ticket_id, avg(minutes_over) as minutes_over select ticket_id, avg(cast(minutes_over as numeric(18,6))) as minutes_over from sla_breaches where breach_kind = 'resolution' group by ticket_id),per_priority as ( select b.priority::text as priority, CAST(b.priority AS text) as priority, count(*) as ticket_count_with_sla, round(avg(b.first_response_target_minutes)) as typical_first_response_target_minutes, round(avg(b.resolution_target_minutes)) as typical_resolution_target_minutes, round(100.0 * count(fr.ticket_id) / count(*), 1) as first_response_breach_rate, round(100.0 * count(res.ticket_id) / count(*), 1) as resolution_breach_rate, round(avg(fr.minutes_over)) as avg_minutes_over_first_response, round(avg(res.minutes_over)) as avg_minutes_over_resolution avg(cast(b.first_response_target_minutes as numeric(18,6))) as typical_first_response_target_minutes, avg(cast(b.resolution_target_minutes as numeric(18,6))) as typical_resolution_target_minutes, 100.0 * count(fr.ticket_id) / count(*) as first_response_breach_rate, 100.0 * count(res.ticket_id) / count(*) as resolution_breach_rate, avg(fr.minutes_over) as avg_minutes_over_first_response, avg(res.minutes_over) as avg_minutes_over_resolution from base b left join breach_fr fr on fr.ticket_id = b.ticket_id left join breach_res res on res.ticket_id = b.ticket_id group by b.priority),overall as ( select 'All priorities'::text as priority, CAST('All priorities' AS text) as priority, count(*) as ticket_count_with_sla, null::numeric as typical_first_response_target_minutes, null::numeric as typical_resolution_target_minutes, round(100.0 * count(fr.ticket_id) / count(*), 1) as first_response_breach_rate, round(100.0 * count(res.ticket_id) / count(*), 1) as resolution_breach_rate, round(avg(fr.minutes_over)) as avg_minutes_over_first_response, round(avg(res.minutes_over)) as avg_minutes_over_resolution CAST(null AS numeric(18,6)) as typical_first_response_target_minutes, CAST(null AS numeric(18,6)) as typical_resolution_target_minutes, 100.0 * count(fr.ticket_id) / NULLIF(count(*), 0) as first_response_breach_rate, 100.0 * count(res.ticket_id) / NULLIF(count(*), 0) as resolution_breach_rate, avg(fr.minutes_over) as avg_minutes_over_first_response, avg(res.minutes_over) as avg_minutes_over_resolution from base b left join breach_fr fr on fr.ticket_id = b.ticket_id left join breach_res res on res.ticket_id = b.ticket_id),combined as ( select 0 as sort_rank, * from overall union all select 1 as sort_rank, * from per_priority)select priority, ticket_count_with_sla, typical_first_response_target_minutes, typical_resolution_target_minutes, first_response_breach_rate, resolution_breach_rate, avg_minutes_over_first_response, avg_minutes_over_resolutionfrom combinedorder by sort_rank, resolution_breach_rate desc nulls last, priorityorder by sort_rank NULLS LAST, resolution_breach_rate desc nulls last, priority NULLS LAST
with base as ( select t.id as ticket_id, t.priority AS priority, sp.first_response_target_minutes AS first_response_target_minutes, sp.resolution_target_minutes AS resolution_target_minutes from tickets t join sla_policies sp on sp.id = t.sla_policy_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 ), breach_fr as ( select ticket_id, avg(cast(minutes_over as numeric(18,6))) as minutes_over from sla_breaches where breach_kind = 'first_response' group by ticket_id ), breach_res as ( select ticket_id, avg(cast(minutes_over as numeric(18,6))) as minutes_over from sla_breaches where breach_kind = 'resolution' group by ticket_id ), per_priority as ( select CAST(b.priority AS text) as priority, count(*) as ticket_count_with_sla, avg(cast(b.first_response_target_minutes as numeric(18,6))) as typical_first_response_target_minutes, avg(cast(b.resolution_target_minutes as numeric(18,6))) as typical_resolution_target_minutes, 100.0 * count(fr.ticket_id) / count(*) as first_response_breach_rate, 100.0 * count(res.ticket_id) / count(*) as resolution_breach_rate, avg(fr.minutes_over) as avg_minutes_over_first_response, avg(res.minutes_over) as avg_minutes_over_resolution from base b left join breach_fr fr on fr.ticket_id = b.ticket_id left join breach_res res on res.ticket_id = b.ticket_id group by b.priority ), overall as ( select CAST('All priorities' AS text) as priority, count(*) as ticket_count_with_sla, CAST(null AS numeric(18,6)) as typical_first_response_target_minutes, CAST(null AS numeric(18,6)) as typical_resolution_target_minutes, 100.0 * count(fr.ticket_id) / NULLIF(count(*), 0) as first_response_breach_rate, 100.0 * count(res.ticket_id) / NULLIF(count(*), 0) as resolution_breach_rate, avg(fr.minutes_over) as avg_minutes_over_first_response, avg(res.minutes_over) as avg_minutes_over_resolution from base b left join breach_fr fr on fr.ticket_id = b.ticket_id left join breach_res res on res.ticket_id = b.ticket_id ), combined as ( select 0 as sort_rank, * from overall union all select 1 as sort_rank, * from per_priority ) select priority, ticket_count_with_sla, typical_first_response_target_minutes, typical_resolution_target_minutes, first_response_breach_rate, resolution_breach_rate, avg_minutes_over_first_response, avg_minutes_over_resolution from combined order by sort_rank NULLS LAST, resolution_breach_rate desc nulls last, priority 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 priority, with overall headline rate Question: What is our overall SLA breach rate for 2025, and which priority levels drive the misses? Grain: one row per ticket priority (low, normal, high, urgent) plus one summary row labeled 'All priorities' totaling across all of them Columns: - priority (or 'All priorities' for the total row) - ticket_count_with_sla - typical_first_response_target_minutes (from sla_policies for that priority, blank on the All row) - typical_resolution_target_minutes (from sla_policies for that priority, blank on the All row) - first_response_breach_rate - resolution_breach_rate - avg_minutes_over_first_response - avg_minutes_over_resolution Details: Window: tickets.created_at between 2025-01-01 and 2025-12-31 inclusive, restricted to tickets with sla_policy_id not null. Source tables: tickets, sla_breaches, sla_policies. Compute breach rates per priority as % of that priority's tickets with a matching sla_breaches row (split by breach_kind = first_response / resolution); the 'All priorities' row aggregates across every priority (this is the headline number for the leadership review). Order: 'All priorities' row first, then the four priorities sorted by resolution_breach_rate descending. 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 as ticket_id, t.priority, sp.first_response_target_minutes, sp.resolution_target_minutes from tickets t join sla_policies sp on sp.id = t.sla_policy_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 ), breach_fr as ( select ticket_id, avg(minutes_over) as minutes_over from sla_breaches where breach_kind = 'first_response' group by ticket_id ), breach_res as ( select ticket_id, avg(minutes_over) as minutes_over from sla_breaches where breach_kind = 'resolution' group by ticket_id ), per_priority as ( select b.priority::text as priority, count(*) as ticket_count_with_sla, round(avg(b.first_response_target_minutes)) as typical_first_response_target_minutes, round(avg(b.resolution_target_minutes)) as typical_resolution_target_minutes, round(100.0 * count(fr.ticket_id) / count(*), 1) as first_response_breach_rate, round(100.0 * count(res.ticket_id) / count(*), 1) as resolution_breach_rate, round(avg(fr.minutes_over)) as avg_minutes_over_first_response, round(avg(res.minutes_over)) as avg_minutes_over_resolution from base b left join breach_fr fr on fr.ticket_id = b.ticket_id left join breach_res res on res.ticket_id = b.ticket_id group by b.priority ), overall as ( select 'All priorities'::text as priority, count(*) as ticket_count_with_sla, null::numeric as typical_first_response_target_minutes, null::numeric as typical_resolution_target_minutes, round(100.0 * count(fr.ticket_id) / count(*), 1) as first_response_breach_rate, round(100.0 * count(res.ticket_id) / count(*), 1) as resolution_breach_rate, round(avg(fr.minutes_over)) as avg_minutes_over_first_response, round(avg(res.minutes_over)) as avg_minutes_over_resolution from base b left join breach_fr fr on fr.ticket_id = b.ticket_id left join breach_res res on res.ticket_id = b.ticket_id ), combined as ( select 0 as sort_rank, * from overall union all select 1 as sort_rank, * from per_priority ) select priority, ticket_count_with_sla, typical_first_response_target_minutes, typical_resolution_target_minutes, first_response_breach_rate, resolution_breach_rate, avg_minutes_over_first_response, avg_minutes_over_resolution from combined order by sort_rank, resolution_breach_rate desc nulls last, priority
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: priority, ticket_count_with_sla, typical_first_response_target_minutes, typical_resolution_target_minutes, first_response_breach_rate, resolution_breach_rate, avg_minutes_over_first_response, avg_minutes_over_resolution
submitwith base as ( select t.id as ticket_id, t.priority, sp.first_response_target_minutes, sp.resolution_target_minutes from tickets t join sla_policies sp on sp.id = t.sla_policy_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 ), breach_fr as ( select ticket_id, avg(minutes_over) as minutes_over from sla_breaches where breach_kind = 'first_response' group by ticket_id ), breach_res as ( select ticket_id, avg(minutes_over) as minutes_over from sla_breaches where breach_kind = 'resolution' group by ticket_id ), per_priority as ( select b.priority::text as priority, count(*) as ticket_count_with_sla, round(avg(b.first_response_target_minutes)) as typical_first_response_target_minutes, round(avg(b.resolution_target_minutes)) as typical_resolution_target_minutes, round(100.0 * count(fr.ticket_id) / count(*), 1) as first_response_breach_rate, round(100.0 * count(res.ticket_id) / count(*), 1) as resolution_breach_rate, round(avg(fr.minutes_over)) as avg_minutes_over_first_response, round(avg(res.minutes_over)) as avg_minutes_over_resolution from base b left join breach_fr fr on fr.ticket_id = b.ticket_id left join breach_res res on res.ticket_id = b.ticket_id group by b.priority ), overall as ( select 'All priorities'::text as priority, count(*) as ticket_count_with_sla, null::numeric as typical_first_response_target_minutes, null::numeric as typical_resolution_target_minutes, round(100.0 * count(fr.ticket_id) / count(*), 1) as first_response_breach_rate, round(100.0 * count(res.ticket_id) / count(*), 1) as resolution_breach_rate, round(avg(fr.minutes_over)) as avg_minutes_over_first_response, round(avg(res.minutes_over)) as avg_minutes_over_resolution from base b left join breach_fr fr on fr.ticket_id = b.ticket_id left join breach_res res on res.ticket_id = b.ticket_id ), combined as ( select 0 as sort_rank, * from overall union all select 1 as sort_rank, * from per_priority ) select priority, ticket_count_with_sla, typical_first_response_target_minutes, typical_resolution_target_minutes, first_response_breach_rate, resolution_breach_rate, avg_minutes_over_first_response, avg_minutes_over_resolution from combined order by sort_rank, resolution_breach_rate desc nulls last, priority
Submitted to tonight's batch. Your work on this request is done.