DB Olympics

Queries · q196 · Support Operations Manager · week 1, night 1

Ticket volume and staffing by agent group

“How is ticket volume distributed across our agent groups, and does staffing (agent count) look balanced against that load?”

Manager · asked the questionMia SchultzSupport Operations Manager. Built support tooling and processes at a software company.
Analyst · planned the reportBen CarterQuantifier: puts a money value on every finding.
Engineer · wrote the SQLGraceWrites tidy, layered CTEs with descriptive names, one step per CTE, then a final select.

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 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:

    1. Where is ticket volume concentrated, and are the right groups/agents carrying it? I want to see how load splits across our queues and agent groups, and whether staffing looks balanced against that load — this feeds a near-term staffing/routing decision.
    2. Are we meeting our SLA promises, and where do we miss most? I want to understand how often we breach first-response and resolution targets, and whether certain priorities, groups, or times are the repeat offenders. This is the headline number Daniel will want for the review.
    3. What actually makes a ticket slow? I'd like a sense of what separates a ticket that resolves quickly from one that drags — whether that's certain topics, channels, or simply sitting unassigned — so we know where to intervene first.
    4. Are macros and knowledge base articles pulling their weight? I want to know how much agents lean on canned replies and how the knowledge base is doing with customers (are articles helping people avoid a ticket, and are the ones we have actually rated as useful) — this feeds a content/investment decision for next quarter.
    5. (If there's room this week) How satisfied are customers coming out of resolved tickets, and does that satisfaction track with the SLA and speed picture above, or is something else driving it?

    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

    A note from the VP Customer Experience, Daniel Achterberg

    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:

    1. Where do tickets actually get stuck? I'd like to understand which queues or agent groups have the slowest response and resolution times, and whether that's a staffing/routing issue or certain ticket types being inherently harder.
    2. Are we actually meeting the SLA targets we've set, and for the ones we miss, is there a pattern (specific merchants, priorities, time of day/week)?
    3. Are agents getting value from macros and the knowledge base, or are they re-typing answers every time? I'd like a sense of whether better self-serve content or canned responses could take load off the queue.

    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

  3. Ben Carter plans the night

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

    Night 1: establish the baseline Mia and Daniel both asked for first — where volume sits, whether staffing matches it, and the headline SLA miss numbers. I'm starting at the agent-group grain (ties load to staffing and lets us see which queues run slow) and the priority grain (the number Daniel wants for the review, with an 'All' rollup row so we have one true headline rate plus the breakdown). I'm holding macros/KB, root-cause-of-slowness, and CSAT for later nights once I've seen where the pain concentrates — no point drilling into 'why slow' before I know 'where slow'. All three reports use 2025-01-01 to 2025-12-31 (last full year) as the baseline window so later weekly/monthly cuts have something to compare against.

    Ticket volume and staffing by agent group
    Question
    How is ticket volume distributed across our agent groups, and does staffing (agent count) look balanced against that load?
    One row per
    agent group (plus one row for tickets with no agent_group_id, labeled 'Unassigned queue')
    Columns
    • agent_group_name
    • ticket_count (tickets created in 2025)
    • open_ticket_count (status not in solved, closed, as of 2025-12-31)
    • agent_count (distinct active support_agents in that group via agent_group_members)
    • tickets_per_agent (ticket_count / agent_count)
    • pct_unassigned (share of the group's tickets with assignee_agent_id null)
    • avg_first_response_minutes (first_response_at - created_at, tickets with a first response)
    • avg_resolution_minutes (resolved_at - created_at, tickets resolved)

  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 group_names AS (
  SELECT DISTINCT name AS agent_group_name
  FROM agent_groups
),

ticket_window AS (
  SELECT
    t.id,
    t.agent_group_id,
    t.tenant_id,
    ag.name AS group_name,
    t.status,
    t.assignee_agent_id,
    t.created_at,
    t.first_response_at,
    t.resolved_at
  FROM tickets t
  LEFT JOIN agent_groups ag
    ON ag.id = t.agent_group_id AND ag.tenant_id = t.tenant_id
  WHERE t.created_at >= TIMESTAMP '2025-01-01'
    AND t.created_at < TIMESTAMP '2026-01-01'
),

ticket_metrics AS (
  SELECT
    COALESCE(group_name, 'Unassigned queue') AS agent_group_name,
    COUNT(*) AS ticket_count,
    COUNT(*) FILTER (WHERE status NOT IN ('solved','closed')) AS open_ticket_count,
    COUNT(*) FILTER (WHERE assignee_agent_id IS NULL) AS unassigned_ticket_count,
    AVG(EXTRACT(EPOCH FROM (first_response_at - created_at)) / 60.0)
      FILTER (WHERE first_response_at IS NOT NULL) AS avg_first_response_minutes,
    AVG(EXTRACT(EPOCH FROM (resolved_at - created_at)) / 60.0)
      FILTER (WHERE resolved_at IS NOT NULL) AS avg_resolution_minutes
  FROM ticket_window
  GROUP BY COALESCE(group_name, 'Unassigned queue')
),

agent_counts AS (
  SELECT
    ag.name AS agent_group_name,
    COUNT(DISTINCT agm.tenant_id::text || ':' || agm.support_agent_id::text) AS agent_count
  FROM agent_group_members agm
  JOIN agent_groups ag
    ON ag.id = agm.agent_group_id AND ag.tenant_id = agm.tenant_id
  GROUP BY ag.name
),

all_rows AS (
  SELECT agent_group_name FROM group_names
  UNION
  SELECT 'Unassigned queue'
)

SELECT
  r.agent_group_name,
  COALESCE(tm.ticket_count, 0) AS ticket_count,
  COALESCE(tm.open_ticket_count, 0) AS open_ticket_count,
  ac.agent_count,
  ROUND(COALESCE(tm.ticket_count,0)::numeric / NULLIF(ac.agent_count,0), 1) AS tickets_per_agent,
  ROUND(100.0 * COALESCE(tm.unassigned_ticket_count,0) / NULLIF(tm.ticket_count,0), 1) AS pct_unassigned,
  ROUND(tm.avg_first_response_minutes::numeric) AS avg_first_response_minutes,
  ROUND(tm.avg_resolution_minutes::numeric) AS avg_resolution_minutes
FROM all_rows r
LEFT JOIN ticket_metrics tm ON tm.agent_group_name = r.agent_group_name
LEFT JOIN agent_counts ac ON ac.agent_group_name = r.agent_group_name
ORDER BY
  CASE WHEN r.agent_group_name = 'Unassigned queue' THEN 1 ELSE 0 END,
  COALESCE(tm.ticket_count,0) DESC,
  r.agent_group_name

    “This compiles correctly with all the requested columns. Submitting now.”

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

    “Submitted. One thing worth flagging for Ben: since agent_groups are tenant-scoped (each of the ~120 merchants has its own groups), this report aggregates by group name across all tenants — e.g. every merchant's "Tier 1" group lands in one combined row — rather than isolating a single merchant's support desk. I noted this in the submit note so he can confirm whether a per-tenant breakdown is actually what the Support Ops Manager wants, in case a follow-up request is needed.”

  5. The report comes back overnight

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

    agent_group_name  | ticket_count | open_ticket_count | agent_count | tickets_per_agent | pct_unassigned | avg_first_response_minutes | avg_resolution_minutes
------------------+--------------+-------------------+-------------+-------------------+----------------+----------------------------+-----------------------
Customer Care     |         1035 |                11 |         255 |               4.1 |            0.5 |                       1577 |                   2708
Returns & Refunds |          639 |                17 |          39 |              16.4 |            0.9 |                       1590 |                   2677
Delivery Issues   |          524 |                 7 |          36 |              14.6 |            0.2 |                       1384 |                   2908
Customer Service  |           76 |                 0 |          17 |               4.5 |            0.0 |                        962 |                   1637
Key Accounts      |           23 |                 0 |           5 |               4.6 |            0.0 |                        536 |                   1593
Returns & Claims  |           23 |                 0 |           5 |               4.6 |            0.0 |                       1001 |                   1644
Unassigned queue  |            0 |                 0 |        NULL |              NULL |           NULL |                       NULL |                   NULL
(7 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:

  7. It becomes benchmark query q196

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

    • spellingcast (5×)
    • 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) (2×)
    • made explicita selected t.col gets AS col, its standard name made explicit (10×)
    • placeholderseconds between (2×)
    • 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 group_names AS (  SELECT DISTINCT name AS agent_group_name  FROM agent_groups),ticket_window AS (  SELECT    t.id,    t.agent_group_id,    t.tenant_id,    t.id AS id,    t.agent_group_id AS agent_group_id,    t.tenant_id AS tenant_id,    ag.name AS group_name,    t.status,    t.assignee_agent_id,    t.created_at,    t.first_response_at,    t.resolved_at    t.status AS status,    t.assignee_agent_id AS assignee_agent_id,    t.created_at AS created_at,    t.first_response_at AS first_response_at,    t.resolved_at AS resolved_at  FROM tickets t  LEFT JOIN agent_groups ag    ON ag.id = t.agent_group_id AND ag.tenant_id = t.tenant_id  WHERE t.created_at >= TIMESTAMP '2025-01-01'    AND t.created_at < TIMESTAMP '2026-01-01'),ticket_metrics AS (  SELECT    COALESCE(group_name, 'Unassigned queue') AS agent_group_name,    COUNT(*) AS ticket_count,    COUNT(*) FILTER (WHERE status NOT IN ('solved','closed')) AS open_ticket_count,    COUNT(*) FILTER (WHERE assignee_agent_id IS NULL) AS unassigned_ticket_count,    AVG(EXTRACT(EPOCH FROM (first_response_at - created_at)) / 60.0)    AVG({{seconds_between(created_at, first_response_at)}} / 60.0)      FILTER (WHERE first_response_at IS NOT NULL) AS avg_first_response_minutes,    AVG(EXTRACT(EPOCH FROM (resolved_at - created_at)) / 60.0)    AVG({{seconds_between(created_at, resolved_at)}} / 60.0)      FILTER (WHERE resolved_at IS NOT NULL) AS avg_resolution_minutes  FROM ticket_window  GROUP BY COALESCE(group_name, 'Unassigned queue')),agent_counts AS (  SELECT    ag.name AS agent_group_name,    COUNT(DISTINCT agm.tenant_id::text || ':' || agm.support_agent_id::text) AS agent_count    COUNT(DISTINCT CAST(agm.tenant_id AS text) || ':' || CAST(agm.support_agent_id AS text)) AS agent_count  FROM agent_group_members agm  JOIN agent_groups ag    ON ag.id = agm.agent_group_id AND ag.tenant_id = agm.tenant_id  GROUP BY ag.name),all_rows AS (  SELECT agent_group_name FROM group_names  UNION  SELECT 'Unassigned queue')SELECT  r.agent_group_name,  r.agent_group_name AS agent_group_name,  COALESCE(tm.ticket_count, 0) AS ticket_count,  COALESCE(tm.open_ticket_count, 0) AS open_ticket_count,  ac.agent_count,  ROUND(COALESCE(tm.ticket_count,0)::numeric / NULLIF(ac.agent_count,0), 1) AS tickets_per_agent,  ROUND(100.0 * COALESCE(tm.unassigned_ticket_count,0) / NULLIF(tm.ticket_count,0), 1) AS pct_unassigned,  ROUND(tm.avg_first_response_minutes::numeric) AS avg_first_response_minutes,  ROUND(tm.avg_resolution_minutes::numeric) AS avg_resolution_minutes  ac.agent_count AS agent_count,  CAST(COALESCE(tm.ticket_count,0) AS numeric(18,6)) / NULLIF(ac.agent_count,0) AS tickets_per_agent,  100.0 * COALESCE(tm.unassigned_ticket_count,0) / NULLIF(tm.ticket_count,0) AS pct_unassigned,  tm.avg_first_response_minutes AS avg_first_response_minutes,  tm.avg_resolution_minutes AS avg_resolution_minutesFROM all_rows rLEFT JOIN ticket_metrics tm ON tm.agent_group_name = r.agent_group_nameLEFT JOIN agent_counts ac ON ac.agent_group_name = r.agent_group_nameORDER BY  CASE WHEN r.agent_group_name = 'Unassigned queue' THEN 1 ELSE 0 END,  COALESCE(tm.ticket_count,0) DESC,  r.agent_group_name  CASE WHEN r.agent_group_name = 'Unassigned queue' THEN 1 ELSE 0 END NULLS LAST,  COALESCE(tm.ticket_count,0) DESC NULLS FIRST,  r.agent_group_name NULLS LAST

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

    WITH group_names AS (
  SELECT DISTINCT name AS agent_group_name
  FROM agent_groups
),

ticket_window AS (
  SELECT
    t.id AS id,
    t.agent_group_id AS agent_group_id,
    t.tenant_id AS tenant_id,
    ag.name AS group_name,
    t.status AS status,
    t.assignee_agent_id AS assignee_agent_id,
    t.created_at AS created_at,
    t.first_response_at AS first_response_at,
    t.resolved_at AS resolved_at
  FROM tickets t
  LEFT JOIN agent_groups ag
    ON ag.id = t.agent_group_id AND ag.tenant_id = t.tenant_id
  WHERE t.created_at >= TIMESTAMP '2025-01-01'
    AND t.created_at < TIMESTAMP '2026-01-01'
),

ticket_metrics AS (
  SELECT
    COALESCE(group_name, 'Unassigned queue') AS agent_group_name,
    COUNT(*) AS ticket_count,
    COUNT(*) FILTER (WHERE status NOT IN ('solved','closed')) AS open_ticket_count,
    COUNT(*) FILTER (WHERE assignee_agent_id IS NULL) AS unassigned_ticket_count,
    AVG(CAST(EXTRACT(EPOCH FROM (first_response_at - created_at)) AS NUMERIC(18,6)) / 60.0)
      FILTER (WHERE first_response_at IS NOT NULL) AS avg_first_response_minutes,
    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 ticket_window
  GROUP BY COALESCE(group_name, 'Unassigned queue')
),

agent_counts AS (
  SELECT
    ag.name AS agent_group_name,
    COUNT(DISTINCT CAST(agm.tenant_id AS text) || ':' || CAST(agm.support_agent_id AS text)) AS agent_count
  FROM agent_group_members agm
  JOIN agent_groups ag
    ON ag.id = agm.agent_group_id AND ag.tenant_id = agm.tenant_id
  GROUP BY ag.name
),

all_rows AS (
  SELECT agent_group_name FROM group_names
  UNION
  SELECT 'Unassigned queue'
)

SELECT
  r.agent_group_name AS agent_group_name,
  COALESCE(tm.ticket_count, 0) AS ticket_count,
  COALESCE(tm.open_ticket_count, 0) AS open_ticket_count,
  ac.agent_count AS agent_count,
  CAST(COALESCE(tm.ticket_count,0) AS numeric(18,6)) / NULLIF(ac.agent_count,0) AS tickets_per_agent,
  100.0 * COALESCE(tm.unassigned_ticket_count,0) / NULLIF(tm.ticket_count,0) AS pct_unassigned,
  tm.avg_first_response_minutes AS avg_first_response_minutes,
  tm.avg_resolution_minutes AS avg_resolution_minutes
FROM all_rows r
LEFT JOIN ticket_metrics tm ON tm.agent_group_name = r.agent_group_name
LEFT JOIN agent_counts ac ON ac.agent_group_name = r.agent_group_name
ORDER BY
  CASE WHEN r.agent_group_name = 'Unassigned queue' THEN 1 ELSE 0 END NULLS LAST,
  COALESCE(tm.ticket_count,0) DESC NULLS FIRST,
  r.agent_group_name NULLS LAST
    10 ms100 ms1 sHyper13.0 msDuckDB20.5 msGizmoSQL26.2 msclickhouse_brew78.9 msFirebolt60.5 msDataFusion59.8 msClickHouse175 ms

    Scale factor 1000, c8a.24xlarge, after the last load. q196 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: Ticket volume and staffing by agent group
Question: How is ticket volume distributed across our agent groups, and does staffing (agent count) look balanced against that load?
Grain: one row per agent group (plus one row for tickets with no agent_group_id, labeled 'Unassigned queue')
Columns:
- agent_group_name
- ticket_count (tickets created in 2025)
- open_ticket_count (status not in solved, closed, as of 2025-12-31)
- agent_count (distinct active support_agents in that group via agent_group_members)
- tickets_per_agent (ticket_count / agent_count)
- pct_unassigned (share of the group's tickets with assignee_agent_id null)
- avg_first_response_minutes (first_response_at - created_at, tickets with a first response)
- avg_resolution_minutes (resolved_at - created_at, tickets resolved)
Details: Window: tickets.created_at between 2025-01-01 and 2025-12-31 inclusive. Source tables: tickets, agent_groups, agent_group_members, support_agents. Join tickets.agent_group_id to agent_groups; count distinct support_agents per group from agent_group_members (count membership, no activity filter). Order by ticket_count descending, with the 'Unassigned queue' row last regardless of count so it reads as a footnote. Round averages to whole minutes and 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 group_names AS (
  SELECT DISTINCT name AS agent_group_name
  FROM agent_groups
),

ticket_window AS (
  SELECT
    t.id,
    t.agent_group_id,
    t.tenant_id,
    ag.name AS group_name,
    t.status,
    t.assignee_agent_id,
    t.created_at,
    t.first_response_at,
    t.resolved_at
  FROM tickets t
  LEFT JOIN agent_groups ag
    ON ag.id = t.agent_group_id AND ag.tenant_id = t.tenant_id
  WHERE t.created_at >= TIMESTAMP '2025-01-01'
    AND t.created_at < TIMESTAMP '2026-01-01'
),

ticket_metrics AS (
  SELECT
    COALESCE(group_name, 'Unassigned queue') AS agent_group_name,
    COUNT(*) AS ticket_count,
    COUNT(*) FILTER (WHERE status NOT IN ('solved','closed')) AS open_ticket_count,
    COUNT(*) FILTER (WHERE assignee_agent_id IS NULL) AS unassigned_ticket_count,
    AVG(EXTRACT(EPOCH FROM (first_response_at - created_at)) / 60.0)
      FILTER (WHERE first_response_at IS NOT NULL) AS avg_first_response_minutes,
    AVG(EXTRACT(EPOCH FROM (resolved_at - created_at)) / 60.0)
      FILTER (WHERE resolved_at IS NOT NULL) AS avg_resolution_minutes
  FROM ticket_window
  GROUP BY COALESCE(group_name, 'Unassigned queue')
),

agent_counts AS (
  SELECT
    ag.name AS agent_group_name,
    COUNT(DISTINCT agm.tenant_id::text || ':' || agm.support_agent_id::text) AS agent_count
  FROM agent_group_members agm
  JOIN agent_groups ag
    ON ag.id = agm.agent_group_id AND ag.tenant_id = agm.tenant_id
  GROUP BY ag.name
),

all_rows AS (
  SELECT agent_group_name FROM group_names
  UNION
  SELECT 'Unassigned queue'
)

SELECT
  r.agent_group_name,
  COALESCE(tm.ticket_count, 0) AS ticket_count,
  COALESCE(tm.open_ticket_count, 0) AS open_ticket_count,
  ac.agent_count,
  ROUND(COALESCE(tm.ticket_count,0)::numeric / NULLIF(ac.agent_count,0), 1) AS tickets_per_agent,
  ROUND(100.0 * COALESCE(tm.unassigned_ticket_count,0) / NULLIF(tm.ticket_count,0), 1) AS pct_unassigned,
  ROUND(tm.avg_first_response_minutes::numeric) AS avg_first_response_minutes,
  ROUND(tm.avg_resolution_minutes::numeric) AS avg_resolution_minutes
FROM all_rows r
LEFT JOIN ticket_metrics tm ON tm.agent_group_name = r.agent_group_name
LEFT JOIN agent_counts ac ON ac.agent_group_name = r.agent_group_name
ORDER BY
  CASE WHEN r.agent_group_name = 'Unassigned queue' THEN 1 ELSE 0 END,
  COALESCE(tm.ticket_count,0) DESC,
  r.agent_group_name
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: agent_group_name, ticket_count, open_ticket_count, agent_count, tickets_per_agent, pct_unassigned, avg_first_response_minutes, avg_resolution_minutes
    • GraceThis compiles correctly with all the requested columns. Submitting now.
    • Grace calls submit
      WITH group_names AS (
  SELECT DISTINCT name AS agent_group_name
  FROM agent_groups
),

ticket_window AS (
  SELECT
    t.id,
    t.agent_group_id,
    t.tenant_id,
    ag.name AS group_name,
    t.status,
    t.assignee_agent_id,
    t.created_at,
    t.first_response_at,
    t.resolved_at
  FROM tickets t
  LEFT JOIN agent_groups ag
    ON ag.id = t.agent_group_id AND ag.tenant_id = t.tenant_id
  WHERE t.created_at >= TIMESTAMP '2025-01-01'
    AND t.created_at < TIMESTAMP '2026-01-01'
),

ticket_metrics AS (
  SELECT
    COALESCE(group_name, 'Unassigned queue') AS agent_group_name,
    COUNT(*) AS ticket_count,
    COUNT(*) FILTER (WHERE status NOT IN ('solved','closed')) AS open_ticket_count,
    COUNT(*) FILTER (WHERE assignee_agent_id IS NULL) AS unassigned_ticket_count,
    AVG(EXTRACT(EPOCH FROM (first_response_at - created_at)) / 60.0)
      FILTER (WHERE first_response_at IS NOT NULL) AS avg_first_response_minutes,
    AVG(EXTRACT(EPOCH FROM (resolved_at - created_at)) / 60.0)
      FILTER (WHERE resolved_at IS NOT NULL) AS avg_resolution_minutes
  FROM ticket_window
  GROUP BY COALESCE(group_name, 'Unassigned queue')
),

agent_counts AS (
  SELECT
    ag.name AS agent_group_name,
    COUNT(DISTINCT agm.tenant_id::text || ':' || agm.support_agent_id::text) AS agent_count
  FROM agent_group_members agm
  JOIN agent_groups ag
    ON ag.id = agm.agent_group_id AND ag.tenant_id = agm.tenant_id
  GROUP BY ag.name
),

all_rows AS (
  SELECT agent_group_name FROM group_names
  UNION
  SELECT 'Unassigned queue'
)

SELECT
  r.agent_group_name,
  COALESCE(tm.ticket_count, 0) AS ticket_count,
  COALESCE(tm.open_ticket_count, 0) AS open_ticket_count,
  ac.agent_count,
  ROUND(COALESCE(tm.ticket_count,0)::numeric / NULLIF(ac.agent_count,0), 1) AS tickets_per_agent,
  ROUND(100.0 * COALESCE(tm.unassigned_ticket_count,0) / NULLIF(tm.ticket_count,0), 1) AS pct_unassigned,
  ROUND(tm.avg_first_response_minutes::numeric) AS avg_first_response_minutes,
  ROUND(tm.avg_resolution_minutes::numeric) AS avg_resolution_minutes
FROM all_rows r
LEFT JOIN ticket_metrics tm ON tm.agent_group_name = r.agent_group_name
LEFT JOIN agent_counts ac ON ac.agent_group_name = r.agent_group_name
ORDER BY
  CASE WHEN r.agent_group_name = 'Unassigned queue' THEN 1 ELSE 0 END,
  COALESCE(tm.ticket_count,0) DESC,
  r.agent_group_name
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • GraceSubmitted. One thing worth flagging for Ben: since `agent_groups` are tenant-scoped (each of the ~120 merchants has its own groups), this report aggregates by group *name* across all tenants — e.g. every merchant's "Tier 1" group lands in one combined row — rather than isolating a single merchant's support desk. I noted this in the submit note so he can confirm whether a per-tenant breakdown is actually what the Support Ops Manager wants, in case a follow-up request is needed.

    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 (289 KB) · Ben Carter's transcript (813 KB) · Mia Schultz's transcript (260 KB) · the night's plan (4 KB) · the night's reports (15 KB) · the week's memo (3 KB)