DB Olympics

Queries · q030 · Chief Operating Officer · week 1, night 2

Order fulfillment stage breakdown, monthly 2025

“When total delivery time lengthens (e.g. during peak), is the slowdown in warehouse processing (pick/pack/ship) or in carrier transit?”

Manager · asked the questionPriya RamanChief Operating Officer. Came from a third-party logistics provider. Wants to know how reliable the delivery promise is that merchants make on Tidewick.
Analyst · planned the reportLena HartmannSkeptical: checks definitions and sanity totals before trusting a trend.
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. Priya Raman writes to Lena Hartmann

    The manager, at the start of week 1

    Subject: Getting my bearings on fulfillment — questions for this week

    Hi Lena,

    Thanks for taking these on. I'm new here and coming from the logistics side, so my first priority is understanding whether the delivery promise we make to shoppers actually holds up — that's the lens for most of what's below. Given the slot budget, here's what matters most, roughly in priority order:

    1. How long does it actually take us to get an order into a customer's hands, and how much does that vary? I want to understand the real end-to-end time from order placed to delivered, and whether that's gotten better, worse, or stayed flat over the past year or so. I'm especially interested in how consistent we are — are there merchants or categories where delivery is reliably fast, and others where it's a coin toss?
    2. How did we do during the last peak/busy season? I'd like to know whether fulfillment speed and reliability held up when volume spiked, or whether we saw things slip — delays, backlogs, stockouts — and how quickly we recovered afterward. This matters because peak is the real stress test of the whole operation.
    3. Where are the weak links — specific carriers, warehouses, or regions? I want to know if there are particular carriers or fulfillment sites that are consistently underperforming on delivery time or dependability, so we know where to focus attention or have a harder conversation with a partner.
    4. How well do we manage stock so we're not promising things we can't deliver? I'm curious about how often we run into stockouts or have to delay orders for lack of inventory, and whether our replenishment from suppliers (purchase orders) is keeping pace — are we chronically waiting too long on restocks?
    5. (If there's room) What does the returns process look like from a logistics standpoint? Specifically, how long it takes for returned goods to get back into our system and become sellable again — that's a cost and customer-experience lever I want to keep an eye on.

    What I'll use this for: I'm forming my first impressions of operational health in this area and want to walk into conversations with merchants and carrier partners armed with facts, not assumptions. I'd rather have a smaller number of really clear answers than a lot of surface-level ones — so if you need to cut scope to fit your slots this week, prioritize #1 and #2, and we can pick up the rest next week.

    Thanks — looking forward to Friday's memo.

    Priya

  2. Lena Hartmann plans the night

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

    Night 1 take-aways: (a) delivery times are broadly stable most of the year (avg ~3.6-4.1 days, median 3-4, ~83-90% within 5 days), but both Nov 2025 and Dec 2024 show clear peak-season degradation (Nov 2025 worst: avg 4.7, only 78.7% within 5 days) -- confirms Priya's stress-test hypothesis; (b) the Oct-Dec 2025 weekly report pinpoints the worst weeks as 2025-11-24 and 2025-12-01 (Black Friday/Cyber Monday through early Dec), where pct-within-5-days fell to 73.5% and 56.1% and backlog (undelivered) roughly tripled, before recovering to 80-93% by mid-to-late December -- so the operation did wobble under peak load but recovered within 1-2 weeks; (c) the merchant-level report shows huge spread in reliability (pct-within-5-days from ~0.92 down to ~0.54) that does NOT line up cleanly with vertical (Food & Coffee has both the best and some of the worst performers), which points to carrier or warehouse-specific causes rather than product-category causes -- this is the natural next question (Priya's priority #3) and also helps explain the peak-season wobble. Tonight I'm moving into carriers and warehouses, and decomposing total delivery time into warehouse processing time vs. carrier transit time so we know which stage is actually the bottleneck, especially during peak.

    Order fulfillment stage breakdown, monthly 2025
    Question
    When total delivery time lengthens (e.g. during peak), is the slowdown in warehouse processing (pick/pack/ship) or in carrier transit?
    One row per
    calendar month of order placement, 2025 only
    Columns
    • month (YYYY-MM)
    • orders_qualifying (count used for this report)
    • avg_processing_days (placed_at to fulfillment.shipped_at)
    • avg_transit_days (fulfillment.shipped_at to fulfillment.delivered_at)
    • avg_total_days (placed_at to fulfillment.delivered_at)
    • pct_within_5_days_total

  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
  o.month_label AS month,
  COUNT(*) AS orders_qualifying,
  ROUND(AVG(f.shipped_at::date - o.placed_at::date), 2) AS avg_processing_days,
  ROUND(AVG(f.delivered_at::date - f.shipped_at::date), 2) AS avg_transit_days,
  ROUND(AVG(f.delivered_at::date - o.placed_at::date), 2) AS avg_total_days,
  ROUND(100.0 * SUM(CASE WHEN (f.delivered_at::date - o.placed_at::date) <= 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_5_days_total
FROM (
  SELECT orders.id, orders.placed_at, cd.month_label
  FROM orders
  JOIN calendar_days cd ON cd.calendar_date = orders.placed_at::date
  WHERE orders.status = 'fulfilled'
    AND orders.placed_at >= TIMESTAMP '2025-01-01'
    AND orders.placed_at < TIMESTAMP '2026-01-01'
) o
JOIN fulfillments f ON f.order_id = o.id
WHERE f.shipped_at IS NOT NULL
  AND f.delivered_at IS NOT NULL
  AND (SELECT COUNT(*) FROM fulfillments f2 WHERE f2.order_id = o.id) = 1
GROUP BY o.month_label
ORDER BY o.month_label

    “Query compiles and returns exactly 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. The report groups fulfilled orders placed in 2025 by placement month, restricted to the single-fulfillment subset with both ship and delivery timestamps, and computes average processing days (placed→shipped), transit days (shipped→delivered), total days (placed→delivered), and the share delivered within 5 days — exactly the 6 requested columns, 12 rows expected (one per month).”

  4. The report comes back overnight

    As Lena Hartmann saw it, from the small dataset the agents worked on

    month   | orders_qualifying | avg_processing_days | avg_transit_days | avg_total_days | pct_within_5_days_total
--------+-------------------+---------------------+------------------+----------------+------------------------
2025-01 |              1717 |                1.08 |             2.51 |           3.59 |                    90.3
2025-02 |              1329 |                 1.1 |             2.58 |           3.68 |                    89.4
2025-03 |              1545 |                1.13 |             2.69 |           3.82 |                    87.1
2025-04 |              1477 |                1.14 |             2.56 |            3.7 |                    88.8
2025-05 |              1574 |                1.51 |             2.76 |           4.27 |                    80.6
2025-06 |              1419 |                1.21 |             2.63 |           3.84 |                    86.3
2025-07 |              1565 |                1.14 |             2.79 |           3.93 |                    85.2
2025-08 |              1457 |                1.22 |             2.64 |           3.86 |                    87.2
2025-09 |              1611 |                1.18 |             2.87 |           4.05 |                    83.2
2025-10 |              1767 |                1.14 |             2.94 |           4.08 |                    83.1
2025-11 |              2419 |                 1.2 |             3.46 |           4.66 |                    78.7
2025-12 |              2653 |                1.16 |             3.07 |           4.24 |                    81.1
(12 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 q030

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

    • spellingcast (9×)
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision; 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 (3×)
    • placeholderdate diff days (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
    The benchmark text, against the submitted SQL
    SELECT  o.month_label AS month,  COUNT(*) AS orders_qualifying,  ROUND(AVG(f.shipped_at::date - o.placed_at::date), 2) AS avg_processing_days,  ROUND(AVG(f.delivered_at::date - f.shipped_at::date), 2) AS avg_transit_days,  ROUND(AVG(f.delivered_at::date - o.placed_at::date), 2) AS avg_total_days,  ROUND(100.0 * SUM(CASE WHEN (f.delivered_at::date - o.placed_at::date) <= 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_5_days_total  AVG(CAST({{date_diff_days(o.placed_at, f.shipped_at)}} AS NUMERIC(18,6))) AS avg_processing_days,  AVG(CAST({{date_diff_days(f.shipped_at, f.delivered_at)}} AS NUMERIC(18,6))) AS avg_transit_days,  AVG(CAST({{date_diff_days(o.placed_at, f.delivered_at)}} AS NUMERIC(18,6))) AS avg_total_days,  100.0 * SUM(CASE WHEN ({{date_diff_days(o.placed_at, f.delivered_at)}}) <= 5 THEN 1 ELSE 0 END) / COUNT(*) AS pct_within_5_days_totalFROM (  SELECT orders.id, orders.placed_at, cd.month_label  SELECT orders.id AS id, orders.placed_at AS placed_at, cd.month_label AS month_label  FROM orders  JOIN calendar_days cd ON cd.calendar_date = orders.placed_at::date  JOIN calendar_days cd ON cd.calendar_date = CAST(orders.placed_at AS date)  WHERE orders.status = 'fulfilled'    AND orders.placed_at >= TIMESTAMP '2025-01-01'    AND orders.placed_at < TIMESTAMP '2026-01-01') oJOIN fulfillments f ON f.order_id = o.idWHERE f.shipped_at IS NOT NULL  AND f.delivered_at IS NOT NULL  AND (SELECT COUNT(*) FROM fulfillments f2 WHERE f2.order_id = o.id) = 1GROUP BY o.month_labelORDER BY o.month_labelORDER BY o.month_label NULLS LAST

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

    SELECT
  o.month_label AS month,
  COUNT(*) AS orders_qualifying,
  AVG(CAST(DATE_DIFF('day', o.placed_at, f.shipped_at) AS NUMERIC(18,6))) AS avg_processing_days,
  AVG(CAST(DATE_DIFF('day', f.shipped_at, f.delivered_at) AS NUMERIC(18,6))) AS avg_transit_days,
  AVG(CAST(DATE_DIFF('day', o.placed_at, f.delivered_at) AS NUMERIC(18,6))) AS avg_total_days,
  100.0 * SUM(CASE WHEN (DATE_DIFF('day', o.placed_at, f.delivered_at)) <= 5 THEN 1 ELSE 0 END) / COUNT(*) AS pct_within_5_days_total
FROM (
  SELECT orders.id AS id, orders.placed_at AS placed_at, cd.month_label AS month_label
  FROM orders
  JOIN calendar_days cd ON cd.calendar_date = CAST(orders.placed_at AS date)
  WHERE orders.status = 'fulfilled'
    AND orders.placed_at >= TIMESTAMP '2025-01-01'
    AND orders.placed_at < TIMESTAMP '2026-01-01'
) o
JOIN fulfillments f ON f.order_id = o.id
WHERE f.shipped_at IS NOT NULL
  AND f.delivered_at IS NOT NULL
  AND (SELECT COUNT(*) FROM fulfillments f2 WHERE f2.order_id = o.id) = 1
GROUP BY o.month_label
ORDER BY o.month_label NULLS LAST
    100 ms1 s10 s100 sHyper10.2 sDuckDB575 msGizmoSQL456 msclickhouse_brew598 msFirebolt637 msDataFusion619 msClickHouse867 ms

    Scale factor 1000, c8a.24xlarge, after the last load. q030 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 Lena Hartmann (analyst for the Chief Operating Officer):

Title: Order fulfillment stage breakdown, monthly 2025
Question: When total delivery time lengthens (e.g. during peak), is the slowdown in warehouse processing (pick/pack/ship) or in carrier transit?
Grain: one row per calendar month of order placement, 2025 only
Columns:
- month (YYYY-MM)
- orders_qualifying (count used for this report)
- avg_processing_days (placed_at to fulfillment.shipped_at)
- avg_transit_days (fulfillment.shipped_at to fulfillment.delivered_at)
- avg_total_days (placed_at to fulfillment.delivered_at)
- pct_within_5_days_total
Details: Scope: orders with placed_at in 2025-01-01 through 2025-12-31, status='fulfilled', restricted to orders with exactly one row in fulfillments (same simplifying rule as the warehouse report, for clean stage attribution) where that fulfillment has shipped_at and delivered_at both not null. processing_days = DATEDIFF(day, orders.placed_at, fulfillments.shipped_at); transit_days = DATEDIFF(day, fulfillments.shipped_at, fulfillments.delivered_at); total_days = DATEDIFF(day, orders.placed_at, fulfillments.delivered_at) (should roughly equal processing_days + transit_days -- use this as an internal sanity check, note any material mismatch in the report). Round all day averages to 2 decimals, pct to 1 decimal. Group by month_label of placed_at. Order by month ascending. Expect 12 rows. Also report orders_qualifying so I can compare this subset's size against the 'orders_delivered' counts in the original monthly trend report (single-fulfillment orders will be a subset of all delivered orders).

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Felix calls dry_run
      SELECT
  o.month_label AS month,
  COUNT(*) AS orders_qualifying,
  ROUND(AVG(f.shipped_at::date - o.placed_at::date), 2) AS avg_processing_days,
  ROUND(AVG(f.delivered_at::date - f.shipped_at::date), 2) AS avg_transit_days,
  ROUND(AVG(f.delivered_at::date - o.placed_at::date), 2) AS avg_total_days,
  ROUND(100.0 * SUM(CASE WHEN (f.delivered_at::date - o.placed_at::date) <= 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_5_days_total
FROM (
  SELECT orders.id, orders.placed_at, cd.month_label
  FROM orders
  JOIN calendar_days cd ON cd.calendar_date = orders.placed_at::date
  WHERE orders.status = 'fulfilled'
    AND orders.placed_at >= TIMESTAMP '2025-01-01'
    AND orders.placed_at < TIMESTAMP '2026-01-01'
) o
JOIN fulfillments f ON f.order_id = o.id
WHERE f.shipped_at IS NOT NULL
  AND f.delivered_at IS NOT NULL
  AND (SELECT COUNT(*) FROM fulfillments f2 WHERE f2.order_id = o.id) = 1
GROUP BY o.month_label
ORDER BY o.month_label
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: month, orders_qualifying, avg_processing_days, avg_transit_days, avg_total_days, pct_within_5_days_total
    • FelixQuery compiles and returns exactly the requested columns. Submitting now.
    • Felix calls submit
      SELECT
  o.month_label AS month,
  COUNT(*) AS orders_qualifying,
  ROUND(AVG(f.shipped_at::date - o.placed_at::date), 2) AS avg_processing_days,
  ROUND(AVG(f.delivered_at::date - f.shipped_at::date), 2) AS avg_transit_days,
  ROUND(AVG(f.delivered_at::date - o.placed_at::date), 2) AS avg_total_days,
  ROUND(100.0 * SUM(CASE WHEN (f.delivered_at::date - o.placed_at::date) <= 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_5_days_total
FROM (
  SELECT orders.id, orders.placed_at, cd.month_label
  FROM orders
  JOIN calendar_days cd ON cd.calendar_date = orders.placed_at::date
  WHERE orders.status = 'fulfilled'
    AND orders.placed_at >= TIMESTAMP '2025-01-01'
    AND orders.placed_at < TIMESTAMP '2026-01-01'
) o
JOIN fulfillments f ON f.order_id = o.id
WHERE f.shipped_at IS NOT NULL
  AND f.delivered_at IS NOT NULL
  AND (SELECT COUNT(*) FROM fulfillments f2 WHERE f2.order_id = o.id) = 1
GROUP BY o.month_label
ORDER BY o.month_label
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • FelixSubmitted. The report groups fulfilled orders placed in 2025 by placement month, restricted to the single-fulfillment subset with both ship and delivery timestamps, and computes average processing days (placed→shipped), transit days (shipped→delivered), total days (placed→delivered), and the share delivered within 5 days — exactly the 6 requested columns, 12 rows expected (one per month).

    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 (264 KB) · Lena Hartmann's transcript (921 KB) · Priya Raman's transcript (266 KB) · the night's plan (5 KB) · the night's reports (18 KB) · the week's memo (3 KB)