DB Olympics

Queries · q407 · Support Operations Manager · week 2, night 4

Albion Parcel monthly on-time performance at Marrowby & Vane

“Did Albion Parcel's on-time performance at Marrowby & Vane specifically collapse in November-December 2025, and has it recovered by year end?”

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 2

    Subject: Re: Helpdesk Operating Review — great start, here's where to push next

    Ben,

    This is exactly the kind of baseline I wanted — and the speed-to-CSAT finding alone is worth putting in front of Daniel as-is. A few things stood out that I want sharpened before we present or act on them.

    What surprised me / needs explaining:

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

    Questions for next week, in priority order:

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

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

    Mia

  3. Ben Carter plans the night

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

    The picture is now very clear and it changes the recommendation completely. Report 1 shows Marrowby & Vane gets 51.6% of shipments from Albion Parcel — the exact carrier the COO team flagged as broken from November onward — while Report 2 shows Marrowby & Vane's delivery-tagged tickets were near-normal all year (240-1,196 min/ticket, Feb-Oct) until November (9,533 min, 61.8% breach) and December (19,274 min, 100% breach). That's a live, ongoing carrier failure, not a staffing gap. Studio Vrij tells a different story: its delivery-ticket resolution time exploded in May-July 2025 (8,528 / 29,549 / 8,763 minutes) and then fully recovered by September-December (83-680 minutes, 0% breach) — this matches almost exactly what the VP Customer Experience team posted last week about a merchant's 'local May 2025 delivery breakdown... not a platform-wide issue.' That was very likely Studio Vrij, and it's already over. Neither merchant looks like a 'hire more agents' case. Tonight I want to (1) pin the Albion Parcel collapse specifically to Marrowby & Vane's own shipments month-by-month to nail the mechanism and check if it's still ongoing, (2) find which of Studio Vrij's carriers caused the May-July spike and confirm it's genuinely resolved, and (3) run the clean controlled comparison Mia actually needs for a decision: strip the known incident months out of each tenant's numbers and see if what's left still looks worse than Cragsley's baseline, or whether the whole gap was the carrier event. On the knowledge-base question: the event_name inventory (Report 3) shows only 8 tracked events platform-wide — none related to viewing a help/KB article — so combined with last week's zero-match page_views test, I'm closing that question as 'not measurable with current instrumentation' rather than spending another slot guessing.

    Albion Parcel monthly on-time performance at Marrowby & Vane
    Question
    Did Albion Parcel's on-time performance at Marrowby & Vane specifically collapse in November-December 2025, and has it recovered by year end?
    One row per
    month in 2025, for Marrowby & Vane shipments carried by Albion Parcel only
    Columns
    • month_label (YYYY-MM)
    • shipment_count (shipped_at in that month)
    • avg_delivery_days
    • pct_delivered_within_3_days

  4. Grace writes the SQL

    The engineer · 2 dry runs on the empty schema, then the submission

    Dry run 1 · STATUS: REJECTED
    WITH base AS (
  SELECT
    s.id,
    s.shipped_at,
    s.delivered_at,
    to_char(s.shipped_at, 'YYYY-MM') AS month_label
  FROM shipments s
  JOIN tenants t ON t.id = s.tenant_id
  WHERE t.name = 'Marrowby & Vane'
    AND s.carrier = 'Albion Parcel'
    AND s.shipped_at >= DATE '2025-01-01'
    AND s.shipped_at < DATE '2026-01-01'
),
monthly_shipment_counts AS (
  SELECT
    month_label,
    COUNT(*) AS shipment_count
  FROM base
  GROUP BY month_label
),
delivered_base AS (
  SELECT
    month_label,
    EXTRACT(EPOCH FROM (delivered_at - shipped_at)) / 86400.0 AS delivery_days
  FROM base
  WHERE shipped_at IS NOT NULL
    AND delivered_at IS NOT NULL
),
monthly_delivery_stats AS (
  SELECT
    month_label,
    ROUND(AVG(delivery_days)::numeric, 1) AS avg_delivery_days,
    ROUND(100.0 * SUM(CASE WHEN delivery_days <= 3 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_delivered_within_3_days
  FROM delivered_base
  GROUP BY month_label
)
SELECT
  c.month_label,
  c.shipment_count,
  d.avg_delivery_days,
  d.pct_delivered_within_3_days
FROM monthly_shipment_counts c
LEFT JOIN monthly_delivery_stats d ON c.month_label = d.month_label
ORDER BY c.month_label ASC
    Dry run 2 · 1 change from the dry run before · STATUS: COMPILES (dry run on the empty schema, no data read)
    WITH base AS (  SELECT    s.id,    s.shipped_at,    s.delivered_at,    to_char(s.shipped_at, 'YYYY-MM') AS month_label    strftime(s.shipped_at, '%Y-%m') AS month_label  FROM shipments s  JOIN tenants t ON t.id = s.tenant_id  WHERE t.name = 'Marrowby & Vane'    AND s.carrier = 'Albion Parcel'    AND s.shipped_at >= DATE '2025-01-01'    AND s.shipped_at < DATE '2026-01-01'),monthly_shipment_counts AS (  SELECT    month_label,    COUNT(*) AS shipment_count  FROM base  GROUP BY month_label),delivered_base AS (  SELECT    month_label,    EXTRACT(EPOCH FROM (delivered_at - shipped_at)) / 86400.0 AS delivery_days  FROM base  WHERE shipped_at IS NOT NULL    AND delivered_at IS NOT NULL),monthly_delivery_stats AS (  SELECT    month_label,    ROUND(AVG(delivery_days)::numeric, 1) AS avg_delivery_days,    ROUND(100.0 * SUM(CASE WHEN delivery_days <= 3 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_delivered_within_3_days  FROM delivered_base  GROUP BY month_label)SELECT  c.month_label,  c.shipment_count,  d.avg_delivery_days,  d.pct_delivered_within_3_daysFROM monthly_shipment_counts cLEFT JOIN monthly_delivery_stats d ON c.month_label = d.month_labelORDER BY c.month_label ASC

    “Query compiles and returns the expected columns. Submitting.”

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

    “Submitted. The report gives one row per month in 2025 (Jan–Dec) for Marrowby & Vane shipments carried by Albion Parcel, with shipment_count, avg_delivery_days, and pct_delivered_within_3_days — this should let Ben see directly whether Nov–Dec 2025 dipped and whether December recovered.”

  5. The report comes back overnight

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

    month_label | shipment_count | avg_delivery_days | pct_delivered_within_3_days
------------+----------------+-------------------+----------------------------
2025-01     |            121 |               2.0 |                        84.3
2025-02     |             50 |               2.0 |                        94.0
2025-03     |             77 |               2.2 |                        79.2
2025-04     |             70 |               2.3 |                        75.7
2025-05     |             76 |               2.4 |                        75.0
2025-06     |             73 |               2.2 |                        75.3
2025-07     |             86 |               2.2 |                        86.0
2025-08     |             60 |               2.2 |                        88.3
2025-09     |             85 |               2.1 |                        80.0
2025-10     |             80 |               2.4 |                        77.5
2025-11     |             85 |               5.2 |                        36.5
(11 rows)

  6. What came of it

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

    It builds on:

  7. It becomes benchmark query q407

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

    • spellingcast
    • placeholderyear month
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision; bare NUMERIC cast that only served the rounding removed
    • numericsdocs/NUMERICS.md: EXTRACT(EPOCH FROM (b - a)) cast to NUMERIC(18,6)
    • made explicita selected t.col gets AS col, its standard name made explicit (7×)
    • placeholderseconds between
    • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results
    The benchmark text, against the submitted SQL
    WITH base AS (  SELECT    s.id,    s.shipped_at,    s.delivered_at,    strftime(s.shipped_at, '%Y-%m') AS month_label    s.id AS id,    s.shipped_at AS shipped_at,    s.delivered_at AS delivered_at,    {{year_month(s.shipped_at)}} AS month_label  FROM shipments s  JOIN tenants t ON t.id = s.tenant_id  WHERE t.name = 'Marrowby & Vane'    AND s.carrier = 'Albion Parcel'    AND s.shipped_at >= DATE '2025-01-01'    AND s.shipped_at < DATE '2026-01-01'),monthly_shipment_counts AS (  SELECT    month_label,    COUNT(*) AS shipment_count  FROM base  GROUP BY month_label),delivered_base AS (  SELECT    month_label,    EXTRACT(EPOCH FROM (delivered_at - shipped_at)) / 86400.0 AS delivery_days    {{seconds_between(shipped_at, delivered_at)}} / 86400.0 AS delivery_days  FROM base  WHERE shipped_at IS NOT NULL    AND delivered_at IS NOT NULL),monthly_delivery_stats AS (  SELECT    month_label,    ROUND(AVG(delivery_days)::numeric, 1) AS avg_delivery_days,    ROUND(100.0 * SUM(CASE WHEN delivery_days <= 3 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_delivered_within_3_days    AVG(delivery_days) AS avg_delivery_days,    100.0 * SUM(CASE WHEN delivery_days <= 3 THEN 1 ELSE 0 END) / COUNT(*) AS pct_delivered_within_3_days  FROM delivered_base  GROUP BY month_label)SELECT  c.month_label,  c.shipment_count,  d.avg_delivery_days,  d.pct_delivered_within_3_days  c.month_label AS month_label,  c.shipment_count AS shipment_count,  d.avg_delivery_days AS avg_delivery_days,  d.pct_delivered_within_3_days AS pct_delivered_within_3_daysFROM monthly_shipment_counts cLEFT JOIN monthly_delivery_stats d ON c.month_label = d.month_labelORDER BY c.month_label ASCORDER BY c.month_label ASC NULLS LAST

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

    WITH base AS (
  SELECT
    s.id AS id,
    s.shipped_at AS shipped_at,
    s.delivered_at AS delivered_at,
    strftime(s.shipped_at, '%Y-%m') AS month_label
  FROM shipments s
  JOIN tenants t ON t.id = s.tenant_id
  WHERE t.name = 'Marrowby & Vane'
    AND s.carrier = 'Albion Parcel'
    AND s.shipped_at >= DATE '2025-01-01'
    AND s.shipped_at < DATE '2026-01-01'
),
monthly_shipment_counts AS (
  SELECT
    month_label,
    COUNT(*) AS shipment_count
  FROM base
  GROUP BY month_label
),
delivered_base AS (
  SELECT
    month_label,
    CAST(EXTRACT(EPOCH FROM (delivered_at - shipped_at)) AS NUMERIC(18,6)) / 86400.0 AS delivery_days
  FROM base
  WHERE shipped_at IS NOT NULL
    AND delivered_at IS NOT NULL
),
monthly_delivery_stats AS (
  SELECT
    month_label,
    AVG(delivery_days) AS avg_delivery_days,
    100.0 * SUM(CASE WHEN delivery_days <= 3 THEN 1 ELSE 0 END) / COUNT(*) AS pct_delivered_within_3_days
  FROM delivered_base
  GROUP BY month_label
)
SELECT
  c.month_label AS month_label,
  c.shipment_count AS shipment_count,
  d.avg_delivery_days AS avg_delivery_days,
  d.pct_delivered_within_3_days AS pct_delivered_within_3_days
FROM monthly_shipment_counts c
LEFT JOIN monthly_delivery_stats d ON c.month_label = d.month_label
ORDER BY c.month_label ASC NULLS LAST
    10 ms100 ms1 sHyper10.6 msDuckDB10.3 msGizmoSQL16.4 msclickhouse_brew102 msFirebolt129 msDataFusionwrong resultClickHouse115 ms
    • DataFusionrow count 11 vs 1

    Scale factor 1000, c8a.24xlarge, after the last load. q407 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, 9 turns
    • The request, as Grace received it
      Report request from Ben Carter (analyst for the Support Operations Manager):

Title: Albion Parcel monthly on-time performance at Marrowby & Vane
Question: Did Albion Parcel's on-time performance at Marrowby & Vane specifically collapse in November-December 2025, and has it recovered by year end?
Grain: one row per month in 2025, for Marrowby & Vane shipments carried by Albion Parcel only
Columns:
- month_label (YYYY-MM)
- shipment_count (shipped_at in that month)
- avg_delivery_days
- pct_delivered_within_3_days
Details: Window: shipments.shipped_at between 2025-01-01 and 2025-12-31, restricted to tenant = Marrowby & Vane and carrier = 'Albion Parcel'. Source table: shipments. Compute avg_delivery_days and pct_delivered_within_3_days only over shipments with both shipped_at and delivered_at populated. Order by month_label ascending. Round avg_delivery_days to 1 decimal, pct 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 base AS (
  SELECT
    s.id,
    s.shipped_at,
    s.delivered_at,
    to_char(s.shipped_at, 'YYYY-MM') AS month_label
  FROM shipments s
  JOIN tenants t ON t.id = s.tenant_id
  WHERE t.name = 'Marrowby & Vane'
    AND s.carrier = 'Albion Parcel'
    AND s.shipped_at >= DATE '2025-01-01'
    AND s.shipped_at < DATE '2026-01-01'
),
monthly_shipment_counts AS (
  SELECT
    month_label,
    COUNT(*) AS shipment_count
  FROM base
  GROUP BY month_label
),
delivered_base AS (
  SELECT
    month_label,
    EXTRACT(EPOCH FROM (delivered_at - shipped_at)) / 86400.0 AS delivery_days
  FROM base
  WHERE shipped_at IS NOT NULL
    AND delivered_at IS NOT NULL
),
monthly_delivery_stats AS (
  SELECT
    month_label,
    ROUND(AVG(delivery_days)::numeric, 1) AS avg_delivery_days,
    ROUND(100.0 * SUM(CASE WHEN delivery_days <= 3 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_delivered_within_3_days
  FROM delivered_base
  GROUP BY month_label
)
SELECT
  c.month_label,
  c.shipment_count,
  d.avg_delivery_days,
  d.pct_delivered_within_3_days
FROM monthly_shipment_counts c
LEFT JOIN monthly_delivery_stats d ON c.month_label = d.month_label
ORDER BY c.month_label ASC
    • The warehouse answers
      STATUS: REJECTED
  - does not compile: Catalog Error: Scalar Function with name to_char does not exist!
    • Grace calls dry_run
      WITH base AS (
  SELECT
    s.id,
    s.shipped_at,
    s.delivered_at,
    strftime(s.shipped_at, '%Y-%m') AS month_label
  FROM shipments s
  JOIN tenants t ON t.id = s.tenant_id
  WHERE t.name = 'Marrowby & Vane'
    AND s.carrier = 'Albion Parcel'
    AND s.shipped_at >= DATE '2025-01-01'
    AND s.shipped_at < DATE '2026-01-01'
),
monthly_shipment_counts AS (
  SELECT
    month_label,
    COUNT(*) AS shipment_count
  FROM base
  GROUP BY month_label
),
delivered_base AS (
  SELECT
    month_label,
    EXTRACT(EPOCH FROM (delivered_at - shipped_at)) / 86400.0 AS delivery_days
  FROM base
  WHERE shipped_at IS NOT NULL
    AND delivered_at IS NOT NULL
),
monthly_delivery_stats AS (
  SELECT
    month_label,
    ROUND(AVG(delivery_days)::numeric, 1) AS avg_delivery_days,
    ROUND(100.0 * SUM(CASE WHEN delivery_days <= 3 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_delivered_within_3_days
  FROM delivered_base
  GROUP BY month_label
)
SELECT
  c.month_label,
  c.shipment_count,
  d.avg_delivery_days,
  d.pct_delivered_within_3_days
FROM monthly_shipment_counts c
LEFT JOIN monthly_delivery_stats d ON c.month_label = d.month_label
ORDER BY c.month_label ASC
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: month_label, shipment_count, avg_delivery_days, pct_delivered_within_3_days
    • GraceQuery compiles and returns the expected columns. Submitting.
    • Grace calls submit
      WITH base AS (
  SELECT
    s.id,
    s.shipped_at,
    s.delivered_at,
    strftime(s.shipped_at, '%Y-%m') AS month_label
  FROM shipments s
  JOIN tenants t ON t.id = s.tenant_id
  WHERE t.name = 'Marrowby & Vane'
    AND s.carrier = 'Albion Parcel'
    AND s.shipped_at >= DATE '2025-01-01'
    AND s.shipped_at < DATE '2026-01-01'
),
monthly_shipment_counts AS (
  SELECT
    month_label,
    COUNT(*) AS shipment_count
  FROM base
  GROUP BY month_label
),
delivered_base AS (
  SELECT
    month_label,
    EXTRACT(EPOCH FROM (delivered_at - shipped_at)) / 86400.0 AS delivery_days
  FROM base
  WHERE shipped_at IS NOT NULL
    AND delivered_at IS NOT NULL
),
monthly_delivery_stats AS (
  SELECT
    month_label,
    ROUND(AVG(delivery_days)::numeric, 1) AS avg_delivery_days,
    ROUND(100.0 * SUM(CASE WHEN delivery_days <= 3 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_delivered_within_3_days
  FROM delivered_base
  GROUP BY month_label
)
SELECT
  c.month_label,
  c.shipment_count,
  d.avg_delivery_days,
  d.pct_delivered_within_3_days
FROM monthly_shipment_counts c
LEFT JOIN monthly_delivery_stats d ON c.month_label = d.month_label
ORDER BY c.month_label ASC
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • GraceSubmitted. The report gives one row per month in 2025 (Jan–Dec) for Marrowby & Vane shipments carried by Albion Parcel, with shipment_count, avg_delivery_days, and pct_delivered_within_3_days — this should let Ben see directly whether Nov–Dec 2025 dipped and whether December recovered.

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