Queries · q199 · Support Operations Manager · week 1, night 2
“Is ticket volume and SLA breach pain concentrated in a handful of merchants, and do any merchants stand out as outliers?”
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 1
Subject: Helpdesk operating review — what I need this week
Hi Ben,
Daniel and I need a baseline read on how the helpdesk actually runs, so we can walk into our first leadership review with a clear picture instead of impressions. This feeds our decisions on how we staff/structure agent groups, where we invest in macros and knowledge base content, and whether our current SLA targets are realistic. Given you only get a handful of reports a night, here's what matters most, roughly in priority order:
No need to go deep on all five — pick what fits in the nightly slots and flag anything surprising even if I didn't ask directly. Keep it at a level Daniel and I can act on quickly; we'll dig deeper live once we see the numbers.
Thanks, Mia
Subject: A few things I'd like your review to cover
Hi Ben,
As you dig into the helpdesk review this week, a few questions from my side:
If you find anything that points to a specific merchant or category driving a disproportionate share of the pain, flag it even if it's outside these three.
Thanks, Daniel
The analyst · week 1, night 2, request 1 of 3
Night 1 surfaced a methodology problem before any business insight: Grace's agent-group report rolled up every tenant's same-named group (e.g. all 120 merchants' 'Customer Care') into one row, while Omar's SLA-breach-by-group report kept each tenant's group separate (171 rows). The two reports use different grains under the same label, so they can't be compared, and neither tells us whether load/staffing is balanced within a real operational unit (a tenant's own group). Also, the company briefing says a handful of merchants drive most volume — I haven't checked that at all yet, and Daniel explicitly asked to flag any single merchant driving disproportionate pain. So tonight I'm fixing the grain: first, ticket volume and SLA performance by merchant to find concentration and any outlier tenant; second, the highest-volume tenant+group combinations (the real staffing units) to redo the staffing-balance read properly; third, a day-of-week/time-of-day cut of SLA breaches since Daniel specifically asked about time patterns and I have a slot free for it. Headline SLA numbers from report 3 (37.7% first-response breach, 20.3% resolution breach, urgent tickets breaching resolution over half the time) are worth noting to Mia/Daniel now even before drill-down.
The engineer · 1 dry run on the empty schema, then the submission
SELECT tenant_name, ticket_count, round(pct_of_total_tickets, 1) AS pct_of_total_tickets, round(sum(pct_of_total_tickets) OVER (ORDER BY ticket_count DESC, tenant_name), 1) AS cumulative_pct_of_total_tickets, round(first_response_breach_rate, 1) AS first_response_breach_rate, round(resolution_breach_rate, 1) AS resolution_breach_rate, round(avg_resolution_minutes, 1) AS avg_resolution_minutes, agent_count, total_tenants_with_tickets FROM ( SELECT t.name AS tenant_name, tk.tenant_id, tk.ticket_count, 100.0 * tk.ticket_count / tot.total_tickets AS pct_of_total_tickets, 100.0 * coalesce(frb.breach_count, 0) / nullif(tk.sla_ticket_count, 0) AS first_response_breach_rate, 100.0 * coalesce(rb.breach_count, 0) / nullif(tk.sla_ticket_count, 0) AS resolution_breach_rate, tk.avg_resolution_minutes, (SELECT count(DISTINCT sa.id) FROM support_agents sa WHERE sa.tenant_id = tk.tenant_id) AS agent_count, tot.total_tickets, tnt.total_tenants_with_tickets FROM ( SELECT tenant_id, count(*) AS ticket_count, count(*) FILTER (WHERE sla_policy_id IS NOT NULL) AS sla_ticket_count, avg(extract(epoch FROM (resolved_at - created_at)) / 60.0) FILTER (WHERE resolved_at IS NOT NULL) AS avg_resolution_minutes FROM tickets WHERE created_at >= timestamp '2025-01-01' AND created_at < timestamp '2026-01-01' GROUP BY tenant_id ) tk JOIN tenants t ON t.id = tk.tenant_id CROSS JOIN ( SELECT count(*) AS total_tickets FROM tickets WHERE created_at >= timestamp '2025-01-01' AND created_at < timestamp '2026-01-01' ) tot CROSS JOIN ( SELECT count(DISTINCT tenant_id) AS total_tenants_with_tickets FROM tickets WHERE created_at >= timestamp '2025-01-01' AND created_at < timestamp '2026-01-01' ) tnt LEFT JOIN ( SELECT sb.tenant_id, count(*) AS breach_count FROM sla_breaches sb JOIN tickets tk2 ON tk2.id = sb.ticket_id WHERE sb.breach_kind = 'first_response' AND tk2.created_at >= timestamp '2025-01-01' AND tk2.created_at < timestamp '2026-01-01' AND tk2.sla_policy_id IS NOT NULL GROUP BY sb.tenant_id ) frb ON frb.tenant_id = tk.tenant_id LEFT JOIN ( SELECT sb.tenant_id, count(*) AS breach_count FROM sla_breaches sb JOIN tickets tk3 ON tk3.id = sb.ticket_id WHERE sb.breach_kind = 'resolution' AND tk3.created_at >= timestamp '2025-01-01' AND tk3.created_at < timestamp '2026-01-01' AND tk3.sla_policy_id IS NOT NULL GROUP BY sb.tenant_id ) rb ON rb.tenant_id = tk.tenant_id ) x ORDER BY ticket_count DESC, tenant_name LIMIT 30
“Query compiles cleanly with the expected columns. Submitting.”
“Submitted. Summary of what the query does:
- Grain: one row per tenant, filtered to tenants with ≥1 ticket created in 2025.
- Volume & concentration: ticket_count, pct_of_total_tickets, and a running cumulative_pct_of_total_tickets (window function ordered by ticket_count desc, computed over the full tenant set before the LIMIT so the cumulative curve is correct even though only the top 30 rows print).
- SLA pain: first_response_breach_rate and resolution_breach_rate as % of that tenant's tickets that had an SLA policy assigned, each counted from sla_breaches by breach_kind.
- Resolution speed: avg_resolution_minutes from resolved_at - created_at for resolved tickets.
- Staffing: agent_count from support_agents.tenant_id (the simpler option offered).
- Added one constant column, total_tenants_with_tickets, to carry the total merchant count with 2025 tickets, satisfying the "note the total" ask directly in the data rather than relying on out-of-band reporting metadata.”
As Ben Carter saw it, from the small dataset the agents worked on
tenant_name | ticket_count | pct_of_total_tickets | cumulative_pct_of_total_tickets | first_response_breach_rate | resolution_breach_rate | avg_resolution_minutes | agent_count | total_tenants_with_tickets ------------------------------+--------------+----------------------+---------------------------------+----------------------------+------------------------+------------------------+-------------+--------------------------- Cragsley Outfitters | 400 | 17.2 | 17.2 | 57.0 | 29.8 | 2348.3 | 8 | 94 Marrowby & Vane | 231 | 10.0 | 27.2 | 59.7 | 39.4 | 5096.0 | 5 | 94 Studio Vrij | 166 | 7.2 | 34.4 | 55.4 | 38.6 | 7503.6 | 4 | 94 Brennquell Kaffee | 137 | 5.9 | 40.3 | 26.3 | 11.7 | 956.3 | 4 | 94 Grind Collective | 122 | 5.3 | 45.5 | 44.3 | 33.6 | 3865.3 | 3 | 94 Petalcourt Beauty | 119 | 5.1 | 50.6 | 54.6 | 32.8 | 2922.5 | 4 | 94 Kibblewick | 72 | 3.1 | 53.8 | 44.4 | 33.3 | 2446.2 | 4 | 94 Spice Route Box | 71 | 3.1 | 56.8 | 28.2 | 11.3 | 1014.9 | 3 | 94 Fennel & Flint | 67 | 2.9 | 59.7 | 35.8 | 17.9 | 2112.0 | 4 | 94 Honeycomb Larder | 58 | 2.5 | 62.2 | 24.1 | 10.3 | 1968.2 | 3 | 94 Ampfeld Elektronik | 56 | 2.4 | 64.6 | 23.2 | 14.3 | 1203.4 | 5 | 94 Sourdough Club | 47 | 2.0 | 66.6 | 29.8 | 8.5 | 1958.7 | 3 | 94 Ballycorran Industrial Supply | 44 | 1.9 | 68.5 | 31.8 | 6.8 | 1470.2 | 3 | 94 Cinder Peak Sports Co. | 43 | 1.9 | 70.4 | 30.2 | 9.3 | 1036.6 | 4 | 94 Wiesgrund Wohnen | 32 | 1.4 | 71.8 | 15.6 | 6.3 | 654.6 | 3 | 94 Linden & Loom | 29 | 1.3 | 73.0 | 24.1 | 13.8 | 809.4 | 3 | 94 Gratlinie Outdoor | 27 | 1.2 | 74.2 | 11.1 | 3.7 | 650.6 | 4 | 94 Oakhurst Tailoring | 27 | 1.2 | 75.3 | 11.1 | 0.0 | 2000.0 | 3 | 94 Rue Lafayette Studio | 27 | 1.2 | 76.5 | 18.5 | 0.0 | 732.5 | 3 | 94 Coastline Interiors | 21 | 0.9 | 77.4 | 4.8 | 4.8 | 1014.0 | 3 | 94 Thistlecombe | 21 | 0.9 | 78.3 | 9.5 | 0.0 | 1075.7 | 3 | 94 Kayak Corner | 20 | 0.9 | 79.2 | 20.0 | 0.0 | 2255.2 | 3 | 94 Velvet Ash | 19 | 0.8 | 80.0 | 5.3 | 0.0 | 1347.4 | 3 | 94 Willow & Wren | 19 | 0.8 | 80.8 | 15.8 | 5.3 | 1967.8 | 3 | 94 Juniper Apothecary | 17 | 0.7 | 81.6 | 17.6 | 0.0 | 1896.9 | 3 | 94 OfficeHub Nordic | 15 | 0.6 | 82.2 | 13.3 | 20.0 | 1313.0 | 3 | 94 The Balm Room | 15 | 0.6 | 82.8 | 6.7 | 0.0 | 2050.4 | 3 | 94 Wohnglück | 15 | 0.6 | 83.5 | 6.7 | 13.3 | 877.1 | 3 | 94 Feather & Fin | 14 | 0.6 | 84.1 | 35.7 | 7.1 | 2795.8 | 3 | 94 Fjordline Wear | 14 | 0.6 | 84.7 | 42.9 | 7.1 | 2071.0 | 3 | 94 (30 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 tenant_name, ticket_count, round(pct_of_total_tickets, 1) AS pct_of_total_tickets, round(sum(pct_of_total_tickets) OVER (ORDER BY ticket_count DESC, tenant_name), 1) AS cumulative_pct_of_total_tickets, round(first_response_breach_rate, 1) AS first_response_breach_rate, round(resolution_breach_rate, 1) AS resolution_breach_rate, round(avg_resolution_minutes, 1) AS avg_resolution_minutes, pct_of_total_tickets AS pct_of_total_tickets, sum(pct_of_total_tickets) OVER (ORDER BY ticket_count DESC NULLS FIRST, tenant_name NULLS LAST) AS cumulative_pct_of_total_tickets, first_response_breach_rate AS first_response_breach_rate, resolution_breach_rate AS resolution_breach_rate, avg_resolution_minutes AS avg_resolution_minutes, agent_count, total_tenants_with_ticketsFROM ( SELECT t.name AS tenant_name, tk.tenant_id, tk.ticket_count, tk.tenant_id AS tenant_id, tk.ticket_count AS ticket_count, 100.0 * tk.ticket_count / tot.total_tickets AS pct_of_total_tickets, 100.0 * coalesce(frb.breach_count, 0) / nullif(tk.sla_ticket_count, 0) AS first_response_breach_rate, 100.0 * coalesce(rb.breach_count, 0) / nullif(tk.sla_ticket_count, 0) AS resolution_breach_rate, tk.avg_resolution_minutes, tk.avg_resolution_minutes AS avg_resolution_minutes, (SELECT count(DISTINCT sa.id) FROM support_agents sa WHERE sa.tenant_id = tk.tenant_id) AS agent_count, tot.total_tickets, tnt.total_tenants_with_tickets tot.total_tickets AS total_tickets, tnt.total_tenants_with_tickets AS total_tenants_with_tickets FROM ( SELECT tenant_id, count(*) AS ticket_count, count(*) FILTER (WHERE sla_policy_id IS NOT NULL) AS sla_ticket_count, avg(extract(epoch FROM (resolved_at - created_at)) / 60.0) FILTER (WHERE resolved_at IS NOT NULL) AS avg_resolution_minutes avg({{seconds_between(created_at, resolved_at)}} / 60.0) FILTER (WHERE resolved_at IS NOT NULL) AS avg_resolution_minutes FROM tickets WHERE created_at >= timestamp '2025-01-01' AND created_at < timestamp '2026-01-01' GROUP BY tenant_id ) tk JOIN tenants t ON t.id = tk.tenant_id CROSS JOIN ( SELECT count(*) AS total_tickets FROM tickets WHERE created_at >= timestamp '2025-01-01' AND created_at < timestamp '2026-01-01' ) tot CROSS JOIN ( SELECT count(DISTINCT tenant_id) AS total_tenants_with_tickets FROM tickets WHERE created_at >= timestamp '2025-01-01' AND created_at < timestamp '2026-01-01' ) tnt LEFT JOIN ( SELECT sb.tenant_id, count(*) AS breach_count SELECT sb.tenant_id AS tenant_id, count(*) AS breach_count FROM sla_breaches sb JOIN tickets tk2 ON tk2.id = sb.ticket_id WHERE sb.breach_kind = 'first_response' AND tk2.created_at >= timestamp '2025-01-01' AND tk2.created_at < timestamp '2026-01-01' AND tk2.sla_policy_id IS NOT NULL GROUP BY sb.tenant_id ) frb ON frb.tenant_id = tk.tenant_id LEFT JOIN ( SELECT sb.tenant_id, count(*) AS breach_count SELECT sb.tenant_id AS tenant_id, count(*) AS breach_count FROM sla_breaches sb JOIN tickets tk3 ON tk3.id = sb.ticket_id WHERE sb.breach_kind = 'resolution' AND tk3.created_at >= timestamp '2025-01-01' AND tk3.created_at < timestamp '2026-01-01' AND tk3.sla_policy_id IS NOT NULL GROUP BY sb.tenant_id ) rb ON rb.tenant_id = tk.tenant_id) xORDER BY ticket_count DESC, tenant_nameLIMIT 30ORDER BY ticket_count DESC NULLS FIRST, tenant_name NULLS LAST, tenant_id NULLS LAST{{fetch_first(30)}}
SELECT tenant_name, ticket_count, pct_of_total_tickets AS pct_of_total_tickets, sum(pct_of_total_tickets) OVER (ORDER BY ticket_count DESC NULLS FIRST, tenant_name NULLS LAST) AS cumulative_pct_of_total_tickets, first_response_breach_rate AS first_response_breach_rate, resolution_breach_rate AS resolution_breach_rate, avg_resolution_minutes AS avg_resolution_minutes, agent_count, total_tenants_with_tickets FROM ( SELECT t.name AS tenant_name, tk.tenant_id AS tenant_id, tk.ticket_count AS ticket_count, 100.0 * tk.ticket_count / tot.total_tickets AS pct_of_total_tickets, 100.0 * coalesce(frb.breach_count, 0) / nullif(tk.sla_ticket_count, 0) AS first_response_breach_rate, 100.0 * coalesce(rb.breach_count, 0) / nullif(tk.sla_ticket_count, 0) AS resolution_breach_rate, tk.avg_resolution_minutes AS avg_resolution_minutes, (SELECT count(DISTINCT sa.id) FROM support_agents sa WHERE sa.tenant_id = tk.tenant_id) AS agent_count, tot.total_tickets AS total_tickets, tnt.total_tenants_with_tickets AS total_tenants_with_tickets FROM ( SELECT tenant_id, count(*) AS ticket_count, count(*) FILTER (WHERE sla_policy_id IS NOT NULL) AS sla_ticket_count, avg(CAST(EXTRACT(EPOCH FROM (resolved_at - created_at)) AS NUMERIC(18,6)) / 60.0) FILTER (WHERE resolved_at IS NOT NULL) AS avg_resolution_minutes FROM tickets WHERE created_at >= timestamp '2025-01-01' AND created_at < timestamp '2026-01-01' GROUP BY tenant_id ) tk JOIN tenants t ON t.id = tk.tenant_id CROSS JOIN ( SELECT count(*) AS total_tickets FROM tickets WHERE created_at >= timestamp '2025-01-01' AND created_at < timestamp '2026-01-01' ) tot CROSS JOIN ( SELECT count(DISTINCT tenant_id) AS total_tenants_with_tickets FROM tickets WHERE created_at >= timestamp '2025-01-01' AND created_at < timestamp '2026-01-01' ) tnt LEFT JOIN ( SELECT sb.tenant_id AS tenant_id, count(*) AS breach_count FROM sla_breaches sb JOIN tickets tk2 ON tk2.id = sb.ticket_id WHERE sb.breach_kind = 'first_response' AND tk2.created_at >= timestamp '2025-01-01' AND tk2.created_at < timestamp '2026-01-01' AND tk2.sla_policy_id IS NOT NULL GROUP BY sb.tenant_id ) frb ON frb.tenant_id = tk.tenant_id LEFT JOIN ( SELECT sb.tenant_id AS tenant_id, count(*) AS breach_count FROM sla_breaches sb JOIN tickets tk3 ON tk3.id = sb.ticket_id WHERE sb.breach_kind = 'resolution' AND tk3.created_at >= timestamp '2025-01-01' AND tk3.created_at < timestamp '2026-01-01' AND tk3.sla_policy_id IS NOT NULL GROUP BY sb.tenant_id ) rb ON rb.tenant_id = tk.tenant_id ) x ORDER BY ticket_count DESC NULLS FIRST, tenant_name NULLS LAST, tenant_id NULLS LAST FETCH FIRST 30 ROWS ONLY
SELECT tenant_name, ticket_count, pct_of_total_tickets AS pct_of_total_tickets, sum(pct_of_total_tickets) OVER (ORDER BY ticket_count DESC NULLS FIRST, tenant_name NULLS LAST) AS cumulative_pct_of_total_tickets, first_response_breach_rate AS first_response_breach_rate, resolution_breach_rate AS resolution_breach_rate, avg_resolution_minutes AS avg_resolution_minutes, agent_count, total_tenants_with_tickets FROM ( SELECT t.name AS tenant_name, tk.tenant_id AS tenant_id, tk.ticket_count AS ticket_count, 100.0 * tk.ticket_count / tot.total_tickets AS pct_of_total_tickets, 100.0 * coalesce(frb.breach_count, 0) / nullif(tk.sla_ticket_count, 0) AS first_response_breach_rate, 100.0 * coalesce(rb.breach_count, 0) / nullif(tk.sla_ticket_count, 0) AS resolution_breach_rate, tk.avg_resolution_minutes AS avg_resolution_minutes, (SELECT count(DISTINCT sa.id) FROM support_agents sa WHERE sa.tenant_id = tk.tenant_id) AS agent_count, tot.total_tickets AS total_tickets, tnt.total_tenants_with_tickets AS total_tenants_with_tickets FROM ( SELECT tenant_id, count(*) AS ticket_count, count(*) FILTER (WHERE sla_policy_id IS NOT NULL) AS sla_ticket_count, avg(CAST(EXTRACT(EPOCH FROM (resolved_at - created_at)) AS NUMERIC(18,6)) / 60.0) FILTER (WHERE resolved_at IS NOT NULL) AS avg_resolution_minutes FROM tickets WHERE created_at >= timestamp '2025-01-01' AND created_at < timestamp '2026-01-01' GROUP BY tenant_id ) tk JOIN tenants t ON t.id = tk.tenant_id CROSS JOIN ( SELECT count(*) AS total_tickets FROM tickets WHERE created_at >= timestamp '2025-01-01' AND created_at < timestamp '2026-01-01' ) tot CROSS JOIN ( SELECT count(DISTINCT tenant_id) AS total_tenants_with_tickets FROM tickets WHERE created_at >= timestamp '2025-01-01' AND created_at < timestamp '2026-01-01' ) tnt LEFT JOIN ( SELECT sb.tenant_id AS tenant_id, count(*) AS breach_count FROM sla_breaches sb JOIN tickets tk2 ON tk2.id = sb.ticket_id WHERE sb.breach_kind = 'first_response' AND tk2.created_at >= timestamp '2025-01-01' AND tk2.created_at < timestamp '2026-01-01' AND tk2.sla_policy_id IS NOT NULL GROUP BY sb.tenant_id ) frb ON frb.tenant_id = tk.tenant_id LEFT JOIN ( SELECT sb.tenant_id AS tenant_id, count(*) AS breach_count FROM sla_breaches sb JOIN tickets tk3 ON tk3.id = sb.ticket_id WHERE sb.breach_kind = 'resolution' AND tk3.created_at >= timestamp '2025-01-01' AND tk3.created_at < timestamp '2026-01-01' AND tk3.sla_policy_id IS NOT NULL GROUP BY sb.tenant_id ) rb ON rb.tenant_id = tk.tenant_id ) x ORDER BY ticket_count DESC NULLS FIRST, tenant_name NULLS LAST, tenant_id NULLS LAST LIMIT 30
SELECT tenant_name, ticket_count, pct_of_total_tickets AS pct_of_total_tickets, sum(pct_of_total_tickets) OVER (ORDER BY ticket_count DESC NULLS FIRST, tenant_name NULLS LAST) AS cumulative_pct_of_total_tickets, first_response_breach_rate AS first_response_breach_rate, resolution_breach_rate AS resolution_breach_rate, avg_resolution_minutes AS avg_resolution_minutes, agent_count, total_tenants_with_tickets FROM ( SELECT t.name AS tenant_name, tk.tenant_id AS tenant_id, tk.ticket_count AS ticket_count, 100.0 * tk.ticket_count / tot.total_tickets AS pct_of_total_tickets, 100.0 * coalesce(frb.breach_count, 0) / nullif(tk.sla_ticket_count, 0) AS first_response_breach_rate, 100.0 * coalesce(rb.breach_count, 0) / nullif(tk.sla_ticket_count, 0) AS resolution_breach_rate, tk.avg_resolution_minutes AS avg_resolution_minutes, (SELECT count(DISTINCT sa.id) FROM support_agents sa WHERE sa.tenant_id = tk.tenant_id) AS agent_count, tot.total_tickets AS total_tickets, tnt.total_tenants_with_tickets AS total_tenants_with_tickets FROM ( SELECT tenant_id, count(*) AS ticket_count, count(*) FILTER (WHERE sla_policy_id IS NOT NULL) AS sla_ticket_count, avg(CAST(divideDecimal(toDecimal128(dateDiff('microsecond', created_at, resolved_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6))) / 60.0) FILTER (WHERE resolved_at IS NOT NULL) AS avg_resolution_minutes FROM tickets WHERE created_at >= timestamp '2025-01-01' AND created_at < timestamp '2026-01-01' GROUP BY tenant_id ) tk JOIN tenants t ON t.id = tk.tenant_id CROSS JOIN ( SELECT count(*) AS total_tickets FROM tickets WHERE created_at >= timestamp '2025-01-01' AND created_at < timestamp '2026-01-01' ) tot CROSS JOIN ( SELECT count(DISTINCT tenant_id) AS total_tenants_with_tickets FROM tickets WHERE created_at >= timestamp '2025-01-01' AND created_at < timestamp '2026-01-01' ) tnt LEFT JOIN ( SELECT sb.tenant_id AS tenant_id, count(*) AS breach_count FROM sla_breaches sb JOIN tickets tk2 ON tk2.id = sb.ticket_id WHERE sb.breach_kind = 'first_response' AND tk2.created_at >= timestamp '2025-01-01' AND tk2.created_at < timestamp '2026-01-01' AND tk2.sla_policy_id IS NOT NULL GROUP BY sb.tenant_id ) frb ON frb.tenant_id = tk.tenant_id LEFT JOIN ( SELECT sb.tenant_id AS tenant_id, count(*) AS breach_count FROM sla_breaches sb JOIN tickets tk3 ON tk3.id = sb.ticket_id WHERE sb.breach_kind = 'resolution' AND tk3.created_at >= timestamp '2025-01-01' AND tk3.created_at < timestamp '2026-01-01' AND tk3.sla_policy_id IS NOT NULL GROUP BY sb.tenant_id ) rb ON rb.tenant_id = tk.tenant_id ) x ORDER BY ticket_count DESC NULLS FIRST, tenant_name NULLS LAST, tenant_id NULLS LAST FETCH FIRST 30 ROWS ONLY
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: Ticket volume and SLA performance by merchant Question: Is ticket volume and SLA breach pain concentrated in a handful of merchants, and do any merchants stand out as outliers? Grain: one row per tenant (merchant) Columns: - tenant_name - ticket_count (created in 2025) - pct_of_total_tickets - cumulative_pct_of_total_tickets (rows ordered by ticket_count desc) - first_response_breach_rate - resolution_breach_rate - avg_resolution_minutes - agent_count (distinct support_agents belonging to that tenant via agent_group_members, or via support_agents.tenant_id if simpler) Details: Window: tickets.created_at between 2025-01-01 and 2025-12-31. Source tables: tickets (join tenants for name), sla_breaches (breach_kind = first_response / resolution, as % of that tenant's tickets with sla_policy_id not null), support_agents. Order by ticket_count descending; print the top 30 rows but note the total number of tenants with at least one ticket in 2025. Round rates to 1 decimal percent, cumulative_pct to 1 decimal. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runSELECT tenant_name, ticket_count, round(pct_of_total_tickets, 1) AS pct_of_total_tickets, round(sum(pct_of_total_tickets) OVER (ORDER BY ticket_count DESC, tenant_name), 1) AS cumulative_pct_of_total_tickets, round(first_response_breach_rate, 1) AS first_response_breach_rate, round(resolution_breach_rate, 1) AS resolution_breach_rate, round(avg_resolution_minutes, 1) AS avg_resolution_minutes, agent_count, total_tenants_with_tickets FROM ( SELECT t.name AS tenant_name, tk.tenant_id, tk.ticket_count, 100.0 * tk.ticket_count / tot.total_tickets AS pct_of_total_tickets, 100.0 * coalesce(frb.breach_count, 0) / nullif(tk.sla_ticket_count, 0) AS first_response_breach_rate, 100.0 * coalesce(rb.breach_count, 0) / nullif(tk.sla_ticket_count, 0) AS resolution_breach_rate, tk.avg_resolution_minutes, (SELECT count(DISTINCT sa.id) FROM support_agents sa WHERE sa.tenant_id = tk.tenant_id) AS agent_count, tot.total_tickets, tnt.total_tenants_with_tickets FROM ( SELECT tenant_id, count(*) AS ticket_count, count(*) FILTER (WHERE sla_policy_id IS NOT NULL) AS sla_ticket_count, avg(extract(epoch FROM (resolved_at - created_at)) / 60.0) FILTER (WHERE resolved_at IS NOT NULL) AS avg_resolution_minutes FROM tickets WHERE created_at >= timestamp '2025-01-01' AND created_at < timestamp '2026-01-01' GROUP BY tenant_id ) tk JOIN tenants t ON t.id = tk.tenant_id CROSS JOIN ( SELECT count(*) AS total_tickets FROM tickets WHERE created_at >= timestamp '2025-01-01' AND created_at < timestamp '2026-01-01' ) tot CROSS JOIN ( SELECT count(DISTINCT tenant_id) AS total_tenants_with_tickets FROM tickets WHERE created_at >= timestamp '2025-01-01' AND created_at < timestamp '2026-01-01' ) tnt LEFT JOIN ( SELECT sb.tenant_id, count(*) AS breach_count FROM sla_breaches sb JOIN tickets tk2 ON tk2.id = sb.ticket_id WHERE sb.breach_kind = 'first_response' AND tk2.created_at >= timestamp '2025-01-01' AND tk2.created_at < timestamp '2026-01-01' AND tk2.sla_policy_id IS NOT NULL GROUP BY sb.tenant_id ) frb ON frb.tenant_id = tk.tenant_id LEFT JOIN ( SELECT sb.tenant_id, count(*) AS breach_count FROM sla_breaches sb JOIN tickets tk3 ON tk3.id = sb.ticket_id WHERE sb.breach_kind = 'resolution' AND tk3.created_at >= timestamp '2025-01-01' AND tk3.created_at < timestamp '2026-01-01' AND tk3.sla_policy_id IS NOT NULL GROUP BY sb.tenant_id ) rb ON rb.tenant_id = tk.tenant_id ) x ORDER BY ticket_count DESC, tenant_name LIMIT 30
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: tenant_name, ticket_count, pct_of_total_tickets, cumulative_pct_of_total_tickets, first_response_breach_rate, resolution_breach_rate, avg_resolution_minutes, agent_count, total_tenants_with_tickets
submitSELECT tenant_name, ticket_count, round(pct_of_total_tickets, 1) AS pct_of_total_tickets, round(sum(pct_of_total_tickets) OVER (ORDER BY ticket_count DESC, tenant_name), 1) AS cumulative_pct_of_total_tickets, round(first_response_breach_rate, 1) AS first_response_breach_rate, round(resolution_breach_rate, 1) AS resolution_breach_rate, round(avg_resolution_minutes, 1) AS avg_resolution_minutes, agent_count, total_tenants_with_tickets FROM ( SELECT t.name AS tenant_name, tk.tenant_id, tk.ticket_count, 100.0 * tk.ticket_count / tot.total_tickets AS pct_of_total_tickets, 100.0 * coalesce(frb.breach_count, 0) / nullif(tk.sla_ticket_count, 0) AS first_response_breach_rate, 100.0 * coalesce(rb.breach_count, 0) / nullif(tk.sla_ticket_count, 0) AS resolution_breach_rate, tk.avg_resolution_minutes, (SELECT count(DISTINCT sa.id) FROM support_agents sa WHERE sa.tenant_id = tk.tenant_id) AS agent_count, tot.total_tickets, tnt.total_tenants_with_tickets FROM ( SELECT tenant_id, count(*) AS ticket_count, count(*) FILTER (WHERE sla_policy_id IS NOT NULL) AS sla_ticket_count, avg(extract(epoch FROM (resolved_at - created_at)) / 60.0) FILTER (WHERE resolved_at IS NOT NULL) AS avg_resolution_minutes FROM tickets WHERE created_at >= timestamp '2025-01-01' AND created_at < timestamp '2026-01-01' GROUP BY tenant_id ) tk JOIN tenants t ON t.id = tk.tenant_id CROSS JOIN ( SELECT count(*) AS total_tickets FROM tickets WHERE created_at >= timestamp '2025-01-01' AND created_at < timestamp '2026-01-01' ) tot CROSS JOIN ( SELECT count(DISTINCT tenant_id) AS total_tenants_with_tickets FROM tickets WHERE created_at >= timestamp '2025-01-01' AND created_at < timestamp '2026-01-01' ) tnt LEFT JOIN ( SELECT sb.tenant_id, count(*) AS breach_count FROM sla_breaches sb JOIN tickets tk2 ON tk2.id = sb.ticket_id WHERE sb.breach_kind = 'first_response' AND tk2.created_at >= timestamp '2025-01-01' AND tk2.created_at < timestamp '2026-01-01' AND tk2.sla_policy_id IS NOT NULL GROUP BY sb.tenant_id ) frb ON frb.tenant_id = tk.tenant_id LEFT JOIN ( SELECT sb.tenant_id, count(*) AS breach_count FROM sla_breaches sb JOIN tickets tk3 ON tk3.id = sb.ticket_id WHERE sb.breach_kind = 'resolution' AND tk3.created_at >= timestamp '2025-01-01' AND tk3.created_at < timestamp '2026-01-01' AND tk3.sla_policy_id IS NOT NULL GROUP BY sb.tenant_id ) rb ON rb.tenant_id = tk.tenant_id ) x ORDER BY ticket_count DESC, tenant_name LIMIT 30
Submitted to tonight's batch. Your work on this request is done.