Queries · q253 · VP Customer Experience · week 2, night 4
“Does the load-correlation pattern found for urgent tickets (worse performance in the quarter each merchant's ticket-per-agent ratio spiked) also hold across all tickets, not just urgent ones?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
The manager, at the start of week 2
Subject: Good progress — now I want to understand the "why" behind the hotspots
Mateo — this is exactly the kind of baseline I needed, thank you. Two things from your memo and Mia's notes are sitting with me:
First, the speed→satisfaction link is strong and now confirmed two ways (yours and Mia's numbers line up almost exactly), so I believe it. But I don't yet know if slowness causes the dissatisfaction or if the worst merchants are simply worse across the board (harder products, different shopper expectations, something else) and speed is just the symptom we can see. That matters a lot for what I recommend as the fix.
Second, Mia's finding that three merchants account for a third of all tickets and the worst response times lines up with what you found independently — that's a strong signal, not a coincidence, and it's where I want to spend our next slots.
Questions for this week:
Let's prioritize #1 and #2 this week — those feel most actionable. #3 and #4 can wait if slots are tight.
Thanks, Daniel
The analyst · week 2, night 4, request 1 of 3
Night 3 wraps up the "why" story nicely: the quarterly urgent-ticket data shows resolution genuinely degrades in the exact quarter each merchant's load spiked (Cragsley/Marrowby worst in Q4 - e.g. Marrowby's avg urgent resolution hours jumps to 139.5h in Q4 vs 8.8-43.6h other quarters; Studio Vrij's catastrophic Q2 (219.5h avg) bleeds into Q3 (109h) before recovering to a healthy 3.9h in Q4, which is itself reassuring evidence of recovery). The Q4-vs-rest report shows the "flat headcount through a volume surge" pattern is NOT unique to the three named merchants - roughly half of the 22-merchant qualifying book shows a real Q4 ratio jump (Grind Collective's Q4 ratio, 23.3, is actually worse than Cragsley's), so this looks like a broad, platform-fixable seasonal-staffing gap rather than three merchants being uniquely mismanaged. The resolution-time-distribution report adds useful differentiation: Cragsley's slowness is spread broadly across most tickets (a genuine capacity shortfall), while Marrowby and Studio Vrij each have a distinct ~18-21% long tail of urgent tickets stuck past 7 days or still open - a different, more surgical fix (stuck-ticket escalation) than Cragsley's. For this final night, I want to (1) confirm the load-correlation effect holds across all priorities, not just urgent, to make sure the core finding generalizes, (2) get a quick merchant-profile cut (industry, size, order volume) across the qualifying book to address Daniel's question #4 about whether "something else" (not just speed) explains the hotspots, and (3) check whether the long tails at Marrowby/Studio Vrij are concentrated in one or two agents (a coaching fix) or spread across their whole team (a true capacity fix) - directly useful for recommending a fix in the memo.
The engineer · 1 dry run on the empty schema, then the submission
WITH filtered_tickets AS ( SELECT tk.id, t.name AS merchant_name, extract(year from tk.created_at) AS yr, extract(quarter from tk.created_at) AS qtr, tk.created_at, tk.resolved_at, EXISTS (SELECT 1 FROM sla_breaches sb WHERE sb.ticket_id = tk.id) AS has_breach FROM tickets tk JOIN tenants t ON tk.tenant_id = t.id WHERE t.name IN ('Cragsley Outfitters','Marrowby & Vane','Studio Vrij','Grind Collective') AND tk.created_at >= DATE '2025-01-01' AND tk.created_at < DATE '2026-01-01' ) SELECT merchant_name, yr::text || '-Q' || qtr::text AS quarter, count(*) AS tickets_created, round(avg(extract(epoch from (resolved_at - created_at))/3600) FILTER (WHERE resolved_at IS NOT NULL)::numeric, 1) AS avg_resolution_hours, round(percentile_cont(0.5) WITHIN GROUP (ORDER BY extract(epoch from (resolved_at - created_at))/3600) FILTER (WHERE resolved_at IS NOT NULL)::numeric, 1) AS median_resolution_hours, round(100.0 * count(*) FILTER (WHERE has_breach) / count(*), 1) AS sla_breach_rate_pct FROM filtered_tickets GROUP BY merchant_name, yr, qtr ORDER BY merchant_name, yr, qtr
“Looks good. Submitting.”
“Submitted. The report computes, for each of the four named merchants and each 2025 quarter (grained on tickets.created_at): tickets created, avg and median resolution hours (all priorities, resolved tickets only), and SLA breach rate (% of tickets with ≥1 linked sla_breaches row), matching the definition used in the prior monthly trend report.”
As Mateo Ruiz saw it, from the small dataset the agents worked on
merchant_name | quarter | tickets_created | avg_resolution_hours | median_resolution_hours | sla_breach_rate_pct --------------------+---------+-----------------+----------------------+-------------------------+-------------------- Cragsley Outfitters | 2025-Q1 | 76 | 64.9 | 21.2 | 60.5 Cragsley Outfitters | 2025-Q2 | 81 | 21.0 | 14.7 | 53.1 Cragsley Outfitters | 2025-Q3 | 92 | 28.7 | 17.5 | 54.3 Cragsley Outfitters | 2025-Q4 | 151 | 42.4 | 27.6 | 70.2 Grind Collective | 2025-Q1 | 12 | 9.5 | 3.5 | 8.3 Grind Collective | 2025-Q2 | 25 | 16.9 | 12.7 | 12.0 Grind Collective | 2025-Q3 | 15 | 15.4 | 10.9 | 13.3 Grind Collective | 2025-Q4 | 70 | 103.6 | 58.4 | 70.0 Marrowby & Vane | 2025-Q1 | 46 | 115.0 | 31.2 | 71.7 Marrowby & Vane | 2025-Q2 | 37 | 14.4 | 14.1 | 40.5 Marrowby & Vane | 2025-Q3 | 43 | 16.4 | 13.5 | 41.9 Marrowby & Vane | 2025-Q4 | 105 | 131.3 | 62.6 | 72.4 Studio Vrij | 2025-Q1 | 13 | 20.8 | 17.3 | 53.8 Studio Vrij | 2025-Q2 | 96 | 198.0 | 94.9 | 66.7 Studio Vrij | 2025-Q3 | 18 | 41.7 | 19.5 | 38.9 Studio Vrij | 2025-Q4 | 39 | 16.0 | 9.9 | 41.0 (16 rows)
The memo and the queries that followed; links found by title, so inferred
It builds on:
Changed only where the benchmark needs the same result on every system
WITH filtered_tickets AS ( SELECT tk.id, t.name AS merchant_name, SELECT tk.id AS id, t.name AS merchant_name, extract(year from tk.created_at) AS yr, extract(quarter from tk.created_at) AS qtr, tk.created_at, tk.resolved_at, tk.created_at AS created_at, tk.resolved_at AS resolved_at, EXISTS (SELECT 1 FROM sla_breaches sb WHERE sb.ticket_id = tk.id) AS has_breach FROM tickets tk JOIN tenants t ON tk.tenant_id = t.id WHERE t.name IN ('Cragsley Outfitters','Marrowby & Vane','Studio Vrij','Grind Collective') AND tk.created_at >= DATE '2025-01-01' AND tk.created_at < DATE '2026-01-01')SELECT merchant_name, yr::text || '-Q' || qtr::text AS quarter, CAST(yr AS text) || '-Q' || CAST(qtr AS text) AS quarter, count(*) AS tickets_created, round(avg(extract(epoch from (resolved_at - created_at))/3600) FILTER (WHERE resolved_at IS NOT NULL)::numeric, 1) AS avg_resolution_hours, round(percentile_cont(0.5) WITHIN GROUP (ORDER BY extract(epoch from (resolved_at - created_at))/3600) FILTER (WHERE resolved_at IS NOT NULL)::numeric, 1) AS median_resolution_hours, round(100.0 * count(*) FILTER (WHERE has_breach) / count(*), 1) AS sla_breach_rate_pct avg({{seconds_between(created_at, resolved_at)}}/3600) FILTER (WHERE resolved_at IS NOT NULL) AS avg_resolution_hours, {{percentile_cont_filter(0.5, {{seconds_between(created_at, resolved_at)}}/3600, resolved_at IS NOT NULL)}} AS median_resolution_hours, 100.0 * count(*) FILTER (WHERE has_breach) / count(*) AS sla_breach_rate_pctFROM filtered_ticketsGROUP BY merchant_name, yr, qtrORDER BY merchant_name, yr, qtrORDER BY merchant_name NULLS LAST, yr NULLS LAST, qtr NULLS LAST
WITH filtered_tickets AS ( SELECT tk.id AS id, t.name AS merchant_name, extract(year from tk.created_at) AS yr, extract(quarter from tk.created_at) AS qtr, tk.created_at AS created_at, tk.resolved_at AS resolved_at, EXISTS (SELECT 1 FROM sla_breaches sb WHERE sb.ticket_id = tk.id) AS has_breach FROM tickets tk JOIN tenants t ON tk.tenant_id = t.id WHERE t.name IN ('Cragsley Outfitters','Marrowby & Vane','Studio Vrij','Grind Collective') AND tk.created_at >= DATE '2025-01-01' AND tk.created_at < DATE '2026-01-01' ) SELECT merchant_name, CAST(yr AS text) || '-Q' || CAST(qtr AS text) AS quarter, count(*) AS tickets_created, avg(CAST(EXTRACT(EPOCH FROM (resolved_at - created_at)) AS NUMERIC(18,6))/3600) FILTER (WHERE resolved_at IS NOT NULL) AS avg_resolution_hours, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CAST(EXTRACT(EPOCH FROM (resolved_at - created_at)) AS NUMERIC(18,6))/3600) FILTER (WHERE resolved_at IS NOT NULL) AS median_resolution_hours, 100.0 * count(*) FILTER (WHERE has_breach) / count(*) AS sla_breach_rate_pct FROM filtered_tickets GROUP BY merchant_name, yr, qtr ORDER BY merchant_name NULLS LAST, yr NULLS LAST, qtr NULLS LAST
WITH filtered_tickets AS ( SELECT tk.id AS id, t.name AS merchant_name, extract(year from tk.created_at) AS yr, extract(quarter from tk.created_at) AS qtr, tk.created_at AS created_at, tk.resolved_at AS resolved_at, EXISTS (SELECT 1 FROM sla_breaches sb WHERE sb.ticket_id = tk.id) AS has_breach FROM tickets tk JOIN tenants t ON tk.tenant_id = t.id WHERE t.name IN ('Cragsley Outfitters','Marrowby & Vane','Studio Vrij','Grind Collective') AND tk.created_at >= DATE '2025-01-01' AND tk.created_at < DATE '2026-01-01' ) SELECT merchant_name, CAST(yr AS text) || '-Q' || CAST(qtr AS text) AS quarter, count(*) AS tickets_created, avg(CAST(EXTRACT(EPOCH FROM (resolved_at - created_at)) AS NUMERIC(18,6))/3600) FILTER (WHERE resolved_at IS NOT NULL) AS avg_resolution_hours, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CASE WHEN resolved_at IS NOT NULL THEN CAST(EXTRACT(EPOCH FROM (resolved_at - created_at)) AS NUMERIC(18,6))/3600 END) AS median_resolution_hours, 100.0 * count(*) FILTER (WHERE has_breach) / count(*) AS sla_breach_rate_pct FROM filtered_tickets GROUP BY merchant_name, yr, qtr ORDER BY merchant_name NULLS LAST, yr NULLS LAST, qtr NULLS LAST
WITH filtered_tickets AS ( SELECT tk.id AS id, t.name AS merchant_name, extract(year from tk.created_at) AS yr, extract(quarter from tk.created_at) AS qtr, tk.created_at AS created_at, tk.resolved_at AS resolved_at, EXISTS (SELECT 1 FROM sla_breaches sb WHERE sb.ticket_id = tk.id) AS has_breach FROM tickets tk JOIN tenants t ON tk.tenant_id = t.id WHERE t.name IN ('Cragsley Outfitters','Marrowby & Vane','Studio Vrij','Grind Collective') AND tk.created_at >= DATE '2025-01-01' AND tk.created_at < DATE '2026-01-01' ) SELECT merchant_name, CAST(yr AS text) || '-Q' || CAST(qtr AS text) AS quarter, count(*) AS tickets_created, avg(CAST(EXTRACT(EPOCH FROM (resolved_at - created_at)) AS NUMERIC(18,6))/3600) FILTER (WHERE resolved_at IS NOT NULL) AS avg_resolution_hours, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CAST(CAST(EXTRACT(EPOCH FROM (resolved_at - created_at)) AS NUMERIC(18,6))/3600 AS DOUBLE PRECISION)) FILTER (WHERE resolved_at IS NOT NULL) AS median_resolution_hours, 100.0 * count(*) FILTER (WHERE has_breach) / count(*) AS sla_breach_rate_pct FROM filtered_tickets GROUP BY merchant_name, yr, qtr ORDER BY merchant_name NULLS LAST, yr NULLS LAST, qtr NULLS LAST
WITH filtered_tickets AS ( SELECT tk.id AS id, t.name AS merchant_name, extract(year from tk.created_at) AS yr, extract(quarter from tk.created_at) AS qtr, tk.created_at AS created_at, tk.resolved_at AS resolved_at, EXISTS (SELECT 1 FROM sla_breaches sb WHERE sb.ticket_id = tk.id) AS has_breach FROM tickets tk JOIN tenants t ON tk.tenant_id = t.id WHERE t.name IN ('Cragsley Outfitters','Marrowby & Vane','Studio Vrij','Grind Collective') AND tk.created_at >= DATE '2025-01-01' AND tk.created_at < DATE '2026-01-01' ) SELECT merchant_name, CAST(yr AS text) || '-Q' || CAST(qtr AS text) AS quarter, count(*) AS tickets_created, avg(CAST(divideDecimal(toDecimal128(dateDiff('microsecond', created_at, resolved_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6)))/3600) FILTER (WHERE resolved_at IS NOT NULL) AS avg_resolution_hours, quantileExactInclusiveIf(0.5)(toFloat64(CAST(divideDecimal(toDecimal128(dateDiff('microsecond', created_at, resolved_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6)))/3600), resolved_at IS NOT NULL) AS median_resolution_hours, 100.0 * count(*) FILTER (WHERE has_breach) / count(*) AS sla_breach_rate_pct FROM filtered_tickets GROUP BY merchant_name, yr, qtr ORDER BY merchant_name NULLS LAST, yr NULLS LAST, qtr NULLS LAST
The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted
Report request from Mateo Ruiz (analyst for the VP Customer Experience): Title: Quarterly all-priority ticket performance vs load, hotspot merchants Question: Does the load-correlation pattern found for urgent tickets (worse performance in the quarter each merchant's ticket-per-agent ratio spiked) also hold across all tickets, not just urgent ones? Grain: one row per tenant and calendar quarter in 2025, for Cragsley Outfitters, Marrowby & Vane, Studio Vrij, and Grind Collective, all priorities Columns: - merchant name - quarter (2025-Q1..Q4) - tickets created - avg resolution hours (all priorities) - median resolution hours (all priorities) - SLA breach rate (%) Details: Source: tickets joined to sla_breaches (for breach rate) and tenants, filtered to the four named tenants. Grain the quarter on tickets.created_at within 2025, all priorities included. avg/median resolution hours = extract(epoch from resolved_at - created_at)/3600 over tickets with resolved_at not null that quarter. SLA breach rate = distinct tickets that quarter with >=1 row in sla_breaches ÷ tickets created that quarter, percentage rounded to 1 decimal (same definition used in the original monthly trend report from two weeks ago). Round resolution hours to 1 decimal. Order by merchant name ascending, then quarter ascending. Expect 16 rows; this is meant to sit alongside the already-collected quarterly tickets-per-agent figures (from 'Ticket load per agent over 2025') for comparison, so no need to repeat that column here. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runWITH filtered_tickets AS ( SELECT tk.id, t.name AS merchant_name, extract(year from tk.created_at) AS yr, extract(quarter from tk.created_at) AS qtr, tk.created_at, tk.resolved_at, EXISTS (SELECT 1 FROM sla_breaches sb WHERE sb.ticket_id = tk.id) AS has_breach FROM tickets tk JOIN tenants t ON tk.tenant_id = t.id WHERE t.name IN ('Cragsley Outfitters','Marrowby & Vane','Studio Vrij','Grind Collective') AND tk.created_at >= DATE '2025-01-01' AND tk.created_at < DATE '2026-01-01' ) SELECT merchant_name, yr::text || '-Q' || qtr::text AS quarter, count(*) AS tickets_created, round(avg(extract(epoch from (resolved_at - created_at))/3600) FILTER (WHERE resolved_at IS NOT NULL)::numeric, 1) AS avg_resolution_hours, round(percentile_cont(0.5) WITHIN GROUP (ORDER BY extract(epoch from (resolved_at - created_at))/3600) FILTER (WHERE resolved_at IS NOT NULL)::numeric, 1) AS median_resolution_hours, round(100.0 * count(*) FILTER (WHERE has_breach) / count(*), 1) AS sla_breach_rate_pct FROM filtered_tickets GROUP BY merchant_name, yr, qtr ORDER BY merchant_name, yr, qtr
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: merchant_name, quarter, tickets_created, avg_resolution_hours, median_resolution_hours, sla_breach_rate_pct
submitWITH filtered_tickets AS ( SELECT tk.id, t.name AS merchant_name, extract(year from tk.created_at) AS yr, extract(quarter from tk.created_at) AS qtr, tk.created_at, tk.resolved_at, EXISTS (SELECT 1 FROM sla_breaches sb WHERE sb.ticket_id = tk.id) AS has_breach FROM tickets tk JOIN tenants t ON tk.tenant_id = t.id WHERE t.name IN ('Cragsley Outfitters','Marrowby & Vane','Studio Vrij','Grind Collective') AND tk.created_at >= DATE '2025-01-01' AND tk.created_at < DATE '2026-01-01' ) SELECT merchant_name, yr::text || '-Q' || qtr::text AS quarter, count(*) AS tickets_created, round(avg(extract(epoch from (resolved_at - created_at))/3600) FILTER (WHERE resolved_at IS NOT NULL)::numeric, 1) AS avg_resolution_hours, round(percentile_cont(0.5) WITHIN GROUP (ORDER BY extract(epoch from (resolved_at - created_at))/3600) FILTER (WHERE resolved_at IS NOT NULL)::numeric, 1) AS median_resolution_hours, round(100.0 * count(*) FILTER (WHERE has_breach) / count(*), 1) AS sla_breach_rate_pct FROM filtered_tickets GROUP BY merchant_name, yr, qtr ORDER BY merchant_name, yr, qtr
Submitted to tonight's batch. Your work on this request is done.