Queries · q401 · Support Operations Manager · week 2, night 2
“Is Marrowby & Vane's and Studio Vrij's slowness concentrated in specific topics (especially delivery-related ones), or uniformly slow across all topics, compared to Cragsley Outfitters?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
Week 1; shared with Daniel Achterberg, VP Customer Experience
Review how the helpdesk runs: queues and agent groups, use of macros and knowledge articles, SLA targets and breaches, and what makes tickets slow.
The manager, at the start of week 2
Subject: Re: Helpdesk Operating Review — great start, here's where to push next
Ben,
This is exactly the kind of baseline I wanted — and the speed-to-CSAT finding alone is worth putting in front of Daniel as-is. A few things stood out that I want sharpened before we present or act on them.
What surprised me / needs explaining:
Questions for next week, in priority order:
Thanks — this is shaping up well. Let's make next week about turning the Marrowby & Vane / Studio Vrij question into something actionable.
Mia
The analyst · week 2, night 2, request 1 of 3
Two of Mia's four questions are now answered without needing more queries: tag coverage is 100% (every 2025 ticket carries at least one tag — Report 3 only returned a 'Tagged' row, no 'Untagged' row exists), so last week's topic breakdown isn't a skewed sample. And the macro question is closed too, just not the way we'd like: all 5 top macros show zero matching rows in change_history for usage_count changes (Report 2) — there is no audit trail behind that counter at all, so I'm recommending we drop it from reporting entirely rather than chase it further. On the staffing-vs-complexity question, Report 1 actually argues against under-staffing: Cragsley runs the highest tickets-per-agent (50.0) of the group yet has the lowest resolution time and breach rate, while Marrowby & Vane (46.2/agent) and Studio Vrij (41.5/agent) are slower despite lighter per-agent load — and messages-per-ticket/priority-mix don't explain it either (Studio Vrij actually has a lower urgent/high share than Cragsley). So tonight I want to test the next two hypotheses: (1) it's a specific topic (especially delivery/where-is-my-order, which the COO and VP CX teams flagged as carrier-driven this week) concentrated or uniquely slow at these two merchants, and (2) the delay is back-loaded (tickets drag after the first reply, consistent with waiting on an external process like shipping) rather than front-loaded (agents slow to even start). Third slot starts Mia's Q4 (KB deflection) since we have two more nights after this.
The engineer · 1 dry run on the empty schema, then the submission
WITH tenant_filter AS ( SELECT id, name FROM tenants WHERE name IN ('Marrowby & Vane','Studio Vrij','Cragsley Outfitters') ), ticket_tags AS ( SELECT t.id AS ticket_id, t.tenant_id, t.created_at, t.resolved_at, st.name AS tag_name 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 JOIN tenant_filter tf ON tf.id = t.tenant_id WHERE t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01' ), tenant_totals AS ( SELECT t.tenant_id, COUNT(*) AS total_tickets FROM tickets t JOIN tenant_filter tf ON tf.id = t.tenant_id WHERE t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01' GROUP BY t.tenant_id ), breaches AS ( SELECT DISTINCT ticket_id FROM sla_breaches WHERE breach_kind = 'resolution' ) SELECT tf.name AS tenant_name, tt.tag_name, COUNT(*) AS ticket_count, ROUND(100.0 * COUNT(*) / tot.total_tickets, 1) AS pct_of_tenant_tickets, ROUND(AVG(CASE WHEN tt.resolved_at IS NOT NULL THEN EXTRACT(EPOCH FROM (tt.resolved_at - tt.created_at)) / 60.0 END)) AS avg_resolution_minutes, ROUND(100.0 * COUNT(DISTINCT CASE WHEN b.ticket_id IS NOT NULL THEN tt.ticket_id END) / COUNT(*), 1) AS resolution_breach_rate FROM ticket_tags tt JOIN tenant_filter tf ON tf.id = tt.tenant_id JOIN tenant_totals tot ON tot.tenant_id = tt.tenant_id LEFT JOIN breaches b ON b.ticket_id = tt.ticket_id GROUP BY tf.name, tt.tag_name, tot.total_tickets ORDER BY tf.name, ticket_count DESC, tt.tag_name
“Query compiles and returns the expected columns. Submitting.”
“Submitted. Summary of what the query does:
- Scopes to the three named tenants and tickets created in calendar-year 2025.
- Grain is one row per (tenant, tag), built from tickets joined through ticket_tags_map/support_tags.
- ticket_count and pct_of_tenant_tickets are straightforward counts/shares per tenant.
- avg_resolution_minutes averages resolved_at - created_at in minutes over resolved tickets carrying that tag.
- resolution_breach_rate is the share of that tag's tickets with at least one sla_breaches row where breach_kind = 'resolution'.
- Sorted by tenant name, then ticket count descending, with tag name as the deterministic tie-break.
Noted two caveats in the submit note: the resolution-time average excludes still-unresolved tickets, and per-tenant percentages can exceed 100% in total since a ticket may carry more than one tag.”
As Ben Carter saw it, from the small dataset the agents worked on
tenant_name | tag_name | ticket_count | pct_of_tenant_tickets | avg_resolution_minutes | resolution_breach_rate --------------------+-------------------+--------------+-----------------------+------------------------+----------------------- Cragsley Outfitters | return | 161 | 40.3 | 2996.0 | 30.4 Cragsley Outfitters | delivery | 97 | 24.3 | 1697.0 | 18.6 Cragsley Outfitters | complaint | 86 | 21.5 | 1703.0 | 55.8 Cragsley Outfitters | where-is-my-order | 75 | 18.8 | 1819.0 | 18.7 Cragsley Outfitters | product-question | 56 | 14.0 | 2628.0 | 7.1 Cragsley Outfitters | damaged | 39 | 9.8 | 1681.0 | 61.5 Cragsley Outfitters | refund | 24 | 6.0 | 1634.0 | 50.0 Cragsley Outfitters | account | 5 | 1.3 | 844.0 | 80.0 Marrowby & Vane | return | 78 | 33.8 | 5141.0 | 34.6 Marrowby & Vane | delivery | 65 | 28.1 | 7157.0 | 43.1 Marrowby & Vane | complaint | 60 | 26.0 | 3825.0 | 53.3 Marrowby & Vane | where-is-my-order | 58 | 25.1 | 7331.0 | 44.8 Marrowby & Vane | damaged | 30 | 13.0 | 5168.0 | 66.7 Marrowby & Vane | product-question | 24 | 10.4 | 2938.0 | 12.5 Marrowby & Vane | refund | 10 | 4.3 | 1522.0 | 30.0 Marrowby & Vane | payment | 4 | 1.7 | 1884.0 | 25.0 Marrowby & Vane | account | 2 | 0.9 | 1343.0 | 50.0 Studio Vrij | delivery | 98 | 59.0 | 10482.0 | 49.0 Studio Vrij | where-is-my-order | 95 | 57.2 | 10801.0 | 50.5 Studio Vrij | return | 38 | 22.9 | 3170.0 | 15.8 Studio Vrij | complaint | 19 | 11.4 | 3219.0 | 47.4 Studio Vrij | product-question | 11 | 6.6 | 2944.0 | 9.1 Studio Vrij | damaged | 8 | 4.8 | 5165.0 | 75.0 Studio Vrij | account | 2 | 1.2 | 1440.0 | 50.0 Studio Vrij | refund | 2 | 1.2 | 85.0 | 0.0 (25 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
WITH tenant_filter AS ( SELECT id, name FROM tenants WHERE name IN ('Marrowby & Vane','Studio Vrij','Cragsley Outfitters')),ticket_tags AS ( SELECT t.id AS ticket_id, t.tenant_id, t.created_at, t.resolved_at, st.name AS tag_name SELECT t.id AS ticket_id, t.tenant_id AS tenant_id, t.created_at AS created_at, t.resolved_at AS resolved_at, st.name AS tag_name 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 JOIN tenant_filter tf ON tf.id = t.tenant_id WHERE t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01'),tenant_totals AS ( SELECT t.tenant_id, COUNT(*) AS total_tickets SELECT t.tenant_id AS tenant_id, COUNT(*) AS total_tickets FROM tickets t JOIN tenant_filter tf ON tf.id = t.tenant_id WHERE t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01' GROUP BY t.tenant_id),breaches AS ( SELECT DISTINCT ticket_id FROM sla_breaches WHERE breach_kind = 'resolution')SELECT tf.name AS tenant_name, tt.tag_name, tt.tag_name AS tag_name, COUNT(*) AS ticket_count, ROUND(100.0 * COUNT(*) / tot.total_tickets, 1) AS pct_of_tenant_tickets, ROUND(AVG(CASE WHEN tt.resolved_at IS NOT NULL THEN EXTRACT(EPOCH FROM (tt.resolved_at - tt.created_at)) / 60.0 END)) AS avg_resolution_minutes, ROUND(100.0 * COUNT(DISTINCT CASE WHEN b.ticket_id IS NOT NULL THEN tt.ticket_id END) / COUNT(*), 1) AS resolution_breach_rate 100.0 * COUNT(*) / tot.total_tickets AS pct_of_tenant_tickets, AVG(CASE WHEN tt.resolved_at IS NOT NULL THEN {{seconds_between(tt.created_at, tt.resolved_at)}} / 60.0 END) AS avg_resolution_minutes, 100.0 * COUNT(DISTINCT CASE WHEN b.ticket_id IS NOT NULL THEN tt.ticket_id END) / COUNT(*) AS resolution_breach_rateFROM ticket_tags ttJOIN tenant_filter tf ON tf.id = tt.tenant_idJOIN tenant_totals tot ON tot.tenant_id = tt.tenant_idLEFT JOIN breaches b ON b.ticket_id = tt.ticket_idGROUP BY tf.name, tt.tag_name, tot.total_ticketsORDER BY tf.name, ticket_count DESC, tt.tag_nameORDER BY tf.name NULLS LAST, ticket_count DESC NULLS FIRST, tt.tag_name NULLS LAST, tot.total_tickets NULLS LAST
WITH tenant_filter AS ( SELECT id, name FROM tenants WHERE name IN ('Marrowby & Vane','Studio Vrij','Cragsley Outfitters') ), ticket_tags AS ( SELECT t.id AS ticket_id, t.tenant_id AS tenant_id, t.created_at AS created_at, t.resolved_at AS resolved_at, st.name AS tag_name 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 JOIN tenant_filter tf ON tf.id = t.tenant_id WHERE t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01' ), tenant_totals AS ( SELECT t.tenant_id AS tenant_id, COUNT(*) AS total_tickets FROM tickets t JOIN tenant_filter tf ON tf.id = t.tenant_id WHERE t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01' GROUP BY t.tenant_id ), breaches AS ( SELECT DISTINCT ticket_id FROM sla_breaches WHERE breach_kind = 'resolution' ) SELECT tf.name AS tenant_name, tt.tag_name AS tag_name, COUNT(*) AS ticket_count, 100.0 * COUNT(*) / tot.total_tickets AS pct_of_tenant_tickets, AVG(CASE WHEN tt.resolved_at IS NOT NULL THEN CAST(EXTRACT(EPOCH FROM (tt.resolved_at - tt.created_at)) AS NUMERIC(18,6)) / 60.0 END) AS avg_resolution_minutes, 100.0 * COUNT(DISTINCT CASE WHEN b.ticket_id IS NOT NULL THEN tt.ticket_id END) / COUNT(*) AS resolution_breach_rate FROM ticket_tags tt JOIN tenant_filter tf ON tf.id = tt.tenant_id JOIN tenant_totals tot ON tot.tenant_id = tt.tenant_id LEFT JOIN breaches b ON b.ticket_id = tt.ticket_id GROUP BY tf.name, tt.tag_name, tot.total_tickets ORDER BY tf.name NULLS LAST, ticket_count DESC NULLS FIRST, tt.tag_name NULLS LAST, tot.total_tickets NULLS LAST
WITH tenant_filter AS ( SELECT id, name FROM tenants WHERE name IN ('Marrowby & Vane','Studio Vrij','Cragsley Outfitters') ), ticket_tags AS ( SELECT t.id AS ticket_id, t.tenant_id AS tenant_id, t.created_at AS created_at, t.resolved_at AS resolved_at, st.name AS tag_name 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 JOIN tenant_filter tf ON tf.id = t.tenant_id WHERE t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01' ), tenant_totals AS ( SELECT t.tenant_id AS tenant_id, COUNT(*) AS total_tickets FROM tickets t JOIN tenant_filter tf ON tf.id = t.tenant_id WHERE t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01' GROUP BY t.tenant_id ), breaches AS ( SELECT DISTINCT ticket_id FROM sla_breaches WHERE breach_kind = 'resolution' ) SELECT tf.name AS tenant_name, tt.tag_name AS tag_name, COUNT(*) AS ticket_count, 100.0 * COUNT(*) / tot.total_tickets AS pct_of_tenant_tickets, AVG(CASE WHEN tt.resolved_at IS NOT NULL THEN CAST(divideDecimal(toDecimal128(dateDiff('microsecond', tt.created_at, tt.resolved_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6))) / 60.0 END) AS avg_resolution_minutes, 100.0 * COUNT(DISTINCT CASE WHEN b.ticket_id IS NOT NULL THEN tt.ticket_id END) / COUNT(*) AS resolution_breach_rate FROM ticket_tags tt JOIN tenant_filter tf ON tf.id = tt.tenant_id JOIN tenant_totals tot ON tot.tenant_id = tt.tenant_id LEFT JOIN breaches b ON b.ticket_id = tt.ticket_id GROUP BY tf.name, tt.tag_name, tot.total_tickets ORDER BY tf.name NULLS LAST, ticket_count DESC NULLS FIRST, tt.tag_name NULLS LAST, tot.total_tickets 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 Ben Carter (analyst for the Support Operations Manager): Title: Topic mix and resolution time by tag, flagged merchants vs. Cragsley Question: Is Marrowby & Vane's and Studio Vrij's slowness concentrated in specific topics (especially delivery-related ones), or uniformly slow across all topics, compared to Cragsley Outfitters? Grain: one row per (tenant, tag) combination, restricted to Marrowby & Vane, Studio Vrij, and Cragsley Outfitters Columns: - tenant_name - tag_name - ticket_count (2025) - pct_of_tenant_tickets (this tag's ticket_count / that tenant's total 2025 ticket_count) - avg_resolution_minutes - resolution_breach_rate Details: Window: tickets.created_at between 2025-01-01 and 2025-12-31, restricted to tenant_id for Marrowby & Vane, Studio Vrij, Cragsley Outfitters. Source tables: tickets, ticket_tags_map, support_tags, sla_breaches (breach_kind = 'resolution'). Include every tag used by each of the three tenants (not just top N). Order by tenant_name, then ticket_count descending within tenant. Round pct and rates to 1 decimal percent, minutes to whole numbers. Goal: see whether 'delivery'/'where-is-my-order' (or any other single tag) is both a larger share of volume and notably slower than the platform-wide tag averages from last week (delivery: 3045 min, where-is-my-order: 3209 min) specifically for these two flagged merchants. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runWITH tenant_filter AS ( SELECT id, name FROM tenants WHERE name IN ('Marrowby & Vane','Studio Vrij','Cragsley Outfitters') ), ticket_tags AS ( SELECT t.id AS ticket_id, t.tenant_id, t.created_at, t.resolved_at, st.name AS tag_name 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 JOIN tenant_filter tf ON tf.id = t.tenant_id WHERE t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01' ), tenant_totals AS ( SELECT t.tenant_id, COUNT(*) AS total_tickets FROM tickets t JOIN tenant_filter tf ON tf.id = t.tenant_id WHERE t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01' GROUP BY t.tenant_id ), breaches AS ( SELECT DISTINCT ticket_id FROM sla_breaches WHERE breach_kind = 'resolution' ) SELECT tf.name AS tenant_name, tt.tag_name, COUNT(*) AS ticket_count, ROUND(100.0 * COUNT(*) / tot.total_tickets, 1) AS pct_of_tenant_tickets, ROUND(AVG(CASE WHEN tt.resolved_at IS NOT NULL THEN EXTRACT(EPOCH FROM (tt.resolved_at - tt.created_at)) / 60.0 END)) AS avg_resolution_minutes, ROUND(100.0 * COUNT(DISTINCT CASE WHEN b.ticket_id IS NOT NULL THEN tt.ticket_id END) / COUNT(*), 1) AS resolution_breach_rate FROM ticket_tags tt JOIN tenant_filter tf ON tf.id = tt.tenant_id JOIN tenant_totals tot ON tot.tenant_id = tt.tenant_id LEFT JOIN breaches b ON b.ticket_id = tt.ticket_id GROUP BY tf.name, tt.tag_name, tot.total_tickets ORDER BY tf.name, ticket_count DESC, tt.tag_name
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: tenant_name, tag_name, ticket_count, pct_of_tenant_tickets, avg_resolution_minutes, resolution_breach_rate
submitWITH tenant_filter AS ( SELECT id, name FROM tenants WHERE name IN ('Marrowby & Vane','Studio Vrij','Cragsley Outfitters') ), ticket_tags AS ( SELECT t.id AS ticket_id, t.tenant_id, t.created_at, t.resolved_at, st.name AS tag_name 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 JOIN tenant_filter tf ON tf.id = t.tenant_id WHERE t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01' ), tenant_totals AS ( SELECT t.tenant_id, COUNT(*) AS total_tickets FROM tickets t JOIN tenant_filter tf ON tf.id = t.tenant_id WHERE t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01' GROUP BY t.tenant_id ), breaches AS ( SELECT DISTINCT ticket_id FROM sla_breaches WHERE breach_kind = 'resolution' ) SELECT tf.name AS tenant_name, tt.tag_name, COUNT(*) AS ticket_count, ROUND(100.0 * COUNT(*) / tot.total_tickets, 1) AS pct_of_tenant_tickets, ROUND(AVG(CASE WHEN tt.resolved_at IS NOT NULL THEN EXTRACT(EPOCH FROM (tt.resolved_at - tt.created_at)) / 60.0 END)) AS avg_resolution_minutes, ROUND(100.0 * COUNT(DISTINCT CASE WHEN b.ticket_id IS NOT NULL THEN tt.ticket_id END) / COUNT(*), 1) AS resolution_breach_rate FROM ticket_tags tt JOIN tenant_filter tf ON tf.id = tt.tenant_id JOIN tenant_totals tot ON tot.tenant_id = tt.tenant_id LEFT JOIN breaches b ON b.ticket_id = tt.ticket_id GROUP BY tf.name, tt.tag_name, tot.total_tickets ORDER BY tf.name, ticket_count DESC, tt.tag_name
Submitted to tonight's batch. Your work on this request is done.