Queries · q248 · VP Customer Experience · week 2, night 2
“Is the broad urgent-ticket resolution failure driven by unrealistically tight SLA targets, or are merchants genuinely resolving urgent tickets slower than a reasonable target?”
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 2 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
WITH evaluable_tickets AS ( SELECT t0.id AS ticket_id, t0.tenant_id AS tenant_id, t0.created_at AS created_at, t0.resolved_at AS resolved_at, t1.resolution_target_minutes AS resolution_target_minutes, CASE WHEN t0.resolved_at IS NULL THEN NULL ELSE EXTRACT(EPOCH FROM (t0.resolved_at - t0.created_at)) / 3600.0 END AS actual_hours, CASE WHEN t0.resolved_at IS NULL THEN 1 WHEN EXTRACT(EPOCH FROM (t0.resolved_at - t0.created_at)) / 60.0 > t1.resolution_target_minutes THEN 1 ELSE 0 END AS is_miss FROM tickets t0 JOIN sla_policies t1 ON t1.id = t0.sla_policy_id WHERE t0.priority = 'urgent' AND t0.sla_policy_id IS NOT NULL AND t0.created_at >= DATE '2025-01-01' AND t0.created_at < DATE '2026-01-01' ), tenant_counts AS ( SELECT tenant_id, COUNT(*) AS evaluated_count FROM evaluable_tickets GROUP BY tenant_id HAVING COUNT(*) >= 5 ), tenant_target AS ( SELECT e.tenant_id AS tenant_id, SUM(e.resolution_target_minutes) / COUNT(*) / 60.0 AS weighted_target_hours, COUNT(DISTINCT e.resolution_target_minutes) AS distinct_target_count FROM evaluable_tickets e JOIN tenant_counts c ON c.tenant_id = e.tenant_id GROUP BY e.tenant_id ), tenant_actual AS ( SELECT e.tenant_id AS tenant_id, AVG(e.actual_hours) AS avg_actual_hours, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY e.actual_hours) AS median_actual_hours FROM evaluable_tickets e JOIN tenant_counts c ON c.tenant_id = e.tenant_id WHERE e.resolved_at IS NOT NULL GROUP BY e.tenant_id ), tenant_miss AS ( SELECT e.tenant_id AS tenant_id, SUM(e.is_miss) AS miss_count FROM evaluable_tickets e JOIN tenant_counts c ON c.tenant_id = e.tenant_id GROUP BY e.tenant_id ) SELECT t9.name AS merchant_name, ROUND(t1.weighted_target_hours, 1) AS urgent_resolution_target_hours, t0.evaluated_count AS urgent_tickets_evaluated_2025, ROUND(t2.median_actual_hours, 1) AS median_actual_resolution_hours, ROUND(t2.avg_actual_hours, 1) AS avg_actual_resolution_hours, ROUND(100.0 * t3.miss_count / t0.evaluated_count, 1) AS urgent_miss_rate_pct, CASE WHEN t1.distinct_target_count > 1 THEN 'Y' ELSE 'N' END AS multiple_urgent_sla_policies FROM tenant_counts t0 JOIN tenant_target t1 ON t1.tenant_id = t0.tenant_id LEFT JOIN tenant_actual t2 ON t2.tenant_id = t0.tenant_id JOIN tenant_miss t3 ON t3.tenant_id = t0.tenant_id JOIN tenants t9 ON t9.id = t0.tenant_id ORDER BY urgent_miss_rate_pct DESC, merchant_name ASC LIMIT 50
“Query compiles cleanly with the expected 7 columns (the 6 requested plus a flag for tenants with more than one urgent SLA target, since the request asked that case be noted). Submitting.”
“Submitted. Summary of what the report does:
- Grain: one row per tenant, restricted to tenants with ≥5 evaluable urgent tickets created in 2025 (priority='urgent', sla_policy_id not null, created_at in 2025).
- Target hours: ticket-count-weighted average of resolution_target_minutes/60 across that tenant's evaluated urgent tickets.
- Actual hours (median/avg): computed only over tickets with a non-null resolved_at, in hours.
- Miss rate: unresolved-as-of-cutoff or resolved past target, divided by all evaluated tickets (including unresolved ones), as a percentage.
- Added one extra column, multiple_urgent_sla_policies (Y/N), to surface the case the analyst asked to have noted — when a tenant's evaluated tickets span more than one distinct resolution target.
- Ordered by miss rate descending, tie-broken by merchant name; capped at 50 rows.”
As Mateo Ruiz saw it, from the small dataset the agents worked on
merchant_name | urgent_resolution_target_hours | urgent_tickets_evaluated_2025 | median_actual_resolution_hours | avg_actual_resolution_hours | urgent_miss_rate_pct | multiple_urgent_sla_policies --------------------+--------------------------------+-------------------------------+--------------------------------+-----------------------------+----------------------+----------------------------- Honeycomb Larder | 8.0 | 9 | 59.5 | 56.1 | 100.0 | N Sourdough Club | 8.0 | 7 | 23.9 | 32.0 | 85.7 | N Ampfeld Elektronik | 8.0 | 6 | 12.0 | 30.2 | 83.3 | N Marrowby & Vane | 8.0 | 33 | 18.2 | 79.6 | 66.7 | N Cragsley Outfitters | 8.0 | 50 | 13.4 | 27.6 | 66.0 | N Studio Vrij | 8.0 | 11 | 19.9 | 67.1 | 63.6 | N Linden & Loom | 8.0 | 6 | 5.8 | 6.1 | 50.0 | N Petalcourt Beauty | 8.0 | 10 | 14.6 | 30.3 | 50.0 | N Fennel & Flint | 8.0 | 5 | 1.2 | 15.3 | 40.0 | N Wohnglück | 8.0 | 5 | 3.1 | 16.6 | 40.0 | N Spice Route Box | 8.0 | 9 | 5.1 | 9.0 | 33.3 | N Brennquell Kaffee | 8.0 | 16 | 4.1 | 9.3 | 31.3 | N Grind Collective | 8.0 | 6 | 1.1 | 4.0 | 16.7 | N Wiesgrund Wohnen | 8.0 | 7 | 0.8 | 2.4 | 14.3 | N (14 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 evaluable_tickets AS ( SELECT t0.id AS ticket_id, t0.tenant_id AS tenant_id, t0.created_at AS created_at, t0.resolved_at AS resolved_at, t1.resolution_target_minutes AS resolution_target_minutes, CASE WHEN t0.resolved_at IS NULL THEN NULL ELSE EXTRACT(EPOCH FROM (t0.resolved_at - t0.created_at)) / 3600.0 ELSE {{seconds_between(t0.created_at, t0.resolved_at)}} / 3600.0 END AS actual_hours, CASE WHEN t0.resolved_at IS NULL THEN 1 WHEN EXTRACT(EPOCH FROM (t0.resolved_at - t0.created_at)) / 60.0 > t1.resolution_target_minutes THEN 1 WHEN {{seconds_between(t0.created_at, t0.resolved_at)}} / 60.0 > t1.resolution_target_minutes THEN 1 ELSE 0 END AS is_miss FROM tickets t0 JOIN sla_policies t1 ON t1.id = t0.sla_policy_id WHERE t0.priority = 'urgent' AND t0.sla_policy_id IS NOT NULL AND t0.created_at >= DATE '2025-01-01' AND t0.created_at < DATE '2026-01-01'),tenant_counts AS ( SELECT tenant_id, COUNT(*) AS evaluated_count FROM evaluable_tickets GROUP BY tenant_id HAVING COUNT(*) >= 5),tenant_target AS ( SELECT e.tenant_id AS tenant_id, SUM(e.resolution_target_minutes) / COUNT(*) / 60.0 AS weighted_target_hours, CAST(SUM(e.resolution_target_minutes) AS NUMERIC(18,6)) / COUNT(*) / 60.0 AS weighted_target_hours, COUNT(DISTINCT e.resolution_target_minutes) AS distinct_target_count FROM evaluable_tickets e JOIN tenant_counts c ON c.tenant_id = e.tenant_id GROUP BY e.tenant_id),tenant_actual AS ( SELECT e.tenant_id AS tenant_id, AVG(e.actual_hours) AS avg_actual_hours, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY e.actual_hours) AS median_actual_hours {{percentile_cont(0.5, e.actual_hours)}} AS median_actual_hours FROM evaluable_tickets e JOIN tenant_counts c ON c.tenant_id = e.tenant_id WHERE e.resolved_at IS NOT NULL GROUP BY e.tenant_id),tenant_miss AS ( SELECT e.tenant_id AS tenant_id, SUM(e.is_miss) AS miss_count FROM evaluable_tickets e JOIN tenant_counts c ON c.tenant_id = e.tenant_id GROUP BY e.tenant_id)SELECT t9.name AS merchant_name, ROUND(t1.weighted_target_hours, 1) AS urgent_resolution_target_hours, t1.weighted_target_hours AS urgent_resolution_target_hours, t0.evaluated_count AS urgent_tickets_evaluated_2025, ROUND(t2.median_actual_hours, 1) AS median_actual_resolution_hours, ROUND(t2.avg_actual_hours, 1) AS avg_actual_resolution_hours, ROUND(100.0 * t3.miss_count / t0.evaluated_count, 1) AS urgent_miss_rate_pct, t2.median_actual_hours AS median_actual_resolution_hours, t2.avg_actual_hours AS avg_actual_resolution_hours, 100.0 * t3.miss_count / t0.evaluated_count AS urgent_miss_rate_pct, CASE WHEN t1.distinct_target_count > 1 THEN 'Y' ELSE 'N' END AS multiple_urgent_sla_policiesFROM tenant_counts t0JOIN tenant_target t1 ON t1.tenant_id = t0.tenant_idLEFT JOIN tenant_actual t2 ON t2.tenant_id = t0.tenant_idJOIN tenant_miss t3 ON t3.tenant_id = t0.tenant_idJOIN tenants t9 ON t9.id = t0.tenant_idORDER BY urgent_miss_rate_pct DESC, merchant_name ASCLIMIT 50ORDER BY urgent_miss_rate_pct DESC NULLS FIRST, merchant_name ASC NULLS LAST, t9.id ASC NULLS LAST{{fetch_first(50)}}
WITH evaluable_tickets AS ( SELECT t0.id AS ticket_id, t0.tenant_id AS tenant_id, t0.created_at AS created_at, t0.resolved_at AS resolved_at, t1.resolution_target_minutes AS resolution_target_minutes, CASE WHEN t0.resolved_at IS NULL THEN NULL ELSE CAST(EXTRACT(EPOCH FROM (t0.resolved_at - t0.created_at)) AS NUMERIC(18,6)) / 3600.0 END AS actual_hours, CASE WHEN t0.resolved_at IS NULL THEN 1 WHEN CAST(EXTRACT(EPOCH FROM (t0.resolved_at - t0.created_at)) AS NUMERIC(18,6)) / 60.0 > t1.resolution_target_minutes THEN 1 ELSE 0 END AS is_miss FROM tickets t0 JOIN sla_policies t1 ON t1.id = t0.sla_policy_id WHERE t0.priority = 'urgent' AND t0.sla_policy_id IS NOT NULL AND t0.created_at >= DATE '2025-01-01' AND t0.created_at < DATE '2026-01-01' ), tenant_counts AS ( SELECT tenant_id, COUNT(*) AS evaluated_count FROM evaluable_tickets GROUP BY tenant_id HAVING COUNT(*) >= 5 ), tenant_target AS ( SELECT e.tenant_id AS tenant_id, CAST(SUM(e.resolution_target_minutes) AS NUMERIC(18,6)) / COUNT(*) / 60.0 AS weighted_target_hours, COUNT(DISTINCT e.resolution_target_minutes) AS distinct_target_count FROM evaluable_tickets e JOIN tenant_counts c ON c.tenant_id = e.tenant_id GROUP BY e.tenant_id ), tenant_actual AS ( SELECT e.tenant_id AS tenant_id, AVG(e.actual_hours) AS avg_actual_hours, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY e.actual_hours) AS median_actual_hours FROM evaluable_tickets e JOIN tenant_counts c ON c.tenant_id = e.tenant_id WHERE e.resolved_at IS NOT NULL GROUP BY e.tenant_id ), tenant_miss AS ( SELECT e.tenant_id AS tenant_id, SUM(e.is_miss) AS miss_count FROM evaluable_tickets e JOIN tenant_counts c ON c.tenant_id = e.tenant_id GROUP BY e.tenant_id ) SELECT t9.name AS merchant_name, t1.weighted_target_hours AS urgent_resolution_target_hours, t0.evaluated_count AS urgent_tickets_evaluated_2025, t2.median_actual_hours AS median_actual_resolution_hours, t2.avg_actual_hours AS avg_actual_resolution_hours, 100.0 * t3.miss_count / t0.evaluated_count AS urgent_miss_rate_pct, CASE WHEN t1.distinct_target_count > 1 THEN 'Y' ELSE 'N' END AS multiple_urgent_sla_policies FROM tenant_counts t0 JOIN tenant_target t1 ON t1.tenant_id = t0.tenant_id LEFT JOIN tenant_actual t2 ON t2.tenant_id = t0.tenant_id JOIN tenant_miss t3 ON t3.tenant_id = t0.tenant_id JOIN tenants t9 ON t9.id = t0.tenant_id ORDER BY urgent_miss_rate_pct DESC NULLS FIRST, merchant_name ASC NULLS LAST, t9.id ASC NULLS LAST FETCH FIRST 50 ROWS ONLY
WITH evaluable_tickets AS ( SELECT t0.id AS ticket_id, t0.tenant_id AS tenant_id, t0.created_at AS created_at, t0.resolved_at AS resolved_at, t1.resolution_target_minutes AS resolution_target_minutes, CASE WHEN t0.resolved_at IS NULL THEN NULL ELSE CAST(EXTRACT(EPOCH FROM (t0.resolved_at - t0.created_at)) AS NUMERIC(18,6)) / 3600.0 END AS actual_hours, CASE WHEN t0.resolved_at IS NULL THEN 1 WHEN CAST(EXTRACT(EPOCH FROM (t0.resolved_at - t0.created_at)) AS NUMERIC(18,6)) / 60.0 > t1.resolution_target_minutes THEN 1 ELSE 0 END AS is_miss FROM tickets t0 JOIN sla_policies t1 ON t1.id = t0.sla_policy_id WHERE t0.priority = 'urgent' AND t0.sla_policy_id IS NOT NULL AND t0.created_at >= DATE '2025-01-01' AND t0.created_at < DATE '2026-01-01' ), tenant_counts AS ( SELECT tenant_id, COUNT(*) AS evaluated_count FROM evaluable_tickets GROUP BY tenant_id HAVING COUNT(*) >= 5 ), tenant_target AS ( SELECT e.tenant_id AS tenant_id, CAST(SUM(e.resolution_target_minutes) AS NUMERIC(18,6)) / COUNT(*) / 60.0 AS weighted_target_hours, COUNT(DISTINCT e.resolution_target_minutes) AS distinct_target_count FROM evaluable_tickets e JOIN tenant_counts c ON c.tenant_id = e.tenant_id GROUP BY e.tenant_id ), tenant_actual AS ( SELECT e.tenant_id AS tenant_id, AVG(e.actual_hours) AS avg_actual_hours, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY e.actual_hours) AS median_actual_hours FROM evaluable_tickets e JOIN tenant_counts c ON c.tenant_id = e.tenant_id WHERE e.resolved_at IS NOT NULL GROUP BY e.tenant_id ), tenant_miss AS ( SELECT e.tenant_id AS tenant_id, SUM(e.is_miss) AS miss_count FROM evaluable_tickets e JOIN tenant_counts c ON c.tenant_id = e.tenant_id GROUP BY e.tenant_id ) SELECT t9.name AS merchant_name, t1.weighted_target_hours AS urgent_resolution_target_hours, t0.evaluated_count AS urgent_tickets_evaluated_2025, t2.median_actual_hours AS median_actual_resolution_hours, t2.avg_actual_hours AS avg_actual_resolution_hours, 100.0 * t3.miss_count / t0.evaluated_count AS urgent_miss_rate_pct, CASE WHEN t1.distinct_target_count > 1 THEN 'Y' ELSE 'N' END AS multiple_urgent_sla_policies FROM tenant_counts t0 JOIN tenant_target t1 ON t1.tenant_id = t0.tenant_id LEFT JOIN tenant_actual t2 ON t2.tenant_id = t0.tenant_id JOIN tenant_miss t3 ON t3.tenant_id = t0.tenant_id JOIN tenants t9 ON t9.id = t0.tenant_id ORDER BY urgent_miss_rate_pct DESC NULLS FIRST, merchant_name ASC NULLS LAST, t9.id ASC NULLS LAST LIMIT 50
WITH evaluable_tickets AS ( SELECT t0.id AS ticket_id, t0.tenant_id AS tenant_id, t0.created_at AS created_at, t0.resolved_at AS resolved_at, t1.resolution_target_minutes AS resolution_target_minutes, CASE WHEN t0.resolved_at IS NULL THEN NULL ELSE CAST(EXTRACT(EPOCH FROM (t0.resolved_at - t0.created_at)) AS NUMERIC(18,6)) / 3600.0 END AS actual_hours, CASE WHEN t0.resolved_at IS NULL THEN 1 WHEN CAST(EXTRACT(EPOCH FROM (t0.resolved_at - t0.created_at)) AS NUMERIC(18,6)) / 60.0 > t1.resolution_target_minutes THEN 1 ELSE 0 END AS is_miss FROM tickets t0 JOIN sla_policies t1 ON t1.id = t0.sla_policy_id WHERE t0.priority = 'urgent' AND t0.sla_policy_id IS NOT NULL AND t0.created_at >= DATE '2025-01-01' AND t0.created_at < DATE '2026-01-01' ), tenant_counts AS ( SELECT tenant_id, COUNT(*) AS evaluated_count FROM evaluable_tickets GROUP BY tenant_id HAVING COUNT(*) >= 5 ), tenant_target AS ( SELECT e.tenant_id AS tenant_id, CAST(SUM(e.resolution_target_minutes) AS NUMERIC(18,6)) / COUNT(*) / 60.0 AS weighted_target_hours, COUNT(DISTINCT e.resolution_target_minutes) AS distinct_target_count FROM evaluable_tickets e JOIN tenant_counts c ON c.tenant_id = e.tenant_id GROUP BY e.tenant_id ), tenant_actual AS ( SELECT e.tenant_id AS tenant_id, AVG(e.actual_hours) AS avg_actual_hours, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CAST(e.actual_hours AS DOUBLE PRECISION)) AS median_actual_hours FROM evaluable_tickets e JOIN tenant_counts c ON c.tenant_id = e.tenant_id WHERE e.resolved_at IS NOT NULL GROUP BY e.tenant_id ), tenant_miss AS ( SELECT e.tenant_id AS tenant_id, SUM(e.is_miss) AS miss_count FROM evaluable_tickets e JOIN tenant_counts c ON c.tenant_id = e.tenant_id GROUP BY e.tenant_id ) SELECT t9.name AS merchant_name, t1.weighted_target_hours AS urgent_resolution_target_hours, t0.evaluated_count AS urgent_tickets_evaluated_2025, t2.median_actual_hours AS median_actual_resolution_hours, t2.avg_actual_hours AS avg_actual_resolution_hours, 100.0 * t3.miss_count / t0.evaluated_count AS urgent_miss_rate_pct, CASE WHEN t1.distinct_target_count > 1 THEN 'Y' ELSE 'N' END AS multiple_urgent_sla_policies FROM tenant_counts t0 JOIN tenant_target t1 ON t1.tenant_id = t0.tenant_id LEFT JOIN tenant_actual t2 ON t2.tenant_id = t0.tenant_id JOIN tenant_miss t3 ON t3.tenant_id = t0.tenant_id JOIN tenants t9 ON t9.id = t0.tenant_id ORDER BY urgent_miss_rate_pct DESC NULLS FIRST, merchant_name ASC NULLS LAST, t9.id ASC NULLS LAST FETCH FIRST 50 ROWS ONLY
WITH evaluable_tickets AS ( SELECT t0.id AS ticket_id, t0.tenant_id AS tenant_id, t0.created_at AS created_at, t0.resolved_at AS resolved_at, t1.resolution_target_minutes AS resolution_target_minutes, CASE WHEN t0.resolved_at IS NULL THEN NULL ELSE CAST(divideDecimal(toDecimal128(dateDiff('microsecond', t0.created_at, t0.resolved_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6))) / 3600.0 END AS actual_hours, CASE WHEN t0.resolved_at IS NULL THEN 1 WHEN CAST(divideDecimal(toDecimal128(dateDiff('microsecond', t0.created_at, t0.resolved_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6))) / 60.0 > t1.resolution_target_minutes THEN 1 ELSE 0 END AS is_miss FROM tickets t0 JOIN sla_policies t1 ON t1.id = t0.sla_policy_id WHERE t0.priority = 'urgent' AND t0.sla_policy_id IS NOT NULL AND t0.created_at >= DATE '2025-01-01' AND t0.created_at < DATE '2026-01-01' ), tenant_counts AS ( SELECT tenant_id, COUNT(*) AS evaluated_count FROM evaluable_tickets GROUP BY tenant_id HAVING COUNT(*) >= 5 ), tenant_target AS ( SELECT e.tenant_id AS tenant_id, CAST(SUM(e.resolution_target_minutes) AS NUMERIC(18,6)) / COUNT(*) / 60.0 AS weighted_target_hours, COUNT(DISTINCT e.resolution_target_minutes) AS distinct_target_count FROM evaluable_tickets e JOIN tenant_counts c ON c.tenant_id = e.tenant_id GROUP BY e.tenant_id ), tenant_actual AS ( SELECT e.tenant_id AS tenant_id, AVG(e.actual_hours) AS avg_actual_hours, quantileExactInclusive(0.5)(toFloat64(e.actual_hours)) AS median_actual_hours FROM evaluable_tickets e JOIN tenant_counts c ON c.tenant_id = e.tenant_id WHERE e.resolved_at IS NOT NULL GROUP BY e.tenant_id ), tenant_miss AS ( SELECT e.tenant_id AS tenant_id, SUM(e.is_miss) AS miss_count FROM evaluable_tickets e JOIN tenant_counts c ON c.tenant_id = e.tenant_id GROUP BY e.tenant_id ) SELECT t9.name AS merchant_name, t1.weighted_target_hours AS urgent_resolution_target_hours, t0.evaluated_count AS urgent_tickets_evaluated_2025, t2.median_actual_hours AS median_actual_resolution_hours, t2.avg_actual_hours AS avg_actual_resolution_hours, 100.0 * t3.miss_count / t0.evaluated_count AS urgent_miss_rate_pct, CASE WHEN t1.distinct_target_count > 1 THEN 'Y' ELSE 'N' END AS multiple_urgent_sla_policies FROM tenant_counts t0 JOIN tenant_target t1 ON t1.tenant_id = t0.tenant_id LEFT JOIN tenant_actual t2 ON t2.tenant_id = t0.tenant_id JOIN tenant_miss t3 ON t3.tenant_id = t0.tenant_id JOIN tenants t9 ON t9.id = t0.tenant_id ORDER BY urgent_miss_rate_pct DESC NULLS FIRST, merchant_name ASC NULLS LAST, t9.id ASC NULLS LAST FETCH FIRST 50 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 Mateo Ruiz (analyst for the VP Customer Experience): Title: Urgent-ticket SLA target vs actual resolution time, by merchant Question: Is the broad urgent-ticket resolution failure driven by unrealistically tight SLA targets, or are merchants genuinely resolving urgent tickets slower than a reasonable target? Grain: one row per tenant (merchant), among tenants with at least 5 evaluable urgent-priority tickets created in 2025 Columns: - merchant name - urgent resolution target (hours) - urgent tickets evaluated, 2025 - median actual resolution hours (urgent) - avg actual resolution hours (urgent) - urgent miss rate (%) Details: Source: tickets (priority='urgent') joined to sla_policies via tickets.sla_policy_id, and tenants (name). Window: tickets.created_at 2025-01-01 through 2025-12-31, excluding tickets with a null sla_policy_id (same 'evaluable' definition as last night's urgent-miss-rate report). 'urgent resolution target (hours)' = sla_policies.resolution_target_minutes / 60 for that tenant's urgent policy; if a tenant's evaluated urgent tickets reference more than one distinct resolution_target_minutes value, report the ticket-count-weighted average target in hours and note in the report that the tenant has more than one urgent SLA policy in effect. 'median/avg actual resolution hours' = extract(epoch from resolved_at - created_at)/3600 over evaluable urgent tickets with resolved_at not null (tickets still unresolved as of 2025-12-31 are excluded from the actual-hours average/median but still count as a miss in the miss-rate numerator, consistent with last night's definition). 'urgent miss rate (%)' = same definition as last night's report (missing if unresolved as of cutoff, or resolved later than target), rounded to 1 decimal. Round target and actual hours to 1 decimal. Only include tenants with >=5 evaluable urgent tickets. Order by urgent miss rate 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_runWITH evaluable_tickets AS ( SELECT t0.id AS ticket_id, t0.tenant_id AS tenant_id, t0.created_at AS created_at, t0.resolved_at AS resolved_at, t1.resolution_target_minutes AS resolution_target_minutes, CASE WHEN t0.resolved_at IS NULL THEN NULL ELSE EXTRACT(EPOCH FROM (t0.resolved_at - t0.created_at)) / 3600.0 END AS actual_hours, CASE WHEN t0.resolved_at IS NULL THEN 1 WHEN EXTRACT(EPOCH FROM (t0.resolved_at - t0.created_at)) / 60.0 > t1.resolution_target_minutes THEN 1 ELSE 0 END AS is_miss FROM tickets t0 JOIN sla_policies t1 ON t1.id = t0.sla_policy_id WHERE t0.priority = 'urgent' AND t0.sla_policy_id IS NOT NULL AND t0.created_at >= DATE '2025-01-01' AND t0.created_at < DATE '2026-01-01' ), tenant_counts AS ( SELECT tenant_id, COUNT(*) AS evaluated_count FROM evaluable_tickets GROUP BY tenant_id HAVING COUNT(*) >= 5 ), tenant_target AS ( SELECT e.tenant_id AS tenant_id, SUM(e.resolution_target_minutes) / COUNT(*) / 60.0 AS weighted_target_hours, COUNT(DISTINCT e.resolution_target_minutes) AS distinct_target_count FROM evaluable_tickets e JOIN tenant_counts c ON c.tenant_id = e.tenant_id GROUP BY e.tenant_id ), tenant_actual AS ( SELECT e.tenant_id AS tenant_id, AVG(e.actual_hours) AS avg_actual_hours, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY e.actual_hours) AS median_actual_hours FROM evaluable_tickets e JOIN tenant_counts c ON c.tenant_id = e.tenant_id WHERE e.resolved_at IS NOT NULL GROUP BY e.tenant_id ), tenant_miss AS ( SELECT e.tenant_id AS tenant_id, SUM(e.is_miss) AS miss_count FROM evaluable_tickets e JOIN tenant_counts c ON c.tenant_id = e.tenant_id GROUP BY e.tenant_id ) SELECT t9.name AS merchant_name, ROUND(t1.weighted_target_hours, 1) AS urgent_resolution_target_hours, t0.evaluated_count AS urgent_tickets_evaluated_2025, ROUND(t2.median_actual_hours, 1) AS median_actual_resolution_hours, ROUND(t2.avg_actual_hours, 1) AS avg_actual_resolution_hours, ROUND(100.0 * t3.miss_count / t0.evaluated_count, 1) AS urgent_miss_rate_pct, CASE WHEN t1.distinct_target_count > 1 THEN 'Y' ELSE 'N' END AS multiple_urgent_sla_policies FROM tenant_counts t0 JOIN tenant_target t1 ON t1.tenant_id = t0.tenant_id LEFT JOIN tenant_actual t2 ON t2.tenant_id = t0.tenant_id JOIN tenant_miss t3 ON t3.tenant_id = t0.tenant_id JOIN tenants t9 ON t9.id = t0.tenant_id ORDER BY urgent_miss_rate_pct DESC, merchant_name ASC LIMIT 50
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: merchant_name, urgent_resolution_target_hours, urgent_tickets_evaluated_2025, median_actual_resolution_hours, avg_actual_resolution_hours, urgent_miss_rate_pct, multiple_urgent_sla_policies
submitWITH evaluable_tickets AS ( SELECT t0.id AS ticket_id, t0.tenant_id AS tenant_id, t0.created_at AS created_at, t0.resolved_at AS resolved_at, t1.resolution_target_minutes AS resolution_target_minutes, CASE WHEN t0.resolved_at IS NULL THEN NULL ELSE EXTRACT(EPOCH FROM (t0.resolved_at - t0.created_at)) / 3600.0 END AS actual_hours, CASE WHEN t0.resolved_at IS NULL THEN 1 WHEN EXTRACT(EPOCH FROM (t0.resolved_at - t0.created_at)) / 60.0 > t1.resolution_target_minutes THEN 1 ELSE 0 END AS is_miss FROM tickets t0 JOIN sla_policies t1 ON t1.id = t0.sla_policy_id WHERE t0.priority = 'urgent' AND t0.sla_policy_id IS NOT NULL AND t0.created_at >= DATE '2025-01-01' AND t0.created_at < DATE '2026-01-01' ), tenant_counts AS ( SELECT tenant_id, COUNT(*) AS evaluated_count FROM evaluable_tickets GROUP BY tenant_id HAVING COUNT(*) >= 5 ), tenant_target AS ( SELECT e.tenant_id AS tenant_id, SUM(e.resolution_target_minutes) / COUNT(*) / 60.0 AS weighted_target_hours, COUNT(DISTINCT e.resolution_target_minutes) AS distinct_target_count FROM evaluable_tickets e JOIN tenant_counts c ON c.tenant_id = e.tenant_id GROUP BY e.tenant_id ), tenant_actual AS ( SELECT e.tenant_id AS tenant_id, AVG(e.actual_hours) AS avg_actual_hours, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY e.actual_hours) AS median_actual_hours FROM evaluable_tickets e JOIN tenant_counts c ON c.tenant_id = e.tenant_id WHERE e.resolved_at IS NOT NULL GROUP BY e.tenant_id ), tenant_miss AS ( SELECT e.tenant_id AS tenant_id, SUM(e.is_miss) AS miss_count FROM evaluable_tickets e JOIN tenant_counts c ON c.tenant_id = e.tenant_id GROUP BY e.tenant_id ) SELECT t9.name AS merchant_name, ROUND(t1.weighted_target_hours, 1) AS urgent_resolution_target_hours, t0.evaluated_count AS urgent_tickets_evaluated_2025, ROUND(t2.median_actual_hours, 1) AS median_actual_resolution_hours, ROUND(t2.avg_actual_hours, 1) AS avg_actual_resolution_hours, ROUND(100.0 * t3.miss_count / t0.evaluated_count, 1) AS urgent_miss_rate_pct, CASE WHEN t1.distinct_target_count > 1 THEN 'Y' ELSE 'N' END AS multiple_urgent_sla_policies FROM tenant_counts t0 JOIN tenant_target t1 ON t1.tenant_id = t0.tenant_id LEFT JOIN tenant_actual t2 ON t2.tenant_id = t0.tenant_id JOIN tenant_miss t3 ON t3.tenant_id = t0.tenant_id JOIN tenants t9 ON t9.id = t0.tenant_id ORDER BY urgent_miss_rate_pct DESC, merchant_name ASC LIMIT 50
Submitted to tonight's batch. Your work on this request is done.