Queries · q252 · VP Customer Experience · week 2, night 3
“Is the big three's inflated average urgent resolution time caused by a handful of extreme-delay tickets, or by generally slow handling across the board?”
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 3, request 3 of 3
Night 2 results are decisive on complexity: Cragsley (1.36 tags/ticket, 3.46 messages/ticket) and Marrowby (1.43, 3.29) are actually at the LOW end of complexity among all 22 qualifying merchants - Studio Vrij is mid-pack (1.64, 3.71). So "harder issues" is refuted as an explanation; it really does look like a volume/staffing story. The SLA-target report also refutes the "unrealistic target" theory for the broad urgent-miss problem - every merchant evaluated uses the identical 8-hour urgent target, so the wide spread in miss rates (14%-100%) reflects genuine differences in execution speed, not target calibration. The quarterly load report is the key new finding: all three hotspots (and comparison peer Grind Collective) run a completely flat agent headcount all four quarters, but ticket volume surges hard in specific quarters - Cragsley and Marrowby both spike in Q4 (tickets-per-agent roughly doubles vs Q1-Q3), Studio Vrij spikes specifically in Q2 (the already-known fulfillment incident), and Grind Collective also spikes in Q4 even harder than Cragsley. That's a strong lead: nobody scales support staff for volume surges, and it's not unique to the three named merchants. I want to (1) confirm urgent-ticket performance specifically degrades in the same quarters where each merchant's load spiked (a within-merchant causal test), (2) check whether this "flat staffing through a seasonal/incident surge" pattern is common across the whole qualifying book, not just these four - that determines if it's a platform-level guidance opportunity, and (3) characterize whether the big three's inflated average (vs. median) urgent resolution time is a few catastrophic outlier tickets or generally slow handling, since that changes what the fix would look like (triage/escalation vs. core speed).
The engineer · 1 dry run on the empty schema, then the submission
SELECT t.name AS merchant_name, COUNT(*) AS urgent_tickets_evaluated, ROUND(100.0 * SUM(CASE WHEN b.bucket = 1 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_8h, ROUND(100.0 * SUM(CASE WHEN b.bucket = 2 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_8h_24h, ROUND(100.0 * SUM(CASE WHEN b.bucket = 3 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_1d_3d, ROUND(100.0 * SUM(CASE WHEN b.bucket = 4 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_3d_7d, ROUND(100.0 * SUM(CASE WHEN b.bucket = 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_over_7d_or_unresolved FROM ( SELECT tk.id, tk.tenant_id, CASE WHEN tk.resolved_at IS NULL THEN 5 WHEN extract(epoch FROM tk.resolved_at - tk.created_at) / 3600.0 <= 8 THEN 1 WHEN extract(epoch FROM tk.resolved_at - tk.created_at) / 3600.0 <= 24 THEN 2 WHEN extract(epoch FROM tk.resolved_at - tk.created_at) / 3600.0 <= 72 THEN 3 WHEN extract(epoch FROM tk.resolved_at - tk.created_at) / 3600.0 <= 168 THEN 4 ELSE 5 END AS bucket FROM tickets tk JOIN sla_policies sp ON sp.id = tk.sla_policy_id WHERE tk.priority = 'urgent' AND tk.sla_policy_id IS NOT NULL AND tk.created_at >= timestamp '2025-01-01' AND tk.created_at < timestamp '2026-01-01' AND tk.tenant_id IN ( SELECT id FROM tenants WHERE name IN ('Cragsley Outfitters', 'Marrowby & Vane', 'Studio Vrij', 'Grind Collective', 'Wiesgrund Wohnen') ) ) b JOIN tenants t ON t.id = b.tenant_id GROUP BY t.name ORDER BY t.name ASC
“Query compiles cleanly with the expected columns. Submitting.”
“Submitted. The report returns one row per tenant (5 rows, ordered by merchant name) with urgent-ticket counts and the five resolution-time bucket percentages for 2025, as specified.”
As Mateo Ruiz saw it, from the small dataset the agents worked on
merchant_name | urgent_tickets_evaluated | pct_within_8h | pct_8h_24h | pct_1d_3d | pct_3d_7d | pct_over_7d_or_unresolved --------------------+--------------------------+---------------+------------+-----------+-----------+-------------------------- Cragsley Outfitters | 50 | 34.0 | 34.0 | 18.0 | 10.0 | 4.0 Grind Collective | 6 | 83.3 | 16.7 | 0.0 | 0.0 | 0.0 Marrowby & Vane | 33 | 33.3 | 18.2 | 9.1 | 18.2 | 21.2 Studio Vrij | 11 | 36.4 | 18.2 | 27.3 | 0.0 | 18.2 Wiesgrund Wohnen | 7 | 85.7 | 14.3 | 0.0 | 0.0 | 0.0 (5 rows)
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
It builds on:
Changed only where the benchmark needs the same result on every system
SELECT t.name AS merchant_name, COUNT(*) AS urgent_tickets_evaluated, ROUND(100.0 * SUM(CASE WHEN b.bucket = 1 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_8h, ROUND(100.0 * SUM(CASE WHEN b.bucket = 2 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_8h_24h, ROUND(100.0 * SUM(CASE WHEN b.bucket = 3 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_1d_3d, ROUND(100.0 * SUM(CASE WHEN b.bucket = 4 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_3d_7d, ROUND(100.0 * SUM(CASE WHEN b.bucket = 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_over_7d_or_unresolved 100.0 * SUM(CASE WHEN b.bucket = 1 THEN 1 ELSE 0 END) / COUNT(*) AS pct_within_8h, 100.0 * SUM(CASE WHEN b.bucket = 2 THEN 1 ELSE 0 END) / COUNT(*) AS pct_8h_24h, 100.0 * SUM(CASE WHEN b.bucket = 3 THEN 1 ELSE 0 END) / COUNT(*) AS pct_1d_3d, 100.0 * SUM(CASE WHEN b.bucket = 4 THEN 1 ELSE 0 END) / COUNT(*) AS pct_3d_7d, 100.0 * SUM(CASE WHEN b.bucket = 5 THEN 1 ELSE 0 END) / COUNT(*) AS pct_over_7d_or_unresolvedFROM ( SELECT tk.id, tk.tenant_id, tk.id AS id, tk.tenant_id AS tenant_id, CASE WHEN tk.resolved_at IS NULL THEN 5 WHEN extract(epoch FROM tk.resolved_at - tk.created_at) / 3600.0 <= 8 THEN 1 WHEN extract(epoch FROM tk.resolved_at - tk.created_at) / 3600.0 <= 24 THEN 2 WHEN extract(epoch FROM tk.resolved_at - tk.created_at) / 3600.0 <= 72 THEN 3 WHEN extract(epoch FROM tk.resolved_at - tk.created_at) / 3600.0 <= 168 THEN 4 WHEN {{seconds_between(tk.created_at, tk.resolved_at)}} / 3600.0 <= 8 THEN 1 WHEN {{seconds_between(tk.created_at, tk.resolved_at)}} / 3600.0 <= 24 THEN 2 WHEN {{seconds_between(tk.created_at, tk.resolved_at)}} / 3600.0 <= 72 THEN 3 WHEN {{seconds_between(tk.created_at, tk.resolved_at)}} / 3600.0 <= 168 THEN 4 ELSE 5 END AS bucket FROM tickets tk JOIN sla_policies sp ON sp.id = tk.sla_policy_id WHERE tk.priority = 'urgent' AND tk.sla_policy_id IS NOT NULL AND tk.created_at >= timestamp '2025-01-01' AND tk.created_at < timestamp '2026-01-01' AND tk.tenant_id IN ( SELECT id FROM tenants WHERE name IN ('Cragsley Outfitters', 'Marrowby & Vane', 'Studio Vrij', 'Grind Collective', 'Wiesgrund Wohnen') )) bJOIN tenants t ON t.id = b.tenant_idGROUP BY t.nameORDER BY t.name ASCORDER BY t.name ASC NULLS LAST
SELECT t.name AS merchant_name, COUNT(*) AS urgent_tickets_evaluated, 100.0 * SUM(CASE WHEN b.bucket = 1 THEN 1 ELSE 0 END) / COUNT(*) AS pct_within_8h, 100.0 * SUM(CASE WHEN b.bucket = 2 THEN 1 ELSE 0 END) / COUNT(*) AS pct_8h_24h, 100.0 * SUM(CASE WHEN b.bucket = 3 THEN 1 ELSE 0 END) / COUNT(*) AS pct_1d_3d, 100.0 * SUM(CASE WHEN b.bucket = 4 THEN 1 ELSE 0 END) / COUNT(*) AS pct_3d_7d, 100.0 * SUM(CASE WHEN b.bucket = 5 THEN 1 ELSE 0 END) / COUNT(*) AS pct_over_7d_or_unresolved FROM ( SELECT tk.id AS id, tk.tenant_id AS tenant_id, CASE WHEN tk.resolved_at IS NULL THEN 5 WHEN CAST(EXTRACT(EPOCH FROM (tk.resolved_at - tk.created_at)) AS NUMERIC(18,6)) / 3600.0 <= 8 THEN 1 WHEN CAST(EXTRACT(EPOCH FROM (tk.resolved_at - tk.created_at)) AS NUMERIC(18,6)) / 3600.0 <= 24 THEN 2 WHEN CAST(EXTRACT(EPOCH FROM (tk.resolved_at - tk.created_at)) AS NUMERIC(18,6)) / 3600.0 <= 72 THEN 3 WHEN CAST(EXTRACT(EPOCH FROM (tk.resolved_at - tk.created_at)) AS NUMERIC(18,6)) / 3600.0 <= 168 THEN 4 ELSE 5 END AS bucket FROM tickets tk JOIN sla_policies sp ON sp.id = tk.sla_policy_id WHERE tk.priority = 'urgent' AND tk.sla_policy_id IS NOT NULL AND tk.created_at >= timestamp '2025-01-01' AND tk.created_at < timestamp '2026-01-01' AND tk.tenant_id IN ( SELECT id FROM tenants WHERE name IN ('Cragsley Outfitters', 'Marrowby & Vane', 'Studio Vrij', 'Grind Collective', 'Wiesgrund Wohnen') ) ) b JOIN tenants t ON t.id = b.tenant_id GROUP BY t.name ORDER BY t.name ASC NULLS LAST
SELECT t.name AS merchant_name, COUNT(*) AS urgent_tickets_evaluated, 100.0 * SUM(CASE WHEN b.bucket = 1 THEN 1 ELSE 0 END) / COUNT(*) AS pct_within_8h, 100.0 * SUM(CASE WHEN b.bucket = 2 THEN 1 ELSE 0 END) / COUNT(*) AS pct_8h_24h, 100.0 * SUM(CASE WHEN b.bucket = 3 THEN 1 ELSE 0 END) / COUNT(*) AS pct_1d_3d, 100.0 * SUM(CASE WHEN b.bucket = 4 THEN 1 ELSE 0 END) / COUNT(*) AS pct_3d_7d, 100.0 * SUM(CASE WHEN b.bucket = 5 THEN 1 ELSE 0 END) / COUNT(*) AS pct_over_7d_or_unresolved FROM ( SELECT tk.id AS id, tk.tenant_id AS tenant_id, CASE WHEN tk.resolved_at IS NULL THEN 5 WHEN CAST(divideDecimal(toDecimal128(dateDiff('microsecond', tk.created_at, tk.resolved_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6))) / 3600.0 <= 8 THEN 1 WHEN CAST(divideDecimal(toDecimal128(dateDiff('microsecond', tk.created_at, tk.resolved_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6))) / 3600.0 <= 24 THEN 2 WHEN CAST(divideDecimal(toDecimal128(dateDiff('microsecond', tk.created_at, tk.resolved_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6))) / 3600.0 <= 72 THEN 3 WHEN CAST(divideDecimal(toDecimal128(dateDiff('microsecond', tk.created_at, tk.resolved_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6))) / 3600.0 <= 168 THEN 4 ELSE 5 END AS bucket FROM tickets tk JOIN sla_policies sp ON sp.id = tk.sla_policy_id WHERE tk.priority = 'urgent' AND tk.sla_policy_id IS NOT NULL AND tk.created_at >= timestamp '2025-01-01' AND tk.created_at < timestamp '2026-01-01' AND tk.tenant_id IN ( SELECT id FROM tenants WHERE name IN ('Cragsley Outfitters', 'Marrowby & Vane', 'Studio Vrij', 'Grind Collective', 'Wiesgrund Wohnen') ) ) b JOIN tenants t ON t.id = b.tenant_id GROUP BY t.name ORDER BY t.name ASC 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: Urgent-ticket resolution-time distribution, slow merchants vs a fast baseline
Question: Is the big three's inflated average urgent resolution time caused by a handful of extreme-delay tickets, or by generally slow handling across the board?
Grain: one row per tenant, for Cragsley Outfitters, Marrowby & Vane, Studio Vrij, Grind Collective, and Wiesgrund Wohnen (included as a fast-performing baseline), urgent-priority tickets only, 2025
Columns:
- merchant name
- urgent tickets evaluated
- % resolved within 8h (meets target)
- % resolved 8-24h
- % resolved 1-3 days
- % resolved 3-7 days
- % resolved after 7 days or still unresolved as of 2025-12-31
Details: Source: tickets (priority='urgent') joined to sla_policies via tickets.sla_policy_id, filtered to the five named tenants. Window: tickets.created_at in 2025-01-01 through 2025-12-31, excluding tickets with null sla_policy_id (same evaluable definition as prior reports). resolution_hours = extract(epoch from resolved_at - created_at)/3600 for tickets with resolved_at not null; tickets with resolved_at null as of 2025-12-31 fall into the last bucket ('after 7 days or still unresolved') regardless of elapsed time. Bucket boundaries: <=8h, >8-24h, >24-72h (1-3 days), >72-168h (3-7 days), >168h or unresolved. Each tenant's five percentages should sum to ~100 (rounded to 1 decimal each). Order by merchant name ascending. Expect exactly 5 rows.
Check your query with the warehouse's dry_run tool, then hand it in with submit.dry_runSELECT t.name AS merchant_name, COUNT(*) AS urgent_tickets_evaluated, ROUND(100.0 * SUM(CASE WHEN b.bucket = 1 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_8h, ROUND(100.0 * SUM(CASE WHEN b.bucket = 2 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_8h_24h, ROUND(100.0 * SUM(CASE WHEN b.bucket = 3 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_1d_3d, ROUND(100.0 * SUM(CASE WHEN b.bucket = 4 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_3d_7d, ROUND(100.0 * SUM(CASE WHEN b.bucket = 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_over_7d_or_unresolved FROM ( SELECT tk.id, tk.tenant_id, CASE WHEN tk.resolved_at IS NULL THEN 5 WHEN extract(epoch FROM tk.resolved_at - tk.created_at) / 3600.0 <= 8 THEN 1 WHEN extract(epoch FROM tk.resolved_at - tk.created_at) / 3600.0 <= 24 THEN 2 WHEN extract(epoch FROM tk.resolved_at - tk.created_at) / 3600.0 <= 72 THEN 3 WHEN extract(epoch FROM tk.resolved_at - tk.created_at) / 3600.0 <= 168 THEN 4 ELSE 5 END AS bucket FROM tickets tk JOIN sla_policies sp ON sp.id = tk.sla_policy_id WHERE tk.priority = 'urgent' AND tk.sla_policy_id IS NOT NULL AND tk.created_at >= timestamp '2025-01-01' AND tk.created_at < timestamp '2026-01-01' AND tk.tenant_id IN ( SELECT id FROM tenants WHERE name IN ('Cragsley Outfitters', 'Marrowby & Vane', 'Studio Vrij', 'Grind Collective', 'Wiesgrund Wohnen') ) ) b JOIN tenants t ON t.id = b.tenant_id GROUP BY t.name ORDER BY t.name ASC
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: merchant_name, urgent_tickets_evaluated, pct_within_8h, pct_8h_24h, pct_1d_3d, pct_3d_7d, pct_over_7d_or_unresolved
submitSELECT t.name AS merchant_name, COUNT(*) AS urgent_tickets_evaluated, ROUND(100.0 * SUM(CASE WHEN b.bucket = 1 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_8h, ROUND(100.0 * SUM(CASE WHEN b.bucket = 2 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_8h_24h, ROUND(100.0 * SUM(CASE WHEN b.bucket = 3 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_1d_3d, ROUND(100.0 * SUM(CASE WHEN b.bucket = 4 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_3d_7d, ROUND(100.0 * SUM(CASE WHEN b.bucket = 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_over_7d_or_unresolved FROM ( SELECT tk.id, tk.tenant_id, CASE WHEN tk.resolved_at IS NULL THEN 5 WHEN extract(epoch FROM tk.resolved_at - tk.created_at) / 3600.0 <= 8 THEN 1 WHEN extract(epoch FROM tk.resolved_at - tk.created_at) / 3600.0 <= 24 THEN 2 WHEN extract(epoch FROM tk.resolved_at - tk.created_at) / 3600.0 <= 72 THEN 3 WHEN extract(epoch FROM tk.resolved_at - tk.created_at) / 3600.0 <= 168 THEN 4 ELSE 5 END AS bucket FROM tickets tk JOIN sla_policies sp ON sp.id = tk.sla_policy_id WHERE tk.priority = 'urgent' AND tk.sla_policy_id IS NOT NULL AND tk.created_at >= timestamp '2025-01-01' AND tk.created_at < timestamp '2026-01-01' AND tk.tenant_id IN ( SELECT id FROM tenants WHERE name IN ('Cragsley Outfitters', 'Marrowby & Vane', 'Studio Vrij', 'Grind Collective', 'Wiesgrund Wohnen') ) ) b JOIN tenants t ON t.id = b.tenant_id GROUP BY t.name ORDER BY t.name ASC
Submitted to tonight's batch. Your work on this request is done.