Queries · q207 · Support Operations Manager · week 1, night 4

CSAT score by resolution speed bucket

“Does customer satisfaction after a resolved ticket track with how fast it was resolved and whether it breached SLA, or does something else seem to drive it?”

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 SQLFelixJunior engineer who nests subqueries in FROM and WHERE (IN, EXISTS, correlated subqueries) rather than CTEs.

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

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
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
Ben Carter plans the night
The analyst · week 1, night 4, request 3 of 3
Good news first: the breach_kind audit confirms only two codes exist (first_response: 878 rows, resolution: 474 rows) matching our assumption almost exactly, so the 37.7%/20.3% headline breach rates from night 1 are solid — I can give Daniel that number with confidence. The channel/assignment report was mostly a null result: only 12 tickets platform-wide are currently unassigned (vs ~2,300 total), so 'sitting unassigned' isn't a scale driver of slowness even though those 12 do breach badly — and channel spreads resolution time by only ~12% (2626-2940 min), far smaller than the 3x spread we saw across merchants. So channel/assignment are minor levers; merchant identity remains the dominant driver, which I'll state plainly in the memo. The macro report shows real concentration (top 20 of 600 macros carry ~27% of lifetime usage) but also a red flag I need to note rather than resolve: lifetime macro usage (37,230) dwarfs even several years of 2025-scale agent reply volume (4,279), so I can't respond with a clean 'reliance %' — I'll flag that as a data caveat, not a finding. For this last night I'm closing out the remaining asks: KB article effectiveness (priority 4b), ticket volume/speed by topic (Daniel's 'certain topics' question, and it also tells us where KB investment should go), and CSAT against resolution speed (priority 5, directly testing whether satisfaction tracks the SLA/speed picture).
CSAT score by resolution speed bucket
Question
Does customer satisfaction after a resolved ticket track with how fast it was resolved and whether it breached SLA, or does something else seem to drive it?
One row per
resolution-time bucket, for tickets created in 2025 that have both a resolved_at timestamp and a csat_responses score
Columns
- resolution_time_bucket (Under 4h, 4-24h, 1-3 days, 3-7 days, Over 7 days, based on resolved_at - created_at)
- ticket_count
- avg_csat_score
- pct_resolution_sla_breached (share of tickets in the bucket with a sla_breaches row of breach_kind = 'resolution')
- avg_resolution_minutes (actual average within the bucket, as a sanity check on the bucket boundaries)

Felix writes the SQL

The engineer · 2 dry runs on the empty schema, then the submission

Dry run 1 · STATUS: COMPILES (dry run on the empty schema, no data read)

SELECT
  bucket,
  COUNT(*) AS ticket_count,
  ROUND(AVG(score), 2) AS avg_csat_score,
  ROUND(100.0 * SUM(CASE WHEN breached THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_resolution_sla_breached,
  ROUND(AVG(resolution_minutes)) AS avg_resolution_minutes,
  MIN(bucket_order) AS bucket_order
FROM (
  SELECT
    t.id AS ticket_id,
    c.score AS score,
    EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 60.0 AS resolution_minutes,
    EXISTS (
      SELECT 1 FROM sla_breaches sb
      WHERE sb.ticket_id = t.id AND sb.breach_kind = 'resolution'
    ) AS breached,
    CASE
      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 4 THEN 'Under 4h'
      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 24 THEN '4-24h'
      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 72 THEN '1-3 days'
      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 168 THEN '3-7 days'
      ELSE 'Over 7 days'
    END AS bucket,
    CASE
      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 4 THEN 1
      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 24 THEN 2
      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 72 THEN 3
      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 168 THEN 4
      ELSE 5
    END AS bucket_order
  FROM tickets t
  JOIN csat_responses c ON c.ticket_id = t.id
  WHERE t.created_at >= DATE '2025-01-01'
    AND t.created_at < DATE '2026-01-01'
    AND t.resolved_at IS NOT NULL
) x
GROUP BY bucket
ORDER BY bucket_order

Dry run 2 · 4 changes from the dry run before · STATUS: COMPILES (dry run on the empty schema, no data read)

SELECT  bucket,  bucket AS resolution_time_bucket,  COUNT(*) AS ticket_count,  ROUND(AVG(score), 2) AS avg_csat_score,  ROUND(100.0 * SUM(CASE WHEN breached THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_resolution_sla_breached,  ROUND(AVG(resolution_minutes)) AS avg_resolution_minutes,  MIN(bucket_order) AS bucket_order  ROUND(AVG(resolution_minutes)) AS avg_resolution_minutesFROM (  SELECT    t.id AS ticket_id,    c.score AS score,    EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 60.0 AS resolution_minutes,    EXISTS (      SELECT 1 FROM sla_breaches sb      WHERE sb.ticket_id = t.id AND sb.breach_kind = 'resolution'    ) AS breached,    CASE      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 4 THEN 'Under 4h'      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 24 THEN '4-24h'      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 72 THEN '1-3 days'      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 168 THEN '3-7 days'      ELSE 'Over 7 days'    END AS bucket,    CASE      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 4 THEN 1      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 24 THEN 2      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 72 THEN 3      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 168 THEN 4      ELSE 5    END AS bucket_order    END AS bucket  FROM tickets t  JOIN csat_responses c ON c.ticket_id = t.id  WHERE t.created_at >= DATE '2025-01-01'    AND t.created_at < DATE '2026-01-01'    AND t.resolved_at IS NOT NULL) xGROUP BY bucketORDER BY bucket_orderORDER BY  CASE bucket    WHEN 'Under 4h' THEN 1    WHEN '4-24h' THEN 2    WHEN '1-3 days' THEN 3    WHEN '3-7 days' THEN 4    ELSE 5  END

“This matches the requested grain and columns exactly. Submitting now.”

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

“Submitted. The query buckets resolved tickets created in 2025 into the five resolution-speed buckets, joins each to its single CSAT response, and flags SLA resolution breaches via an EXISTS subquery against sla_breaches. It returns one row per bucket (5 rows total) ordered fastest to slowest, with the ticket count, average CSAT, SLA-breach share, and average resolution minutes as a sanity check on the bucket boundaries.”

The report comes back overnight

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

resolution_time_bucket | ticket_count | avg_csat_score | pct_resolution_sla_breached | avg_resolution_minutes
-----------------------+--------------+----------------+-----------------------------+-----------------------
Under 4h               |          109 |           4.38 |                         0.0 |                  115.0
4-24h                  |          279 |           3.81 |                         5.4 |                  781.0
1-3 days               |          200 |           2.74 |                        18.5 |                 2410.0
3-7 days               |           66 |           1.77 |                        74.2 |                 6471.0
Over 7 days            |           31 |           1.71 |                       100.0 |                24926.0
(5 rows)

What came of it
The memo and the queries that followed; links found by title, so inferred
It builds on:
- q202SLA breach_kind code audit and validation

It becomes benchmark query q207

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

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: EXTRACT(EPOCH FROM (b - a)) cast to NUMERIC(18,6) (5×)
placeholderseconds between (5×)
determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results

The benchmark text, against the submitted SQL

SELECT  bucket AS resolution_time_bucket,  COUNT(*) AS ticket_count,  ROUND(AVG(score), 2) AS avg_csat_score,  ROUND(100.0 * SUM(CASE WHEN breached THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_resolution_sla_breached,  ROUND(AVG(resolution_minutes)) AS avg_resolution_minutes  AVG(CAST(score AS NUMERIC(18,6))) AS avg_csat_score,  100.0 * SUM(CASE WHEN breached THEN 1 ELSE 0 END) / COUNT(*) AS pct_resolution_sla_breached,  AVG(resolution_minutes) AS avg_resolution_minutesFROM (  SELECT    t.id AS ticket_id,    c.score AS score,    EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 60.0 AS resolution_minutes,    {{seconds_between(t.created_at, t.resolved_at)}} / 60.0 AS resolution_minutes,    EXISTS (      SELECT 1 FROM sla_breaches sb      WHERE sb.ticket_id = t.id AND sb.breach_kind = 'resolution'    ) AS breached,    CASE      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 4 THEN 'Under 4h'      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 24 THEN '4-24h'      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 72 THEN '1-3 days'      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 168 THEN '3-7 days'      WHEN {{seconds_between(t.created_at, t.resolved_at)}} / 3600.0 < 4 THEN 'Under 4h'      WHEN {{seconds_between(t.created_at, t.resolved_at)}} / 3600.0 < 24 THEN '4-24h'      WHEN {{seconds_between(t.created_at, t.resolved_at)}} / 3600.0 < 72 THEN '1-3 days'      WHEN {{seconds_between(t.created_at, t.resolved_at)}} / 3600.0 < 168 THEN '3-7 days'      ELSE 'Over 7 days'    END AS bucket  FROM tickets t  JOIN csat_responses c ON c.ticket_id = t.id  WHERE t.created_at >= DATE '2025-01-01'    AND t.created_at < DATE '2026-01-01'    AND t.resolved_at IS NOT NULL) xGROUP BY bucketORDER BY  CASE bucket    WHEN 'Under 4h' THEN 1    WHEN '4-24h' THEN 2    WHEN '1-3 days' THEN 3    WHEN '3-7 days' THEN 4    ELSE 5  END  END NULLS LAST

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

SELECT
  bucket AS resolution_time_bucket,
  COUNT(*) AS ticket_count,
  AVG(CAST(score AS NUMERIC(18,6))) AS avg_csat_score,
  100.0 * SUM(CASE WHEN breached THEN 1 ELSE 0 END) / COUNT(*) AS pct_resolution_sla_breached,
  AVG(resolution_minutes) AS avg_resolution_minutes
FROM (
  SELECT
    t.id AS ticket_id,
    c.score AS score,
    CAST(EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) AS NUMERIC(18,6)) / 60.0 AS resolution_minutes,
    EXISTS (
      SELECT 1 FROM sla_breaches sb
      WHERE sb.ticket_id = t.id AND sb.breach_kind = 'resolution'
    ) AS breached,
    CASE
      WHEN CAST(EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) AS NUMERIC(18,6)) / 3600.0 < 4 THEN 'Under 4h'
      WHEN CAST(EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) AS NUMERIC(18,6)) / 3600.0 < 24 THEN '4-24h'
      WHEN CAST(EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) AS NUMERIC(18,6)) / 3600.0 < 72 THEN '1-3 days'
      WHEN CAST(EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) AS NUMERIC(18,6)) / 3600.0 < 168 THEN '3-7 days'
      ELSE 'Over 7 days'
    END AS bucket
  FROM tickets t
  JOIN csat_responses c ON c.ticket_id = t.id
  WHERE t.created_at >= DATE '2025-01-01'
    AND t.created_at < DATE '2026-01-01'
    AND t.resolved_at IS NOT NULL
) x
GROUP BY bucket
ORDER BY
  CASE bucket
    WHEN 'Under 4h' THEN 1
    WHEN '4-24h' THEN 2
    WHEN '1-3 days' THEN 3
    WHEN '3-7 days' THEN 4
    ELSE 5
  END NULLS LAST

SELECT
  bucket AS resolution_time_bucket,
  COUNT(*) AS ticket_count,
  AVG(CAST(score AS NUMERIC(18,6))) AS avg_csat_score,
  100.0 * SUM(CASE WHEN breached THEN 1 ELSE 0 END) / COUNT(*) AS pct_resolution_sla_breached,
  AVG(resolution_minutes) AS avg_resolution_minutes
FROM (
  SELECT
    t.id AS ticket_id,
    c.score AS score,
    CAST(divideDecimal(toDecimal128(dateDiff('microsecond', t.created_at, t.resolved_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6))) / 60.0 AS resolution_minutes,
    EXISTS (
      SELECT 1 FROM sla_breaches sb
      WHERE sb.ticket_id = t.id AND sb.breach_kind = 'resolution'
    ) AS breached,
    CASE
      WHEN CAST(divideDecimal(toDecimal128(dateDiff('microsecond', t.created_at, t.resolved_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6))) / 3600.0 < 4 THEN 'Under 4h'
      WHEN CAST(divideDecimal(toDecimal128(dateDiff('microsecond', t.created_at, t.resolved_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6))) / 3600.0 < 24 THEN '4-24h'
      WHEN CAST(divideDecimal(toDecimal128(dateDiff('microsecond', t.created_at, t.resolved_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6))) / 3600.0 < 72 THEN '1-3 days'
      WHEN CAST(divideDecimal(toDecimal128(dateDiff('microsecond', t.created_at, t.resolved_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6))) / 3600.0 < 168 THEN '3-7 days'
      ELSE 'Over 7 days'
    END AS bucket
  FROM tickets t
  JOIN csat_responses c ON c.ticket_id = t.id
  WHERE t.created_at >= DATE '2025-01-01'
    AND t.created_at < DATE '2026-01-01'
    AND t.resolved_at IS NOT NULL
) x
GROUP BY bucket
ORDER BY
  CASE bucket
    WHEN 'Under 4h' THEN 1
    WHEN '4-24h' THEN 2
    WHEN '1-3 days' THEN 3
    WHEN '3-7 days' THEN 4
    ELSE 5
  END NULLS LAST

DataFusionThis feature is not implemented: Physical plan does not support logical expression Exists(Exists { subquery: <subquery>, negated: false })
ClickHouseCode: 48. DB::Exception: Current query is not supported yet, because can't find correlated column '__table5.id' in current header: __table5.created_at, __table5.resolved_at, __table6.score. (NOT_IMPLEMENTED)

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

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

Felix's whole session, 9 turns

The request, as Felix received it

Report request from Ben Carter (analyst for the Support Operations Manager):

Title: CSAT score by resolution speed bucket
Question: Does customer satisfaction after a resolved ticket track with how fast it was resolved and whether it breached SLA, or does something else seem to drive it?
Grain: one row per resolution-time bucket, for tickets created in 2025 that have both a resolved_at timestamp and a csat_responses score
Columns:
- resolution_time_bucket (Under 4h, 4-24h, 1-3 days, 3-7 days, Over 7 days, based on resolved_at - created_at)
- ticket_count
- avg_csat_score
- pct_resolution_sla_breached (share of tickets in the bucket with a sla_breaches row of breach_kind = 'resolution')
- avg_resolution_minutes (actual average within the bucket, as a sanity check on the bucket boundaries)
Details: Window: tickets.created_at between 2025-01-01 and 2025-12-31, resolved_at not null, joined to csat_responses on ticket_id (one response per ticket per the unique constraint). Source tables: tickets, csat_responses, sla_breaches. Order rows from fastest to slowest bucket (Under 4h first, Over 7 days last). Round avg_csat_score to 2 decimals, pct to 1 decimal percent, minutes to whole numbers.

Check your query with the warehouse's dry_run tool, then hand it in with submit.

Felix calls dry_run

SELECT
  bucket,
  COUNT(*) AS ticket_count,
  ROUND(AVG(score), 2) AS avg_csat_score,
  ROUND(100.0 * SUM(CASE WHEN breached THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_resolution_sla_breached,
  ROUND(AVG(resolution_minutes)) AS avg_resolution_minutes,
  MIN(bucket_order) AS bucket_order
FROM (
  SELECT
    t.id AS ticket_id,
    c.score AS score,
    EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 60.0 AS resolution_minutes,
    EXISTS (
      SELECT 1 FROM sla_breaches sb
      WHERE sb.ticket_id = t.id AND sb.breach_kind = 'resolution'
    ) AS breached,
    CASE
      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 4 THEN 'Under 4h'
      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 24 THEN '4-24h'
      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 72 THEN '1-3 days'
      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 168 THEN '3-7 days'
      ELSE 'Over 7 days'
    END AS bucket,
    CASE
      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 4 THEN 1
      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 24 THEN 2
      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 72 THEN 3
      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 168 THEN 4
      ELSE 5
    END AS bucket_order
  FROM tickets t
  JOIN csat_responses c ON c.ticket_id = t.id
  WHERE t.created_at >= DATE '2025-01-01'
    AND t.created_at < DATE '2026-01-01'
    AND t.resolved_at IS NOT NULL
) x
GROUP BY bucket
ORDER BY bucket_order

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: bucket, ticket_count, avg_csat_score, pct_resolution_sla_breached, avg_resolution_minutes, bucket_order

Felix calls dry_run

SELECT
  bucket AS resolution_time_bucket,
  COUNT(*) AS ticket_count,
  ROUND(AVG(score), 2) AS avg_csat_score,
  ROUND(100.0 * SUM(CASE WHEN breached THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_resolution_sla_breached,
  ROUND(AVG(resolution_minutes)) AS avg_resolution_minutes
FROM (
  SELECT
    t.id AS ticket_id,
    c.score AS score,
    EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 60.0 AS resolution_minutes,
    EXISTS (
      SELECT 1 FROM sla_breaches sb
      WHERE sb.ticket_id = t.id AND sb.breach_kind = 'resolution'
    ) AS breached,
    CASE
      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 4 THEN 'Under 4h'
      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 24 THEN '4-24h'
      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 72 THEN '1-3 days'
      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 168 THEN '3-7 days'
      ELSE 'Over 7 days'
    END AS bucket
  FROM tickets t
  JOIN csat_responses c ON c.ticket_id = t.id
  WHERE t.created_at >= DATE '2025-01-01'
    AND t.created_at < DATE '2026-01-01'
    AND t.resolved_at IS NOT NULL
) x
GROUP BY bucket
ORDER BY
  CASE bucket
    WHEN 'Under 4h' THEN 1
    WHEN '4-24h' THEN 2
    WHEN '1-3 days' THEN 3
    WHEN '3-7 days' THEN 4
    ELSE 5
  END

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: resolution_time_bucket, ticket_count, avg_csat_score, pct_resolution_sla_breached, avg_resolution_minutes

FelixThis matches the requested grain and columns exactly. Submitting now.

Felix calls submit

SELECT
  bucket AS resolution_time_bucket,
  COUNT(*) AS ticket_count,
  ROUND(AVG(score), 2) AS avg_csat_score,
  ROUND(100.0 * SUM(CASE WHEN breached THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_resolution_sla_breached,
  ROUND(AVG(resolution_minutes)) AS avg_resolution_minutes
FROM (
  SELECT
    t.id AS ticket_id,
    c.score AS score,
    EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 60.0 AS resolution_minutes,
    EXISTS (
      SELECT 1 FROM sla_breaches sb
      WHERE sb.ticket_id = t.id AND sb.breach_kind = 'resolution'
    ) AS breached,
    CASE
      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 4 THEN 'Under 4h'
      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 24 THEN '4-24h'
      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 72 THEN '1-3 days'
      WHEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600.0 < 168 THEN '3-7 days'
      ELSE 'Over 7 days'
    END AS bucket
  FROM tickets t
  JOIN csat_responses c ON c.ticket_id = t.id
  WHERE t.created_at >= DATE '2025-01-01'
    AND t.created_at < DATE '2026-01-01'
    AND t.resolved_at IS NOT NULL
) x
GROUP BY bucket
ORDER BY
  CASE bucket
    WHEN 'Under 4h' THEN 1
    WHEN '4-24h' THEN 2
    WHEN '1-3 days' THEN 3
    WHEN '3-7 days' THEN 4
    ELSE 5
  END

The warehouse answers

Submitted to tonight's batch. Your work on this request is done.

FelixSubmitted. The query buckets resolved tickets created in 2025 into the five resolution-speed buckets, joins each to its single CSAT response, and flags SLA resolution breaches via an EXISTS subquery against `sla_breaches`. It returns one row per bucket (5 rows total) ordered fastest to slowest, with the ticket count, average CSAT, SLA-breach share, and average resolution minutes as a sanity check on the bucket boundaries.

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

Felix's transcript (271 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)

CSAT score by resolution speed bucket

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

Mia Schultz writes to Ben Carter

Ben Carter plans the night

Felix writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q207

Everything, as recorded