=== Report 1: Quarterly urgent-ticket performance, hotspot merchants vs comparison peer (written by Viktor) === Engineer's note: Built a full 4-tenant x 4-quarter grid with LEFT JOIN so quarters with zero evaluable urgent tickets still print a row (count 0, miss rate/avg/median NULL) instead of silently vanishing. Miss rate denominator and avg/median are strictly over tickets with sla_policy_id not null, per the 'evaluable' definition; resolved_at null is treated as a miss since data ends 2025-12-31. REPORT: Quarterly urgent-ticket performance, hotspot merchants vs comparison peer QUESTION: Does urgent-ticket resolution specifically get worse in the same quarters where each merchant's ticket-per-agent load spiked? merchant_name | quarter | urgent_tickets_evaluated | urgent_miss_rate_pct | avg_actual_resolution_hours | median_actual_resolution_hours --------------------+---------+--------------------------+----------------------+-----------------------------+------------------------------- Cragsley Outfitters | 2025-Q1 | 14 | 71.4 | 30.0 | 10.5 Cragsley Outfitters | 2025-Q2 | 13 | 53.8 | 18.0 | 14.0 Cragsley Outfitters | 2025-Q3 | 8 | 50.0 | 23.1 | 9.8 Cragsley Outfitters | 2025-Q4 | 15 | 80.0 | 36.6 | 21.5 Grind Collective | 2025-Q1 | 1 | 0.0 | 0.8 | 0.8 Grind Collective | 2025-Q2 | 1 | 0.0 | 0.9 | 0.9 Grind Collective | 2025-Q3 | 1 | 0.0 | 1.0 | 1.0 Grind Collective | 2025-Q4 | 3 | 33.3 | 7.0 | 7.1 Marrowby & Vane | 2025-Q1 | 8 | 62.5 | 43.6 | 24.8 Marrowby & Vane | 2025-Q2 | 2 | 50.0 | 16.9 | 16.9 Marrowby & Vane | 2025-Q3 | 6 | 50.0 | 8.8 | 6.4 Marrowby & Vane | 2025-Q4 | 17 | 76.5 | 139.5 | 87.2 Studio Vrij | 2025-Q1 | 4 | 50.0 | 17.4 | 10.7 Studio Vrij | 2025-Q2 | 2 | 100.0 | 219.5 | 219.5 Studio Vrij | 2025-Q3 | 2 | 100.0 | 109.0 | 109.0 Studio Vrij | 2025-Q4 | 3 | 33.3 | 3.9 | 1.2 (16 rows) === Report 2: Q4-vs-rest-of-year ticket load per agent, all qualifying merchants (written by Felix) === Engineer's note: Grain is one row per qualifying tenant (>=20 tickets created in 2025), as requested. Q1-Q3 quarterly agent counts are zero-filled for any quarter with no tickets so the three-quarter average divisor is always 3, matching the spec exactly. REPORT: Q4-vs-rest-of-year ticket load per agent, all qualifying merchants QUESTION: Is 'flat support staffing through a seasonal ticket surge' a pattern specific to the three hotspot merchants, or common across the book? merchant_name | tickets_q123 | avg_agents_q1_q3 | tickets_per_agent_q1_q3 | tickets_q4 | agents_q4 | tickets_per_agent_q4 ------------------------------+--------------+------------------+-------------------------+------------+-----------+--------------------- Grind Collective | 52 | 3.0 | 5.8 | 70 | 3 | 23.3 Marrowby & Vane | 126 | 5.0 | 8.4 | 105 | 5 | 21.0 Cragsley Outfitters | 249 | 8.0 | 10.4 | 151 | 8 | 18.9 Ballycorran Industrial Supply | 27 | 3.0 | 3.0 | 17 | 1 | 17.0 Petalcourt Beauty | 55 | 4.0 | 4.6 | 64 | 4 | 16.0 Fennel & Flint | 21 | 3.0 | 2.3 | 46 | 4 | 11.5 Brennquell Kaffee | 93 | 4.0 | 7.8 | 44 | 4 | 11.0 Kibblewick | 29 | 3.7 | 2.6 | 43 | 4 | 10.8 Studio Vrij | 127 | 4.0 | 10.6 | 39 | 4 | 9.8 Sourdough Club | 20 | 3.0 | 2.2 | 27 | 3 | 9.0 Ampfeld Elektronik | 20 | 4.3 | 1.5 | 36 | 5 | 7.2 Spice Route Box | 52 | 3.0 | 5.8 | 19 | 3 | 6.3 Oakhurst Tailoring | 9 | 1.3 | 2.3 | 18 | 3 | 6.0 Linden & Loom | 14 | 2.3 | 2.0 | 15 | 3 | 5.0 Wiesgrund Wohnen | 17 | 2.3 | 2.4 | 15 | 3 | 5.0 Honeycomb Larder | 45 | 3.0 | 5.0 | 13 | 3 | 4.3 Cinder Peak Sports Co. | 27 | 2.7 | 3.4 | 16 | 4 | 4.0 Thistlecombe | 10 | 2.0 | 1.7 | 11 | 3 | 3.7 Rue Lafayette Studio | 17 | 2.7 | 2.1 | 10 | 3 | 3.3 Coastline Interiors | 12 | 1.7 | 2.4 | 9 | 3 | 3.0 Gratlinie Outdoor | 18 | 3.3 | 1.8 | 9 | 4 | 2.3 Kayak Corner | 13 | 2.3 | 1.9 | 7 | 3 | 2.3 (22 rows) === Report 3: Urgent-ticket resolution-time distribution, slow merchants vs a fast baseline (written by Felix) === Engineer's note: Resolution-time buckets computed directly from tickets.resolved_at - created_at (sla_policies joined only to confirm an SLA policy exists, per the exclusion rule); unresolved-as-of-2025-12-31 tickets fall into the last bucket as specified. REPORT: Urgent-ticket resolution-time distribution, slow merchants vs a fast baseline QUESTION: Is the big three's inflated average urgent resolution time caused by a handful of extreme-delay tickets, or by generally slow handling across the board? merchant_name | urgent_tickets_evaluated | pct_within_8h | pct_8h_24h | pct_1d_3d | pct_3d_7d | pct_over_7d_or_unresolved --------------------+--------------------------+---------------+------------+-----------+-----------+-------------------------- Cragsley Outfitters | 50 | 34.0 | 34.0 | 18.0 | 10.0 | 4.0 Grind Collective | 6 | 83.3 | 16.7 | 0.0 | 0.0 | 0.0 Marrowby & Vane | 33 | 33.3 | 18.2 | 9.1 | 18.2 | 21.2 Studio Vrij | 11 | 36.4 | 18.2 | 27.3 | 0.0 | 18.2 Wiesgrund Wohnen | 7 | 85.7 | 14.3 | 0.0 | 0.0 | 0.0 (5 rows)