DB Olympics

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

Albion Parcel performance by destination country, baseline vs collapse

“Is Albion Parcel's November-December breakdown concentrated in specific destination countries, or did it hit all regions roughly equally?”

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 SQLOmarPragmatic: the shortest correct query, aggregates first and joins dimensions afterwards, FILTER clauses for conditional counts.

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

    Priya wants the carrier narrative airtight before it goes anywhere, and specifically wants to know whether Albion Parcel's November collapse is a localized, fixable problem (one hub/country/merchant) versus Landfall Freight and Lowlands Express being broadly, chronically weak (a relationship-ending problem). That's priority #1 and it needs its own evidence on both the destination side (country/region) and the origin side (warehouse/hub), split into the pre-collapse baseline (Jan-Oct) vs. the collapse window (Nov-Dec) for Albion specifically. I'm spending all 3 slots tonight on that, since getting this distinction right shapes two very different conversations with carrier partners. I'll tackle priority #2 (the reliability "ceiling" if weak carriers matched good ones) first thing tomorrow night, using a full-year, non-truncated carrier table I don't yet have cleanly. Priority #3 (clean PO backlog view) and #4 (picking discrepancies vs. delivery) will follow later this week as time allows, per Priya's own prioritization.

    Albion Parcel performance by destination country, baseline vs collapse
    Question
    Is Albion Parcel's November-December breakdown concentrated in specific destination countries, or did it hit all regions roughly equally?
    One row per
    destination country and period, for Albion Parcel shipments only
    Columns
    • destination_country
    • period ('baseline' = shipped_at 2025-01-01 to 2025-10-31; 'collapse' = shipped_at 2025-11-01 to 2025-12-31)
    • shipments_shipped
    • avg_transit_days (shipped_at to delivered_at, delivered subset only)
    • pct_within_3_days
    • share_of_period_shipments_pct (this country's shipments / all Albion shipments in that period, x100)

  3. Omar 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 base AS (
  SELECT
    s.id,
    s.shipped_at,
    s.delivered_at,
    c.name AS destination_country,
    (s.delivered_at::date - s.shipped_at::date) AS transit_days,
    CASE
      WHEN s.shipped_at >= DATE '2025-01-01' AND s.shipped_at < DATE '2025-11-01' THEN 'baseline'
      WHEN s.shipped_at >= DATE '2025-11-01' AND s.shipped_at < DATE '2026-01-01' THEN 'collapse'
    END AS period
  FROM shipments s
  JOIN fulfillments f ON s.fulfillment_id = f.id
  JOIN orders o ON f.order_id = o.id
  JOIN addresses a ON o.shipping_address_id = a.id
  JOIN countries c ON a.country_id = c.id
  WHERE s.carrier = 'Albion Parcel'
    AND s.shipped_at >= DATE '2025-01-01'
    AND s.shipped_at < DATE '2026-01-01'
),
agg AS (
  SELECT
    destination_country,
    period,
    COUNT(*) AS shipments_shipped,
    COUNT(*) FILTER (WHERE delivered_at IS NOT NULL) AS delivered_count,
    AVG(transit_days) FILTER (WHERE delivered_at IS NOT NULL) AS avg_transit_days,
    COUNT(*) FILTER (WHERE delivered_at IS NOT NULL AND transit_days <= 3) AS within3
  FROM base
  WHERE period IS NOT NULL
  GROUP BY destination_country, period
),
totals AS (
  SELECT period, SUM(shipments_shipped) AS total_shipments
  FROM agg
  GROUP BY period
),
qualified AS (
  SELECT destination_country
  FROM agg
  GROUP BY destination_country
  HAVING MAX(shipments_shipped) >= 15
)
SELECT
  a.destination_country,
  a.period,
  a.shipments_shipped,
  ROUND(a.avg_transit_days::numeric, 2) AS avg_transit_days,
  ROUND(100.0 * a.within3 / NULLIF(a.delivered_count, 0), 1) AS pct_within_3_days,
  ROUND(100.0 * a.shipments_shipped / t.total_shipments, 1) AS share_of_period_shipments_pct
FROM agg a
JOIN totals t ON a.period = t.period
JOIN qualified q ON q.destination_country = a.destination_country
ORDER BY a.destination_country ASC, a.period 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 destination country × period (baseline/collapse) for Albion Parcel shipments, with shipment counts, average transit days and on-time share computed on the delivered subset, and each country's share of that period's total Albion volume — restricted to countries with at least 15 shipments in either period to filter out noise.”

  4. The report comes back overnight

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

    destination_country | period   | shipments_shipped | avg_transit_days | pct_within_3_days | share_of_period_shipments_pct
--------------------+----------+-------------------+------------------+-------------------+------------------------------
Ireland             | baseline |               147 |             2.30 |              91.8 |                           6.9
Ireland             | collapse |                44 |             8.81 |              35.7 |                           8.0
United Kingdom      | baseline |              1982 |             2.19 |              93.0 |                          93.1
United Kingdom      | collapse |               503 |             8.46 |              31.4 |                          92.0
(4 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 q232

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

    • spellingcast (3×)
    • numericsdocs/NUMERICS.md: AVG over an integer computed as a decimal: AVG(CAST(x AS NUMERIC(18,6)))
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision; bare NUMERIC cast that only served the rounding removed
    • made explicita selected t.col gets AS col, its standard name made explicit (6×)
    • 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 (2×)
    The benchmark text, against the submitted SQL
    WITH base AS (  SELECT    s.id,    s.shipped_at,    s.delivered_at,    s.id AS id,    s.shipped_at AS shipped_at,    s.delivered_at AS delivered_at,    c.name AS destination_country,    (s.delivered_at::date - s.shipped_at::date) AS transit_days,    {{date_diff_days(s.shipped_at, s.delivered_at)}} AS transit_days,    CASE      WHEN s.shipped_at >= DATE '2025-01-01' AND s.shipped_at < DATE '2025-11-01' THEN 'baseline'      WHEN s.shipped_at >= DATE '2025-11-01' AND s.shipped_at < DATE '2026-01-01' THEN 'collapse'    END AS period  FROM shipments s  JOIN fulfillments f ON s.fulfillment_id = f.id  JOIN orders o ON f.order_id = o.id  JOIN addresses a ON o.shipping_address_id = a.id  JOIN countries c ON a.country_id = c.id  WHERE s.carrier = 'Albion Parcel'    AND s.shipped_at >= DATE '2025-01-01'    AND s.shipped_at < DATE '2026-01-01'),agg AS (  SELECT    destination_country,    period,    COUNT(*) AS shipments_shipped,    COUNT(*) FILTER (WHERE delivered_at IS NOT NULL) AS delivered_count,    AVG(transit_days) FILTER (WHERE delivered_at IS NOT NULL) AS avg_transit_days,    AVG(CAST(transit_days AS NUMERIC(18,6))) FILTER (WHERE delivered_at IS NOT NULL) AS avg_transit_days,    COUNT(*) FILTER (WHERE delivered_at IS NOT NULL AND transit_days <= 3) AS within3  FROM base  WHERE period IS NOT NULL  GROUP BY destination_country, period),totals AS (  SELECT period, SUM(shipments_shipped) AS total_shipments  FROM agg  GROUP BY period),qualified AS (  SELECT destination_country  FROM agg  GROUP BY destination_country  HAVING MAX(shipments_shipped) >= 15)SELECT  a.destination_country,  a.period,  a.shipments_shipped,  ROUND(a.avg_transit_days::numeric, 2) AS avg_transit_days,  ROUND(100.0 * a.within3 / NULLIF(a.delivered_count, 0), 1) AS pct_within_3_days,  ROUND(100.0 * a.shipments_shipped / t.total_shipments, 1) AS share_of_period_shipments_pct  a.destination_country AS destination_country,  a.period AS period,  a.shipments_shipped AS shipments_shipped,  a.avg_transit_days AS avg_transit_days,  100.0 * a.within3 / NULLIF(a.delivered_count, 0) AS pct_within_3_days,  100.0 * a.shipments_shipped / t.total_shipments AS share_of_period_shipments_pctFROM agg aJOIN totals t ON a.period = t.periodJOIN qualified q ON q.destination_country = a.destination_countryORDER BY a.destination_country ASC, a.period ASCORDER BY a.destination_country ASC NULLS LAST, a.period 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,
    c.name AS destination_country,
    DATE_DIFF('day', s.shipped_at, s.delivered_at) AS transit_days,
    CASE
      WHEN s.shipped_at >= DATE '2025-01-01' AND s.shipped_at < DATE '2025-11-01' THEN 'baseline'
      WHEN s.shipped_at >= DATE '2025-11-01' AND s.shipped_at < DATE '2026-01-01' THEN 'collapse'
    END AS period
  FROM shipments s
  JOIN fulfillments f ON s.fulfillment_id = f.id
  JOIN orders o ON f.order_id = o.id
  JOIN addresses a ON o.shipping_address_id = a.id
  JOIN countries c ON a.country_id = c.id
  WHERE s.carrier = 'Albion Parcel'
    AND s.shipped_at >= DATE '2025-01-01'
    AND s.shipped_at < DATE '2026-01-01'
),
agg AS (
  SELECT
    destination_country,
    period,
    COUNT(*) AS shipments_shipped,
    COUNT(*) FILTER (WHERE delivered_at IS NOT NULL) AS delivered_count,
    AVG(CAST(transit_days AS NUMERIC(18,6))) FILTER (WHERE delivered_at IS NOT NULL) AS avg_transit_days,
    COUNT(*) FILTER (WHERE delivered_at IS NOT NULL AND transit_days <= 3) AS within3
  FROM base
  WHERE period IS NOT NULL
  GROUP BY destination_country, period
),
totals AS (
  SELECT period, SUM(shipments_shipped) AS total_shipments
  FROM agg
  GROUP BY period
),
qualified AS (
  SELECT destination_country
  FROM agg
  GROUP BY destination_country
  HAVING MAX(shipments_shipped) >= 15
)
SELECT
  a.destination_country AS destination_country,
  a.period AS period,
  a.shipments_shipped AS shipments_shipped,
  a.avg_transit_days AS avg_transit_days,
  100.0 * a.within3 / NULLIF(a.delivered_count, 0) AS pct_within_3_days,
  100.0 * a.shipments_shipped / t.total_shipments AS share_of_period_shipments_pct
FROM agg a
JOIN totals t ON a.period = t.period
JOIN qualified q ON q.destination_country = a.destination_country
ORDER BY a.destination_country ASC NULLS LAST, a.period ASC NULLS LAST
    10 ms100 ms1 s10 sHyper85.6 msDuckDB101 msGizmoSQL111 msclickhouse_brew576 msFirebolt1.27 sDataFusion880 msClickHouse1.01 s

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

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

Title: Albion Parcel performance by destination country, baseline vs collapse
Question: Is Albion Parcel's November-December breakdown concentrated in specific destination countries, or did it hit all regions roughly equally?
Grain: one row per destination country and period, for Albion Parcel shipments only
Columns:
- destination_country
- period ('baseline' = shipped_at 2025-01-01 to 2025-10-31; 'collapse' = shipped_at 2025-11-01 to 2025-12-31)
- shipments_shipped
- avg_transit_days (shipped_at to delivered_at, delivered subset only)
- pct_within_3_days
- share_of_period_shipments_pct (this country's shipments / all Albion shipments in that period, x100)
Details: Tables: shipments (carrier='Albion Parcel', shipped_at, delivered_at) joined via fulfillments (fulfillment_id) -> orders (order_id) -> addresses (orders.shipping_address_id) -> countries (destination country name). transit_days = DATEDIFF(day, shipped_at, delivered_at), delivered subset only. Round avg_transit_days to 2 decimals, pct columns to 1 decimal. Include only countries with at least 15 shipments in at least one of the two periods (to avoid noise from one-off destinations). Order by destination_country ascending, then period (baseline before collapse).

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Omar calls dry_run
      WITH base AS (
  SELECT
    s.id,
    s.shipped_at,
    s.delivered_at,
    c.name AS destination_country,
    (s.delivered_at::date - s.shipped_at::date) AS transit_days,
    CASE
      WHEN s.shipped_at >= DATE '2025-01-01' AND s.shipped_at < DATE '2025-11-01' THEN 'baseline'
      WHEN s.shipped_at >= DATE '2025-11-01' AND s.shipped_at < DATE '2026-01-01' THEN 'collapse'
    END AS period
  FROM shipments s
  JOIN fulfillments f ON s.fulfillment_id = f.id
  JOIN orders o ON f.order_id = o.id
  JOIN addresses a ON o.shipping_address_id = a.id
  JOIN countries c ON a.country_id = c.id
  WHERE s.carrier = 'Albion Parcel'
    AND s.shipped_at >= DATE '2025-01-01'
    AND s.shipped_at < DATE '2026-01-01'
),
agg AS (
  SELECT
    destination_country,
    period,
    COUNT(*) AS shipments_shipped,
    COUNT(*) FILTER (WHERE delivered_at IS NOT NULL) AS delivered_count,
    AVG(transit_days) FILTER (WHERE delivered_at IS NOT NULL) AS avg_transit_days,
    COUNT(*) FILTER (WHERE delivered_at IS NOT NULL AND transit_days <= 3) AS within3
  FROM base
  WHERE period IS NOT NULL
  GROUP BY destination_country, period
),
totals AS (
  SELECT period, SUM(shipments_shipped) AS total_shipments
  FROM agg
  GROUP BY period
),
qualified AS (
  SELECT destination_country
  FROM agg
  GROUP BY destination_country
  HAVING MAX(shipments_shipped) >= 15
)
SELECT
  a.destination_country,
  a.period,
  a.shipments_shipped,
  ROUND(a.avg_transit_days::numeric, 2) AS avg_transit_days,
  ROUND(100.0 * a.within3 / NULLIF(a.delivered_count, 0), 1) AS pct_within_3_days,
  ROUND(100.0 * a.shipments_shipped / t.total_shipments, 1) AS share_of_period_shipments_pct
FROM agg a
JOIN totals t ON a.period = t.period
JOIN qualified q ON q.destination_country = a.destination_country
ORDER BY a.destination_country ASC, a.period ASC
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: destination_country, period, shipments_shipped, avg_transit_days, pct_within_3_days, share_of_period_shipments_pct
    • OmarQuery compiles and returns the expected columns. Submitting.
    • Omar calls submit
      WITH base AS (
  SELECT
    s.id,
    s.shipped_at,
    s.delivered_at,
    c.name AS destination_country,
    (s.delivered_at::date - s.shipped_at::date) AS transit_days,
    CASE
      WHEN s.shipped_at >= DATE '2025-01-01' AND s.shipped_at < DATE '2025-11-01' THEN 'baseline'
      WHEN s.shipped_at >= DATE '2025-11-01' AND s.shipped_at < DATE '2026-01-01' THEN 'collapse'
    END AS period
  FROM shipments s
  JOIN fulfillments f ON s.fulfillment_id = f.id
  JOIN orders o ON f.order_id = o.id
  JOIN addresses a ON o.shipping_address_id = a.id
  JOIN countries c ON a.country_id = c.id
  WHERE s.carrier = 'Albion Parcel'
    AND s.shipped_at >= DATE '2025-01-01'
    AND s.shipped_at < DATE '2026-01-01'
),
agg AS (
  SELECT
    destination_country,
    period,
    COUNT(*) AS shipments_shipped,
    COUNT(*) FILTER (WHERE delivered_at IS NOT NULL) AS delivered_count,
    AVG(transit_days) FILTER (WHERE delivered_at IS NOT NULL) AS avg_transit_days,
    COUNT(*) FILTER (WHERE delivered_at IS NOT NULL AND transit_days <= 3) AS within3
  FROM base
  WHERE period IS NOT NULL
  GROUP BY destination_country, period
),
totals AS (
  SELECT period, SUM(shipments_shipped) AS total_shipments
  FROM agg
  GROUP BY period
),
qualified AS (
  SELECT destination_country
  FROM agg
  GROUP BY destination_country
  HAVING MAX(shipments_shipped) >= 15
)
SELECT
  a.destination_country,
  a.period,
  a.shipments_shipped,
  ROUND(a.avg_transit_days::numeric, 2) AS avg_transit_days,
  ROUND(100.0 * a.within3 / NULLIF(a.delivered_count, 0), 1) AS pct_within_3_days,
  ROUND(100.0 * a.shipments_shipped / t.total_shipments, 1) AS share_of_period_shipments_pct
FROM agg a
JOIN totals t ON a.period = t.period
JOIN qualified q ON q.destination_country = a.destination_country
ORDER BY a.destination_country ASC, a.period ASC
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • OmarSubmitted. The report gives one row per destination country × period (baseline/collapse) for Albion Parcel shipments, with shipment counts, average transit days and on-time share computed on the delivered subset, and each country's share of that period's total Albion volume — restricted to countries with at least 15 shipments in either period to filter out noise.

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

    Omar's transcript (267 KB) · Lena Hartmann's transcript (921 KB) · Priya Raman's transcript (266 KB) · the night's plan (4 KB) · the night's reports (13 KB) · the week's memo (4 KB)