Queries · q398 · Support Operations Manager · week 2, night 1

Staffing load vs. ticket complexity, top merchants benchmark

“For Marrowby & Vane and Studio Vrij, does their high breach rate look like a staffing shortfall (high tickets-per-agent) or inherently harder tickets (more back-and-forth, higher-priority mix), compared to other top merchants like Cragsley Outfitters?”

Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told

1. A business event lands on the Support Operations Manager's desk

   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.

   One of the 35 events we wrote for the role play; it shaped the manager's first note. How events arise · All the events

2. Mia Schultz writes to Ben Carter

   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:

   • Marrowby & Vane and Studio Vrij are clearly our weak spot, but "under-resourced vs. harder cases" are two very different fixes (hire vs. retrain/reprioritize), and right now we can't tell which it is.
   • The macro usage number not reconciling with actual reply volume bothers me — I'd rather have no macro story this week than a wrong one, so let's not repeat that figure anywhere until it's explained.
   • The topic/tag breakdown (damaged, account, product-question) is useful but I don't want to act on it until we know how much of our ticket volume isn't tagged at all.

   Questions for next week, in priority order:

   1. For Marrowby & Vane and Studio Vrij specifically: is their breach rate because they're thinner on agents relative to their ticket load, or because their tickets take genuinely longer regardless of staffing (harder issues, bigger customers, more back-and-forth)? I want enough to recommend either "add headcount" or "something else is going on" with confidence.
   2. Before we use the macro usage figures for anything: what is that count actually tracking, and can we get a trustworthy sense of how much agents lean on canned replies versus writing from scratch?
   3. What share of tickets carry no topic tag at all, and does that change the story on which topics breach most? I don't want to walk into a training conversation with a skewed sample.
   4. If there's room: can we get even a rough signal on whether people who view a knowledge article before contacting us end up not filing a ticket at all, or filing a faster one? Even a partial read would tell us if KB investment is worth pushing on next quarter.

   Thanks — this is shaping up well. Let's make next week about turning the Marrowby & Vane / Studio Vrij question into something actionable.

   Mia

   Show the whole note

3. Ben Carter plans the night

   The analyst · week 2, night 1, request 1 of 3

   Mia's top priority is turning the Marrowby & Vane / Studio Vrij finding into "hire" vs "something else" — so tonight's first slot benchmarks their staffing ratio (tickets per agent) against complexity signals (messages per ticket, priority mix) alongside Cragsley and a few other top merchants as controls. I already have a hint this isn't pure under-staffing: last week's data showed Cragsley's Customer Care group runs a similar or higher tickets-per-agent ratio than Studio Vrij's yet breaches far less, which points toward ticket difficulty rather than headcount — tonight's report will test that properly with complexity proxies instead of my eyeballing two reports side by side. Second, since there's no usage-event ledger behind macros.usage_count, the only way to sanity-check it is to see if the audit trail (change_history/audit_log) corroborates the counter for our top macros — if the audit trail barely covers the counter's growth, that's itself the finding (and echoes the audit-gap theme several other teams hit this week), and I'll say so plainly rather than repeat the unreliable number. Third, a cheap but necessary check before any topic/tag training conversation: how much of 2025 ticket volume carries no tag at all, and whether untagged tickets look different enough to change the story. KB-deflection (Mia's Q4) waits for night 2 since these three fill tonight's slots and matter more.

   Read the whole plan
   Staffing load vs. ticket complexity, top merchants benchmark

   Question

   For Marrowby & Vane and Studio Vrij, does their high breach rate look like a staffing shortfall (high tickets-per-agent) or inherently harder tickets (more back-and-forth, higher-priority mix), compared to other top merchants like Cragsley Outfitters?

   One row per

   merchant, limited to the 6 highest-volume merchants from last week's by-merchant report (Cragsley Outfitters, Marrowby & Vane, Studio Vrij, Brennquell Kaffee, Grind Collective, Petalcourt Beauty)

   Columns

   • tenant_name
   • ticket_count (2025)
   • agent_count (distinct support_agents for that tenant)
   • tickets_per_agent
   • avg_messages_per_ticket (count of ticket_messages per ticket, both directions, as a back-and-forth / effort proxy)
   • pct_high_or_urgent_priority (share of the tenant's tickets with priority in (high, urgent))
   • avg_resolution_minutes
   • resolution_breach_rate

4. Grace writes the SQL

   The engineer · 1 dry run on the empty schema, then the submission

   Dry run 1 · STATUS: COMPILES (dry run on the empty schema, no data read)

   WITH target_tenants AS (
     SELECT id AS tenant_id, name AS tenant_name
     FROM tenants
     WHERE name IN ('Cragsley Outfitters','Marrowby & Vane','Studio Vrij','Brennquell Kaffee','Grind Collective','Petalcourt Beauty')
   ),
   tickets_2025 AS (
     SELECT t.id, t.tenant_id, t.priority, t.created_at, t.resolved_at
     FROM tickets t
     JOIN target_tenants tt ON tt.tenant_id = t.tenant_id
     WHERE t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01'
   ),
   ticket_counts AS (
     SELECT tenant_id, COUNT(*) AS ticket_count
     FROM tickets_2025
     GROUP BY tenant_id
   ),
   message_counts AS (
     SELECT t.tenant_id, COUNT(tm.id) AS message_count
     FROM tickets_2025 t
     JOIN ticket_messages tm ON tm.ticket_id = t.id
     GROUP BY t.tenant_id
   ),
   agent_counts AS (
     SELECT tenant_id, COUNT(DISTINCT id) AS agent_count
     FROM support_agents
     WHERE tenant_id IN (SELECT tenant_id FROM target_tenants)
     GROUP BY tenant_id
   ),
   priority_counts AS (
     SELECT tenant_id,
       SUM(CASE WHEN priority IN ('high','urgent') THEN 1 ELSE 0 END) AS high_urgent_count
     FROM tickets_2025
     GROUP BY tenant_id
   ),
   resolution_stats AS (
     SELECT tenant_id,
       AVG(EXTRACT(EPOCH FROM (resolved_at - created_at)) / 60.0) AS avg_resolution_minutes
     FROM tickets_2025
     WHERE resolved_at IS NOT NULL
     GROUP BY tenant_id
   ),
   breach_counts AS (
     SELECT t.tenant_id, COUNT(DISTINCT sb.ticket_id) AS breach_count
     FROM tickets_2025 t
     JOIN sla_breaches sb ON sb.ticket_id = t.id AND sb.breach_kind = 'resolution'
     GROUP BY t.tenant_id
   )
   SELECT
     tt.tenant_name,
     tc.ticket_count,
     COALESCE(ac.agent_count, 0) AS agent_count,
     ROUND(tc.ticket_count::numeric / NULLIF(ac.agent_count, 0), 1) AS tickets_per_agent,
     ROUND(COALESCE(mc.message_count, 0)::numeric / NULLIF(tc.ticket_count, 0), 1) AS avg_messages_per_ticket,
     ROUND(100.0 * COALESCE(pc.high_urgent_count, 0) / NULLIF(tc.ticket_count, 0), 1) AS pct_high_or_urgent_priority,
     ROUND(rs.avg_resolution_minutes, 1) AS avg_resolution_minutes,
     ROUND(100.0 * COALESCE(bc.breach_count, 0) / NULLIF(tc.ticket_count, 0), 1) AS resolution_breach_rate
   FROM target_tenants tt
   JOIN ticket_counts tc ON tc.tenant_id = tt.tenant_id
   LEFT JOIN agent_counts ac ON ac.tenant_id = tt.tenant_id
   LEFT JOIN message_counts mc ON mc.tenant_id = tt.tenant_id
   LEFT JOIN priority_counts pc ON pc.tenant_id = tt.tenant_id
   LEFT JOIN resolution_stats rs ON rs.tenant_id = tt.tenant_id
   LEFT JOIN breach_counts bc ON bc.tenant_id = tt.tenant_id
   ORDER BY resolution_breach_rate DESC, tt.tenant_name

   “Query compiles cleanly with the expected columns. Submitting.”

   Submitted · the SQL of the last dry run, unchanged · Submitted to tonight's batch. Your work on this request is done.

   “Submitted. The report gives one row per merchant (6 rows, ordered by resolution breach rate descending) with ticket volume, agent headcount, tickets-per-agent, messages-per-ticket, high/urgent priority share, average resolution time, and the resolution SLA breach rate — enough to see at a glance whether Marrowby & Vane and Studio Vrij's breach rates line up with elevated tickets-per-agent (staffing) or with higher complexity signals (more messages per ticket, more high/urgent tickets) relative to Cragsley and the others.”

5. The report comes back overnight

   As Ben Carter saw it, from the small dataset the agents worked on

   tenant_name         | ticket_count | agent_count | tickets_per_agent | avg_messages_per_ticket | pct_high_or_urgent_priority | avg_resolution_minutes | resolution_breach_rate
   --------------------+--------------+-------------+-------------------+-------------------------+-----------------------------+------------------------+-----------------------
   Marrowby & Vane     |          231 |           5 |              46.2 |                     3.3 |                        31.6 |                 5096.0 |                   39.4
   Studio Vrij         |          166 |           4 |              41.5 |                     3.7 |                        15.7 |                 7503.6 |                   38.6
   Grind Collective    |          122 |           3 |              40.7 |                     3.8 |                         9.8 |                 3865.3 |                   33.6
   Petalcourt Beauty   |          119 |           4 |              29.8 |                     3.8 |                        31.9 |                 2922.5 |                   32.8
   Cragsley Outfitters |          400 |           8 |              50.0 |                     3.5 |                        26.0 |                 2348.3 |                   29.8
   Brennquell Kaffee   |          137 |           4 |              34.3 |                     3.7 |                        39.4 |                  956.3 |                   11.7
   (6 rows)

6. What came of it

   The memo and the queries that followed; links found by title, so inferred

   Later queries that build on this report:

   • q401Topic mix and resolution time by tag, flagged merchants vs. Cragsley
   • q402Where the delay accumulates: first-response lag vs. post-response handling time
   • q409Ticket performance with known carrier-incident months excluded

   It builds on:

   • q199Ticket volume and SLA performance by merchant
   • q200Highest-volume merchant + agent-group units

7. It becomes benchmark query q398

   Changed only where the benchmark needs the same result on every system

   • spellingcast (2×)
   • determinismtie-breaker: tenant names are not unique
   • numericsdocs/NUMERICS.md: rounding removed; results keep full precision; bare NUMERIC cast that only served the rounding removed; intended fraction: integer numerator cast to NUMERIC(18,6) before the division
   • numericsdocs/NUMERICS.md: EXTRACT(EPOCH FROM (b - a)) cast to NUMERIC(18,6)
   • made explicita selected t.col gets AS col, its standard name made explicit (9×)
   • placeholderseconds between
   • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (3×)
   The benchmark text, against the submitted SQL

   WITH target_tenants AS (  SELECT id AS tenant_id, name AS tenant_name  FROM tenants  WHERE name IN ('Cragsley Outfitters','Marrowby & Vane','Studio Vrij','Brennquell Kaffee','Grind Collective','Petalcourt Beauty')),tickets_2025 AS (  SELECT t.id, t.tenant_id, t.priority, t.created_at, t.resolved_at  SELECT t.id AS id, t.tenant_id AS tenant_id, t.priority AS priority, t.created_at AS created_at, t.resolved_at AS resolved_at  FROM tickets t  JOIN target_tenants tt ON tt.tenant_id = t.tenant_id  WHERE t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01'),ticket_counts AS (  SELECT tenant_id, COUNT(*) AS ticket_count  FROM tickets_2025  GROUP BY tenant_id),message_counts AS (  SELECT t.tenant_id, COUNT(tm.id) AS message_count  SELECT t.tenant_id AS tenant_id, COUNT(tm.id) AS message_count  FROM tickets_2025 t  JOIN ticket_messages tm ON tm.ticket_id = t.id  GROUP BY t.tenant_id),agent_counts AS (  SELECT tenant_id, COUNT(DISTINCT id) AS agent_count  FROM support_agents  WHERE tenant_id IN (SELECT tenant_id FROM target_tenants)  GROUP BY tenant_id),priority_counts AS (  SELECT tenant_id,    SUM(CASE WHEN priority IN ('high','urgent') THEN 1 ELSE 0 END) AS high_urgent_count  FROM tickets_2025  GROUP BY tenant_id),resolution_stats AS (  SELECT tenant_id,    AVG(EXTRACT(EPOCH FROM (resolved_at - created_at)) / 60.0) AS avg_resolution_minutes    AVG({{seconds_between(created_at, resolved_at)}} / 60.0) AS avg_resolution_minutes  FROM tickets_2025  WHERE resolved_at IS NOT NULL  GROUP BY tenant_id),breach_counts AS (  SELECT t.tenant_id, COUNT(DISTINCT sb.ticket_id) AS breach_count  SELECT t.tenant_id AS tenant_id, COUNT(DISTINCT sb.ticket_id) AS breach_count  FROM tickets_2025 t  JOIN sla_breaches sb ON sb.ticket_id = t.id AND sb.breach_kind = 'resolution'  GROUP BY t.tenant_id)SELECT  tt.tenant_name,  tc.ticket_count,  tt.tenant_name AS tenant_name,  tc.ticket_count AS ticket_count,  COALESCE(ac.agent_count, 0) AS agent_count,  ROUND(tc.ticket_count::numeric / NULLIF(ac.agent_count, 0), 1) AS tickets_per_agent,  ROUND(COALESCE(mc.message_count, 0)::numeric / NULLIF(tc.ticket_count, 0), 1) AS avg_messages_per_ticket,  ROUND(100.0 * COALESCE(pc.high_urgent_count, 0) / NULLIF(tc.ticket_count, 0), 1) AS pct_high_or_urgent_priority,  ROUND(rs.avg_resolution_minutes, 1) AS avg_resolution_minutes,  ROUND(100.0 * COALESCE(bc.breach_count, 0) / NULLIF(tc.ticket_count, 0), 1) AS resolution_breach_rate  CAST(tc.ticket_count AS numeric(18,6)) / NULLIF(ac.agent_count, 0) AS tickets_per_agent,  CAST(COALESCE(mc.message_count, 0) AS numeric(18,6)) / NULLIF(tc.ticket_count, 0) AS avg_messages_per_ticket,  100.0 * COALESCE(pc.high_urgent_count, 0) / NULLIF(tc.ticket_count, 0) AS pct_high_or_urgent_priority,  rs.avg_resolution_minutes AS avg_resolution_minutes,  100.0 * COALESCE(bc.breach_count, 0) / NULLIF(tc.ticket_count, 0) AS resolution_breach_rateFROM target_tenants ttJOIN ticket_counts tc ON tc.tenant_id = tt.tenant_idLEFT JOIN agent_counts ac ON ac.tenant_id = tt.tenant_idLEFT JOIN message_counts mc ON mc.tenant_id = tt.tenant_idLEFT JOIN priority_counts pc ON pc.tenant_id = tt.tenant_idLEFT JOIN resolution_stats rs ON rs.tenant_id = tt.tenant_idLEFT JOIN breach_counts bc ON bc.tenant_id = tt.tenant_idORDER BY resolution_breach_rate DESC, tt.tenant_nameORDER BY resolution_breach_rate DESC NULLS FIRST, tt.tenant_name NULLS LAST, tt.tenant_id NULLS LAST

   As each system received it; highlighted, the parts spelled for that system

   DuckDB · Hyper · GizmoSQL · CedarDB · pg_duckdb · PostgreSQL · DataFusion · Firebolt · clickhouse_brewClickHouse · chDB

   WITH target_tenants AS (
     SELECT id AS tenant_id, name AS tenant_name
     FROM tenants
     WHERE name IN ('Cragsley Outfitters','Marrowby & Vane','Studio Vrij','Brennquell Kaffee','Grind Collective','Petalcourt Beauty')
   ),
   tickets_2025 AS (
     SELECT t.id AS id, t.tenant_id AS tenant_id, t.priority AS priority, t.created_at AS created_at, t.resolved_at AS resolved_at
     FROM tickets t
     JOIN target_tenants tt ON tt.tenant_id = t.tenant_id
     WHERE t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01'
   ),
   ticket_counts AS (
     SELECT tenant_id, COUNT(*) AS ticket_count
     FROM tickets_2025
     GROUP BY tenant_id
   ),
   message_counts AS (
     SELECT t.tenant_id AS tenant_id, COUNT(tm.id) AS message_count
     FROM tickets_2025 t
     JOIN ticket_messages tm ON tm.ticket_id = t.id
     GROUP BY t.tenant_id
   ),
   agent_counts AS (
     SELECT tenant_id, COUNT(DISTINCT id) AS agent_count
     FROM support_agents
     WHERE tenant_id IN (SELECT tenant_id FROM target_tenants)
     GROUP BY tenant_id
   ),
   priority_counts AS (
     SELECT tenant_id,
       SUM(CASE WHEN priority IN ('high','urgent') THEN 1 ELSE 0 END) AS high_urgent_count
     FROM tickets_2025
     GROUP BY tenant_id
   ),
   resolution_stats AS (
     SELECT tenant_id,
       AVG(CAST(EXTRACT(EPOCH FROM (resolved_at - created_at)) AS NUMERIC(18,6)) / 60.0) AS avg_resolution_minutes
     FROM tickets_2025
     WHERE resolved_at IS NOT NULL
     GROUP BY tenant_id
   ),
   breach_counts AS (
     SELECT t.tenant_id AS tenant_id, COUNT(DISTINCT sb.ticket_id) AS breach_count
     FROM tickets_2025 t
     JOIN sla_breaches sb ON sb.ticket_id = t.id AND sb.breach_kind = 'resolution'
     GROUP BY t.tenant_id
   )
   SELECT
     tt.tenant_name AS tenant_name,
     tc.ticket_count AS ticket_count,
     COALESCE(ac.agent_count, 0) AS agent_count,
     CAST(tc.ticket_count AS numeric(18,6)) / NULLIF(ac.agent_count, 0) AS tickets_per_agent,
     CAST(COALESCE(mc.message_count, 0) AS numeric(18,6)) / NULLIF(tc.ticket_count, 0) AS avg_messages_per_ticket,
     100.0 * COALESCE(pc.high_urgent_count, 0) / NULLIF(tc.ticket_count, 0) AS pct_high_or_urgent_priority,
     rs.avg_resolution_minutes AS avg_resolution_minutes,
     100.0 * COALESCE(bc.breach_count, 0) / NULLIF(tc.ticket_count, 0) AS resolution_breach_rate
   FROM target_tenants tt
   JOIN ticket_counts tc ON tc.tenant_id = tt.tenant_id
   LEFT JOIN agent_counts ac ON ac.tenant_id = tt.tenant_id
   LEFT JOIN message_counts mc ON mc.tenant_id = tt.tenant_id
   LEFT JOIN priority_counts pc ON pc.tenant_id = tt.tenant_id
   LEFT JOIN resolution_stats rs ON rs.tenant_id = tt.tenant_id
   LEFT JOIN breach_counts bc ON bc.tenant_id = tt.tenant_id
   ORDER BY resolution_breach_rate DESC NULLS FIRST, tt.tenant_name NULLS LAST, tt.tenant_id NULLS LAST

   Scale factor 1000, c8a.24xlarge, after the last load. q398 on the start page, with every configuration.

8. Everything, as recorded

   The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted

   Grace's whole session, 7 turns

   • The request, as Grace received it

     Report request from Ben Carter (analyst for the Support Operations Manager):
     
     Title: Staffing load vs. ticket complexity, top merchants benchmark
     Question: For Marrowby & Vane and Studio Vrij, does their high breach rate look like a staffing shortfall (high tickets-per-agent) or inherently harder tickets (more back-and-forth, higher-priority mix), compared to other top merchants like Cragsley Outfitters?
     Grain: one row per merchant, limited to the 6 highest-volume merchants from last week's by-merchant report (Cragsley Outfitters, Marrowby & Vane, Studio Vrij, Brennquell Kaffee, Grind Collective, Petalcourt Beauty)
     Columns:
     - tenant_name
     - ticket_count (2025)
     - agent_count (distinct support_agents for that tenant)
     - tickets_per_agent
     - avg_messages_per_ticket (count of ticket_messages per ticket, both directions, as a back-and-forth / effort proxy)
     - pct_high_or_urgent_priority (share of the tenant's tickets with priority in (high, urgent))
     - avg_resolution_minutes
     - resolution_breach_rate
     Details: Window: tickets.created_at between 2025-01-01 and 2025-12-31. Source tables: tickets, ticket_messages, support_agents, sla_breaches (breach_kind = 'resolution', confirmed codes from last week's audit), tenants. agent_count via support_agents.tenant_id (as used in last week's by-merchant report, for consistency). avg_messages_per_ticket = total ticket_messages rows for that tenant's 2025 tickets / ticket_count. Order by resolution_breach_rate descending so the two flagged merchants' position relative to the others (especially Cragsley, which has a similar or higher tickets-per-agent ratio per last week's group-level data) is clear. Round tickets_per_agent and avg_messages_per_ticket to 1 decimal, rates to 1 decimal percent.
     
     Check your query with the warehouse's dry_run tool, then hand it in with submit.

   • Grace calls dry_run

     WITH target_tenants AS (
       SELECT id AS tenant_id, name AS tenant_name
       FROM tenants
       WHERE name IN ('Cragsley Outfitters','Marrowby & Vane','Studio Vrij','Brennquell Kaffee','Grind Collective','Petalcourt Beauty')
     ),
     tickets_2025 AS (
       SELECT t.id, t.tenant_id, t.priority, t.created_at, t.resolved_at
       FROM tickets t
       JOIN target_tenants tt ON tt.tenant_id = t.tenant_id
       WHERE t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01'
     ),
     ticket_counts AS (
       SELECT tenant_id, COUNT(*) AS ticket_count
       FROM tickets_2025
       GROUP BY tenant_id
     ),
     message_counts AS (
       SELECT t.tenant_id, COUNT(tm.id) AS message_count
       FROM tickets_2025 t
       JOIN ticket_messages tm ON tm.ticket_id = t.id
       GROUP BY t.tenant_id
     ),
     agent_counts AS (
       SELECT tenant_id, COUNT(DISTINCT id) AS agent_count
       FROM support_agents
       WHERE tenant_id IN (SELECT tenant_id FROM target_tenants)
       GROUP BY tenant_id
     ),
     priority_counts AS (
       SELECT tenant_id,
         SUM(CASE WHEN priority IN ('high','urgent') THEN 1 ELSE 0 END) AS high_urgent_count
       FROM tickets_2025
       GROUP BY tenant_id
     ),
     resolution_stats AS (
       SELECT tenant_id,
         AVG(EXTRACT(EPOCH FROM (resolved_at - created_at)) / 60.0) AS avg_resolution_minutes
       FROM tickets_2025
       WHERE resolved_at IS NOT NULL
       GROUP BY tenant_id
     ),
     breach_counts AS (
       SELECT t.tenant_id, COUNT(DISTINCT sb.ticket_id) AS breach_count
       FROM tickets_2025 t
       JOIN sla_breaches sb ON sb.ticket_id = t.id AND sb.breach_kind = 'resolution'
       GROUP BY t.tenant_id
     )
     SELECT
       tt.tenant_name,
       tc.ticket_count,
       COALESCE(ac.agent_count, 0) AS agent_count,
       ROUND(tc.ticket_count::numeric / NULLIF(ac.agent_count, 0), 1) AS tickets_per_agent,
       ROUND(COALESCE(mc.message_count, 0)::numeric / NULLIF(tc.ticket_count, 0), 1) AS avg_messages_per_ticket,
       ROUND(100.0 * COALESCE(pc.high_urgent_count, 0) / NULLIF(tc.ticket_count, 0), 1) AS pct_high_or_urgent_priority,
       ROUND(rs.avg_resolution_minutes, 1) AS avg_resolution_minutes,
       ROUND(100.0 * COALESCE(bc.breach_count, 0) / NULLIF(tc.ticket_count, 0), 1) AS resolution_breach_rate
     FROM target_tenants tt
     JOIN ticket_counts tc ON tc.tenant_id = tt.tenant_id
     LEFT JOIN agent_counts ac ON ac.tenant_id = tt.tenant_id
     LEFT JOIN message_counts mc ON mc.tenant_id = tt.tenant_id
     LEFT JOIN priority_counts pc ON pc.tenant_id = tt.tenant_id
     LEFT JOIN resolution_stats rs ON rs.tenant_id = tt.tenant_id
     LEFT JOIN breach_counts bc ON bc.tenant_id = tt.tenant_id
     ORDER BY resolution_breach_rate DESC, tt.tenant_name

   • The warehouse answers

     STATUS: COMPILES (dry run on the empty schema, no data read)
     COLUMNS: tenant_name, ticket_count, agent_count, tickets_per_agent, avg_messages_per_ticket, pct_high_or_urgent_priority, avg_resolution_minutes, resolution_breach_rate

   • GraceQuery compiles cleanly with the expected columns. Submitting.
   • Grace calls submit

     WITH target_tenants AS (
       SELECT id AS tenant_id, name AS tenant_name
       FROM tenants
       WHERE name IN ('Cragsley Outfitters','Marrowby & Vane','Studio Vrij','Brennquell Kaffee','Grind Collective','Petalcourt Beauty')
     ),
     tickets_2025 AS (
       SELECT t.id, t.tenant_id, t.priority, t.created_at, t.resolved_at
       FROM tickets t
       JOIN target_tenants tt ON tt.tenant_id = t.tenant_id
       WHERE t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01'
     ),
     ticket_counts AS (
       SELECT tenant_id, COUNT(*) AS ticket_count
       FROM tickets_2025
       GROUP BY tenant_id
     ),
     message_counts AS (
       SELECT t.tenant_id, COUNT(tm.id) AS message_count
       FROM tickets_2025 t
       JOIN ticket_messages tm ON tm.ticket_id = t.id
       GROUP BY t.tenant_id
     ),
     agent_counts AS (
       SELECT tenant_id, COUNT(DISTINCT id) AS agent_count
       FROM support_agents
       WHERE tenant_id IN (SELECT tenant_id FROM target_tenants)
       GROUP BY tenant_id
     ),
     priority_counts AS (
       SELECT tenant_id,
         SUM(CASE WHEN priority IN ('high','urgent') THEN 1 ELSE 0 END) AS high_urgent_count
       FROM tickets_2025
       GROUP BY tenant_id
     ),
     resolution_stats AS (
       SELECT tenant_id,
         AVG(EXTRACT(EPOCH FROM (resolved_at - created_at)) / 60.0) AS avg_resolution_minutes
       FROM tickets_2025
       WHERE resolved_at IS NOT NULL
       GROUP BY tenant_id
     ),
     breach_counts AS (
       SELECT t.tenant_id, COUNT(DISTINCT sb.ticket_id) AS breach_count
       FROM tickets_2025 t
       JOIN sla_breaches sb ON sb.ticket_id = t.id AND sb.breach_kind = 'resolution'
       GROUP BY t.tenant_id
     )
     SELECT
       tt.tenant_name,
       tc.ticket_count,
       COALESCE(ac.agent_count, 0) AS agent_count,
       ROUND(tc.ticket_count::numeric / NULLIF(ac.agent_count, 0), 1) AS tickets_per_agent,
       ROUND(COALESCE(mc.message_count, 0)::numeric / NULLIF(tc.ticket_count, 0), 1) AS avg_messages_per_ticket,
       ROUND(100.0 * COALESCE(pc.high_urgent_count, 0) / NULLIF(tc.ticket_count, 0), 1) AS pct_high_or_urgent_priority,
       ROUND(rs.avg_resolution_minutes, 1) AS avg_resolution_minutes,
       ROUND(100.0 * COALESCE(bc.breach_count, 0) / NULLIF(tc.ticket_count, 0), 1) AS resolution_breach_rate
     FROM target_tenants tt
     JOIN ticket_counts tc ON tc.tenant_id = tt.tenant_id
     LEFT JOIN agent_counts ac ON ac.tenant_id = tt.tenant_id
     LEFT JOIN message_counts mc ON mc.tenant_id = tt.tenant_id
     LEFT JOIN priority_counts pc ON pc.tenant_id = tt.tenant_id
     LEFT JOIN resolution_stats rs ON rs.tenant_id = tt.tenant_id
     LEFT JOIN breach_counts bc ON bc.tenant_id = tt.tenant_id
     ORDER BY resolution_breach_rate DESC, tt.tenant_name

   • The warehouse answers

     Submitted to tonight's batch. Your work on this request is done.

   • GraceSubmitted. The report gives one row per merchant (6 rows, ordered by resolution breach rate descending) with ticket volume, agent headcount, tickets-per-agent, messages-per-ticket, high/urgent priority share, average resolution time, and the resolution SLA breach rate — enough to see at a glance whether Marrowby & Vane and Studio Vrij's breach rates line up with elevated tickets-per-agent (staffing) or with higher complexity signals (more messages per ticket, more high/urgent tickets) relative to Cragsley and the others.

   Not shown here: 239 KB of context the agent was given, its instructions and the schema documentation; it is in the raw transcript.

   Grace's transcript (272 KB) · Ben Carter's transcript (813 KB) · Mia Schultz's transcript (260 KB) · the night's plan (5 KB) · the night's reports (5 KB) · the week's memo (3 KB)