DB Olympics

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

Order-level delivery reliability: Lowlands Express by destination lane, 2025

“How much better would Lowlands Express's orders perform, and how much would that help overall, if we only used it on its strong home lanes (Netherlands, Belgium, France) and moved everything else off it?”

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 3 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: Lowlands Express by destination lane, 2025
    Question
    How much better would Lowlands Express's orders perform, and how much would that help overall, if we only used it on its strong home lanes (Netherlands, Belgium, France) and moved everything else off it?
    One row per
    lane_group: 'Lowlands core (NL/BE/FR)', 'Lowlands non-core (other countries)', 'All other carriers'
    Columns
    • lane_group
    • orders_fully_delivered
    • avg_delivery_days
    • 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 t0 AS (
  SELECT
    o.id AS order_id,
    o.placed_at,
    o.shipping_address_id,
    s.carrier,
    s.delivered_at,
    (EXTRACT(EPOCH FROM (s.delivered_at - o.placed_at)) / 86400.0) AS delivery_days
  FROM orders o
  JOIN fulfillments f ON f.order_id = o.id
  JOIN shipments s ON s.fulfillment_id = f.id
  WHERE o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
    AND o.status = 'fulfilled'
    AND s.delivered_at IS NOT NULL
    AND (SELECT COUNT(*) FROM fulfillments f2 WHERE f2.order_id = o.id) = 1
    AND (SELECT COUNT(*) FROM shipments s2 WHERE s2.fulfillment_id = f.id) = 1
),
t1 AS (
  SELECT
    t0.order_id,
    t0.delivery_days,
    CASE
      WHEN t0.carrier = 'Lowlands Express' AND c.name IN ('Netherlands', 'Belgium', 'France') THEN 'Lowlands core (NL/BE/FR)'
      WHEN t0.carrier = 'Lowlands Express' THEN 'Lowlands non-core (other countries)'
      ELSE 'All other carriers'
    END AS lane_group
  FROM t0
  LEFT JOIN addresses a ON a.id = t0.shipping_address_id
  LEFT JOIN countries c ON c.id = a.country_id
),
t2 AS (
  SELECT
    t1.lane_group,
    COUNT(*) AS orders_fully_delivered,
    ROUND(AVG(t1.delivery_days)::numeric, 2) AS avg_delivery_days,
    ROUND(100.0 * SUM(CASE WHEN t1.delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_5_days
  FROM t1
  GROUP BY t1.lane_group
),
t3 AS (
  SELECT COUNT(*) AS total_orders FROM t1
)
SELECT
  t2.lane_group,
  t2.orders_fully_delivered,
  t2.avg_delivery_days,
  t2.pct_within_5_days,
  ROUND(100.0 * t2.orders_fully_delivered / t3.total_orders, 1) AS share_of_orders_pct
FROM t2
CROSS JOIN t3
ORDER BY
  CASE t2.lane_group
    WHEN 'Lowlands core (NL/BE/FR)' THEN 1
    WHEN 'Lowlands non-core (other countries)' THEN 2
    ELSE 3
  END,
  t2.lane_group

    “Looks correct and compiles. 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, per lane_group ('Lowlands core (NL/BE/FR)', 'Lowlands non-core (other countries)', 'All other carriers'): order count, average delivery days (delivered_at − placed_at), % delivered within 5 days, and each group's share of total qualifying orders — using the same 2025/fulfilled/single-fulfillment/single-shipment/delivered scoping as the other reports in this set.”

  4. The report comes back overnight

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

    lane_group                          | orders_fully_delivered | avg_delivery_days | pct_within_5_days | share_of_orders_pct
------------------------------------+------------------------+-------------------+-------------------+--------------------
Lowlands core (NL/BE/FR)            |                   1581 |              4.34 |              72.9 |                 7.7
Lowlands non-core (other countries) |                    864 |              5.96 |              34.1 |                 4.2
All other carriers                  |                  18088 |              3.92 |              82.0 |                88.1
(3 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 q240

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

    • spellingcast
    • 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
    • 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 (11×)
    • 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 (2×)
    The benchmark text, against the submitted SQL
    WITH t0 AS (  SELECT    o.id AS order_id,    o.placed_at,    o.shipping_address_id,    s.carrier,    s.delivered_at,    (EXTRACT(EPOCH FROM (s.delivered_at - o.placed_at)) / 86400.0) AS delivery_days    o.placed_at AS placed_at,    o.shipping_address_id AS shipping_address_id,    s.carrier AS carrier,    s.delivered_at AS delivered_at,    ({{seconds_between(o.placed_at, s.delivered_at)}} / 86400.0) AS delivery_days  FROM orders o  JOIN fulfillments f ON f.order_id = o.id  JOIN shipments s ON s.fulfillment_id = f.id  WHERE o.placed_at >= DATE '2025-01-01'    AND o.placed_at < DATE '2026-01-01'    AND o.status = 'fulfilled'    AND s.delivered_at IS NOT NULL    AND (SELECT COUNT(*) FROM fulfillments f2 WHERE f2.order_id = o.id) = 1    AND (SELECT COUNT(*) FROM shipments s2 WHERE s2.fulfillment_id = f.id) = 1),t1 AS (  SELECT    t0.order_id,    t0.delivery_days,    t0.order_id AS order_id,    t0.delivery_days AS delivery_days,    CASE      WHEN t0.carrier = 'Lowlands Express' AND c.name IN ('Netherlands', 'Belgium', 'France') THEN 'Lowlands core (NL/BE/FR)'      WHEN t0.carrier = 'Lowlands Express' THEN 'Lowlands non-core (other countries)'      ELSE 'All other carriers'    END AS lane_group  FROM t0  LEFT JOIN addresses a ON a.id = t0.shipping_address_id  LEFT JOIN countries c ON c.id = a.country_id),t2 AS (  SELECT    t1.lane_group,    t1.lane_group AS lane_group,    COUNT(*) AS orders_fully_delivered,    ROUND(AVG(t1.delivery_days)::numeric, 2) AS avg_delivery_days,    ROUND(100.0 * SUM(CASE WHEN t1.delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_5_days    AVG(t1.delivery_days) AS avg_delivery_days,    100.0 * SUM(CASE WHEN t1.delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*) AS pct_within_5_days  FROM t1  GROUP BY t1.lane_group),t3 AS (  SELECT COUNT(*) AS total_orders FROM t1)SELECT  t2.lane_group,  t2.orders_fully_delivered,  t2.avg_delivery_days,  t2.pct_within_5_days,  ROUND(100.0 * t2.orders_fully_delivered / t3.total_orders, 1) AS share_of_orders_pct  t2.lane_group AS lane_group,  t2.orders_fully_delivered AS orders_fully_delivered,  t2.avg_delivery_days AS avg_delivery_days,  t2.pct_within_5_days AS pct_within_5_days,  100.0 * t2.orders_fully_delivered / t3.total_orders AS share_of_orders_pctFROM t2CROSS JOIN t3ORDER BY  CASE t2.lane_group    WHEN 'Lowlands core (NL/BE/FR)' THEN 1    WHEN 'Lowlands non-core (other countries)' THEN 2    ELSE 3  END,  t2.lane_group  END NULLS LAST,  t2.lane_group NULLS LAST

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

    WITH t0 AS (
  SELECT
    o.id AS order_id,
    o.placed_at AS placed_at,
    o.shipping_address_id AS shipping_address_id,
    s.carrier AS carrier,
    s.delivered_at AS delivered_at,
    (CAST(EXTRACT(EPOCH FROM (s.delivered_at - o.placed_at)) AS NUMERIC(18,6)) / 86400.0) AS delivery_days
  FROM orders o
  JOIN fulfillments f ON f.order_id = o.id
  JOIN shipments s ON s.fulfillment_id = f.id
  WHERE o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
    AND o.status = 'fulfilled'
    AND s.delivered_at IS NOT NULL
    AND (SELECT COUNT(*) FROM fulfillments f2 WHERE f2.order_id = o.id) = 1
    AND (SELECT COUNT(*) FROM shipments s2 WHERE s2.fulfillment_id = f.id) = 1
),
t1 AS (
  SELECT
    t0.order_id AS order_id,
    t0.delivery_days AS delivery_days,
    CASE
      WHEN t0.carrier = 'Lowlands Express' AND c.name IN ('Netherlands', 'Belgium', 'France') THEN 'Lowlands core (NL/BE/FR)'
      WHEN t0.carrier = 'Lowlands Express' THEN 'Lowlands non-core (other countries)'
      ELSE 'All other carriers'
    END AS lane_group
  FROM t0
  LEFT JOIN addresses a ON a.id = t0.shipping_address_id
  LEFT JOIN countries c ON c.id = a.country_id
),
t2 AS (
  SELECT
    t1.lane_group AS lane_group,
    COUNT(*) AS orders_fully_delivered,
    AVG(t1.delivery_days) AS avg_delivery_days,
    100.0 * SUM(CASE WHEN t1.delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*) AS pct_within_5_days
  FROM t1
  GROUP BY t1.lane_group
),
t3 AS (
  SELECT COUNT(*) AS total_orders FROM t1
)
SELECT
  t2.lane_group AS lane_group,
  t2.orders_fully_delivered AS orders_fully_delivered,
  t2.avg_delivery_days AS avg_delivery_days,
  t2.pct_within_5_days AS pct_within_5_days,
  100.0 * t2.orders_fully_delivered / t3.total_orders AS share_of_orders_pct
FROM t2
CROSS JOIN t3
ORDER BY
  CASE t2.lane_group
    WHEN 'Lowlands core (NL/BE/FR)' THEN 1
    WHEN 'Lowlands non-core (other countries)' THEN 2
    ELSE 3
  END NULLS LAST,
  t2.lane_group NULLS LAST
    1 s10 sHyper2.45 sDuckDB1.65 sGizmoSQL1.13 sclickhouse_brew2.15 sFirebolt1.78 sDataFusion7.61 sClickHouse3.63 s

    Scale factor 1000, c8a.24xlarge, after the last load. q240 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: Lowlands Express by destination lane, 2025
Question: How much better would Lowlands Express's orders perform, and how much would that help overall, if we only used it on its strong home lanes (Netherlands, Belgium, France) and moved everything else off it?
Grain: one row per lane_group: 'Lowlands core (NL/BE/FR)', 'Lowlands non-core (other countries)', 'All other carriers'
Columns:
- lane_group
- orders_fully_delivered
- avg_delivery_days
- pct_within_5_days
- share_of_orders_pct
Details: Same scoping and single-carrier attribution rule as the other two reports in this set (orders placed_at in 2025, status='fulfilled', exactly one fulfillment with exactly one shipment, delivered_at not null). For orders where shipments.carrier = 'Lowlands Express', further split by the order's destination country (orders.shipping_address_id -> addresses -> countries): 'Lowlands core' if country in ('Netherlands','Belgium','France'), else 'Lowlands non-core'. All other orders (any other carrier) go to 'All other carriers'. Round avg_delivery_days to 2 decimals, pct columns to 1 decimal. Order: 'Lowlands core' first, then 'Lowlands non-core', then 'All other carriers'. Expect 3 rows.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Bea calls dry_run
      WITH t0 AS (
  SELECT
    o.id AS order_id,
    o.placed_at,
    o.shipping_address_id,
    s.carrier,
    s.delivered_at,
    (EXTRACT(EPOCH FROM (s.delivered_at - o.placed_at)) / 86400.0) AS delivery_days
  FROM orders o
  JOIN fulfillments f ON f.order_id = o.id
  JOIN shipments s ON s.fulfillment_id = f.id
  WHERE o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
    AND o.status = 'fulfilled'
    AND s.delivered_at IS NOT NULL
    AND (SELECT COUNT(*) FROM fulfillments f2 WHERE f2.order_id = o.id) = 1
    AND (SELECT COUNT(*) FROM shipments s2 WHERE s2.fulfillment_id = f.id) = 1
),
t1 AS (
  SELECT
    t0.order_id,
    t0.delivery_days,
    CASE
      WHEN t0.carrier = 'Lowlands Express' AND c.name IN ('Netherlands', 'Belgium', 'France') THEN 'Lowlands core (NL/BE/FR)'
      WHEN t0.carrier = 'Lowlands Express' THEN 'Lowlands non-core (other countries)'
      ELSE 'All other carriers'
    END AS lane_group
  FROM t0
  LEFT JOIN addresses a ON a.id = t0.shipping_address_id
  LEFT JOIN countries c ON c.id = a.country_id
),
t2 AS (
  SELECT
    t1.lane_group,
    COUNT(*) AS orders_fully_delivered,
    ROUND(AVG(t1.delivery_days)::numeric, 2) AS avg_delivery_days,
    ROUND(100.0 * SUM(CASE WHEN t1.delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_5_days
  FROM t1
  GROUP BY t1.lane_group
),
t3 AS (
  SELECT COUNT(*) AS total_orders FROM t1
)
SELECT
  t2.lane_group,
  t2.orders_fully_delivered,
  t2.avg_delivery_days,
  t2.pct_within_5_days,
  ROUND(100.0 * t2.orders_fully_delivered / t3.total_orders, 1) AS share_of_orders_pct
FROM t2
CROSS JOIN t3
ORDER BY
  CASE t2.lane_group
    WHEN 'Lowlands core (NL/BE/FR)' THEN 1
    WHEN 'Lowlands non-core (other countries)' THEN 2
    ELSE 3
  END,
  t2.lane_group
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: lane_group, orders_fully_delivered, avg_delivery_days, pct_within_5_days, share_of_orders_pct
    • BeaLooks correct and compiles. Submitting.
    • Bea calls submit
      WITH t0 AS (
  SELECT
    o.id AS order_id,
    o.placed_at,
    o.shipping_address_id,
    s.carrier,
    s.delivered_at,
    (EXTRACT(EPOCH FROM (s.delivered_at - o.placed_at)) / 86400.0) AS delivery_days
  FROM orders o
  JOIN fulfillments f ON f.order_id = o.id
  JOIN shipments s ON s.fulfillment_id = f.id
  WHERE o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
    AND o.status = 'fulfilled'
    AND s.delivered_at IS NOT NULL
    AND (SELECT COUNT(*) FROM fulfillments f2 WHERE f2.order_id = o.id) = 1
    AND (SELECT COUNT(*) FROM shipments s2 WHERE s2.fulfillment_id = f.id) = 1
),
t1 AS (
  SELECT
    t0.order_id,
    t0.delivery_days,
    CASE
      WHEN t0.carrier = 'Lowlands Express' AND c.name IN ('Netherlands', 'Belgium', 'France') THEN 'Lowlands core (NL/BE/FR)'
      WHEN t0.carrier = 'Lowlands Express' THEN 'Lowlands non-core (other countries)'
      ELSE 'All other carriers'
    END AS lane_group
  FROM t0
  LEFT JOIN addresses a ON a.id = t0.shipping_address_id
  LEFT JOIN countries c ON c.id = a.country_id
),
t2 AS (
  SELECT
    t1.lane_group,
    COUNT(*) AS orders_fully_delivered,
    ROUND(AVG(t1.delivery_days)::numeric, 2) AS avg_delivery_days,
    ROUND(100.0 * SUM(CASE WHEN t1.delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_5_days
  FROM t1
  GROUP BY t1.lane_group
),
t3 AS (
  SELECT COUNT(*) AS total_orders FROM t1
)
SELECT
  t2.lane_group,
  t2.orders_fully_delivered,
  t2.avg_delivery_days,
  t2.pct_within_5_days,
  ROUND(100.0 * t2.orders_fully_delivered / t3.total_orders, 1) AS share_of_orders_pct
FROM t2
CROSS JOIN t3
ORDER BY
  CASE t2.lane_group
    WHEN 'Lowlands core (NL/BE/FR)' THEN 1
    WHEN 'Lowlands non-core (other countries)' THEN 2
    ELSE 3
  END,
  t2.lane_group
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • BeaSubmitted. The report computes, per lane_group ('Lowlands core (NL/BE/FR)', 'Lowlands non-core (other countries)', 'All other carriers'): order count, average delivery days (delivered_at − placed_at), % delivered within 5 days, and each group's share of total qualifying orders — using the same 2025/fulfilled/single-fulfillment/single-shipment/delivered scoping as the other reports in this set.

    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 (269 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)