Queries · q245 · VP Customer Experience · week 2, night 1
“Do Cragsley, Marrowby, and Studio Vrij rely more on slower contact channels (e.g. email) than the rest of the book?”
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 1, request 2 of 3
Daniel wants to know, for the 3 ticket-heavy merchants (Cragsley, Marrowby, Studio Vrij), whether slowness is explained by something fixable (staffing, channel mix) vs. harder issues, and whether the urgent-SLA failure (50.6% company-wide per Ben Carter) is concentrated in those 3 or systemic. Other teams' posts give useful context I'll want to connect later (Albion Parcel carrier breakdown hitting delivery regardless of merchant/warehouse; the cart-reminder bug hitting 10 merchants incl. Cragsley in Nov; Cragsley's own product-quality tail) - but those are confounders for Daniel's question #4, which he said can wait. Tonight I'm testing the most direct, fixable-vs-not hypotheses for #1 and #2: (1) are the 3 hotspots simply understaffed relative to ticket volume, (2) do they lean on slower channels, and (3) is the urgent-ticket resolution failure broad across the book or concentrated in those same 3 merchants. All three are merchant-grain reports so I can line them up side by side and see if the 3 hotspots stand out consistently or only on one dimension.
The engineer · 1 dry run on the empty schema, then the submission
SELECT t.name AS merchant_name, COUNT(*) AS tickets_2025, ROUND(100.0 * COUNT(*) FILTER (WHERE tk.channel = 'email') / COUNT(*), 1) AS pct_email, ROUND(100.0 * COUNT(*) FILTER (WHERE tk.channel = 'chat') / COUNT(*), 1) AS pct_chat, ROUND(100.0 * COUNT(*) FILTER (WHERE tk.channel = 'phone') / COUNT(*), 1) AS pct_phone, ROUND(100.0 * COUNT(*) FILTER (WHERE tk.channel = 'web') / COUNT(*), 1) AS pct_web, ROUND(100.0 * COUNT(*) FILTER (WHERE tk.channel = 'social') / COUNT(*), 1) AS pct_social, ROUND(100.0 * COUNT(*) FILTER (WHERE tk.channel = 'api') / COUNT(*), 1) AS pct_api FROM tickets tk JOIN tenants t ON t.id = tk.tenant_id WHERE tk.created_at >= DATE '2025-01-01' AND tk.created_at < DATE '2026-01-01' GROUP BY t.id, t.name HAVING COUNT(*) >= 20 ORDER BY tickets_2025 DESC, merchant_name ASC LIMIT 50
“Submitted. The query computes, for each tenant with ≥20 tickets created in 2025, the ticket count and the percentage split across the six channels (email, chat, phone, web, social, api), ordered by ticket volume descending with merchant name as tie-break — ready for Mateo to compare Cragsley, Marrowby, and Studio Vrij against the rest of the peer set.”
As Mateo Ruiz saw it, from the small dataset the agents worked on
merchant_name | tickets_2025 | pct_email | pct_chat | pct_phone | pct_web | pct_social | pct_api ------------------------------+--------------+-----------+----------+-----------+---------+------------+-------- Cragsley Outfitters | 400 | 36.5 | 27.5 | 11.0 | 14.0 | 11.0 | 0.0 Marrowby & Vane | 231 | 28.1 | 29.4 | 13.4 | 12.1 | 16.9 | 0.0 Studio Vrij | 166 | 38.6 | 21.7 | 19.9 | 9.0 | 10.8 | 0.0 Brennquell Kaffee | 137 | 35.8 | 27.7 | 12.4 | 9.5 | 14.6 | 0.0 Grind Collective | 122 | 43.4 | 22.1 | 9.0 | 10.7 | 14.8 | 0.0 Petalcourt Beauty | 119 | 41.2 | 18.5 | 14.3 | 21.0 | 5.0 | 0.0 Kibblewick | 72 | 36.1 | 25.0 | 11.1 | 12.5 | 15.3 | 0.0 Spice Route Box | 71 | 42.3 | 15.5 | 18.3 | 11.3 | 12.7 | 0.0 Fennel & Flint | 67 | 41.8 | 32.8 | 7.5 | 11.9 | 6.0 | 0.0 Honeycomb Larder | 58 | 39.7 | 24.1 | 12.1 | 8.6 | 15.5 | 0.0 Ampfeld Elektronik | 56 | 30.4 | 30.4 | 10.7 | 10.7 | 17.9 | 0.0 Sourdough Club | 47 | 42.6 | 27.7 | 8.5 | 6.4 | 14.9 | 0.0 Ballycorran Industrial Supply | 44 | 29.5 | 36.4 | 6.8 | 11.4 | 15.9 | 0.0 Cinder Peak Sports Co. | 43 | 27.9 | 27.9 | 9.3 | 18.6 | 16.3 | 0.0 Wiesgrund Wohnen | 32 | 31.3 | 28.1 | 15.6 | 9.4 | 15.6 | 0.0 Linden & Loom | 29 | 24.1 | 17.2 | 17.2 | 31.0 | 10.3 | 0.0 Gratlinie Outdoor | 27 | 48.1 | 18.5 | 11.1 | 18.5 | 3.7 | 0.0 Oakhurst Tailoring | 27 | 44.4 | 18.5 | 7.4 | 14.8 | 14.8 | 0.0 Rue Lafayette Studio | 27 | 33.3 | 37.0 | 3.7 | 18.5 | 7.4 | 0.0 Coastline Interiors | 21 | 52.4 | 14.3 | 9.5 | 14.3 | 9.5 | 0.0 Thistlecombe | 21 | 47.6 | 28.6 | 4.8 | 4.8 | 14.3 | 0.0 Kayak Corner | 20 | 45.0 | 10.0 | 20.0 | 25.0 | 0.0 | 0.0 (22 rows)
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
Changed only where the benchmark needs the same result on every system
SELECT t.name AS merchant_name, COUNT(*) AS tickets_2025, ROUND(100.0 * COUNT(*) FILTER (WHERE tk.channel = 'email') / COUNT(*), 1) AS pct_email, ROUND(100.0 * COUNT(*) FILTER (WHERE tk.channel = 'chat') / COUNT(*), 1) AS pct_chat, ROUND(100.0 * COUNT(*) FILTER (WHERE tk.channel = 'phone') / COUNT(*), 1) AS pct_phone, ROUND(100.0 * COUNT(*) FILTER (WHERE tk.channel = 'web') / COUNT(*), 1) AS pct_web, ROUND(100.0 * COUNT(*) FILTER (WHERE tk.channel = 'social') / COUNT(*), 1) AS pct_social, ROUND(100.0 * COUNT(*) FILTER (WHERE tk.channel = 'api') / COUNT(*), 1) AS pct_api 100.0 * COUNT(*) FILTER (WHERE tk.channel = 'email') / COUNT(*) AS pct_email, 100.0 * COUNT(*) FILTER (WHERE tk.channel = 'chat') / COUNT(*) AS pct_chat, 100.0 * COUNT(*) FILTER (WHERE tk.channel = 'phone') / COUNT(*) AS pct_phone, 100.0 * COUNT(*) FILTER (WHERE tk.channel = 'web') / COUNT(*) AS pct_web, 100.0 * COUNT(*) FILTER (WHERE tk.channel = 'social') / COUNT(*) AS pct_social, 100.0 * COUNT(*) FILTER (WHERE tk.channel = 'api') / COUNT(*) AS pct_apiFROM tickets tkJOIN tenants t ON t.id = tk.tenant_idWHERE tk.created_at >= DATE '2025-01-01' AND tk.created_at < DATE '2026-01-01'GROUP BY t.id, t.nameHAVING COUNT(*) >= 20ORDER BY tickets_2025 DESC, merchant_name ASCLIMIT 50ORDER BY tickets_2025 DESC NULLS FIRST, merchant_name ASC NULLS LAST, t.id ASC NULLS LAST{{fetch_first(50)}}
SELECT t.name AS merchant_name, COUNT(*) AS tickets_2025, 100.0 * COUNT(*) FILTER (WHERE tk.channel = 'email') / COUNT(*) AS pct_email, 100.0 * COUNT(*) FILTER (WHERE tk.channel = 'chat') / COUNT(*) AS pct_chat, 100.0 * COUNT(*) FILTER (WHERE tk.channel = 'phone') / COUNT(*) AS pct_phone, 100.0 * COUNT(*) FILTER (WHERE tk.channel = 'web') / COUNT(*) AS pct_web, 100.0 * COUNT(*) FILTER (WHERE tk.channel = 'social') / COUNT(*) AS pct_social, 100.0 * COUNT(*) FILTER (WHERE tk.channel = 'api') / COUNT(*) AS pct_api FROM tickets tk JOIN tenants t ON t.id = tk.tenant_id WHERE tk.created_at >= DATE '2025-01-01' AND tk.created_at < DATE '2026-01-01' GROUP BY t.id, t.name HAVING COUNT(*) >= 20 ORDER BY tickets_2025 DESC NULLS FIRST, merchant_name ASC NULLS LAST, t.id ASC NULLS LAST FETCH FIRST 50 ROWS ONLY
SELECT t.name AS merchant_name, COUNT(*) AS tickets_2025, 100.0 * COUNT(*) FILTER (WHERE tk.channel = 'email') / COUNT(*) AS pct_email, 100.0 * COUNT(*) FILTER (WHERE tk.channel = 'chat') / COUNT(*) AS pct_chat, 100.0 * COUNT(*) FILTER (WHERE tk.channel = 'phone') / COUNT(*) AS pct_phone, 100.0 * COUNT(*) FILTER (WHERE tk.channel = 'web') / COUNT(*) AS pct_web, 100.0 * COUNT(*) FILTER (WHERE tk.channel = 'social') / COUNT(*) AS pct_social, 100.0 * COUNT(*) FILTER (WHERE tk.channel = 'api') / COUNT(*) AS pct_api FROM tickets tk JOIN tenants t ON t.id = tk.tenant_id WHERE tk.created_at >= DATE '2025-01-01' AND tk.created_at < DATE '2026-01-01' GROUP BY t.id, t.name HAVING COUNT(*) >= 20 ORDER BY tickets_2025 DESC NULLS FIRST, merchant_name ASC NULLS LAST, t.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 channel mix, by merchant Question: Do Cragsley, Marrowby, and Studio Vrij rely more on slower contact channels (e.g. email) than the rest of the book? Grain: one row per tenant (merchant), among tenants with at least 20 tickets created in 2025 Columns: - merchant name - tickets created, 2025 - % channel = email - % channel = chat - % channel = phone - % channel = web - % channel = social - % channel = api Details: Source: tickets joined to tenants (name). Window: tickets.created_at 2025-01-01 through 2025-12-31. For each tenant, compute the percentage of tickets in 2025 in each tickets.channel value (email, chat, phone, web, social, api), each rounded to 1 decimal; the six percentages should sum to ~100 per row. Only include tenants with >=20 tickets created in 2025 (same threshold as the staffing report, for a consistent peer set). Order by tickets created descending, tie-break merchant name ascending. Cap at 50 rows; note total qualifying tenant count. 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 tickets_2025, ROUND(100.0 * COUNT(*) FILTER (WHERE tk.channel = 'email') / COUNT(*), 1) AS pct_email, ROUND(100.0 * COUNT(*) FILTER (WHERE tk.channel = 'chat') / COUNT(*), 1) AS pct_chat, ROUND(100.0 * COUNT(*) FILTER (WHERE tk.channel = 'phone') / COUNT(*), 1) AS pct_phone, ROUND(100.0 * COUNT(*) FILTER (WHERE tk.channel = 'web') / COUNT(*), 1) AS pct_web, ROUND(100.0 * COUNT(*) FILTER (WHERE tk.channel = 'social') / COUNT(*), 1) AS pct_social, ROUND(100.0 * COUNT(*) FILTER (WHERE tk.channel = 'api') / COUNT(*), 1) AS pct_api FROM tickets tk JOIN tenants t ON t.id = tk.tenant_id WHERE tk.created_at >= DATE '2025-01-01' AND tk.created_at < DATE '2026-01-01' GROUP BY t.id, t.name HAVING COUNT(*) >= 20 ORDER BY tickets_2025 DESC, merchant_name ASC LIMIT 50
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: merchant_name, tickets_2025, pct_email, pct_chat, pct_phone, pct_web, pct_social, pct_api
submitSELECT t.name AS merchant_name, COUNT(*) AS tickets_2025, ROUND(100.0 * COUNT(*) FILTER (WHERE tk.channel = 'email') / COUNT(*), 1) AS pct_email, ROUND(100.0 * COUNT(*) FILTER (WHERE tk.channel = 'chat') / COUNT(*), 1) AS pct_chat, ROUND(100.0 * COUNT(*) FILTER (WHERE tk.channel = 'phone') / COUNT(*), 1) AS pct_phone, ROUND(100.0 * COUNT(*) FILTER (WHERE tk.channel = 'web') / COUNT(*), 1) AS pct_web, ROUND(100.0 * COUNT(*) FILTER (WHERE tk.channel = 'social') / COUNT(*), 1) AS pct_social, ROUND(100.0 * COUNT(*) FILTER (WHERE tk.channel = 'api') / COUNT(*), 1) AS pct_api FROM tickets tk JOIN tenants t ON t.id = tk.tenant_id WHERE tk.created_at >= DATE '2025-01-01' AND tk.created_at < DATE '2026-01-01' GROUP BY t.id, t.name HAVING COUNT(*) >= 20 ORDER BY tickets_2025 DESC, merchant_name ASC LIMIT 50
Submitted to tonight's batch. Your work on this request is done.