DB Olympics

Queries · q238 · Chief Operating Officer · week 2, night 3

Order-level delivery reliability: weak carriers vs rest, 2025

“In the same order-level metric I've already reported to Priya (pct within 5 days), how much better would our overall delivery reliability be if the three weak carriers matched everyone else?”

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 SQLBeaWrites SQL the way the company's BI tool generates it: verbose fully qualified aliases (t0, t1, ...), a subquery per measure joined back on the grain keys, COALESCE everywhere.

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 2

    Subject: Carrier picture confirms it — now I want to know what we can actually fix

    Lena,

    This is exactly the clarity I was hoping for — "it's the carriers, not the warehouses" is a much more actionable story than "fulfillment is inconsistent." A few things jumped out, including from what Daniel's and Tomasz's teams posted this week, and I want to chase them down before we draft anything for merchants or carrier partners.

    What surprised me: Albion Parcel going from solid to broken in November and staying broken through December is different from Landfall and Lowlands, which sound like long-term weak performers we've just tolerated. One is a relationship we may need to end; the other looks like it could be an acute problem (capacity, a route, a depot) that might actually be fixable if we understood the trigger. I don't want to lump all three into one "bad carriers" conversation when they may need completely different responses.

    I also noticed Daniel's team's finding that "where's my order" tickets spike in lockstep with late deliveries each November/December — that's a direct line from the carrier problem to support cost and customer satisfaction, and I'd like to be able to say that explicitly in my own numbers rather than borrowing his.

    Questions for this week:

    1. Of the three weak carriers, which ones are tied to specific regions, lanes, or merchants, versus broadly weak everywhere? I want to know if Albion's November collapse was localized (a single hub, country, or set of merchants) or general — that tells us whether this is a "fix one bad link" problem or a "this carrier can't be trusted" problem.
    2. If we could bring our weak carriers up to the standard of our good ones, how much of our delivery unreliability would actually go away? I want a sense of the ceiling — is this 80% of our problem, or just the long tail?
    3. Can you give me a clean, non-censored view of whether we're genuinely behind on restocking from suppliers, or if that was a year-end timing artifact? You flagged this yourself — I'd like it resolved before I mention supplier reliability to anyone.
    4. Is there any sign that the picking discrepancies Tomasz's team flagged (concentrated in apparel/footwear) are contributing to delivery delays or failed promises — e.g., orders stalling or being corrected after the fact — or is that purely a shrinkage/accounting issue with no customer-facing effect? I want to know if it belongs in the same story as delivery reliability or is a separate track entirely.

    If it's a tight week, prioritize #1 and #2 — I want the carrier narrative airtight before I take it anywhere. Returns/restock cycle time can wait one more week if needed.

    Thanks, Priya

  2. Lena Hartmann plans the night

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

    I can already answer most of priorities #2-#4 by hand from data I have: (a) the full-year carrier table lets me compute that today's overall transit on-time rate (pct within 3 days) is ~78%, and if the three weak carriers matched the ~88% weighted average of the other 11 carriers, overall on-time would rise to ~88% -- i.e. roughly 45% of our current unreliability sits with these three carriers, the rest is baseline slop even among good carriers. (b) the non-censored PO report fully resolves #3: pct_matured_still_open is ~0% in every quarter including the mature Oct-only slice of Q4 -- there is no real backlog, the 418 "open" POs were purely a right-censoring artifact. The late-delivery rate itself (47-59%) is real and chronic, but it's a persistent promise-miss rate, not a growing queue -- worth flagging as its own issue. (c) the vertical report resolves #4: Beauty and Apparel & Footwear have the highest fulfillment-shortfall rates (4.5-4.7% of orders), but Apparel is actually the fastest and most reliable vertical once an order fully delivers (3.84 avg days, 82.4% within 5) -- so the picking-discrepancy issue looks like a separate, real completeness problem (customers getting short quantities), not a delivery-speed problem. Tonight I want to re-express the carrier "ceiling" story in the exact metric Priya already quotes (order-level pct-within-5-days, not shipment transit), and isolate how much of the Nov-Dec dip is specifically attributable to Albion vs. everything else, plus quantify the Lowlands lane-fix opportunity -- these three numbers should make the carrier narrative airtight and actionable.

    Order-level delivery reliability: weak carriers vs rest, 2025
    Question
    In the same order-level metric I've already reported to Priya (pct within 5 days), how much better would our overall delivery reliability be if the three weak carriers matched everyone else?
    One row per
    carrier_group: 'Weak three' (Albion Parcel, Landfall Freight, Lowlands Express) vs 'All other carriers'
    Columns
    • carrier_group
    • orders_fully_delivered
    • avg_delivery_days (placed_at to delivered_at)
    • pct_within_5_days
    • share_of_orders_pct

  3. Bea 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 order_fulfillment_counts AS (
  SELECT order_id, COUNT(*) AS cnt
  FROM fulfillments
  GROUP BY order_id
),
fulfillment_shipment_counts AS (
  SELECT fulfillment_id, COUNT(*) AS cnt
  FROM shipments
  GROUP BY fulfillment_id
),
base AS (
  SELECT
    o.id AS order_id,
    o.placed_at,
    s.carrier,
    s.delivered_at
  FROM orders o
  JOIN fulfillments f ON f.order_id = o.id
  JOIN shipments s ON s.fulfillment_id = f.id
  JOIN order_fulfillment_counts ofc ON ofc.order_id = o.id
  JOIN fulfillment_shipment_counts fsc ON fsc.fulfillment_id = f.id
  WHERE o.status = 'fulfilled'
    AND o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
    AND ofc.cnt = 1
    AND fsc.cnt = 1
    AND s.delivered_at IS NOT NULL
),
scored AS (
  SELECT
    order_id,
    CASE WHEN carrier IN ('Albion Parcel','Landfall Freight','Lowlands Express') THEN 'Weak three' ELSE 'All other carriers' END AS carrier_group,
    (delivered_at::date - placed_at::date) AS delivery_days
  FROM base
)
SELECT
  carrier_group,
  COUNT(*) AS orders_fully_delivered,
  ROUND(AVG(delivery_days)::numeric, 2) AS avg_delivery_days,
  ROUND(100.0 * SUM(CASE WHEN delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_5_days,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS share_of_orders_pct
FROM scored
GROUP BY carrier_group
ORDER BY carrier_group 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 computes delivery reliability at the carrier-group grain ('Weak three' vs 'All other carriers') for 2025 fulfilled orders with clean single-carrier attribution, using the same pct-within-5-days metric as Priya's report, so the COO's analyst can directly compare what overall reliability would look like if the weak carriers matched the rest.”

  4. The report comes back overnight

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

    carrier_group      | orders_fully_delivered | avg_delivery_days | pct_within_5_days | share_of_orders_pct
-------------------+------------------------+-------------------+-------------------+--------------------
All other carriers |                  13696 |              3.44 |              93.4 |                66.7
Weak three         |                   6837 |              5.19 |              66.8 |                33.3
(2 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:

    It builds on:

  6. It becomes benchmark query q238

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

    • spellingcast (3×)
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision; bare NUMERIC cast that only served the rounding removed; 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
    • 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 order_fulfillment_counts AS (  SELECT order_id, COUNT(*) AS cnt  FROM fulfillments  GROUP BY order_id),fulfillment_shipment_counts AS (  SELECT fulfillment_id, COUNT(*) AS cnt  FROM shipments  GROUP BY fulfillment_id),base AS (  SELECT    o.id AS order_id,    o.placed_at,    s.carrier,    s.delivered_at    o.placed_at AS placed_at,    s.carrier AS carrier,    s.delivered_at AS delivered_at  FROM orders o  JOIN fulfillments f ON f.order_id = o.id  JOIN shipments s ON s.fulfillment_id = f.id  JOIN order_fulfillment_counts ofc ON ofc.order_id = o.id  JOIN fulfillment_shipment_counts fsc ON fsc.fulfillment_id = f.id  WHERE o.status = 'fulfilled'    AND o.placed_at >= DATE '2025-01-01'    AND o.placed_at < DATE '2026-01-01'    AND ofc.cnt = 1    AND fsc.cnt = 1    AND s.delivered_at IS NOT NULL),scored AS (  SELECT    order_id,    CASE WHEN carrier IN ('Albion Parcel','Landfall Freight','Lowlands Express') THEN 'Weak three' ELSE 'All other carriers' END AS carrier_group,    (delivered_at::date - placed_at::date) AS delivery_days    {{date_diff_days(placed_at, delivered_at)}} AS delivery_days  FROM base)SELECT  carrier_group,  COUNT(*) AS orders_fully_delivered,  ROUND(AVG(delivery_days)::numeric, 2) AS avg_delivery_days,  ROUND(100.0 * SUM(CASE WHEN delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_5_days,  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS share_of_orders_pct  AVG(CAST(delivery_days AS NUMERIC(18,6))) AS avg_delivery_days,  100.0 * SUM(CASE WHEN delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*) AS pct_within_5_days,  100.0 * COUNT(*) / SUM(COUNT(*)) OVER () AS share_of_orders_pctFROM scoredGROUP BY carrier_groupORDER BY carrier_group ASCORDER BY carrier_group ASC NULLS LAST

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

    WITH order_fulfillment_counts AS (
  SELECT order_id, COUNT(*) AS cnt
  FROM fulfillments
  GROUP BY order_id
),
fulfillment_shipment_counts AS (
  SELECT fulfillment_id, COUNT(*) AS cnt
  FROM shipments
  GROUP BY fulfillment_id
),
base AS (
  SELECT
    o.id AS order_id,
    o.placed_at AS placed_at,
    s.carrier AS carrier,
    s.delivered_at AS delivered_at
  FROM orders o
  JOIN fulfillments f ON f.order_id = o.id
  JOIN shipments s ON s.fulfillment_id = f.id
  JOIN order_fulfillment_counts ofc ON ofc.order_id = o.id
  JOIN fulfillment_shipment_counts fsc ON fsc.fulfillment_id = f.id
  WHERE o.status = 'fulfilled'
    AND o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
    AND ofc.cnt = 1
    AND fsc.cnt = 1
    AND s.delivered_at IS NOT NULL
),
scored AS (
  SELECT
    order_id,
    CASE WHEN carrier IN ('Albion Parcel','Landfall Freight','Lowlands Express') THEN 'Weak three' ELSE 'All other carriers' END AS carrier_group,
    DATE_DIFF('day', placed_at, delivered_at) AS delivery_days
  FROM base
)
SELECT
  carrier_group,
  COUNT(*) AS orders_fully_delivered,
  AVG(CAST(delivery_days AS NUMERIC(18,6))) AS avg_delivery_days,
  100.0 * SUM(CASE WHEN delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*) AS pct_within_5_days,
  100.0 * COUNT(*) / SUM(COUNT(*)) OVER () AS share_of_orders_pct
FROM scored
GROUP BY carrier_group
ORDER BY carrier_group ASC NULLS LAST
    100 ms1 s10 s30 sHyper844 msDuckDB2.46 sGizmoSQL1.99 sclickhouse_brew1.48 sFirebolt762 msDataFusiontimeout (30 s)ClickHouse1.17 s
    • DataFusionno result within 30 s

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

    Bea's whole session, 7 turns
    • The request, as Bea received it
      Report request from Lena Hartmann (analyst for the Chief Operating Officer):

Title: Order-level delivery reliability: weak carriers vs rest, 2025
Question: In the same order-level metric I've already reported to Priya (pct within 5 days), how much better would our overall delivery reliability be if the three weak carriers matched everyone else?
Grain: one row per carrier_group: 'Weak three' (Albion Parcel, Landfall Freight, Lowlands Express) vs 'All other carriers'
Columns:
- carrier_group
- orders_fully_delivered
- avg_delivery_days (placed_at to delivered_at)
- pct_within_5_days
- share_of_orders_pct
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 and that fulfillment linked to exactly one row in shipments, with that shipment's delivered_at not null (clean single-carrier attribution). delivery_days = DATEDIFF(day, orders.placed_at, shipments.delivered_at). carrier_group = 'Weak three' if shipments.carrier in ('Albion Parcel','Landfall Freight','Lowlands Express'), else 'All other carriers'. Round avg_delivery_days to 2 decimals, pct columns to 1 decimal. Order by carrier_group ascending. Expect 2 rows.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Bea calls dry_run
      WITH order_fulfillment_counts AS (
  SELECT order_id, COUNT(*) AS cnt
  FROM fulfillments
  GROUP BY order_id
),
fulfillment_shipment_counts AS (
  SELECT fulfillment_id, COUNT(*) AS cnt
  FROM shipments
  GROUP BY fulfillment_id
),
base AS (
  SELECT
    o.id AS order_id,
    o.placed_at,
    s.carrier,
    s.delivered_at
  FROM orders o
  JOIN fulfillments f ON f.order_id = o.id
  JOIN shipments s ON s.fulfillment_id = f.id
  JOIN order_fulfillment_counts ofc ON ofc.order_id = o.id
  JOIN fulfillment_shipment_counts fsc ON fsc.fulfillment_id = f.id
  WHERE o.status = 'fulfilled'
    AND o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
    AND ofc.cnt = 1
    AND fsc.cnt = 1
    AND s.delivered_at IS NOT NULL
),
scored AS (
  SELECT
    order_id,
    CASE WHEN carrier IN ('Albion Parcel','Landfall Freight','Lowlands Express') THEN 'Weak three' ELSE 'All other carriers' END AS carrier_group,
    (delivered_at::date - placed_at::date) AS delivery_days
  FROM base
)
SELECT
  carrier_group,
  COUNT(*) AS orders_fully_delivered,
  ROUND(AVG(delivery_days)::numeric, 2) AS avg_delivery_days,
  ROUND(100.0 * SUM(CASE WHEN delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_5_days,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS share_of_orders_pct
FROM scored
GROUP BY carrier_group
ORDER BY carrier_group ASC
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: carrier_group, orders_fully_delivered, avg_delivery_days, pct_within_5_days, share_of_orders_pct
    • BeaQuery compiles and returns the expected columns. Submitting.
    • Bea calls submit
      WITH order_fulfillment_counts AS (
  SELECT order_id, COUNT(*) AS cnt
  FROM fulfillments
  GROUP BY order_id
),
fulfillment_shipment_counts AS (
  SELECT fulfillment_id, COUNT(*) AS cnt
  FROM shipments
  GROUP BY fulfillment_id
),
base AS (
  SELECT
    o.id AS order_id,
    o.placed_at,
    s.carrier,
    s.delivered_at
  FROM orders o
  JOIN fulfillments f ON f.order_id = o.id
  JOIN shipments s ON s.fulfillment_id = f.id
  JOIN order_fulfillment_counts ofc ON ofc.order_id = o.id
  JOIN fulfillment_shipment_counts fsc ON fsc.fulfillment_id = f.id
  WHERE o.status = 'fulfilled'
    AND o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
    AND ofc.cnt = 1
    AND fsc.cnt = 1
    AND s.delivered_at IS NOT NULL
),
scored AS (
  SELECT
    order_id,
    CASE WHEN carrier IN ('Albion Parcel','Landfall Freight','Lowlands Express') THEN 'Weak three' ELSE 'All other carriers' END AS carrier_group,
    (delivered_at::date - placed_at::date) AS delivery_days
  FROM base
)
SELECT
  carrier_group,
  COUNT(*) AS orders_fully_delivered,
  ROUND(AVG(delivery_days)::numeric, 2) AS avg_delivery_days,
  ROUND(100.0 * SUM(CASE WHEN delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_5_days,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS share_of_orders_pct
FROM scored
GROUP BY carrier_group
ORDER BY carrier_group ASC
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • BeaSubmitted. The report computes delivery reliability at the carrier-group grain ('Weak three' vs 'All other carriers') for 2025 fulfilled orders with clean single-carrier attribution, using the same pct-within-5-days metric as Priya's report, so the COO's analyst can directly compare what overall reliability would look like if the weak carriers matched the rest.

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

    Bea's transcript (266 KB) · Lena Hartmann's transcript (921 KB) · Priya Raman's transcript (266 KB) · the night's plan (5 KB) · the night's reports (3 KB) · the week's memo (4 KB)