Queries · q249 · VP Customer Experience · week 2, night 2
“Has the staffing gap at Cragsley, Marrowby, and Studio Vrij been widening over the course of 2025 (ticket volume outpacing agent headcount), or has it been consistently stretched all year?”
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 2, request 3 of 3
Night 1 results: the staffing report shows Cragsley (50.0 tickets/agent), Marrowby (46.2) and Studio Vrij (41.5) are the three highest ratios in the entire qualifying book, well ahead of the next closest (Grind Collective, 40.7, then a drop to 34.3) - so understaffing-relative-to-volume looks like a real, differentiating factor, not noise (unassigned-ticket rates are near-zero everywhere, so it's not a triage problem). Channel mix, by contrast, looks like a dead end: the three hotspots' email/chat/phone split is unremarkable - several merchants with much heavier email reliance (e.g. Coastline at 52.4% email) actually had the best SLA performance last week, so I don't think channel explains the gap. The urgent-ticket report is the more worrying one: Marrowby (66.7%), Cragsley (66.0%) and Studio Vrij (63.6%) are bad, but several smaller merchants (Honeycomb Larder 100%, Sourdough Club 85.7%, Ampfeld Elektronik 83.3%) are worse - so urgent-SLA failure is NOT concentrated in the three hotspots, it's spread broadly across the book, and nearly every merchant's urgent miss rate is well above their own all-priority miss rate. That pattern (urgent specifically failing everywhere) makes me suspect the urgent SLA target itself may be set unrealistically tight platform-wide, rather than this being pure execution failure merchant-by-merchant - worth testing directly before concluding it's "everyone is bad at urgent tickets." Tonight I'm testing: (1) whether hotspot tickets are genuinely more complex (more back-and-forth) rather than just higher volume, (2) whether urgent SLA targets are achievable given typical actual resolution times, and (3) whether the hotspots' staffing gap has been widening over 2025 (useful for both the "why" question and, incidentally, for planning ahead of Nov/Dec later).
The engineer · 1 dry run on the empty schema, then the submission
SELECT t.name as merchant_name, concat('2025-Q', extract(quarter from tk.created_at)::int) as quarter, count(*) as tickets_created, count(distinct tk.assignee_agent_id) as distinct_agents, round(count(*)::numeric / nullif(count(distinct tk.assignee_agent_id),0), 1) as tickets_per_agent FROM tickets tk JOIN tenants t on t.id = tk.tenant_id WHERE t.name in ('Cragsley Outfitters','Marrowby & Vane','Studio Vrij','Grind Collective') AND tk.created_at >= timestamp '2025-01-01' and tk.created_at < timestamp '2026-01-01' GROUP BY t.name, extract(quarter from tk.created_at) ORDER BY t.name, extract(quarter from tk.created_at)
“Query compiles and returns the expected columns. Submitting.”
“Submitted. The report gives one row per merchant per 2025 quarter (16 rows) with tickets created, distinct agents who handled a ticket, and tickets per active agent, ordered by merchant name then quarter — ready for Mateo to assess whether the staffing gap at the three hotspot merchants is widening versus Grind Collective's peer trend.”
As Mateo Ruiz saw it, from the small dataset the agents worked on
merchant_name | quarter | tickets_created | distinct_agents | tickets_per_agent --------------------+---------+-----------------+-----------------+------------------ Cragsley Outfitters | 2025-Q1 | 76 | 8 | 9.5 Cragsley Outfitters | 2025-Q2 | 81 | 8 | 10.1 Cragsley Outfitters | 2025-Q3 | 92 | 8 | 11.5 Cragsley Outfitters | 2025-Q4 | 151 | 8 | 18.9 Grind Collective | 2025-Q1 | 12 | 3 | 4.0 Grind Collective | 2025-Q2 | 25 | 3 | 8.3 Grind Collective | 2025-Q3 | 15 | 3 | 5.0 Grind Collective | 2025-Q4 | 70 | 3 | 23.3 Marrowby & Vane | 2025-Q1 | 46 | 5 | 9.2 Marrowby & Vane | 2025-Q2 | 37 | 5 | 7.4 Marrowby & Vane | 2025-Q3 | 43 | 5 | 8.6 Marrowby & Vane | 2025-Q4 | 105 | 5 | 21.0 Studio Vrij | 2025-Q1 | 13 | 4 | 3.3 Studio Vrij | 2025-Q2 | 96 | 4 | 24.0 Studio Vrij | 2025-Q3 | 18 | 4 | 4.5 Studio Vrij | 2025-Q4 | 39 | 4 | 9.8 (16 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, concat('2025-Q', extract(quarter from tk.created_at)::int) as quarter, concat('2025-Q', CAST(extract(quarter from tk.created_at) AS int)) as quarter, count(*) as tickets_created, count(distinct tk.assignee_agent_id) as distinct_agents, round(count(*)::numeric / nullif(count(distinct tk.assignee_agent_id),0), 1) as tickets_per_agent CAST(count(*) AS numeric(18,6)) / nullif(count(distinct tk.assignee_agent_id),0) as tickets_per_agentFROM tickets tkJOIN tenants t on t.id = tk.tenant_idWHERE t.name in ('Cragsley Outfitters','Marrowby & Vane','Studio Vrij','Grind Collective') AND tk.created_at >= timestamp '2025-01-01' and tk.created_at < timestamp '2026-01-01'GROUP BY t.name, extract(quarter from tk.created_at)ORDER BY t.name, extract(quarter from tk.created_at)ORDER BY t.name NULLS LAST, extract(quarter from tk.created_at) NULLS LAST
SELECT t.name as merchant_name, concat('2025-Q', CAST(extract(quarter from tk.created_at) AS int)) as quarter, count(*) as tickets_created, count(distinct tk.assignee_agent_id) as distinct_agents, CAST(count(*) AS numeric(18,6)) / nullif(count(distinct tk.assignee_agent_id),0) as tickets_per_agent FROM tickets tk JOIN tenants t on t.id = tk.tenant_id WHERE t.name in ('Cragsley Outfitters','Marrowby & Vane','Studio Vrij','Grind Collective') AND tk.created_at >= timestamp '2025-01-01' and tk.created_at < timestamp '2026-01-01' GROUP BY t.name, extract(quarter from tk.created_at) ORDER BY t.name NULLS LAST, extract(quarter from tk.created_at) 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: Ticket load per agent over 2025, hotspot merchants vs a comparable peer Question: Has the staffing gap at Cragsley, Marrowby, and Studio Vrij been widening over the course of 2025 (ticket volume outpacing agent headcount), or has it been consistently stretched all year? Grain: one row per tenant and calendar quarter in 2025, for Cragsley Outfitters, Marrowby & Vane, Studio Vrij, and Grind Collective (the next-highest tickets-per-agent merchant, included as a comparison peer) Columns: - merchant name - quarter (2025-Q1..Q4) - tickets created that quarter - distinct agents who handled a ticket that quarter - tickets per active agent that quarter Details: Source: tickets joined to tenants, filtered to the four named tenants (Cragsley Outfitters, Marrowby & Vane, Studio Vrij, Grind Collective). Grain the quarter on tickets.created_at (2025-01-01 through 2025-12-31, 4 quarters). 'distinct agents who handled a ticket' = count distinct tickets.assignee_agent_id (excluding null) for that tenant/quarter. 'tickets per active agent' = tickets that quarter ÷ distinct agents that quarter, rounded to 1 decimal (NULL if zero agents). Order by merchant name ascending, then quarter ascending. Expect exactly 16 rows (4 merchants × 4 quarters). Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runSELECT t.name as merchant_name, concat('2025-Q', extract(quarter from tk.created_at)::int) as quarter, count(*) as tickets_created, count(distinct tk.assignee_agent_id) as distinct_agents, round(count(*)::numeric / nullif(count(distinct tk.assignee_agent_id),0), 1) as tickets_per_agent FROM tickets tk JOIN tenants t on t.id = tk.tenant_id WHERE t.name in ('Cragsley Outfitters','Marrowby & Vane','Studio Vrij','Grind Collective') AND tk.created_at >= timestamp '2025-01-01' and tk.created_at < timestamp '2026-01-01' GROUP BY t.name, extract(quarter from tk.created_at) ORDER BY t.name, extract(quarter from tk.created_at)
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: merchant_name, quarter, tickets_created, distinct_agents, tickets_per_agent
submitSELECT t.name as merchant_name, concat('2025-Q', extract(quarter from tk.created_at)::int) as quarter, count(*) as tickets_created, count(distinct tk.assignee_agent_id) as distinct_agents, round(count(*)::numeric / nullif(count(distinct tk.assignee_agent_id),0), 1) as tickets_per_agent FROM tickets tk JOIN tenants t on t.id = tk.tenant_id WHERE t.name in ('Cragsley Outfitters','Marrowby & Vane','Studio Vrij','Grind Collective') AND tk.created_at >= timestamp '2025-01-01' and tk.created_at < timestamp '2026-01-01' GROUP BY t.name, extract(quarter from tk.created_at) ORDER BY t.name, extract(quarter from tk.created_at)
Submitted to tonight's batch. Your work on this request is done.