DB Olympics

Queries · q251 · VP Customer Experience · week 2, night 3

Q4-vs-rest-of-year ticket load per agent, all qualifying merchants

“Is 'flat support staffing through a seasonal ticket surge' a pattern specific to the three hotspot merchants, or common across the book?”

Manager · asked the questionDaniel AchterbergVP Customer Experience. Former head of support at a marketplace. Merchants run their helpdesk on Tidewick, so their shoppers' experience is partly Tidewick's reputation.
Analyst · planned the reportMateo RuizComparative: likes year-over-year and peer comparisons across merchants and countries.
Engineer · wrote the SQLFelixJunior engineer who nests subqueries in FROM and WHERE (IN, EXISTS, correlated subqueries) rather than CTEs.

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

  1. Daniel Achterberg writes to Mateo Ruiz

    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:

    1. What's actually different about how Cragsley, Marrowby, and Studio Vrij run their support compared to the rest of the book — are they understaffed relative to their ticket volume, leaning on slower channels, getting hit with harder/more complex issues, or something else? I want to know if this is something we can fix with guidance or tooling, or if it's specific to each merchant's situation.
    2. Is the urgent-priority failure (missing resolution half the time) concentrated in those same few merchants, or is it a broader pattern across the book? If urgent tickets are failing everywhere, that's a different and more worrying problem than a handful of struggling merchants.
    3. Has Studio Vrij actually recovered since the spring breakdown, and what would it take to get ahead of the Nov/Dec pattern this year before it hits? I'd like to walk into December with a plan, not another postmortem.
    4. Before I take "respond faster" to merchants as the headline fix, can we check whether the slowest merchants differ in other ways (ticket mix, how support is staffed, shopper profile) that might also explain their low satisfaction? I don't want to oversimplify the story if something else is going on underneath.

    Let's prioritize #1 and #2 this week — those feel most actionable. #3 and #4 can wait if slots are tight.

    Thanks, Daniel

  2. Mateo Ruiz plans the night

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

    Night 2 results are decisive on complexity: Cragsley (1.36 tags/ticket, 3.46 messages/ticket) and Marrowby (1.43, 3.29) are actually at the LOW end of complexity among all 22 qualifying merchants - Studio Vrij is mid-pack (1.64, 3.71). So "harder issues" is refuted as an explanation; it really does look like a volume/staffing story. The SLA-target report also refutes the "unrealistic target" theory for the broad urgent-miss problem - every merchant evaluated uses the identical 8-hour urgent target, so the wide spread in miss rates (14%-100%) reflects genuine differences in execution speed, not target calibration. The quarterly load report is the key new finding: all three hotspots (and comparison peer Grind Collective) run a completely flat agent headcount all four quarters, but ticket volume surges hard in specific quarters - Cragsley and Marrowby both spike in Q4 (tickets-per-agent roughly doubles vs Q1-Q3), Studio Vrij spikes specifically in Q2 (the already-known fulfillment incident), and Grind Collective also spikes in Q4 even harder than Cragsley. That's a strong lead: nobody scales support staff for volume surges, and it's not unique to the three named merchants. I want to (1) confirm urgent-ticket performance specifically degrades in the same quarters where each merchant's load spiked (a within-merchant causal test), (2) check whether this "flat staffing through a seasonal/incident surge" pattern is common across the whole qualifying book, not just these four - that determines if it's a platform-level guidance opportunity, and (3) characterize whether the big three's inflated average (vs. median) urgent resolution time is a few catastrophic outlier tickets or generally slow handling, since that changes what the fix would look like (triage/escalation vs. core speed).

    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?
    One row per
    tenant, among tenants with at least 20 tickets created in 2025
    Columns
    • merchant name
    • tickets created, Q1-Q3 2025 (combined)
    • avg distinct agents handling tickets, Q1-Q3 (average of the three quarterly counts)
    • tickets per agent per quarter, Q1-Q3 average
    • tickets created, Q4 2025
    • distinct agents handling tickets, Q4
    • tickets per agent, Q4

  3. Felix 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)
    SELECT
  tn.name AS merchant_name,
  q123.tickets_q123,
  ROUND(q123.avg_agents_q123, 1) AS avg_agents_q1_q3,
  ROUND((q123.tickets_q123 / 3.0) / NULLIF(q123.avg_agents_q123, 0), 1) AS tickets_per_agent_q1_q3,
  q4.tickets_q4,
  q4.agents_q4,
  ROUND(q4.tickets_q4::numeric / NULLIF(q4.agents_q4, 0), 1) AS tickets_per_agent_q4
FROM tenants tn
JOIN (
  SELECT qt.tenant_id,
         SUM(COALESCE(qd.tickets, 0)) AS tickets_q123,
         AVG(COALESCE(qd.agents, 0)) AS avg_agents_q123
  FROM (
    SELECT tenant_id
    FROM tickets
    WHERE created_at >= TIMESTAMP '2025-01-01' AND created_at < TIMESTAMP '2026-01-01'
    GROUP BY tenant_id
    HAVING COUNT(*) >= 20
  ) qt
  CROSS JOIN (SELECT 1 AS q UNION ALL SELECT 2 UNION ALL SELECT 3) quarters
  LEFT JOIN (
    SELECT tenant_id,
           EXTRACT(QUARTER FROM created_at) AS q,
           COUNT(*) AS tickets,
           COUNT(DISTINCT assignee_agent_id) AS agents
    FROM tickets
    WHERE created_at >= TIMESTAMP '2025-01-01' AND created_at < TIMESTAMP '2025-10-01'
    GROUP BY tenant_id, EXTRACT(QUARTER FROM created_at)
  ) qd ON qd.tenant_id = qt.tenant_id AND qd.q = quarters.q
  GROUP BY qt.tenant_id
) q123 ON q123.tenant_id = tn.id
JOIN (
  SELECT tenant_id,
         COUNT(*) AS tickets_q4,
         COUNT(DISTINCT assignee_agent_id) AS agents_q4
  FROM tickets
  WHERE created_at >= TIMESTAMP '2025-10-01' AND created_at < TIMESTAMP '2026-01-01'
  GROUP BY tenant_id
) q4 ON q4.tenant_id = tn.id
ORDER BY tickets_per_agent_q4 DESC, merchant_name ASC

    “The query compiles correctly with the expected columns. Let me submit it.”

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

    “Submitted. The query builds one row per tenant with ≥20 tickets created in 2025, computing Q1-Q3 combined tickets and average distinct agents per quarter (zero-filled for any ticketless quarter so the average is always over 3 quarters), the derived tickets-per-agent-per-quarter rate, and the Q4 equivalents, ordered by Q4 crunch severity descending.”

  4. The report comes back overnight

    As Mateo Ruiz saw it, from the small dataset the agents worked on

    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)

  5. What came of it

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

    It builds on:

  6. It becomes benchmark query q251

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

    • spellingcast
    • determinismtie-breaker: tenant names are not unique
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
    • 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: AVG over an integer computed as a decimal: AVG(CAST(x AS NUMERIC(18,6)))
    • made explicita selected t.col gets AS col, its standard name made explicit (4×)
    • 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
    SELECT  tn.name AS merchant_name,  q123.tickets_q123,  ROUND(q123.avg_agents_q123, 1) AS avg_agents_q1_q3,  ROUND((q123.tickets_q123 / 3.0) / NULLIF(q123.avg_agents_q123, 0), 1) AS tickets_per_agent_q1_q3,  q4.tickets_q4,  q4.agents_q4,  ROUND(q4.tickets_q4::numeric / NULLIF(q4.agents_q4, 0), 1) AS tickets_per_agent_q4  q123.tickets_q123 AS tickets_q123,  q123.avg_agents_q123 AS avg_agents_q1_q3,  (q123.tickets_q123 / 3.0) / NULLIF(q123.avg_agents_q123, 0) AS tickets_per_agent_q1_q3,  q4.tickets_q4 AS tickets_q4,  q4.agents_q4 AS agents_q4,  CAST(q4.tickets_q4 AS numeric(18,6)) / NULLIF(q4.agents_q4, 0) AS tickets_per_agent_q4FROM tenants tnJOIN (  SELECT qt.tenant_id,  SELECT qt.tenant_id AS tenant_id,         SUM(COALESCE(qd.tickets, 0)) AS tickets_q123,         AVG(COALESCE(qd.agents, 0)) AS avg_agents_q123         AVG(CAST(COALESCE(qd.agents, 0) AS NUMERIC(18,6))) AS avg_agents_q123  FROM (    SELECT tenant_id    FROM tickets    WHERE created_at >= TIMESTAMP '2025-01-01' AND created_at < TIMESTAMP '2026-01-01'    GROUP BY tenant_id    HAVING COUNT(*) >= 20  ) qt  CROSS JOIN (SELECT 1 AS q UNION ALL SELECT 2 UNION ALL SELECT 3) quarters  LEFT JOIN (    SELECT tenant_id,           EXTRACT(QUARTER FROM created_at) AS q,           COUNT(*) AS tickets,           COUNT(DISTINCT assignee_agent_id) AS agents    FROM tickets    WHERE created_at >= TIMESTAMP '2025-01-01' AND created_at < TIMESTAMP '2025-10-01'    GROUP BY tenant_id, EXTRACT(QUARTER FROM created_at)  ) qd ON qd.tenant_id = qt.tenant_id AND qd.q = quarters.q  GROUP BY qt.tenant_id) q123 ON q123.tenant_id = tn.idJOIN (  SELECT tenant_id,         COUNT(*) AS tickets_q4,         COUNT(DISTINCT assignee_agent_id) AS agents_q4  FROM tickets  WHERE created_at >= TIMESTAMP '2025-10-01' AND created_at < TIMESTAMP '2026-01-01'  GROUP BY tenant_id) q4 ON q4.tenant_id = tn.idORDER BY tickets_per_agent_q4 DESC, merchant_name ASCORDER BY tickets_per_agent_q4 DESC NULLS FIRST, merchant_name ASC NULLS LAST, tn.id NULLS LAST

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

    SELECT
  tn.name AS merchant_name,
  q123.tickets_q123 AS tickets_q123,
  q123.avg_agents_q123 AS avg_agents_q1_q3,
  (q123.tickets_q123 / 3.0) / NULLIF(q123.avg_agents_q123, 0) AS tickets_per_agent_q1_q3,
  q4.tickets_q4 AS tickets_q4,
  q4.agents_q4 AS agents_q4,
  CAST(q4.tickets_q4 AS numeric(18,6)) / NULLIF(q4.agents_q4, 0) AS tickets_per_agent_q4
FROM tenants tn
JOIN (
  SELECT qt.tenant_id AS tenant_id,
         SUM(COALESCE(qd.tickets, 0)) AS tickets_q123,
         AVG(CAST(COALESCE(qd.agents, 0) AS NUMERIC(18,6))) AS avg_agents_q123
  FROM (
    SELECT tenant_id
    FROM tickets
    WHERE created_at >= TIMESTAMP '2025-01-01' AND created_at < TIMESTAMP '2026-01-01'
    GROUP BY tenant_id
    HAVING COUNT(*) >= 20
  ) qt
  CROSS JOIN (SELECT 1 AS q UNION ALL SELECT 2 UNION ALL SELECT 3) quarters
  LEFT JOIN (
    SELECT tenant_id,
           EXTRACT(QUARTER FROM created_at) AS q,
           COUNT(*) AS tickets,
           COUNT(DISTINCT assignee_agent_id) AS agents
    FROM tickets
    WHERE created_at >= TIMESTAMP '2025-01-01' AND created_at < TIMESTAMP '2025-10-01'
    GROUP BY tenant_id, EXTRACT(QUARTER FROM created_at)
  ) qd ON qd.tenant_id = qt.tenant_id AND qd.q = quarters.q
  GROUP BY qt.tenant_id
) q123 ON q123.tenant_id = tn.id
JOIN (
  SELECT tenant_id,
         COUNT(*) AS tickets_q4,
         COUNT(DISTINCT assignee_agent_id) AS agents_q4
  FROM tickets
  WHERE created_at >= TIMESTAMP '2025-10-01' AND created_at < TIMESTAMP '2026-01-01'
  GROUP BY tenant_id
) q4 ON q4.tenant_id = tn.id
ORDER BY tickets_per_agent_q4 DESC NULLS FIRST, merchant_name ASC NULLS LAST, tn.id NULLS LAST
    10 ms100 ms1 sHyper14.2 msDuckDB18.1 msGizmoSQL22.3 msclickhouse_brew70.0 msFirebolt57.9 msDataFusion112 msClickHouse41.1 ms

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

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

    Felix's whole session, 7 turns
    • The request, as Felix received it
      Report request from Mateo Ruiz (analyst for the VP Customer Experience):

Title: 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?
Grain: one row per tenant, among tenants with at least 20 tickets created in 2025
Columns:
- merchant name
- tickets created, Q1-Q3 2025 (combined)
- avg distinct agents handling tickets, Q1-Q3 (average of the three quarterly counts)
- tickets per agent per quarter, Q1-Q3 average
- tickets created, Q4 2025
- distinct agents handling tickets, Q4
- tickets per agent, Q4
Details: Source: tickets joined to tenants. Window: tickets.created_at in 2025. Q1-Q3 = Jan-Sep 2025 combined; Q4 = Oct-Dec 2025. 'avg distinct agents, Q1-Q3' = average across the three individual quarters of count distinct assignee_agent_id (excluding null) per quarter, rounded to 1 decimal. 'tickets per agent per quarter, Q1-Q3 average' = (Q1-Q3 combined tickets ÷ 3) ÷ avg distinct agents Q1-Q3, rounded to 1 decimal. 'tickets per agent, Q4' = Q4 tickets ÷ distinct agents handling tickets in Q4 (excluding null), rounded to 1 decimal. Only include tenants with >=20 tickets created in 2025 (same threshold as the earlier staffing/channel/complexity reports, for a consistent peer set of 22). Order by 'tickets per agent, Q4' descending (worst Q4 crunch first), tie-break merchant name ascending. Expect at most 22 rows.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Felix calls dry_run
      SELECT
  tn.name AS merchant_name,
  q123.tickets_q123,
  ROUND(q123.avg_agents_q123, 1) AS avg_agents_q1_q3,
  ROUND((q123.tickets_q123 / 3.0) / NULLIF(q123.avg_agents_q123, 0), 1) AS tickets_per_agent_q1_q3,
  q4.tickets_q4,
  q4.agents_q4,
  ROUND(q4.tickets_q4::numeric / NULLIF(q4.agents_q4, 0), 1) AS tickets_per_agent_q4
FROM tenants tn
JOIN (
  SELECT qt.tenant_id,
         SUM(COALESCE(qd.tickets, 0)) AS tickets_q123,
         AVG(COALESCE(qd.agents, 0)) AS avg_agents_q123
  FROM (
    SELECT tenant_id
    FROM tickets
    WHERE created_at >= TIMESTAMP '2025-01-01' AND created_at < TIMESTAMP '2026-01-01'
    GROUP BY tenant_id
    HAVING COUNT(*) >= 20
  ) qt
  CROSS JOIN (SELECT 1 AS q UNION ALL SELECT 2 UNION ALL SELECT 3) quarters
  LEFT JOIN (
    SELECT tenant_id,
           EXTRACT(QUARTER FROM created_at) AS q,
           COUNT(*) AS tickets,
           COUNT(DISTINCT assignee_agent_id) AS agents
    FROM tickets
    WHERE created_at >= TIMESTAMP '2025-01-01' AND created_at < TIMESTAMP '2025-10-01'
    GROUP BY tenant_id, EXTRACT(QUARTER FROM created_at)
  ) qd ON qd.tenant_id = qt.tenant_id AND qd.q = quarters.q
  GROUP BY qt.tenant_id
) q123 ON q123.tenant_id = tn.id
JOIN (
  SELECT tenant_id,
         COUNT(*) AS tickets_q4,
         COUNT(DISTINCT assignee_agent_id) AS agents_q4
  FROM tickets
  WHERE created_at >= TIMESTAMP '2025-10-01' AND created_at < TIMESTAMP '2026-01-01'
  GROUP BY tenant_id
) q4 ON q4.tenant_id = tn.id
ORDER BY tickets_per_agent_q4 DESC, merchant_name ASC
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: merchant_name, tickets_q123, avg_agents_q1_q3, tickets_per_agent_q1_q3, tickets_q4, agents_q4, tickets_per_agent_q4
    • FelixThe query compiles correctly with the expected columns. Let me submit it.
    • Felix calls submit
      SELECT
  tn.name AS merchant_name,
  q123.tickets_q123,
  ROUND(q123.avg_agents_q123, 1) AS avg_agents_q1_q3,
  ROUND((q123.tickets_q123 / 3.0) / NULLIF(q123.avg_agents_q123, 0), 1) AS tickets_per_agent_q1_q3,
  q4.tickets_q4,
  q4.agents_q4,
  ROUND(q4.tickets_q4::numeric / NULLIF(q4.agents_q4, 0), 1) AS tickets_per_agent_q4
FROM tenants tn
JOIN (
  SELECT qt.tenant_id,
         SUM(COALESCE(qd.tickets, 0)) AS tickets_q123,
         AVG(COALESCE(qd.agents, 0)) AS avg_agents_q123
  FROM (
    SELECT tenant_id
    FROM tickets
    WHERE created_at >= TIMESTAMP '2025-01-01' AND created_at < TIMESTAMP '2026-01-01'
    GROUP BY tenant_id
    HAVING COUNT(*) >= 20
  ) qt
  CROSS JOIN (SELECT 1 AS q UNION ALL SELECT 2 UNION ALL SELECT 3) quarters
  LEFT JOIN (
    SELECT tenant_id,
           EXTRACT(QUARTER FROM created_at) AS q,
           COUNT(*) AS tickets,
           COUNT(DISTINCT assignee_agent_id) AS agents
    FROM tickets
    WHERE created_at >= TIMESTAMP '2025-01-01' AND created_at < TIMESTAMP '2025-10-01'
    GROUP BY tenant_id, EXTRACT(QUARTER FROM created_at)
  ) qd ON qd.tenant_id = qt.tenant_id AND qd.q = quarters.q
  GROUP BY qt.tenant_id
) q123 ON q123.tenant_id = tn.id
JOIN (
  SELECT tenant_id,
         COUNT(*) AS tickets_q4,
         COUNT(DISTINCT assignee_agent_id) AS agents_q4
  FROM tickets
  WHERE created_at >= TIMESTAMP '2025-10-01' AND created_at < TIMESTAMP '2026-01-01'
  GROUP BY tenant_id
) q4 ON q4.tenant_id = tn.id
ORDER BY tickets_per_agent_q4 DESC, merchant_name ASC
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • FelixSubmitted. The query builds one row per tenant with ≥20 tickets created in 2025, computing Q1-Q3 combined tickets and average distinct agents per quarter (zero-filled for any ticketless quarter so the average is always over 3 quarters), the derived tickets-per-agent-per-quarter rate, and the Q4 equivalents, ordered by Q4 crunch severity descending.

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

    Felix's transcript (269 KB) · Mateo Ruiz's transcript (892 KB) · Daniel Achterberg's transcript (264 KB) · the night's plan (6 KB) · the night's reports (8 KB) · the week's memo (3 KB)