Queries · q201 · Support Operations Manager · week 1, night 2

SLA breach rates by day of week and time of day

“Is there a time-of-day or day-of-week pattern to when we breach first-response and resolution SLA targets?”

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 SQLInesThinks in window functions: ranks, running totals, LAG/LEAD for comparisons, percent-of-total.

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 2, request 3 of 3
Night 1 surfaced a methodology problem before any business insight: Grace's agent-group report rolled up every tenant's same-named group (e.g. all 120 merchants' 'Customer Care') into one row, while Omar's SLA-breach-by-group report kept each tenant's group separate (171 rows). The two reports use different grains under the same label, so they can't be compared, and neither tells us whether load/staffing is balanced within a real operational unit (a tenant's own group). Also, the company briefing says a handful of merchants drive most volume — I haven't checked that at all yet, and Daniel explicitly asked to flag any single merchant driving disproportionate pain. So tonight I'm fixing the grain: first, ticket volume and SLA performance by merchant to find concentration and any outlier tenant; second, the highest-volume tenant+group combinations (the real staffing units) to redo the staffing-balance read properly; third, a day-of-week/time-of-day cut of SLA breaches since Daniel specifically asked about time patterns and I have a slot free for it. Headline SLA numbers from report 3 (37.7% first-response breach, 20.3% resolution breach, urgent tickets breaching resolution over half the time) are worth noting to Mia/Daniel now even before drill-down.
SLA breach rates by day of week and time of day
Question
Is there a time-of-day or day-of-week pattern to when we breach first-response and resolution SLA targets?
One row per
combination of ticket creation day-of-week (Monday..Sunday) and time-of-day bucket (Night 00-06, Morning 06-12, Afternoon 12-18, Evening 18-24, all in UTC)
Columns
- day_of_week
- time_of_day_bucket
- ticket_count (created 2025, sla_policy_id not null)
- first_response_breach_rate
- resolution_breach_rate
- avg_first_response_minutes

Ines 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.created_at,
    t.first_response_at,
    cd.day_name,
    cd.iso_day_of_week,
    EXTRACT(HOUR FROM t.created_at)::int AS hr
  FROM tickets t
  JOIN calendar_days cd ON cd.calendar_date = CAST(t.created_at AS date)
  WHERE t.created_at >= DATE '2025-01-01'
    AND t.created_at < DATE '2026-01-01'
    AND t.sla_policy_id IS NOT NULL
),
bucketed AS (
  SELECT
    ticket_id,
    created_at,
    first_response_at,
    day_name,
    iso_day_of_week,
    CASE
      WHEN hr >= 0 AND hr < 6 THEN 'Night (00-06)'
      WHEN hr >= 6 AND hr < 12 THEN 'Morning (06-12)'
      WHEN hr >= 12 AND hr < 18 THEN 'Afternoon (12-18)'
      ELSE 'Evening (18-24)'
    END AS time_of_day_bucket,
    CASE
      WHEN hr >= 0 AND hr < 6 THEN 1
      WHEN hr >= 6 AND hr < 12 THEN 2
      WHEN hr >= 12 AND hr < 18 THEN 3
      ELSE 4
    END AS bucket_order
  FROM base
),
breaches AS (
  SELECT
    ticket_id,
    MAX(CASE WHEN breach_kind = 'first_response' THEN 1 ELSE 0 END) AS fr_breach,
    MAX(CASE WHEN breach_kind = 'resolution' THEN 1 ELSE 0 END) AS res_breach
  FROM sla_breaches
  GROUP BY ticket_id
),
joined AS (
  SELECT
    b.*,
    COALESCE(br.fr_breach, 0) AS fr_breach,
    COALESCE(br.res_breach, 0) AS res_breach
  FROM bucketed b
  LEFT JOIN breaches br ON br.ticket_id = b.ticket_id
)
SELECT
  day_name AS day_of_week,
  time_of_day_bucket,
  COUNT(*) AS ticket_count,
  ROUND(100.0 * SUM(fr_breach) / COUNT(*), 1) AS first_response_breach_rate,
  ROUND(100.0 * SUM(res_breach) / COUNT(*), 1) AS resolution_breach_rate,
  ROUND(AVG(CASE WHEN first_response_at IS NOT NULL
                 THEN EXTRACT(EPOCH FROM (first_response_at - created_at)) / 60.0
            END)) AS avg_first_response_minutes
FROM joined
GROUP BY day_name, iso_day_of_week, time_of_day_bucket, bucket_order
ORDER BY iso_day_of_week, bucket_order

“Query compiles cleanly with the expected 6 columns. Submitting now.”

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

“Submitted. One thing to flag for Ben: sla_breaches.breach_kind isn't an enumerated column in the catalog, so I assumed its values are 'first_response' and 'resolution' to match the ticket SLA timestamp naming — I noted this assumption in the submit note in case the actual codes differ and the breach rates need a quick correction.”

The report comes back overnight

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

day_of_week | time_of_day_bucket | ticket_count | first_response_breach_rate | resolution_breach_rate | avg_first_response_minutes
------------+--------------------+--------------+----------------------------+------------------------+---------------------------
Monday      | Night (00-06)      |           75 |                       30.7 |                   20.0 |                     1180.0
Monday      | Morning (06-12)    |           93 |                       41.9 |                   17.2 |                     1381.0
Monday      | Afternoon (12-18)  |          115 |                       33.9 |                   14.8 |                     1062.0
Monday      | Evening (18-24)    |           95 |                       35.8 |                   18.9 |                     1524.0
Tuesday     | Night (00-06)      |           75 |                       32.0 |                   16.0 |                     1414.0
Tuesday     | Morning (06-12)    |           87 |                       36.8 |                   21.8 |                     1149.0
Tuesday     | Afternoon (12-18)  |           95 |                       38.9 |                   16.8 |                     1226.0
Tuesday     | Evening (18-24)    |           95 |                       36.8 |                   28.4 |                     1305.0
Wednesday   | Night (00-06)      |           81 |                       38.3 |                   22.2 |                     1085.0
Wednesday   | Morning (06-12)    |          101 |                       35.6 |                   12.9 |                     1178.0
Wednesday   | Afternoon (12-18)  |          125 |                       39.2 |                   16.8 |                     1328.0
Wednesday   | Evening (18-24)    |           71 |                       38.0 |                   15.5 |                     1888.0
Thursday    | Night (00-06)      |           71 |                       35.2 |                   26.8 |                     2155.0
Thursday    | Morning (06-12)    |           76 |                       35.5 |                   19.7 |                     1610.0
Thursday    | Afternoon (12-18)  |           75 |                       41.3 |                   26.7 |                     1683.0
Thursday    | Evening (18-24)    |           75 |                       36.0 |                   17.3 |                     1406.0
Friday      | Night (00-06)      |           43 |                       48.8 |                   27.9 |                     1797.0
Friday      | Morning (06-12)    |           68 |                       33.8 |                   22.1 |                     1327.0
Friday      | Afternoon (12-18)  |           86 |                       37.2 |                   16.3 |                     1507.0
Friday      | Evening (18-24)    |           75 |                       33.3 |                   22.7 |                     1868.0
Saturday    | Night (00-06)      |           61 |                       45.9 |                   29.5 |                     2124.0
Saturday    | Morning (06-12)    |           54 |                       37.0 |                   31.5 |                     1718.0
Saturday    | Afternoon (12-18)  |           84 |                       35.7 |                   19.0 |                     2088.0
Saturday    | Evening (18-24)    |           91 |                       42.9 |                   25.3 |                     1954.0
Sunday      | Night (00-06)      |           81 |                       37.0 |                   22.2 |                     1804.0
Sunday      | Morning (06-12)    |           75 |                       38.7 |                   18.7 |                      996.0
Sunday      | Afternoon (12-18)  |          104 |                       47.1 |                   22.1 |                     1734.0
Sunday      | Evening (18-24)    |           93 |                       35.5 |                   14.0 |                     1309.0
(28 rows)

What came of it
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
- q202SLA breach_kind code audit and validation
It builds on:
- q198SLA breach rates by priority, with overall headline rate

It becomes benchmark query q201

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

spellingcast
determinismtie-breaker: calendar_days only keys calendar_date, so iso_day_of_week does not determine day_name
numericsdocs/NUMERICS.md: rounding removed; results keep full precision
numericsdocs/NUMERICS.md: EXTRACT(EPOCH FROM (b - a)) cast to NUMERIC(18,6)
made explicita selected t.col gets AS col, its standard name made explicit (4×)
placeholderseconds between
determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (3×)

The benchmark text, against the submitted SQL

WITH base AS (  SELECT    t.id AS ticket_id,    t.created_at,    t.first_response_at,    cd.day_name,    cd.iso_day_of_week,    EXTRACT(HOUR FROM t.created_at)::int AS hr    t.created_at AS created_at,    t.first_response_at AS first_response_at,    cd.day_name AS day_name,    cd.iso_day_of_week AS iso_day_of_week,    CAST(EXTRACT(HOUR FROM t.created_at) AS int) AS hr  FROM tickets t  JOIN calendar_days cd ON cd.calendar_date = CAST(t.created_at AS date)  WHERE t.created_at >= DATE '2025-01-01'    AND t.created_at < DATE '2026-01-01'    AND t.sla_policy_id IS NOT NULL),bucketed AS (  SELECT    ticket_id,    created_at,    first_response_at,    day_name,    iso_day_of_week,    CASE      WHEN hr >= 0 AND hr < 6 THEN 'Night (00-06)'      WHEN hr >= 6 AND hr < 12 THEN 'Morning (06-12)'      WHEN hr >= 12 AND hr < 18 THEN 'Afternoon (12-18)'      ELSE 'Evening (18-24)'    END AS time_of_day_bucket,    CASE      WHEN hr >= 0 AND hr < 6 THEN 1      WHEN hr >= 6 AND hr < 12 THEN 2      WHEN hr >= 12 AND hr < 18 THEN 3      ELSE 4    END AS bucket_order  FROM base),breaches AS (  SELECT    ticket_id,    MAX(CASE WHEN breach_kind = 'first_response' THEN 1 ELSE 0 END) AS fr_breach,    MAX(CASE WHEN breach_kind = 'resolution' THEN 1 ELSE 0 END) AS res_breach  FROM sla_breaches  GROUP BY ticket_id),joined AS (  SELECT    b.*,    COALESCE(br.fr_breach, 0) AS fr_breach,    COALESCE(br.res_breach, 0) AS res_breach  FROM bucketed b  LEFT JOIN breaches br ON br.ticket_id = b.ticket_id)SELECT  day_name AS day_of_week,  time_of_day_bucket,  COUNT(*) AS ticket_count,  ROUND(100.0 * SUM(fr_breach) / COUNT(*), 1) AS first_response_breach_rate,  ROUND(100.0 * SUM(res_breach) / COUNT(*), 1) AS resolution_breach_rate,  ROUND(AVG(CASE WHEN first_response_at IS NOT NULL                 THEN EXTRACT(EPOCH FROM (first_response_at - created_at)) / 60.0            END)) AS avg_first_response_minutes  100.0 * SUM(fr_breach) / COUNT(*) AS first_response_breach_rate,  100.0 * SUM(res_breach) / COUNT(*) AS resolution_breach_rate,  AVG(CASE WHEN first_response_at IS NOT NULL           THEN {{seconds_between(created_at, first_response_at)}} / 60.0      END) AS avg_first_response_minutesFROM joinedGROUP BY day_name, iso_day_of_week, time_of_day_bucket, bucket_orderORDER BY iso_day_of_week, bucket_orderORDER BY iso_day_of_week NULLS LAST, bucket_order NULLS LAST, day_name NULLS LAST

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

WITH base AS (
  SELECT
    t.id AS ticket_id,
    t.created_at AS created_at,
    t.first_response_at AS first_response_at,
    cd.day_name AS day_name,
    cd.iso_day_of_week AS iso_day_of_week,
    CAST(EXTRACT(HOUR FROM t.created_at) AS int) AS hr
  FROM tickets t
  JOIN calendar_days cd ON cd.calendar_date = CAST(t.created_at AS date)
  WHERE t.created_at >= DATE '2025-01-01'
    AND t.created_at < DATE '2026-01-01'
    AND t.sla_policy_id IS NOT NULL
),
bucketed AS (
  SELECT
    ticket_id,
    created_at,
    first_response_at,
    day_name,
    iso_day_of_week,
    CASE
      WHEN hr >= 0 AND hr < 6 THEN 'Night (00-06)'
      WHEN hr >= 6 AND hr < 12 THEN 'Morning (06-12)'
      WHEN hr >= 12 AND hr < 18 THEN 'Afternoon (12-18)'
      ELSE 'Evening (18-24)'
    END AS time_of_day_bucket,
    CASE
      WHEN hr >= 0 AND hr < 6 THEN 1
      WHEN hr >= 6 AND hr < 12 THEN 2
      WHEN hr >= 12 AND hr < 18 THEN 3
      ELSE 4
    END AS bucket_order
  FROM base
),
breaches AS (
  SELECT
    ticket_id,
    MAX(CASE WHEN breach_kind = 'first_response' THEN 1 ELSE 0 END) AS fr_breach,
    MAX(CASE WHEN breach_kind = 'resolution' THEN 1 ELSE 0 END) AS res_breach
  FROM sla_breaches
  GROUP BY ticket_id
),
joined AS (
  SELECT
    b.*,
    COALESCE(br.fr_breach, 0) AS fr_breach,
    COALESCE(br.res_breach, 0) AS res_breach
  FROM bucketed b
  LEFT JOIN breaches br ON br.ticket_id = b.ticket_id
)
SELECT
  day_name AS day_of_week,
  time_of_day_bucket,
  COUNT(*) AS ticket_count,
  100.0 * SUM(fr_breach) / COUNT(*) AS first_response_breach_rate,
  100.0 * SUM(res_breach) / COUNT(*) AS resolution_breach_rate,
  AVG(CASE WHEN first_response_at IS NOT NULL
           THEN CAST(EXTRACT(EPOCH FROM (first_response_at - created_at)) AS NUMERIC(18,6)) / 60.0
      END) AS avg_first_response_minutes
FROM joined
GROUP BY day_name, iso_day_of_week, time_of_day_bucket, bucket_order
ORDER BY iso_day_of_week NULLS LAST, bucket_order NULLS LAST, day_name NULLS LAST

WITH base AS (
  SELECT
    t.id AS ticket_id,
    t.created_at AS created_at,
    t.first_response_at AS first_response_at,
    cd.day_name AS day_name,
    cd.iso_day_of_week AS iso_day_of_week,
    CAST(EXTRACT(HOUR FROM t.created_at) AS int) AS hr
  FROM tickets t
  JOIN calendar_days cd ON cd.calendar_date = CAST(t.created_at AS date)
  WHERE t.created_at >= DATE '2025-01-01'
    AND t.created_at < DATE '2026-01-01'
    AND t.sla_policy_id IS NOT NULL
),
bucketed AS (
  SELECT
    ticket_id,
    created_at,
    first_response_at,
    day_name,
    iso_day_of_week,
    CASE
      WHEN hr >= 0 AND hr < 6 THEN 'Night (00-06)'
      WHEN hr >= 6 AND hr < 12 THEN 'Morning (06-12)'
      WHEN hr >= 12 AND hr < 18 THEN 'Afternoon (12-18)'
      ELSE 'Evening (18-24)'
    END AS time_of_day_bucket,
    CASE
      WHEN hr >= 0 AND hr < 6 THEN 1
      WHEN hr >= 6 AND hr < 12 THEN 2
      WHEN hr >= 12 AND hr < 18 THEN 3
      ELSE 4
    END AS bucket_order
  FROM base
),
breaches AS (
  SELECT
    ticket_id,
    MAX(CASE WHEN breach_kind = 'first_response' THEN 1 ELSE 0 END) AS fr_breach,
    MAX(CASE WHEN breach_kind = 'resolution' THEN 1 ELSE 0 END) AS res_breach
  FROM sla_breaches
  GROUP BY ticket_id
),
joined AS (
  SELECT
    b.*,
    COALESCE(br.fr_breach, 0) AS fr_breach,
    COALESCE(br.res_breach, 0) AS res_breach
  FROM bucketed b
  LEFT JOIN breaches br ON br.ticket_id = b.ticket_id
)
SELECT
  day_name AS day_of_week,
  time_of_day_bucket,
  COUNT(*) AS ticket_count,
  100.0 * SUM(fr_breach) / COUNT(*) AS first_response_breach_rate,
  100.0 * SUM(res_breach) / COUNT(*) AS resolution_breach_rate,
  AVG(CASE WHEN first_response_at IS NOT NULL
           THEN CAST(divideDecimal(toDecimal128(dateDiff('microsecond', created_at, first_response_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6))) / 60.0
      END) AS avg_first_response_minutes
FROM joined
GROUP BY day_name, iso_day_of_week, time_of_day_bucket, bucket_order
ORDER BY iso_day_of_week NULLS LAST, bucket_order NULLS LAST, day_name NULLS LAST

Scale factor 1000, c8a.24xlarge, after the last load. q201 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

Ines's whole session, 7 turns

The request, as Ines received it

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

Title: SLA breach rates by day of week and time of day
Question: Is there a time-of-day or day-of-week pattern to when we breach first-response and resolution SLA targets?
Grain: one row per combination of ticket creation day-of-week (Monday..Sunday) and time-of-day bucket (Night 00-06, Morning 06-12, Afternoon 12-18, Evening 18-24, all in UTC)
Columns:
- day_of_week
- time_of_day_bucket
- ticket_count (created 2025, sla_policy_id not null)
- first_response_breach_rate
- resolution_breach_rate
- avg_first_response_minutes
Details: Window: tickets.created_at between 2025-01-01 and 2025-12-31, restricted to tickets with sla_policy_id not null. Derive day-of-week from tickets.created_at (use calendar_days.day_name joined on the date, or extract directly) and a 6-hour UTC time bucket from the created_at time component. Source tables: tickets, sla_breaches, calendar_days. 28 rows total (7 days x 4 buckets); order by day_of_week (Monday first) then time_of_day_bucket in chronological order (Night, Morning, Afternoon, Evening). 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.

Ines calls dry_run

WITH base AS (
  SELECT
    t.id AS ticket_id,
    t.created_at,
    t.first_response_at,
    cd.day_name,
    cd.iso_day_of_week,
    EXTRACT(HOUR FROM t.created_at)::int AS hr
  FROM tickets t
  JOIN calendar_days cd ON cd.calendar_date = CAST(t.created_at AS date)
  WHERE t.created_at >= DATE '2025-01-01'
    AND t.created_at < DATE '2026-01-01'
    AND t.sla_policy_id IS NOT NULL
),
bucketed AS (
  SELECT
    ticket_id,
    created_at,
    first_response_at,
    day_name,
    iso_day_of_week,
    CASE
      WHEN hr >= 0 AND hr < 6 THEN 'Night (00-06)'
      WHEN hr >= 6 AND hr < 12 THEN 'Morning (06-12)'
      WHEN hr >= 12 AND hr < 18 THEN 'Afternoon (12-18)'
      ELSE 'Evening (18-24)'
    END AS time_of_day_bucket,
    CASE
      WHEN hr >= 0 AND hr < 6 THEN 1
      WHEN hr >= 6 AND hr < 12 THEN 2
      WHEN hr >= 12 AND hr < 18 THEN 3
      ELSE 4
    END AS bucket_order
  FROM base
),
breaches AS (
  SELECT
    ticket_id,
    MAX(CASE WHEN breach_kind = 'first_response' THEN 1 ELSE 0 END) AS fr_breach,
    MAX(CASE WHEN breach_kind = 'resolution' THEN 1 ELSE 0 END) AS res_breach
  FROM sla_breaches
  GROUP BY ticket_id
),
joined AS (
  SELECT
    b.*,
    COALESCE(br.fr_breach, 0) AS fr_breach,
    COALESCE(br.res_breach, 0) AS res_breach
  FROM bucketed b
  LEFT JOIN breaches br ON br.ticket_id = b.ticket_id
)
SELECT
  day_name AS day_of_week,
  time_of_day_bucket,
  COUNT(*) AS ticket_count,
  ROUND(100.0 * SUM(fr_breach) / COUNT(*), 1) AS first_response_breach_rate,
  ROUND(100.0 * SUM(res_breach) / COUNT(*), 1) AS resolution_breach_rate,
  ROUND(AVG(CASE WHEN first_response_at IS NOT NULL
                 THEN EXTRACT(EPOCH FROM (first_response_at - created_at)) / 60.0
            END)) AS avg_first_response_minutes
FROM joined
GROUP BY day_name, iso_day_of_week, time_of_day_bucket, bucket_order
ORDER BY iso_day_of_week, bucket_order

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: day_of_week, time_of_day_bucket, ticket_count, first_response_breach_rate, resolution_breach_rate, avg_first_response_minutes

InesQuery compiles cleanly with the expected 6 columns. Submitting now.

Ines calls submit

WITH base AS (
  SELECT
    t.id AS ticket_id,
    t.created_at,
    t.first_response_at,
    cd.day_name,
    cd.iso_day_of_week,
    EXTRACT(HOUR FROM t.created_at)::int AS hr
  FROM tickets t
  JOIN calendar_days cd ON cd.calendar_date = CAST(t.created_at AS date)
  WHERE t.created_at >= DATE '2025-01-01'
    AND t.created_at < DATE '2026-01-01'
    AND t.sla_policy_id IS NOT NULL
),
bucketed AS (
  SELECT
    ticket_id,
    created_at,
    first_response_at,
    day_name,
    iso_day_of_week,
    CASE
      WHEN hr >= 0 AND hr < 6 THEN 'Night (00-06)'
      WHEN hr >= 6 AND hr < 12 THEN 'Morning (06-12)'
      WHEN hr >= 12 AND hr < 18 THEN 'Afternoon (12-18)'
      ELSE 'Evening (18-24)'
    END AS time_of_day_bucket,
    CASE
      WHEN hr >= 0 AND hr < 6 THEN 1
      WHEN hr >= 6 AND hr < 12 THEN 2
      WHEN hr >= 12 AND hr < 18 THEN 3
      ELSE 4
    END AS bucket_order
  FROM base
),
breaches AS (
  SELECT
    ticket_id,
    MAX(CASE WHEN breach_kind = 'first_response' THEN 1 ELSE 0 END) AS fr_breach,
    MAX(CASE WHEN breach_kind = 'resolution' THEN 1 ELSE 0 END) AS res_breach
  FROM sla_breaches
  GROUP BY ticket_id
),
joined AS (
  SELECT
    b.*,
    COALESCE(br.fr_breach, 0) AS fr_breach,
    COALESCE(br.res_breach, 0) AS res_breach
  FROM bucketed b
  LEFT JOIN breaches br ON br.ticket_id = b.ticket_id
)
SELECT
  day_name AS day_of_week,
  time_of_day_bucket,
  COUNT(*) AS ticket_count,
  ROUND(100.0 * SUM(fr_breach) / COUNT(*), 1) AS first_response_breach_rate,
  ROUND(100.0 * SUM(res_breach) / COUNT(*), 1) AS resolution_breach_rate,
  ROUND(AVG(CASE WHEN first_response_at IS NOT NULL
                 THEN EXTRACT(EPOCH FROM (first_response_at - created_at)) / 60.0
            END)) AS avg_first_response_minutes
FROM joined
GROUP BY day_name, iso_day_of_week, time_of_day_bucket, bucket_order
ORDER BY iso_day_of_week, bucket_order

The warehouse answers

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

InesSubmitted. One thing to flag for Ben: `sla_breaches.breach_kind` isn't an enumerated column in the catalog, so I assumed its values are `'first_response'` and `'resolution'` to match the ticket SLA timestamp naming — I noted this assumption in the submit note in case the actual codes differ and the breach rates need a quick correction.

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

Ines's transcript (268 KB) · Ben Carter's transcript (813 KB) · Mia Schultz's transcript (260 KB) · the night's plan (4 KB) · the night's reports (17 KB) · the week's memo (3 KB)

SLA breach rates by day of week and time of day

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

Mia Schultz writes to Ben Carter

Ben Carter plans the night

Ines writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q201

Everything, as recorded