DB Olympics

Queries · q244 · VP Customer Experience · week 2, night 1

Support agent staffing vs ticket volume, by merchant

“Are Cragsley, Marrowby, and Studio Vrij understaffed relative to their ticket volume compared to the rest of 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 SQLInesThinks in window functions: ranks, running totals, LAG/LEAD for comparisons, percent-of-total.

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

    Daniel wants to know, for the 3 ticket-heavy merchants (Cragsley, Marrowby, Studio Vrij), whether slowness is explained by something fixable (staffing, channel mix) vs. harder issues, and whether the urgent-SLA failure (50.6% company-wide per Ben Carter) is concentrated in those 3 or systemic. Other teams' posts give useful context I'll want to connect later (Albion Parcel carrier breakdown hitting delivery regardless of merchant/warehouse; the cart-reminder bug hitting 10 merchants incl. Cragsley in Nov; Cragsley's own product-quality tail) - but those are confounders for Daniel's question #4, which he said can wait. Tonight I'm testing the most direct, fixable-vs-not hypotheses for #1 and #2: (1) are the 3 hotspots simply understaffed relative to ticket volume, (2) do they lean on slower channels, and (3) is the urgent-ticket resolution failure broad across the book or concentrated in those same 3 merchants. All three are merchant-grain reports so I can line them up side by side and see if the 3 hotspots stand out consistently or only on one dimension.

    Support agent staffing vs ticket volume, by merchant
    Question
    Are Cragsley, Marrowby, and Studio Vrij understaffed relative to their ticket volume compared to the rest of the book?
    One row per
    tenant (merchant), among tenants with at least 20 tickets created in 2025
    Columns
    • merchant name
    • tickets created, 2025
    • registered support agents (support_agents rows for tenant)
    • distinct agents who handled a ticket in 2025 (count distinct assignee_agent_id, excluding null)
    • tickets per active agent (tickets ÷ distinct agents who handled a ticket)
    • % tickets unassigned (assignee_agent_id null)

  3. Ines 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 t2025 as (
  select
    tenant_id,
    count(*) as tickets_created,
    count(distinct assignee_agent_id) as distinct_agents_handled,
    count(*) filter (where assignee_agent_id is null) as unassigned_count
  from tickets
  where created_at >= timestamp '2025-01-01' and created_at < timestamp '2026-01-01'
  group by tenant_id
),
agents as (
  select tenant_id, count(*) as registered_agents
  from support_agents
  group by tenant_id
)
select
  ten.name as merchant_name,
  t.tickets_created,
  coalesce(a.registered_agents, 0) as registered_support_agents,
  t.distinct_agents_handled,
  case when t.distinct_agents_handled > 0
       then round(t.tickets_created::decimal / t.distinct_agents_handled, 1)
       else null
  end as tickets_per_active_agent,
  round(100.0 * t.unassigned_count / t.tickets_created, 1) as pct_unassigned
from t2025 t
join tenants ten on ten.id = t.tenant_id
left join agents a on a.tenant_id = t.tenant_id
where t.tickets_created >= 20
order by tickets_per_active_agent desc nulls last, merchant_name asc
limit 50
    Submitted · the SQL of the last dry run, unchanged · Submitted to tonight's batch. Your work on this request is done.

    “Submitted. The report returns one row per merchant (≥20 tickets created in 2025) with merchant name, 2025 ticket count, registered support agents, distinct agents who handled a ticket, tickets per active agent, and % unassigned — ranked by load (tickets per active agent) descending, so Cragsley, Marrowby, and Studio Vrij's staffing ratios can be compared directly against the rest of the book.”

  4. The report comes back overnight

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

    merchant_name                 | tickets_created | registered_support_agents | distinct_agents_handled | tickets_per_active_agent | pct_unassigned
------------------------------+-----------------+---------------------------+-------------------------+--------------------------+---------------
Cragsley Outfitters           |             400 |                         8 |                       8 |                     50.0 |            0.5
Marrowby & Vane               |             231 |                         5 |                       5 |                     46.2 |            2.6
Studio Vrij                   |             166 |                         4 |                       4 |                     41.5 |            0.0
Grind Collective              |             122 |                         3 |                       3 |                     40.7 |            0.0
Brennquell Kaffee             |             137 |                         4 |                       4 |                     34.3 |            0.7
Petalcourt Beauty             |             119 |                         4 |                       4 |                     29.8 |            0.0
Spice Route Box               |              71 |                         3 |                       3 |                     23.7 |            0.0
Honeycomb Larder              |              58 |                         3 |                       3 |                     19.3 |            0.0
Kibblewick                    |              72 |                         4 |                       4 |                     18.0 |            0.0
Fennel & Flint                |              67 |                         4 |                       4 |                     16.8 |            0.0
Sourdough Club                |              47 |                         3 |                       3 |                     15.7 |            0.0
Ballycorran Industrial Supply |              44 |                         3 |                       3 |                     14.7 |            0.0
Ampfeld Elektronik            |              56 |                         5 |                       5 |                     11.2 |            1.8
Cinder Peak Sports Co.        |              43 |                         4 |                       4 |                     10.8 |            0.0
Wiesgrund Wohnen              |              32 |                         3 |                       3 |                     10.7 |            0.0
Linden & Loom                 |              29 |                         3 |                       3 |                      9.7 |            0.0
Oakhurst Tailoring            |              27 |                         3 |                       3 |                      9.0 |            3.7
Rue Lafayette Studio          |              27 |                         3 |                       3 |                      9.0 |            0.0
Coastline Interiors           |              21 |                         3 |                       3 |                      7.0 |            0.0
Thistlecombe                  |              21 |                         3 |                       3 |                      7.0 |            0.0
Gratlinie Outdoor             |              27 |                         4 |                       4 |                      6.8 |            0.0
Kayak Corner                  |              20 |                         3 |                       3 |                      6.7 |            0.0
(22 rows)

  5. What came of it

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

    Later queries that build on this report:

  6. It becomes benchmark query q244

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

    • spellingcast
    • spellingfetch first
    • determinismtie-breaker: the group key of t2025 (tenants primary key)
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision; bare NUMERIC cast that only served the rounding removed
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
    • made explicita selected t.col gets AS col, its standard name made explicit (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 t2025 as (  select    tenant_id,    count(*) as tickets_created,    count(distinct assignee_agent_id) as distinct_agents_handled,    count(*) filter (where assignee_agent_id is null) as unassigned_count  from tickets  where created_at >= timestamp '2025-01-01' and created_at < timestamp '2026-01-01'  group by tenant_id),agents as (  select tenant_id, count(*) as registered_agents  from support_agents  group by tenant_id)select  ten.name as merchant_name,  t.tickets_created,  t.tickets_created AS tickets_created,  coalesce(a.registered_agents, 0) as registered_support_agents,  t.distinct_agents_handled,  t.distinct_agents_handled AS distinct_agents_handled,  case when t.distinct_agents_handled > 0       then round(t.tickets_created::decimal / t.distinct_agents_handled, 1)       then CAST(t.tickets_created AS decimal(18,6)) / t.distinct_agents_handled       else null  end as tickets_per_active_agent,  round(100.0 * t.unassigned_count / t.tickets_created, 1) as pct_unassigned  100.0 * t.unassigned_count / t.tickets_created as pct_unassignedfrom t2025 tjoin tenants ten on ten.id = t.tenant_idleft join agents a on a.tenant_id = t.tenant_idwhere t.tickets_created >= 20order by tickets_per_active_agent desc nulls last, merchant_name asclimit 50order by tickets_per_active_agent desc nulls last, merchant_name asc NULLS LAST, t.tenant_id asc NULLS LAST{{fetch_first(50)}}

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

    with t2025 as (
  select
    tenant_id,
    count(*) as tickets_created,
    count(distinct assignee_agent_id) as distinct_agents_handled,
    count(*) filter (where assignee_agent_id is null) as unassigned_count
  from tickets
  where created_at >= timestamp '2025-01-01' and created_at < timestamp '2026-01-01'
  group by tenant_id
),
agents as (
  select tenant_id, count(*) as registered_agents
  from support_agents
  group by tenant_id
)
select
  ten.name as merchant_name,
  t.tickets_created AS tickets_created,
  coalesce(a.registered_agents, 0) as registered_support_agents,
  t.distinct_agents_handled AS distinct_agents_handled,
  case when t.distinct_agents_handled > 0
       then CAST(t.tickets_created AS decimal(18,6)) / t.distinct_agents_handled
       else null
  end as tickets_per_active_agent,
  100.0 * t.unassigned_count / t.tickets_created as pct_unassigned
from t2025 t
join tenants ten on ten.id = t.tenant_id
left join agents a on a.tenant_id = t.tenant_id
where t.tickets_created >= 20
order by tickets_per_active_agent desc nulls last, merchant_name asc NULLS LAST, t.tenant_id asc NULLS LAST
FETCH FIRST 50 ROWS ONLY
    1 ms10 ms100 ms1 sHyper9.5 msDuckDB13.9 msGizmoSQL23.3 msclickhouse_brew496 msFirebolt33.5 msDataFusion51.7 msClickHouse916 ms

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

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

Title: Support agent staffing vs ticket volume, by merchant
Question: Are Cragsley, Marrowby, and Studio Vrij understaffed relative to their ticket volume compared to the rest of the book?
Grain: one row per tenant (merchant), among tenants with at least 20 tickets created in 2025
Columns:
- merchant name
- tickets created, 2025
- registered support agents (support_agents rows for tenant)
- distinct agents who handled a ticket in 2025 (count distinct assignee_agent_id, excluding null)
- tickets per active agent (tickets ÷ distinct agents who handled a ticket)
- % tickets unassigned (assignee_agent_id null)
Details: Source: tickets joined to tenants (name) and support_agents (tenant's registered agent headcount, regardless of whether they handled a ticket in 2025). Window: tickets.created_at 2025-01-01 through 2025-12-31. 'Registered support agents' = count of support_agents rows for that tenant_id (current roster, not time-sliced). 'Distinct agents who handled a ticket' = count distinct tickets.assignee_agent_id for that tenant in 2025, excluding nulls. 'Tickets per active agent' = tickets created 2025 ÷ distinct agents who handled a ticket (NULL if zero agents handled any ticket). '% unassigned' = tickets with assignee_agent_id null ÷ tickets created 2025, percentage rounded to 1 decimal. Only include tenants with >=20 tickets created in 2025. Order by tickets per active agent descending (highest ratio = most loaded), tie-break merchant name ascending. Cap at 50 rows per house standard; note total qualifying tenant count.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Ines calls dry_run
      with t2025 as (
  select
    tenant_id,
    count(*) as tickets_created,
    count(distinct assignee_agent_id) as distinct_agents_handled,
    count(*) filter (where assignee_agent_id is null) as unassigned_count
  from tickets
  where created_at >= timestamp '2025-01-01' and created_at < timestamp '2026-01-01'
  group by tenant_id
),
agents as (
  select tenant_id, count(*) as registered_agents
  from support_agents
  group by tenant_id
)
select
  ten.name as merchant_name,
  t.tickets_created,
  coalesce(a.registered_agents, 0) as registered_support_agents,
  t.distinct_agents_handled,
  case when t.distinct_agents_handled > 0
       then round(t.tickets_created::decimal / t.distinct_agents_handled, 1)
       else null
  end as tickets_per_active_agent,
  round(100.0 * t.unassigned_count / t.tickets_created, 1) as pct_unassigned
from t2025 t
join tenants ten on ten.id = t.tenant_id
left join agents a on a.tenant_id = t.tenant_id
where t.tickets_created >= 20
order by tickets_per_active_agent desc nulls last, merchant_name asc
limit 50
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: merchant_name, tickets_created, registered_support_agents, distinct_agents_handled, tickets_per_active_agent, pct_unassigned
    • Ines calls submit
      with t2025 as (
  select
    tenant_id,
    count(*) as tickets_created,
    count(distinct assignee_agent_id) as distinct_agents_handled,
    count(*) filter (where assignee_agent_id is null) as unassigned_count
  from tickets
  where created_at >= timestamp '2025-01-01' and created_at < timestamp '2026-01-01'
  group by tenant_id
),
agents as (
  select tenant_id, count(*) as registered_agents
  from support_agents
  group by tenant_id
)
select
  ten.name as merchant_name,
  t.tickets_created,
  coalesce(a.registered_agents, 0) as registered_support_agents,
  t.distinct_agents_handled,
  case when t.distinct_agents_handled > 0
       then round(t.tickets_created::decimal / t.distinct_agents_handled, 1)
       else null
  end as tickets_per_active_agent,
  round(100.0 * t.unassigned_count / t.tickets_created, 1) as pct_unassigned
from t2025 t
join tenants ten on ten.id = t.tenant_id
left join agents a on a.tenant_id = t.tenant_id
where t.tickets_created >= 20
order by tickets_per_active_agent desc nulls last, merchant_name asc
limit 50
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • InesSubmitted. The report returns one row per merchant (≥20 tickets created in 2025) with merchant name, 2025 ticket count, registered support agents, distinct agents who handled a ticket, tickets per active agent, and % unassigned — ranked by load (tickets per active agent) descending, so Cragsley, Marrowby, and Studio Vrij's staffing ratios can be compared directly against the rest of the book.

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

    Ines's transcript (262 KB) · Mateo Ruiz's transcript (892 KB) · Daniel Achterberg's transcript (264 KB) · the night's plan (5 KB) · the night's reports (9 KB) · the week's memo (3 KB)