DB Olympics

Queries · q202 · Support Operations Manager · week 1, night 3

SLA breach_kind code audit and validation

“What values does sla_breaches.breach_kind actually take, and do our assumed codes ('first_response', 'resolution') account for all breach rows so last night's breach-rate numbers can be trusted?”

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 SQLGraceWrites tidy, layered CTEs with descriptive names, one step per CTE, then a final select.

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.

    One of the 35 events we wrote for the role play; it shaped the manager's first note. How events arise · All the events

  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 3, request 1 of 3

    Before trusting any more SLA numbers, I need to close the risk two separate engineers flagged independently: breach_kind is a free-text column with no enumerated codes, and both guessed 'first_response'/'resolution'. If that guess is wrong (or there's a third kind, or duplicate breach rows per ticket), every breach rate I've reported so far — including the 37.7%/20.3% headline — could be off. That's slot 1 tonight, cheap but non-negotiable before Friday's memo. Report 1 from tonight's merchant cut also showed something worth flagging: our three biggest merchants (Cragsley, Marrowby & Vane, Studio Vrij, 34% of volume) all post first-response breach rates near 55-60%, well above the smaller tail — and Studio Vrij/Marrowby & Vane also have the worst resolution times/breach rates among the top 10, despite having fewer agents than Cragsley. That's a concrete 'specific merchant driving disproportionate pain' candidate for Daniel, but I want the breach-code validation done before I state it as fact. With the other two slots I'm moving to priority #3 (what makes a ticket slow — channel and unassigned status, which both leaders asked about explicitly) and starting priority #4 (macro reliance), since night 4 is the last chance to cover KB effectiveness and CSAT.

    SLA breach_kind code audit and validation
    Question
    What values does sla_breaches.breach_kind actually take, and do our assumed codes ('first_response', 'resolution') account for all breach rows so last night's breach-rate numbers can be trusted?
    One row per
    distinct breach_kind value
    Columns
    • breach_kind
    • row_count (2025)
    • distinct_tickets_affected
    • avg_minutes_over
    • min_minutes_over
    • max_minutes_over
    • earliest_breached_at
    • latest_breached_at

  4. Grace 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 sla_breaches_2025 as (
    select
        breach_kind,
        ticket_id,
        minutes_over,
        breached_at
    from sla_breaches
    where breached_at >= date '2025-01-01'
      and breached_at < date '2026-01-01'
),
breach_kind_summary as (
    select
        breach_kind,
        count(*) as row_count,
        count(distinct ticket_id) as distinct_tickets_affected,
        avg(minutes_over) as avg_minutes_over,
        min(minutes_over) as min_minutes_over,
        max(minutes_over) as max_minutes_over,
        min(breached_at) as earliest_breached_at,
        max(breached_at) as latest_breached_at
    from sla_breaches_2025
    group by breach_kind
)
select
    breach_kind,
    row_count,
    distinct_tickets_affected,
    round(avg_minutes_over, 1) as avg_minutes_over,
    min_minutes_over,
    max_minutes_over,
    earliest_breached_at,
    latest_breached_at
from breach_kind_summary
order by row_count desc, breach_kind asc

    “This looks good. Submitting.”

    Submitted · the SQL of the last dry run, unchanged · Submitted to tonight's batch. Your work on this request is done.

    “Submitted. Summary of what the report will show: - One row per distinct breach_kind value actually present in sla_breaches for 2025 (not filtered to only first_response/resolution), with row_count, distinct tickets affected, avg/min/max minutes_over, and earliest/latest breach timestamps, ordered by row_count descending. - Note flagged to the analyst: I used breached_at in 2025 (not a join to tickets.created_at) since that's the simpler and more natural filter for a breach event; I skipped the "orphan ticket_id" reconciliation since it would require a separate join-based report to do safely without duplicating rows — recommended as a follow-up if needed.”

  5. The report comes back overnight

    As Ben Carter saw it, from the small dataset the agents worked on

    breach_kind    | row_count | distinct_tickets_affected | avg_minutes_over | min_minutes_over | max_minutes_over | earliest_breached_at       | latest_breached_at        
---------------+-----------+---------------------------+------------------+------------------+------------------+----------------------------+---------------------------
first_response |       878 |                       878 |           2500.8 |                0 |            17123 | 2025-01-01 15:00:00        | 2025-12-31 19:03:17.187644
resolution     |       474 |                       474 |           6497.0 |                2 |            66008 | 2025-01-01 01:36:56.663202 | 2025-12-31 14:22:02.996512
(2 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:

    It builds on:

  7. It becomes benchmark query q202

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

    • 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
    • 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 sla_breaches_2025 as (    select        breach_kind,        ticket_id,        minutes_over,        breached_at    from sla_breaches    where breached_at >= date '2025-01-01'      and breached_at < date '2026-01-01'),breach_kind_summary as (    select        breach_kind,        count(*) as row_count,        count(distinct ticket_id) as distinct_tickets_affected,        avg(minutes_over) as avg_minutes_over,        avg(cast(minutes_over as numeric(18,6))) as avg_minutes_over,        min(minutes_over) as min_minutes_over,        max(minutes_over) as max_minutes_over,        min(breached_at) as earliest_breached_at,        max(breached_at) as latest_breached_at    from sla_breaches_2025    group by breach_kind)select    breach_kind,    row_count,    distinct_tickets_affected,    round(avg_minutes_over, 1) as avg_minutes_over,    avg_minutes_over as avg_minutes_over,    min_minutes_over,    max_minutes_over,    earliest_breached_at,    latest_breached_atfrom breach_kind_summaryorder by row_count desc, breach_kind ascorder by row_count desc NULLS FIRST, breach_kind asc NULLS LAST

    As each system received it; highlighted, the parts spelled for that system

    with sla_breaches_2025 as (
    select
        breach_kind,
        ticket_id,
        minutes_over,
        breached_at
    from sla_breaches
    where breached_at >= date '2025-01-01'
      and breached_at < date '2026-01-01'
),
breach_kind_summary as (
    select
        breach_kind,
        count(*) as row_count,
        count(distinct ticket_id) as distinct_tickets_affected,
        avg(cast(minutes_over as numeric(18,6))) as avg_minutes_over,
        min(minutes_over) as min_minutes_over,
        max(minutes_over) as max_minutes_over,
        min(breached_at) as earliest_breached_at,
        max(breached_at) as latest_breached_at
    from sla_breaches_2025
    group by breach_kind
)
select
    breach_kind,
    row_count,
    distinct_tickets_affected,
    avg_minutes_over as avg_minutes_over,
    min_minutes_over,
    max_minutes_over,
    earliest_breached_at,
    latest_breached_at
from breach_kind_summary
order by row_count desc NULLS FIRST, breach_kind asc NULLS LAST
    10 ms100 ms1 sHyper13.4 msDuckDB17.6 msGizmoSQL22.8 msclickhouse_brew68.6 msFirebolt115 msDataFusion61.3 msClickHouse73.8 ms

    Scale factor 1000, c8a.24xlarge, after the last load. q202 on the start page, with every configuration.

  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

    Grace's whole session, 7 turns
    • The request, as Grace received it
      Report request from Ben Carter (analyst for the Support Operations Manager):

Title: SLA breach_kind code audit and validation
Question: What values does sla_breaches.breach_kind actually take, and do our assumed codes ('first_response', 'resolution') account for all breach rows so last night's breach-rate numbers can be trusted?
Grain: one row per distinct breach_kind value
Columns:
- breach_kind
- row_count (2025)
- distinct_tickets_affected
- avg_minutes_over
- min_minutes_over
- max_minutes_over
- earliest_breached_at
- latest_breached_at
Details: Source table: sla_breaches, filtered to breached_at in 2025-01-01 through 2025-12-31 (or occurred ticket creation in 2025, whichever is simpler to join via tickets.id). No assumption on the set of breach_kind values — group by whatever distinct strings exist, do not filter to only 'first_response'/'resolution'. Also include a row for any tickets.id referenced in sla_breaches that doesn't match a 2025-created ticket if that's easy, otherwise skip. Order by row_count descending. This is a data-quality check, not a business metric: if a kind other than first_response/resolution shows up, or counts look inconsistent with ticket_count_with_sla totals from earlier reports (~2320 tickets, ~875 first-response breaches, ~471 resolution breaches), flag it in the engineer's note.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Grace calls dry_run
      with sla_breaches_2025 as (
    select
        breach_kind,
        ticket_id,
        minutes_over,
        breached_at
    from sla_breaches
    where breached_at >= date '2025-01-01'
      and breached_at < date '2026-01-01'
),
breach_kind_summary as (
    select
        breach_kind,
        count(*) as row_count,
        count(distinct ticket_id) as distinct_tickets_affected,
        avg(minutes_over) as avg_minutes_over,
        min(minutes_over) as min_minutes_over,
        max(minutes_over) as max_minutes_over,
        min(breached_at) as earliest_breached_at,
        max(breached_at) as latest_breached_at
    from sla_breaches_2025
    group by breach_kind
)
select
    breach_kind,
    row_count,
    distinct_tickets_affected,
    round(avg_minutes_over, 1) as avg_minutes_over,
    min_minutes_over,
    max_minutes_over,
    earliest_breached_at,
    latest_breached_at
from breach_kind_summary
order by row_count desc, breach_kind asc
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: breach_kind, row_count, distinct_tickets_affected, avg_minutes_over, min_minutes_over, max_minutes_over, earliest_breached_at, latest_breached_at
    • GraceThis looks good. Submitting.
    • Grace calls submit
      with sla_breaches_2025 as (
    select
        breach_kind,
        ticket_id,
        minutes_over,
        breached_at
    from sla_breaches
    where breached_at >= date '2025-01-01'
      and breached_at < date '2026-01-01'
),
breach_kind_summary as (
    select
        breach_kind,
        count(*) as row_count,
        count(distinct ticket_id) as distinct_tickets_affected,
        avg(minutes_over) as avg_minutes_over,
        min(minutes_over) as min_minutes_over,
        max(minutes_over) as max_minutes_over,
        min(breached_at) as earliest_breached_at,
        max(breached_at) as latest_breached_at
    from sla_breaches_2025
    group by breach_kind
)
select
    breach_kind,
    row_count,
    distinct_tickets_affected,
    round(avg_minutes_over, 1) as avg_minutes_over,
    min_minutes_over,
    max_minutes_over,
    earliest_breached_at,
    latest_breached_at
from breach_kind_summary
order by row_count desc, breach_kind asc
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • GraceSubmitted. Summary of what the report will show: - One row per distinct `breach_kind` value actually present in `sla_breaches` for 2025 (not filtered to only `first_response`/`resolution`), with row_count, distinct tickets affected, avg/min/max minutes_over, and earliest/latest breach timestamps, ordered by row_count descending. - Note flagged to the analyst: I used `breached_at` in 2025 (not a join to `tickets.created_at`) since that's the simpler and more natural filter for a breach event; I skipped the "orphan ticket_id" reconciliation since it would require a separate join-based report to do safely without duplicating rows — recommended as a follow-up if needed.

    Not shown here: 239 KB of context the agent was given, its instructions and the schema documentation; it is in the raw transcript.

    Grace's transcript (261 KB) · Ben Carter's transcript (813 KB) · Mia Schultz's transcript (260 KB) · the night's plan (5 KB) · the night's reports (8 KB) · the week's memo (3 KB)