DB Olympics

Queries · q198 · Support Operations Manager · week 1, night 1

SLA breach rates by priority, with overall headline rate

“What is our overall SLA breach rate for 2025, and which priority levels drive the misses?”

Manager · asked the questionMia SchultzSupport Operations Manager. Built support tooling and processes at a software company.
Analyst · planned the reportBen CarterQuantifier: puts a money value on every finding.
Engineer · wrote the SQLOmarPragmatic: the shortest correct query, aggregates first and joins dimensions afterwards, FILTER clauses for conditional counts.

Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told

  1. A business event lands on the Support Operations Manager's desk

    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.

  2. Mia Schultz writes to Ben Carter

    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:

    1. Where is ticket volume concentrated, and are the right groups/agents carrying it? I want to see how load splits across our queues and agent groups, and whether staffing looks balanced against that load — this feeds a near-term staffing/routing decision.
    2. Are we meeting our SLA promises, and where do we miss most? I want to understand how often we breach first-response and resolution targets, and whether certain priorities, groups, or times are the repeat offenders. This is the headline number Daniel will want for the review.
    3. What actually makes a ticket slow? I'd like a sense of what separates a ticket that resolves quickly from one that drags — whether that's certain topics, channels, or simply sitting unassigned — so we know where to intervene first.
    4. Are macros and knowledge base articles pulling their weight? I want to know how much agents lean on canned replies and how the knowledge base is doing with customers (are articles helping people avoid a ticket, and are the ones we have actually rated as useful) — this feeds a content/investment decision for next quarter.
    5. (If there's room this week) How satisfied are customers coming out of resolved tickets, and does that satisfaction track with the SLA and speed picture above, or is something else driving it?

    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

    A note from the VP Customer Experience, Daniel Achterberg

    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:

    1. Where do tickets actually get stuck? I'd like to understand which queues or agent groups have the slowest response and resolution times, and whether that's a staffing/routing issue or certain ticket types being inherently harder.
    2. Are we actually meeting the SLA targets we've set, and for the ones we miss, is there a pattern (specific merchants, priorities, time of day/week)?
    3. Are agents getting value from macros and the knowledge base, or are they re-typing answers every time? I'd like a sense of whether better self-serve content or canned responses could take load off the queue.

    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

  3. Ben Carter plans the night

    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.

    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?
    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
  4. Omar writes the SQL

    The engineer · 1 dry run on the empty schema, then the submission

    Dry run 1 · STATUS: COMPILES (dry run on the empty schema, no data read)
    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 SQL of the last dry run, unchanged · Submitted to tonight's batch. Your work on this request is done.

    “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.”

  5. The report comes back overnight

    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)
  6. What came of it

    The memo and the queries that followed; links found by title, so inferred

    Later queries that build on this report:

  7. It becomes benchmark query q198

    Changed only where the benchmark needs the same result on every system

    • spellingcast (4×)
    • numericsdocs/NUMERICS.md: AVG over an integer computed as a decimal: AVG(CAST(x AS NUMERIC(18,6)))
    • numericsdocs/NUMERICS.md: AVG over an integer computed as a decimal: AVG(CAST(x AS NUMERIC(18,6)))
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision; AVG over an integer computed as a decimal: AVG(CAST(x AS NUMERIC(18,6)))
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision; bare NUMERIC cast that only served the rounding removed
    • numericsdocs/NUMERICS.md: divisor guarded with NULLIF(..., 0); it is zero when only part of the data is loaded (an empty period), where engines raise an error or return inf/nan
    • made explicita selected t.col gets AS col, its standard name made explicit (3×)
    • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (2×)
    The benchmark text, against the submitted SQL
    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
    10 ms100 ms1 sHyper111 msDuckDB73.1 msGizmoSQL76.2 msclickhouse_brew203 msFirebolt135 msDataFusion156 msClickHouse225 ms
  8. Everything, as recorded

    The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted

    Omar's whole session, 7 turns
    • The request, as Omar received it
      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.
    • Omar calls dry_run
      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
    • The warehouse answers
      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
    • OmarQuery compiles and output columns match the request. Submitting.
    • Omar calls submit
      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
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • OmarSubmitted. 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.