DB Olympics

Queries · q206 · Support Operations Manager · week 1, night 4

Ticket volume and speed by topic tag

“Are certain ticket topics (support tags) driving a disproportionate share of volume or taking much longer to resolve than others?”

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 SQLOmarPragmatic: the shortest correct query, aggregates first and joins dimensions afterwards, FILTER clauses for conditional counts.

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 4, request 2 of 3

    Good news first: the breach_kind audit confirms only two codes exist (first_response: 878 rows, resolution: 474 rows) matching our assumption almost exactly, so the 37.7%/20.3% headline breach rates from night 1 are solid — I can give Daniel that number with confidence. The channel/assignment report was mostly a null result: only 12 tickets platform-wide are currently unassigned (vs ~2,300 total), so 'sitting unassigned' isn't a scale driver of slowness even though those 12 do breach badly — and channel spreads resolution time by only ~12% (2626-2940 min), far smaller than the 3x spread we saw across merchants. So channel/assignment are minor levers; merchant identity remains the dominant driver, which I'll state plainly in the memo. The macro report shows real concentration (top 20 of 600 macros carry ~27% of lifetime usage) but also a red flag I need to note rather than resolve: lifetime macro usage (37,230) dwarfs even several years of 2025-scale agent reply volume (4,279), so I can't respond with a clean 'reliance %' — I'll flag that as a data caveat, not a finding. For this last night I'm closing out the remaining asks: KB article effectiveness (priority 4b), ticket volume/speed by topic (Daniel's 'certain topics' question, and it also tells us where KB investment should go), and CSAT against resolution speed (priority 5, directly testing whether satisfaction tracks the SLA/speed picture).

    Ticket volume and speed by topic tag
    Question
    Are certain ticket topics (support tags) driving a disproportionate share of volume or taking much longer to resolve than others?
    One row per
    support_tag, restricted to the top 20 by ticket_count
    Columns
    • tag_name
    • ticket_count (2025, via ticket_tags_map)
    • avg_first_response_minutes
    • avg_resolution_minutes
    • first_response_breach_rate
    • resolution_breach_rate
    • total_tagged_tickets_2025 (constant: count of distinct tickets created in 2025 with at least one tag)
    • total_tickets_2025 (constant: all tickets created in 2025, for comparison against total_tagged_tickets_2025)

  4. Omar 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 tickets_2025 AS (
  SELECT id, tenant_id, created_at, first_response_at, resolved_at
  FROM tickets
  WHERE created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01'
),
tagged AS (
  SELECT
    ttm.ticket_id,
    st.name AS tag_name,
    t.created_at,
    t.first_response_at,
    t.resolved_at
  FROM ticket_tags_map ttm
  JOIN support_tags st
    ON st.id = ttm.support_tag_id AND st.tenant_id = ttm.tenant_id
  JOIN tickets_2025 t
    ON t.id = ttm.ticket_id
),
breaches_per_ticket AS (
  SELECT
    ticket_id,
    BOOL_OR(breach_kind = 'first_response') AS fr_breach,
    BOOL_OR(breach_kind = 'resolution') AS res_breach
  FROM sla_breaches
  WHERE breach_kind IN ('first_response', 'resolution')
  GROUP BY ticket_id
),
tag_stats AS (
  SELECT
    tagged.tag_name,
    COUNT(DISTINCT tagged.ticket_id) AS ticket_count,
    AVG(EXTRACT(EPOCH FROM (tagged.first_response_at - tagged.created_at)) / 60.0)
      FILTER (WHERE tagged.first_response_at IS NOT NULL) AS avg_first_response_minutes,
    AVG(EXTRACT(EPOCH FROM (tagged.resolved_at - tagged.created_at)) / 60.0)
      FILTER (WHERE tagged.resolved_at IS NOT NULL) AS avg_resolution_minutes,
    COUNT(DISTINCT tagged.ticket_id) FILTER (WHERE bpt.fr_breach) AS fr_breach_count,
    COUNT(DISTINCT tagged.ticket_id) FILTER (WHERE bpt.res_breach) AS res_breach_count
  FROM tagged
  LEFT JOIN breaches_per_ticket bpt ON bpt.ticket_id = tagged.ticket_id
  GROUP BY tagged.tag_name
),
totals AS (
  SELECT
    (SELECT COUNT(*) FROM tickets_2025) AS total_tickets_2025,
    (SELECT COUNT(DISTINCT ttm.ticket_id)
       FROM ticket_tags_map ttm
       JOIN tickets_2025 t ON t.id = ttm.ticket_id) AS total_tagged_tickets_2025
)
SELECT
  tag_stats.tag_name,
  tag_stats.ticket_count,
  ROUND(tag_stats.avg_first_response_minutes, 1) AS avg_first_response_minutes,
  ROUND(tag_stats.avg_resolution_minutes, 1) AS avg_resolution_minutes,
  ROUND(tag_stats.fr_breach_count::numeric / NULLIF(tag_stats.ticket_count, 0), 4) AS first_response_breach_rate,
  ROUND(tag_stats.res_breach_count::numeric / NULLIF(tag_stats.ticket_count, 0), 4) AS resolution_breach_rate,
  totals.total_tagged_tickets_2025,
  totals.total_tickets_2025
FROM tag_stats
CROSS JOIN totals
ORDER BY tag_stats.ticket_count DESC, tag_stats.tag_name ASC
LIMIT 20

    “Query compiles and produces 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. Summary of what the query does: - Grain: one row per support tag name (aggregated across tenants, since the report doesn't carry a merchant column), top 20 by ticket count. - Volume: ticket_count = distinct 2025 tickets carrying that tag. - Speed: avg_first_response_minutes / avg_resolution_minutes computed from first_response_at/resolved_at minus created_at, only over tickets where those timestamps are populated. - SLA health: breach rates are the share of that tag's tickets with a matching sla_breaches row (first_response / resolution kinds). - Coverage constants: total_tagged_tickets_2025 (distinct 2025 tickets with ≥1 tag) and total_tickets_2025 (all 2025 tickets) are repeated on every row so Ben can see at a glance how much volume is tag-less. Noted in the submit note: tag names are combined across tenants, breach rates use ticket_count as the denominator (not just tickets with an SLA policy), and the distinct-tag-count-in-use figure wasn't included since it's a separate single-value fact outside this report's grain.”

  5. The report comes back overnight

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

    tag_name          | ticket_count | avg_first_response_minutes | avg_resolution_minutes | first_response_breach_rate | resolution_breach_rate | total_tagged_tickets_2025 | total_tickets_2025
------------------+--------------+----------------------------+------------------------+----------------------------+------------------------+---------------------------+-------------------
delivery          |         1195 |                     1606.5 |                 3045.0 |                     0.3883 |                 0.1757 |                      2320 |               2320
where-is-my-order |         1057 |                     1703.3 |                 3209.0 |                     0.3955 |                 0.1845 |                      2320 |               2320
complaint         |          464 |                      859.0 |                 1630.2 |                     0.3815 |                 0.3621 |                      2320 |               2320
return            |          410 |                     1680.6 |                 2880.1 |                     0.4488 |                 0.2024 |                      2320 |               2320
product-question  |          247 |                     1857.5 |                 2621.0 |                     0.2146 |                 0.0445 |                      2320 |               2320
damaged           |          210 |                     1147.0 |                 2016.8 |                     0.3333 |                    0.5 |                      2320 |               2320
refund            |           65 |                      536.3 |                 1380.0 |                     0.4615 |                 0.3385 |                      2320 |               2320
payment           |           20 |                      927.9 |                 1251.2 |                       0.25 |                   0.05 |                      2320 |               2320
account           |            9 |                      108.7 |                 1087.1 |                     0.4444 |                 0.6667 |                      2320 |               2320
(9 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:

    It builds on:

  7. It becomes benchmark query q206

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

    • spellingcast (2×)
    • spellingfetch first
    • 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 (9×)
    • placeholderbool or (2×)
    • 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 (2×)
    • placeholderfetch first
    The benchmark text, against the submitted SQL
    WITH tickets_2025 AS (  SELECT id, tenant_id, created_at, first_response_at, resolved_at  FROM tickets  WHERE created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01'),tagged AS (  SELECT    ttm.ticket_id,    ttm.ticket_id AS ticket_id,    st.name AS tag_name,    t.created_at,    t.first_response_at,    t.resolved_at    t.created_at AS created_at,    t.first_response_at AS first_response_at,    t.resolved_at AS resolved_at  FROM ticket_tags_map ttm  JOIN support_tags st    ON st.id = ttm.support_tag_id AND st.tenant_id = ttm.tenant_id  JOIN tickets_2025 t    ON t.id = ttm.ticket_id),breaches_per_ticket AS (  SELECT    ticket_id,    BOOL_OR(breach_kind = 'first_response') AS fr_breach,    BOOL_OR(breach_kind = 'resolution') AS res_breach    {{bool_or(breach_kind = 'first_response')}} AS fr_breach,    {{bool_or(breach_kind = 'resolution')}} AS res_breach  FROM sla_breaches  WHERE breach_kind IN ('first_response', 'resolution')  GROUP BY ticket_id),tag_stats AS (  SELECT    tagged.tag_name,    tagged.tag_name AS tag_name,    COUNT(DISTINCT tagged.ticket_id) AS ticket_count,    AVG(EXTRACT(EPOCH FROM (tagged.first_response_at - tagged.created_at)) / 60.0)    AVG({{seconds_between(tagged.created_at, tagged.first_response_at)}} / 60.0)      FILTER (WHERE tagged.first_response_at IS NOT NULL) AS avg_first_response_minutes,    AVG(EXTRACT(EPOCH FROM (tagged.resolved_at - tagged.created_at)) / 60.0)    AVG({{seconds_between(tagged.created_at, tagged.resolved_at)}} / 60.0)      FILTER (WHERE tagged.resolved_at IS NOT NULL) AS avg_resolution_minutes,    COUNT(DISTINCT tagged.ticket_id) FILTER (WHERE bpt.fr_breach) AS fr_breach_count,    COUNT(DISTINCT tagged.ticket_id) FILTER (WHERE bpt.res_breach) AS res_breach_count  FROM tagged  LEFT JOIN breaches_per_ticket bpt ON bpt.ticket_id = tagged.ticket_id  GROUP BY tagged.tag_name),totals AS (  SELECT    (SELECT COUNT(*) FROM tickets_2025) AS total_tickets_2025,    (SELECT COUNT(DISTINCT ttm.ticket_id)       FROM ticket_tags_map ttm       JOIN tickets_2025 t ON t.id = ttm.ticket_id) AS total_tagged_tickets_2025)SELECT  tag_stats.tag_name,  tag_stats.ticket_count,  ROUND(tag_stats.avg_first_response_minutes, 1) AS avg_first_response_minutes,  ROUND(tag_stats.avg_resolution_minutes, 1) AS avg_resolution_minutes,  ROUND(tag_stats.fr_breach_count::numeric / NULLIF(tag_stats.ticket_count, 0), 4) AS first_response_breach_rate,  ROUND(tag_stats.res_breach_count::numeric / NULLIF(tag_stats.ticket_count, 0), 4) AS resolution_breach_rate,  totals.total_tagged_tickets_2025,  totals.total_tickets_2025  tag_stats.tag_name AS tag_name,  tag_stats.ticket_count AS ticket_count,  tag_stats.avg_first_response_minutes AS avg_first_response_minutes,  tag_stats.avg_resolution_minutes AS avg_resolution_minutes,  CAST(tag_stats.fr_breach_count AS numeric(18,6)) / NULLIF(tag_stats.ticket_count, 0) AS first_response_breach_rate,  CAST(tag_stats.res_breach_count AS numeric(18,6)) / NULLIF(tag_stats.ticket_count, 0) AS resolution_breach_rate,  totals.total_tagged_tickets_2025 AS total_tagged_tickets_2025,  totals.total_tickets_2025 AS total_tickets_2025FROM tag_statsCROSS JOIN totalsORDER BY tag_stats.ticket_count DESC, tag_stats.tag_name ASCLIMIT 20ORDER BY tag_stats.ticket_count DESC NULLS FIRST, tag_stats.tag_name ASC NULLS LAST{{fetch_first(20)}}

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

    WITH tickets_2025 AS (
  SELECT id, tenant_id, created_at, first_response_at, resolved_at
  FROM tickets
  WHERE created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01'
),
tagged AS (
  SELECT
    ttm.ticket_id AS ticket_id,
    st.name AS tag_name,
    t.created_at AS created_at,
    t.first_response_at AS first_response_at,
    t.resolved_at AS resolved_at
  FROM ticket_tags_map ttm
  JOIN support_tags st
    ON st.id = ttm.support_tag_id AND st.tenant_id = ttm.tenant_id
  JOIN tickets_2025 t
    ON t.id = ttm.ticket_id
),
breaches_per_ticket AS (
  SELECT
    ticket_id,
    BOOL_OR(breach_kind = 'first_response') AS fr_breach,
    BOOL_OR(breach_kind = 'resolution') AS res_breach
  FROM sla_breaches
  WHERE breach_kind IN ('first_response', 'resolution')
  GROUP BY ticket_id
),
tag_stats AS (
  SELECT
    tagged.tag_name AS tag_name,
    COUNT(DISTINCT tagged.ticket_id) AS ticket_count,
    AVG(CAST(EXTRACT(EPOCH FROM (tagged.first_response_at - tagged.created_at)) AS NUMERIC(18,6)) / 60.0)
      FILTER (WHERE tagged.first_response_at IS NOT NULL) AS avg_first_response_minutes,
    AVG(CAST(EXTRACT(EPOCH FROM (tagged.resolved_at - tagged.created_at)) AS NUMERIC(18,6)) / 60.0)
      FILTER (WHERE tagged.resolved_at IS NOT NULL) AS avg_resolution_minutes,
    COUNT(DISTINCT tagged.ticket_id) FILTER (WHERE bpt.fr_breach) AS fr_breach_count,
    COUNT(DISTINCT tagged.ticket_id) FILTER (WHERE bpt.res_breach) AS res_breach_count
  FROM tagged
  LEFT JOIN breaches_per_ticket bpt ON bpt.ticket_id = tagged.ticket_id
  GROUP BY tagged.tag_name
),
totals AS (
  SELECT
    (SELECT COUNT(*) FROM tickets_2025) AS total_tickets_2025,
    (SELECT COUNT(DISTINCT ttm.ticket_id)
       FROM ticket_tags_map ttm
       JOIN tickets_2025 t ON t.id = ttm.ticket_id) AS total_tagged_tickets_2025
)
SELECT
  tag_stats.tag_name AS tag_name,
  tag_stats.ticket_count AS ticket_count,
  tag_stats.avg_first_response_minutes AS avg_first_response_minutes,
  tag_stats.avg_resolution_minutes AS avg_resolution_minutes,
  CAST(tag_stats.fr_breach_count AS numeric(18,6)) / NULLIF(tag_stats.ticket_count, 0) AS first_response_breach_rate,
  CAST(tag_stats.res_breach_count AS numeric(18,6)) / NULLIF(tag_stats.ticket_count, 0) AS resolution_breach_rate,
  totals.total_tagged_tickets_2025 AS total_tagged_tickets_2025,
  totals.total_tickets_2025 AS total_tickets_2025
FROM tag_stats
CROSS JOIN totals
ORDER BY tag_stats.ticket_count DESC NULLS FIRST, tag_stats.tag_name ASC NULLS LAST
FETCH FIRST 20 ROWS ONLY
    100 ms1 sHyper139 msDuckDB111 msGizmoSQL120 msclickhouse_brew297 msFirebolt383 msDataFusionwrong resultClickHouse402 ms
    • DataFusionrow 9: col 3: "61.7599042122437843522431890787150028771554" vs "61.75153183721252"

    Scale factor 1000, c8a.24xlarge, after the last load. q206 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

    Omar's whole session, 7 turns
    • The request, as Omar received it
      Report request from Ben Carter (analyst for the Support Operations Manager):

Title: Ticket volume and speed by topic tag
Question: Are certain ticket topics (support tags) driving a disproportionate share of volume or taking much longer to resolve than others?
Grain: one row per support_tag, restricted to the top 20 by ticket_count
Columns:
- tag_name
- ticket_count (2025, via ticket_tags_map)
- avg_first_response_minutes
- avg_resolution_minutes
- first_response_breach_rate
- resolution_breach_rate
- total_tagged_tickets_2025 (constant: count of distinct tickets created in 2025 with at least one tag)
- total_tickets_2025 (constant: all tickets created in 2025, for comparison against total_tagged_tickets_2025)
Details: Window: tickets.created_at between 2025-01-01 and 2025-12-31. Source tables: tickets, ticket_tags_map, support_tags, sla_breaches (breach_kind = 'first_response' / 'resolution', confirmed codes from last night's audit). A ticket with multiple tags counts once per tag it carries. Order by ticket_count descending, top 20 rows; note the total number of distinct tags in use and highlight if total_tagged_tickets_2025 is materially below total_tickets_2025 (meaning a lot of tickets carry no topic tag at all, which would limit how far topic-based routing/KB investment can go).

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Omar calls dry_run
      WITH tickets_2025 AS (
  SELECT id, tenant_id, created_at, first_response_at, resolved_at
  FROM tickets
  WHERE created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01'
),
tagged AS (
  SELECT
    ttm.ticket_id,
    st.name AS tag_name,
    t.created_at,
    t.first_response_at,
    t.resolved_at
  FROM ticket_tags_map ttm
  JOIN support_tags st
    ON st.id = ttm.support_tag_id AND st.tenant_id = ttm.tenant_id
  JOIN tickets_2025 t
    ON t.id = ttm.ticket_id
),
breaches_per_ticket AS (
  SELECT
    ticket_id,
    BOOL_OR(breach_kind = 'first_response') AS fr_breach,
    BOOL_OR(breach_kind = 'resolution') AS res_breach
  FROM sla_breaches
  WHERE breach_kind IN ('first_response', 'resolution')
  GROUP BY ticket_id
),
tag_stats AS (
  SELECT
    tagged.tag_name,
    COUNT(DISTINCT tagged.ticket_id) AS ticket_count,
    AVG(EXTRACT(EPOCH FROM (tagged.first_response_at - tagged.created_at)) / 60.0)
      FILTER (WHERE tagged.first_response_at IS NOT NULL) AS avg_first_response_minutes,
    AVG(EXTRACT(EPOCH FROM (tagged.resolved_at - tagged.created_at)) / 60.0)
      FILTER (WHERE tagged.resolved_at IS NOT NULL) AS avg_resolution_minutes,
    COUNT(DISTINCT tagged.ticket_id) FILTER (WHERE bpt.fr_breach) AS fr_breach_count,
    COUNT(DISTINCT tagged.ticket_id) FILTER (WHERE bpt.res_breach) AS res_breach_count
  FROM tagged
  LEFT JOIN breaches_per_ticket bpt ON bpt.ticket_id = tagged.ticket_id
  GROUP BY tagged.tag_name
),
totals AS (
  SELECT
    (SELECT COUNT(*) FROM tickets_2025) AS total_tickets_2025,
    (SELECT COUNT(DISTINCT ttm.ticket_id)
       FROM ticket_tags_map ttm
       JOIN tickets_2025 t ON t.id = ttm.ticket_id) AS total_tagged_tickets_2025
)
SELECT
  tag_stats.tag_name,
  tag_stats.ticket_count,
  ROUND(tag_stats.avg_first_response_minutes, 1) AS avg_first_response_minutes,
  ROUND(tag_stats.avg_resolution_minutes, 1) AS avg_resolution_minutes,
  ROUND(tag_stats.fr_breach_count::numeric / NULLIF(tag_stats.ticket_count, 0), 4) AS first_response_breach_rate,
  ROUND(tag_stats.res_breach_count::numeric / NULLIF(tag_stats.ticket_count, 0), 4) AS resolution_breach_rate,
  totals.total_tagged_tickets_2025,
  totals.total_tickets_2025
FROM tag_stats
CROSS JOIN totals
ORDER BY tag_stats.ticket_count DESC, tag_stats.tag_name ASC
LIMIT 20
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: tag_name, ticket_count, avg_first_response_minutes, avg_resolution_minutes, first_response_breach_rate, resolution_breach_rate, total_tagged_tickets_2025, total_tickets_2025
    • OmarQuery compiles and produces the requested columns. Submitting now.
    • Omar calls submit
      WITH tickets_2025 AS (
  SELECT id, tenant_id, created_at, first_response_at, resolved_at
  FROM tickets
  WHERE created_at >= DATE '2025-01-01' AND created_at < DATE '2026-01-01'
),
tagged AS (
  SELECT
    ttm.ticket_id,
    st.name AS tag_name,
    t.created_at,
    t.first_response_at,
    t.resolved_at
  FROM ticket_tags_map ttm
  JOIN support_tags st
    ON st.id = ttm.support_tag_id AND st.tenant_id = ttm.tenant_id
  JOIN tickets_2025 t
    ON t.id = ttm.ticket_id
),
breaches_per_ticket AS (
  SELECT
    ticket_id,
    BOOL_OR(breach_kind = 'first_response') AS fr_breach,
    BOOL_OR(breach_kind = 'resolution') AS res_breach
  FROM sla_breaches
  WHERE breach_kind IN ('first_response', 'resolution')
  GROUP BY ticket_id
),
tag_stats AS (
  SELECT
    tagged.tag_name,
    COUNT(DISTINCT tagged.ticket_id) AS ticket_count,
    AVG(EXTRACT(EPOCH FROM (tagged.first_response_at - tagged.created_at)) / 60.0)
      FILTER (WHERE tagged.first_response_at IS NOT NULL) AS avg_first_response_minutes,
    AVG(EXTRACT(EPOCH FROM (tagged.resolved_at - tagged.created_at)) / 60.0)
      FILTER (WHERE tagged.resolved_at IS NOT NULL) AS avg_resolution_minutes,
    COUNT(DISTINCT tagged.ticket_id) FILTER (WHERE bpt.fr_breach) AS fr_breach_count,
    COUNT(DISTINCT tagged.ticket_id) FILTER (WHERE bpt.res_breach) AS res_breach_count
  FROM tagged
  LEFT JOIN breaches_per_ticket bpt ON bpt.ticket_id = tagged.ticket_id
  GROUP BY tagged.tag_name
),
totals AS (
  SELECT
    (SELECT COUNT(*) FROM tickets_2025) AS total_tickets_2025,
    (SELECT COUNT(DISTINCT ttm.ticket_id)
       FROM ticket_tags_map ttm
       JOIN tickets_2025 t ON t.id = ttm.ticket_id) AS total_tagged_tickets_2025
)
SELECT
  tag_stats.tag_name,
  tag_stats.ticket_count,
  ROUND(tag_stats.avg_first_response_minutes, 1) AS avg_first_response_minutes,
  ROUND(tag_stats.avg_resolution_minutes, 1) AS avg_resolution_minutes,
  ROUND(tag_stats.fr_breach_count::numeric / NULLIF(tag_stats.ticket_count, 0), 4) AS first_response_breach_rate,
  ROUND(tag_stats.res_breach_count::numeric / NULLIF(tag_stats.ticket_count, 0), 4) AS resolution_breach_rate,
  totals.total_tagged_tickets_2025,
  totals.total_tickets_2025
FROM tag_stats
CROSS JOIN totals
ORDER BY tag_stats.ticket_count DESC, tag_stats.tag_name ASC
LIMIT 20
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • OmarSubmitted. Summary of what the query does: - **Grain:** one row per support tag name (aggregated across tenants, since the report doesn't carry a merchant column), top 20 by ticket count. - **Volume:** `ticket_count` = distinct 2025 tickets carrying that tag. - **Speed:** `avg_first_response_minutes` / `avg_resolution_minutes` computed from `first_response_at`/`resolved_at` minus `created_at`, only over tickets where those timestamps are populated. - **SLA health:** breach rates are the share of that tag's tickets with a matching `sla_breaches` row (`first_response` / `resolution` kinds). - **Coverage constants:** `total_tagged_tickets_2025` (distinct 2025 tickets with ≥1 tag) and `total_tickets_2025` (all 2025 tickets) are repeated on every row so Ben can see at a glance how much volume is tag-less. Noted in the submit note: tag names are combined across tenants, breach rates use ticket_count as the denominator (not just tickets with an SLA policy), and the distinct-tag-count-in-use figure wasn't included since it's a separate single-value fact outside this report's grain.

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

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