Queries · q041 · VP Customer Experience · week 1, night 2
“Was the May-June 2025 jump in ticket volume and resolution time concentrated in a few merchants, or broad-based across the platform?”
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 1
Subject: Where I'd like to start — support & shopper experience
Hi Mateo,
Good to have you on this with me. I'm still building my map of the business, so I want to keep this week broad — I'd rather get a solid first read on a few big questions than a deep dive on one. Here's what's on my mind, roughly in priority order:
What I need these for: I'm meeting merchant success and the exec team soon and want a credible, numbers-backed view of shopper experience — not just war stories. I also want to start spotting the two or three things that, if we fixed them, would move the needle most broadly across merchants, rather than chasing individual complaints.
Given the slot constraints, let's prioritize #1 through #3 this week if we can't fit all five — those feel most urgent to me. Happy to talk through any of this before you lock in requests.
Thanks, Daniel
The analyst · week 1, night 2, request 2 of 3
Reading report 1: year-end (Nov/Dec) spikes in volume, response and resolution time repeat every year (2023, 2024, 2025) — that looks like seasonal/holiday load and is somewhat expected, though worth noting for the memo. What's NOT seasonal is the 2025-05/2025-06 spike (resolution hours jump to 52.7/87.2, response to 26.5/45.1, breach rate to 36.9/45.6%) — nothing comparable appears in 2023 or 2024 mid-year, so I want to find the cause rather than assume it's noise. Report 3 shows delivery + where-is-my-order tags are already >65% of tagged tickets in the last 6 months and growing share while complaint/return/product-question shrink in share — that's the platform-level theme Daniel asked about in priority #2, and it may also explain the resolution-time trend in #1 if delivery tickets take longer to resolve and are concentrated in the spike months. Tonight I'm chasing both of these (merchant concentration of the May-Jun spike, and whether delivery-tagged tickets specifically explain slow/volatile resolution times), plus opening priority #3 (returns/refunds) with a foundational trend report so we have something in hand for that question this week. I don't need a new slot for a plain YoY same-month pivot — I can build that directly off report 1's numbers for the memo.
The engineer · 1 dry run on the empty schema, then the submission
SELECT tn.name AS merchant_name, a.mayjun_count AS tickets_created_may_jun_2025, ROUND(a.other_count / 10.0, 2) AS avg_monthly_tickets_other_2025, CASE WHEN a.other_count = 0 THEN NULL ELSE ROUND( (a.mayjun_count/2.0) / (a.other_count/10.0), 2) END AS spike_ratio, CASE WHEN a.mayjun_count = 0 THEN NULL ELSE ROUND(100.0 * COALESCE(d.delivery_count,0) / a.mayjun_count, 1) END AS delivery_tag_share_pct FROM ( SELECT tenant_id, COUNT(*) FILTER (WHERE created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01') AS total_2025, COUNT(*) FILTER (WHERE created_at >= DATE '2025-05-01' AND created_at < DATE '2025-07-01') AS mayjun_count, COUNT(*) FILTER (WHERE created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01' AND NOT (created_at >= DATE '2025-05-01' AND created_at < DATE '2025-07-01')) AS other_count FROM tickets GROUP BY tenant_id ) a JOIN tenants tn ON tn.id = a.tenant_id LEFT JOIN ( SELECT t.tenant_id, COUNT(DISTINCT t.id) AS delivery_count FROM tickets t JOIN ticket_tags_map ttm ON ttm.ticket_id = t.id JOIN support_tags st ON st.id = ttm.support_tag_id WHERE t.created_at >= DATE '2025-05-01' AND t.created_at < DATE '2025-07-01' AND LOWER(st.name) IN ('delivery', 'where-is-my-order') GROUP BY t.tenant_id ) d ON d.tenant_id = a.tenant_id WHERE a.total_2025 >= 5 ORDER BY a.mayjun_count DESC, tn.name ASC LIMIT 50
“Query compiles cleanly with the expected columns. Submitting.”
“Submitted. Summary of what the query does:
- Grain: one row per tenant, restricted to tenants with ≥5 tickets created anywhere in 2025.
- Tickets May–Jun 2025: count of tickets with created_at in [2025-05-01, 2025-07-01).
- Avg monthly tickets, other months: count of 2025 tickets outside May–Jun, divided by 10.
- Spike ratio: (May–Jun count / 2) ÷ other-months monthly average, NULL when that average is 0.
- Delivery-tag share: distinct May–Jun tickets tagged delivery or where-is-my-order (case-insensitive) as a % of that tenant's May–Jun ticket count.
- Ordered by May–Jun ticket count descending, tie-broken by merchant name, capped at 50 rows as required — I flagged in the note that the total qualifying-tenant count for the footer should be added by the report layer since computing it inline would need a second aggregation pass.”
As Mateo Ruiz saw it, from the small dataset the agents worked on
merchant_name | tickets_created_may_jun_2025 | avg_monthly_tickets_other_2025 | spike_ratio | delivery_tag_share_pct ------------------------------+------------------------------+--------------------------------+-------------+----------------------- Studio Vrij | 86 | 8.0 | 5.38 | 83.7 Cragsley Outfitters | 53 | 34.7 | 0.76 | 18.9 Marrowby & Vane | 25 | 20.6 | 0.61 | 24.0 Grind Collective | 19 | 10.3 | 0.92 | 89.5 Brennquell Kaffee | 18 | 11.9 | 0.76 | 44.4 Honeycomb Larder | 14 | 4.4 | 1.59 | 57.1 Petalcourt Beauty | 14 | 10.5 | 0.67 | 28.6 Ballycorran Industrial Supply | 11 | 3.3 | 1.67 | 81.8 Kayak Corner | 8 | 1.2 | 3.33 | 87.5 Fennel & Flint | 7 | 6.0 | 0.58 | 42.9 Kibblewick | 7 | 6.5 | 0.54 | 71.4 Sourdough Club | 7 | 4.0 | 0.88 | 57.1 Spice Route Box | 7 | 6.4 | 0.55 | 71.4 Rue Lafayette Studio | 6 | 2.1 | 1.43 | 33.3 Ampfeld Elektronik | 5 | 5.1 | 0.49 | 40.0 Gratlinie Outdoor | 5 | 2.2 | 1.14 | 100.0 Whisker Works | 5 | 0.7 | 3.57 | 40.0 Cinder Peak Sports Co. | 4 | 3.9 | 0.51 | 75.0 Hanse Industriebedarf | 4 | 0.4 | 5.0 | 50.0 Velvet Ash | 4 | 1.5 | 1.33 | 25.0 Wiesgrund Wohnen | 4 | 2.8 | 0.71 | 50.0 Fjordline Wear | 3 | 1.1 | 1.36 | 33.3 Midland Safety Wear | 3 | 0.8 | 1.88 | 33.3 Nordic Bloom Cosmetics | 3 | 1.1 | 1.36 | 66.7 Ridgeback Gear | 3 | 0.6 | 2.5 | 33.3 Tiny Atelier | 3 | 0.3 | 5.0 | 33.3 Wohnglück | 3 | 1.2 | 1.25 | 66.7 Ampere & Co. | 2 | 0.4 | 2.5 | 0.0 Brick & Bobbin | 2 | 0.7 | 1.43 | 50.0 Castell Fasteners | 2 | 1.1 | 0.91 | 50.0 Deskline Supplies | 2 | 0.9 | 1.11 | 0.0 Feather & Fin | 2 | 1.2 | 0.83 | 100.0 Juniper Apothecary | 2 | 1.5 | 0.67 | 50.0 Linden & Loom | 2 | 2.7 | 0.37 | 50.0 Pfotenglück | 2 | 0.6 | 1.67 | 0.0 Sorella Skin | 2 | 0.4 | 2.5 | 50.0 Thistlecombe | 2 | 1.9 | 0.53 | 50.0 Willow & Wren | 2 | 1.7 | 0.59 | 50.0 Atelier Nouveau | 1 | 0.5 | 1.0 | 0.0 Buchstabe | 1 | 0.4 | 1.25 | 0.0 Coastline Interiors | 1 | 2.0 | 0.25 | 0.0 Driftwood Home | 1 | 0.5 | 1.0 | 0.0 Fell & Fjell | 1 | 0.7 | 0.71 | 100.0 Folio & Fern | 1 | 1.0 | 0.5 | 100.0 Kontor24 Bürobedarf | 1 | 0.8 | 0.63 | 0.0 Lumen Collective | 1 | 0.7 | 0.71 | 0.0 Mirelle | 1 | 0.6 | 0.83 | 100.0 Oakhurst Tailoring | 1 | 2.6 | 0.19 | 0.0 OfficeHub Nordic | 1 | 1.4 | 0.36 | 0.0 Ondine Botanicals | 1 | 0.5 | 1.0 | 100.0 (50 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 tn.name AS merchant_name, a.mayjun_count AS tickets_created_may_jun_2025, ROUND(a.other_count / 10.0, 2) AS avg_monthly_tickets_other_2025, a.other_count / 10.0 AS avg_monthly_tickets_other_2025, CASE WHEN a.other_count = 0 THEN NULL ELSE ROUND( (a.mayjun_count/2.0) / (a.other_count/10.0), 2) ELSE (a.mayjun_count/2.0) / (a.other_count/10.0) END AS spike_ratio, CASE WHEN a.mayjun_count = 0 THEN NULL ELSE ROUND(100.0 * COALESCE(d.delivery_count,0) / a.mayjun_count, 1) ELSE 100.0 * COALESCE(d.delivery_count,0) / a.mayjun_count END AS delivery_tag_share_pctFROM ( SELECT tenant_id, COUNT(*) FILTER (WHERE created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01') AS total_2025, COUNT(*) FILTER (WHERE created_at >= DATE '2025-05-01' AND created_at < DATE '2025-07-01') AS mayjun_count, COUNT(*) FILTER (WHERE created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01' AND NOT (created_at >= DATE '2025-05-01' AND created_at < DATE '2025-07-01')) AS other_count FROM tickets GROUP BY tenant_id) aJOIN tenants tn ON tn.id = a.tenant_idLEFT JOIN ( SELECT t.tenant_id, COUNT(DISTINCT t.id) AS delivery_count SELECT t.tenant_id AS tenant_id, COUNT(DISTINCT t.id) AS delivery_count FROM tickets t JOIN ticket_tags_map ttm ON ttm.ticket_id = t.id JOIN support_tags st ON st.id = ttm.support_tag_id WHERE t.created_at >= DATE '2025-05-01' AND t.created_at < DATE '2025-07-01' AND LOWER(st.name) IN ('delivery', 'where-is-my-order') GROUP BY t.tenant_id) d ON d.tenant_id = a.tenant_idWHERE a.total_2025 >= 5ORDER BY a.mayjun_count DESC, tn.name ASCLIMIT 50ORDER BY a.mayjun_count DESC NULLS FIRST, tn.name ASC NULLS LAST, tn.id ASC NULLS LAST{{fetch_first(50)}}
SELECT tn.name AS merchant_name, a.mayjun_count AS tickets_created_may_jun_2025, a.other_count / 10.0 AS avg_monthly_tickets_other_2025, CASE WHEN a.other_count = 0 THEN NULL ELSE (a.mayjun_count/2.0) / (a.other_count/10.0) END AS spike_ratio, CASE WHEN a.mayjun_count = 0 THEN NULL ELSE 100.0 * COALESCE(d.delivery_count,0) / a.mayjun_count END AS delivery_tag_share_pct FROM ( SELECT tenant_id, COUNT(*) FILTER (WHERE created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01') AS total_2025, COUNT(*) FILTER (WHERE created_at >= DATE '2025-05-01' AND created_at < DATE '2025-07-01') AS mayjun_count, COUNT(*) FILTER (WHERE created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01' AND NOT (created_at >= DATE '2025-05-01' AND created_at < DATE '2025-07-01')) AS other_count FROM tickets GROUP BY tenant_id ) a JOIN tenants tn ON tn.id = a.tenant_id LEFT JOIN ( SELECT t.tenant_id AS tenant_id, COUNT(DISTINCT t.id) AS delivery_count FROM tickets t JOIN ticket_tags_map ttm ON ttm.ticket_id = t.id JOIN support_tags st ON st.id = ttm.support_tag_id WHERE t.created_at >= DATE '2025-05-01' AND t.created_at < DATE '2025-07-01' AND LOWER(st.name) IN ('delivery', 'where-is-my-order') GROUP BY t.tenant_id ) d ON d.tenant_id = a.tenant_id WHERE a.total_2025 >= 5 ORDER BY a.mayjun_count DESC NULLS FIRST, tn.name ASC NULLS LAST, tn.id ASC NULLS LAST FETCH FIRST 50 ROWS ONLY
SELECT tn.name AS merchant_name, a.mayjun_count AS tickets_created_may_jun_2025, a.other_count / 10.0 AS avg_monthly_tickets_other_2025, CASE WHEN a.other_count = 0 THEN NULL ELSE (a.mayjun_count/2.0) / (a.other_count/10.0) END AS spike_ratio, CASE WHEN a.mayjun_count = 0 THEN NULL ELSE 100.0 * COALESCE(d.delivery_count,0) / a.mayjun_count END AS delivery_tag_share_pct FROM ( SELECT tenant_id, COUNT(*) FILTER (WHERE created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01') AS total_2025, COUNT(*) FILTER (WHERE created_at >= DATE '2025-05-01' AND created_at < DATE '2025-07-01') AS mayjun_count, COUNT(*) FILTER (WHERE created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01' AND NOT (created_at >= DATE '2025-05-01' AND created_at < DATE '2025-07-01')) AS other_count FROM tickets GROUP BY tenant_id ) a JOIN tenants tn ON tn.id = a.tenant_id LEFT JOIN ( SELECT t.tenant_id AS tenant_id, COUNT(DISTINCT t.id) AS delivery_count FROM tickets t JOIN ticket_tags_map ttm ON ttm.ticket_id = t.id JOIN support_tags st ON st.id = ttm.support_tag_id WHERE t.created_at >= DATE '2025-05-01' AND t.created_at < DATE '2025-07-01' AND LOWER(st.name) IN ('delivery', 'where-is-my-order') GROUP BY t.tenant_id ) d ON d.tenant_id = a.tenant_id WHERE a.total_2025 >= 5 ORDER BY a.mayjun_count DESC NULLS FIRST, tn.name ASC NULLS LAST, tn.id ASC NULLS LAST LIMIT 50
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 spike in May-June 2025: which merchants drove it Question: Was the May-June 2025 jump in ticket volume and resolution time concentrated in a few merchants, or broad-based across the platform? Grain: one row per tenant (merchant), among tenants with at least 5 tickets created in 2025 Columns: - merchant name - tickets created, May-Jun 2025 (combined) - avg monthly tickets, other 2025 months (Jan-Apr, Jul-Dec, per-month average) - spike ratio (May-Jun monthly rate ÷ other-months monthly average) - share of May-Jun 2025 tickets tagged delivery or where-is-my-order (%) Details: Source: tickets joined to tenants (for name), and ticket_tags_map/support_tags for the delivery-tag share. 'Tickets created, May-Jun 2025' = count of tickets with created_at between 2025-05-01 and 2025-06-30 inclusive, per tenant. 'avg monthly tickets, other 2025 months' = count of that tenant's tickets with created_at in 2025 but outside May-Jun, divided by 10 (the 10 other months). Spike ratio = (May-Jun combined count / 2) divided by the other-months monthly average; if the other-months average is 0, show NULL rather than erroring. Delivery-tag share uses the same ticket-level classification as the companion report (tag name 'delivery' or 'where-is-my-order', case-insensitive, counted once per distinct ticket). Only include tenants with >=5 tickets created in 2025 total. Order by 'tickets created, May-Jun 2025' descending, tie-break by merchant name ascending. Report will cap at 50 printed rows per house standard; note total qualifying tenant count in the footer. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runSELECT tn.name AS merchant_name, a.mayjun_count AS tickets_created_may_jun_2025, ROUND(a.other_count / 10.0, 2) AS avg_monthly_tickets_other_2025, CASE WHEN a.other_count = 0 THEN NULL ELSE ROUND( (a.mayjun_count/2.0) / (a.other_count/10.0), 2) END AS spike_ratio, CASE WHEN a.mayjun_count = 0 THEN NULL ELSE ROUND(100.0 * COALESCE(d.delivery_count,0) / a.mayjun_count, 1) END AS delivery_tag_share_pct FROM ( SELECT tenant_id, COUNT(*) FILTER (WHERE created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01') AS total_2025, COUNT(*) FILTER (WHERE created_at >= DATE '2025-05-01' AND created_at < DATE '2025-07-01') AS mayjun_count, COUNT(*) FILTER (WHERE created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01' AND NOT (created_at >= DATE '2025-05-01' AND created_at < DATE '2025-07-01')) AS other_count FROM tickets GROUP BY tenant_id ) a JOIN tenants tn ON tn.id = a.tenant_id LEFT JOIN ( SELECT t.tenant_id, COUNT(DISTINCT t.id) AS delivery_count FROM tickets t JOIN ticket_tags_map ttm ON ttm.ticket_id = t.id JOIN support_tags st ON st.id = ttm.support_tag_id WHERE t.created_at >= DATE '2025-05-01' AND t.created_at < DATE '2025-07-01' AND LOWER(st.name) IN ('delivery', 'where-is-my-order') GROUP BY t.tenant_id ) d ON d.tenant_id = a.tenant_id WHERE a.total_2025 >= 5 ORDER BY a.mayjun_count DESC, tn.name ASC LIMIT 50
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: merchant_name, tickets_created_may_jun_2025, avg_monthly_tickets_other_2025, spike_ratio, delivery_tag_share_pct
submitSELECT tn.name AS merchant_name, a.mayjun_count AS tickets_created_may_jun_2025, ROUND(a.other_count / 10.0, 2) AS avg_monthly_tickets_other_2025, CASE WHEN a.other_count = 0 THEN NULL ELSE ROUND( (a.mayjun_count/2.0) / (a.other_count/10.0), 2) END AS spike_ratio, CASE WHEN a.mayjun_count = 0 THEN NULL ELSE ROUND(100.0 * COALESCE(d.delivery_count,0) / a.mayjun_count, 1) END AS delivery_tag_share_pct FROM ( SELECT tenant_id, COUNT(*) FILTER (WHERE created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01') AS total_2025, COUNT(*) FILTER (WHERE created_at >= DATE '2025-05-01' AND created_at < DATE '2025-07-01') AS mayjun_count, COUNT(*) FILTER (WHERE created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01' AND NOT (created_at >= DATE '2025-05-01' AND created_at < DATE '2025-07-01')) AS other_count FROM tickets GROUP BY tenant_id ) a JOIN tenants tn ON tn.id = a.tenant_id LEFT JOIN ( SELECT t.tenant_id, COUNT(DISTINCT t.id) AS delivery_count FROM tickets t JOIN ticket_tags_map ttm ON ttm.ticket_id = t.id JOIN support_tags st ON st.id = ttm.support_tag_id WHERE t.created_at >= DATE '2025-05-01' AND t.created_at < DATE '2025-07-01' AND LOWER(st.name) IN ('delivery', 'where-is-my-order') GROUP BY t.tenant_id ) d ON d.tenant_id = a.tenant_id WHERE a.total_2025 >= 5 ORDER BY a.mayjun_count DESC, tn.name ASC LIMIT 50
Submitted to tonight's batch. Your work on this request is done.