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

Order-level delivery reliability: Albion Parcel vs rest, baseline vs collapse, 2025

“How much of the November-December delivery slowdown is specifically attributable to Albion Parcel, versus everything 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 SQLInesThinks in window functions: ranks, running totals, LAG/LEAD for comparisons, percent-of-total.

Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told

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
Lena Hartmann plans the night
The analyst · week 2, night 3, request 2 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: Albion Parcel vs rest, baseline vs collapse, 2025
Question
How much of the November-December delivery slowdown is specifically attributable to Albion Parcel, versus everything else?
One row per
carrier_group ('Albion Parcel' vs 'All other carriers') and period ('baseline': placed_at Jan-Oct 2025; 'collapse': placed_at Nov-Dec 2025)
Columns
- carrier_group
- period
- orders_fully_delivered
- avg_delivery_days
- pct_within_5_days
- share_of_period_orders_pct

Ines 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 elig_orders AS (
  SELECT o.id AS order_id, o.placed_at
  FROM orders o
  WHERE o.status = 'fulfilled'
    AND o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
),
fulfillment_counts AS (
  SELECT f.order_id, COUNT(*) AS n_fulfillments
  FROM fulfillments f
  JOIN elig_orders eo ON eo.order_id = f.order_id
  GROUP BY f.order_id
),
single_fulfillment AS (
  SELECT f.id AS fulfillment_id, f.order_id
  FROM fulfillments f
  JOIN fulfillment_counts fc ON fc.order_id = f.order_id AND fc.n_fulfillments = 1
),
shipment_counts AS (
  SELECT s.fulfillment_id, COUNT(*) AS n_shipments
  FROM shipments s
  JOIN single_fulfillment sf ON sf.fulfillment_id = s.fulfillment_id
  GROUP BY s.fulfillment_id
),
single_shipment AS (
  SELECT s.fulfillment_id, s.carrier, s.delivered_at
  FROM shipments s
  JOIN shipment_counts sc ON sc.fulfillment_id = s.fulfillment_id AND sc.n_shipments = 1
),
qualified AS (
  SELECT eo.order_id, eo.placed_at, ss.carrier, ss.delivered_at
  FROM elig_orders eo
  JOIN single_fulfillment sf ON sf.order_id = eo.order_id
  JOIN single_shipment ss ON ss.fulfillment_id = sf.fulfillment_id
  WHERE ss.delivered_at IS NOT NULL
),
labeled AS (
  SELECT
    order_id,
    CASE WHEN placed_at < DATE '2025-11-01' THEN 'baseline' ELSE 'collapse' END AS period,
    CASE WHEN carrier = 'Albion Parcel' THEN 'Albion Parcel' ELSE 'All other carriers' END AS carrier_group,
    EXTRACT(EPOCH FROM (delivered_at - placed_at)) / 86400.0 AS delivery_days
  FROM qualified
),
agg AS (
  SELECT
    period,
    carrier_group,
    COUNT(*) AS orders_fully_delivered,
    AVG(delivery_days) AS avg_delivery_days,
    100.0 * SUM(CASE WHEN delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*) AS pct_within_5_days
  FROM labeled
  GROUP BY period, carrier_group
)
SELECT
  carrier_group,
  period,
  orders_fully_delivered,
  ROUND(avg_delivery_days, 2) AS avg_delivery_days,
  ROUND(pct_within_5_days, 1) AS pct_within_5_days,
  ROUND(100.0 * orders_fully_delivered / SUM(orders_fully_delivered) OVER (PARTITION BY period), 1) AS share_of_period_orders_pct
FROM agg
ORDER BY period ASC, 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, for each of the two 2025 periods (baseline Jan–Oct, collapse Nov–Dec), delivery metrics split into 'Albion Parcel' vs 'All other carriers', using orders that are fulfilled with exactly one fulfillment and exactly one shipment with a non-null delivered_at — matching the same single-carrier attribution rule as the prior weak-carriers-vs-rest report.”

The report comes back overnight

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

carrier_group      | period   | orders_fully_delivered | avg_delivery_days | pct_within_5_days | share_of_period_orders_pct
-------------------+----------+------------------------+-------------------+-------------------+---------------------------
Albion Parcel      | baseline |                   2052 |               3.4 |              92.3 |                       13.3
All other carriers | baseline |                  13409 |              3.97 |              78.9 |                       86.7
Albion Parcel      | collapse |                    512 |              9.82 |              31.3 |                       10.1
All other carriers | collapse |                   4560 |              3.85 |              80.0 |                       89.9
(4 rows)

What came of it
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
- q242Order-level delivery reliability by carrier group and quarter, 2025 (unified source)
It builds on:
- q232Albion Parcel performance by destination country, baseline vs collapse
- q233Albion Parcel performance by origin warehouse, baseline vs collapse

It becomes benchmark query q239

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

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 elig_orders AS (  SELECT o.id AS order_id, o.placed_at  SELECT o.id AS order_id, o.placed_at AS placed_at  FROM orders o  WHERE o.status = 'fulfilled'    AND o.placed_at >= DATE '2025-01-01'    AND o.placed_at < DATE '2026-01-01'),fulfillment_counts AS (  SELECT f.order_id, COUNT(*) AS n_fulfillments  SELECT f.order_id AS order_id, COUNT(*) AS n_fulfillments  FROM fulfillments f  JOIN elig_orders eo ON eo.order_id = f.order_id  GROUP BY f.order_id),single_fulfillment AS (  SELECT f.id AS fulfillment_id, f.order_id  SELECT f.id AS fulfillment_id, f.order_id AS order_id  FROM fulfillments f  JOIN fulfillment_counts fc ON fc.order_id = f.order_id AND fc.n_fulfillments = 1),shipment_counts AS (  SELECT s.fulfillment_id, COUNT(*) AS n_shipments  SELECT s.fulfillment_id AS fulfillment_id, COUNT(*) AS n_shipments  FROM shipments s  JOIN single_fulfillment sf ON sf.fulfillment_id = s.fulfillment_id  GROUP BY s.fulfillment_id),single_shipment AS (  SELECT s.fulfillment_id, s.carrier, s.delivered_at  SELECT s.fulfillment_id AS fulfillment_id, s.carrier AS carrier, s.delivered_at AS delivered_at  FROM shipments s  JOIN shipment_counts sc ON sc.fulfillment_id = s.fulfillment_id AND sc.n_shipments = 1),qualified AS (  SELECT eo.order_id, eo.placed_at, ss.carrier, ss.delivered_at  SELECT eo.order_id AS order_id, eo.placed_at AS placed_at, ss.carrier AS carrier, ss.delivered_at AS delivered_at  FROM elig_orders eo  JOIN single_fulfillment sf ON sf.order_id = eo.order_id  JOIN single_shipment ss ON ss.fulfillment_id = sf.fulfillment_id  WHERE ss.delivered_at IS NOT NULL),labeled AS (  SELECT    order_id,    CASE WHEN placed_at < DATE '2025-11-01' THEN 'baseline' ELSE 'collapse' END AS period,    CASE WHEN carrier = 'Albion Parcel' THEN 'Albion Parcel' ELSE 'All other carriers' END AS carrier_group,    EXTRACT(EPOCH FROM (delivered_at - placed_at)) / 86400.0 AS delivery_days    {{seconds_between(placed_at, delivered_at)}} / 86400.0 AS delivery_days  FROM qualified),agg AS (  SELECT    period,    carrier_group,    COUNT(*) AS orders_fully_delivered,    AVG(delivery_days) AS avg_delivery_days,    100.0 * SUM(CASE WHEN delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*) AS pct_within_5_days  FROM labeled  GROUP BY period, carrier_group)SELECT  carrier_group,  period,  orders_fully_delivered,  ROUND(avg_delivery_days, 2) AS avg_delivery_days,  ROUND(pct_within_5_days, 1) AS pct_within_5_days,  ROUND(100.0 * orders_fully_delivered / SUM(orders_fully_delivered) OVER (PARTITION BY period), 1) AS share_of_period_orders_pct  avg_delivery_days AS avg_delivery_days,  pct_within_5_days AS pct_within_5_days,  100.0 * orders_fully_delivered / SUM(orders_fully_delivered) OVER (PARTITION BY period) AS share_of_period_orders_pctFROM aggORDER BY period ASC, carrier_group ASCORDER BY period ASC NULLS LAST, carrier_group ASC NULLS LAST

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

WITH elig_orders AS (
  SELECT o.id AS order_id, o.placed_at AS placed_at
  FROM orders o
  WHERE o.status = 'fulfilled'
    AND o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
),
fulfillment_counts AS (
  SELECT f.order_id AS order_id, COUNT(*) AS n_fulfillments
  FROM fulfillments f
  JOIN elig_orders eo ON eo.order_id = f.order_id
  GROUP BY f.order_id
),
single_fulfillment AS (
  SELECT f.id AS fulfillment_id, f.order_id AS order_id
  FROM fulfillments f
  JOIN fulfillment_counts fc ON fc.order_id = f.order_id AND fc.n_fulfillments = 1
),
shipment_counts AS (
  SELECT s.fulfillment_id AS fulfillment_id, COUNT(*) AS n_shipments
  FROM shipments s
  JOIN single_fulfillment sf ON sf.fulfillment_id = s.fulfillment_id
  GROUP BY s.fulfillment_id
),
single_shipment AS (
  SELECT s.fulfillment_id AS fulfillment_id, s.carrier AS carrier, s.delivered_at AS delivered_at
  FROM shipments s
  JOIN shipment_counts sc ON sc.fulfillment_id = s.fulfillment_id AND sc.n_shipments = 1
),
qualified AS (
  SELECT eo.order_id AS order_id, eo.placed_at AS placed_at, ss.carrier AS carrier, ss.delivered_at AS delivered_at
  FROM elig_orders eo
  JOIN single_fulfillment sf ON sf.order_id = eo.order_id
  JOIN single_shipment ss ON ss.fulfillment_id = sf.fulfillment_id
  WHERE ss.delivered_at IS NOT NULL
),
labeled AS (
  SELECT
    order_id,
    CASE WHEN placed_at < DATE '2025-11-01' THEN 'baseline' ELSE 'collapse' END AS period,
    CASE WHEN carrier = 'Albion Parcel' THEN 'Albion Parcel' ELSE 'All other carriers' END AS carrier_group,
    CAST(EXTRACT(EPOCH FROM (delivered_at - placed_at)) AS NUMERIC(18,6)) / 86400.0 AS delivery_days
  FROM qualified
),
agg AS (
  SELECT
    period,
    carrier_group,
    COUNT(*) AS orders_fully_delivered,
    AVG(delivery_days) AS avg_delivery_days,
    100.0 * SUM(CASE WHEN delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*) AS pct_within_5_days
  FROM labeled
  GROUP BY period, carrier_group
)
SELECT
  carrier_group,
  period,
  orders_fully_delivered,
  avg_delivery_days AS avg_delivery_days,
  pct_within_5_days AS pct_within_5_days,
  100.0 * orders_fully_delivered / SUM(orders_fully_delivered) OVER (PARTITION BY period) AS share_of_period_orders_pct
FROM agg
ORDER BY period ASC NULLS LAST, carrier_group ASC NULLS LAST

WITH elig_orders AS (
  SELECT o.id AS order_id, o.placed_at AS placed_at
  FROM orders o
  WHERE o.status = 'fulfilled'
    AND o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
),
fulfillment_counts AS (
  SELECT f.order_id AS order_id, COUNT(*) AS n_fulfillments
  FROM fulfillments f
  JOIN elig_orders eo ON eo.order_id = f.order_id
  GROUP BY f.order_id
),
single_fulfillment AS (
  SELECT f.id AS fulfillment_id, f.order_id AS order_id
  FROM fulfillments f
  JOIN fulfillment_counts fc ON fc.order_id = f.order_id AND fc.n_fulfillments = 1
),
shipment_counts AS (
  SELECT s.fulfillment_id AS fulfillment_id, COUNT(*) AS n_shipments
  FROM shipments s
  JOIN single_fulfillment sf ON sf.fulfillment_id = s.fulfillment_id
  GROUP BY s.fulfillment_id
),
single_shipment AS (
  SELECT s.fulfillment_id AS fulfillment_id, s.carrier AS carrier, s.delivered_at AS delivered_at
  FROM shipments s
  JOIN shipment_counts sc ON sc.fulfillment_id = s.fulfillment_id AND sc.n_shipments = 1
),
qualified AS (
  SELECT eo.order_id AS order_id, eo.placed_at AS placed_at, ss.carrier AS carrier, ss.delivered_at AS delivered_at
  FROM elig_orders eo
  JOIN single_fulfillment sf ON sf.order_id = eo.order_id
  JOIN single_shipment ss ON ss.fulfillment_id = sf.fulfillment_id
  WHERE ss.delivered_at IS NOT NULL
),
labeled AS (
  SELECT
    order_id,
    CASE WHEN placed_at < DATE '2025-11-01' THEN 'baseline' ELSE 'collapse' END AS period,
    CASE WHEN carrier = 'Albion Parcel' THEN 'Albion Parcel' ELSE 'All other carriers' END AS carrier_group,
    CAST(divideDecimal(toDecimal128(dateDiff('microsecond', placed_at, delivered_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6))) / 86400.0 AS delivery_days
  FROM qualified
),
agg AS (
  SELECT
    period,
    carrier_group,
    COUNT(*) AS orders_fully_delivered,
    AVG(delivery_days) AS avg_delivery_days,
    100.0 * SUM(CASE WHEN delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*) AS pct_within_5_days
  FROM labeled
  GROUP BY period, carrier_group
)
SELECT
  carrier_group,
  period,
  orders_fully_delivered,
  avg_delivery_days AS avg_delivery_days,
  pct_within_5_days AS pct_within_5_days,
  100.0 * orders_fully_delivered / SUM(orders_fully_delivered) OVER (PARTITION BY period) AS share_of_period_orders_pct
FROM agg
ORDER BY period ASC NULLS LAST, carrier_group ASC NULLS LAST

Scale factor 1000, c8a.24xlarge, after the last load. q239 on the start page, with every configuration.

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

Ines's whole session, 7 turns

The request, as Ines received it

Report request from Lena Hartmann (analyst for the Chief Operating Officer):

Title: Order-level delivery reliability: Albion Parcel vs rest, baseline vs collapse, 2025
Question: How much of the November-December delivery slowdown is specifically attributable to Albion Parcel, versus everything else?
Grain: one row per carrier_group ('Albion Parcel' vs 'All other carriers') and period ('baseline': placed_at Jan-Oct 2025; 'collapse': placed_at Nov-Dec 2025)
Columns:
- carrier_group
- period
- orders_fully_delivered
- avg_delivery_days
- pct_within_5_days
- share_of_period_orders_pct
Details: Same scoping and single-carrier attribution rule as the weak-carriers-vs-rest report (orders placed_at in 2025, status='fulfilled', exactly one fulfillment with exactly one shipment, delivered_at not null). period is based on orders.placed_at (baseline = 2025-01-01 to 2025-10-31; collapse = 2025-11-01 to 2025-12-31). carrier_group = 'Albion Parcel' if shipments.carrier = 'Albion Parcel', else 'All other carriers'. Round avg_delivery_days to 2 decimals, pct columns to 1 decimal. Order by period ascending, then carrier_group ascending. Expect 4 rows.

Check your query with the warehouse's dry_run tool, then hand it in with submit.

Ines calls dry_run

WITH elig_orders AS (
  SELECT o.id AS order_id, o.placed_at
  FROM orders o
  WHERE o.status = 'fulfilled'
    AND o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
),
fulfillment_counts AS (
  SELECT f.order_id, COUNT(*) AS n_fulfillments
  FROM fulfillments f
  JOIN elig_orders eo ON eo.order_id = f.order_id
  GROUP BY f.order_id
),
single_fulfillment AS (
  SELECT f.id AS fulfillment_id, f.order_id
  FROM fulfillments f
  JOIN fulfillment_counts fc ON fc.order_id = f.order_id AND fc.n_fulfillments = 1
),
shipment_counts AS (
  SELECT s.fulfillment_id, COUNT(*) AS n_shipments
  FROM shipments s
  JOIN single_fulfillment sf ON sf.fulfillment_id = s.fulfillment_id
  GROUP BY s.fulfillment_id
),
single_shipment AS (
  SELECT s.fulfillment_id, s.carrier, s.delivered_at
  FROM shipments s
  JOIN shipment_counts sc ON sc.fulfillment_id = s.fulfillment_id AND sc.n_shipments = 1
),
qualified AS (
  SELECT eo.order_id, eo.placed_at, ss.carrier, ss.delivered_at
  FROM elig_orders eo
  JOIN single_fulfillment sf ON sf.order_id = eo.order_id
  JOIN single_shipment ss ON ss.fulfillment_id = sf.fulfillment_id
  WHERE ss.delivered_at IS NOT NULL
),
labeled AS (
  SELECT
    order_id,
    CASE WHEN placed_at < DATE '2025-11-01' THEN 'baseline' ELSE 'collapse' END AS period,
    CASE WHEN carrier = 'Albion Parcel' THEN 'Albion Parcel' ELSE 'All other carriers' END AS carrier_group,
    EXTRACT(EPOCH FROM (delivered_at - placed_at)) / 86400.0 AS delivery_days
  FROM qualified
),
agg AS (
  SELECT
    period,
    carrier_group,
    COUNT(*) AS orders_fully_delivered,
    AVG(delivery_days) AS avg_delivery_days,
    100.0 * SUM(CASE WHEN delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*) AS pct_within_5_days
  FROM labeled
  GROUP BY period, carrier_group
)
SELECT
  carrier_group,
  period,
  orders_fully_delivered,
  ROUND(avg_delivery_days, 2) AS avg_delivery_days,
  ROUND(pct_within_5_days, 1) AS pct_within_5_days,
  ROUND(100.0 * orders_fully_delivered / SUM(orders_fully_delivered) OVER (PARTITION BY period), 1) AS share_of_period_orders_pct
FROM agg
ORDER BY period ASC, carrier_group ASC

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: carrier_group, period, orders_fully_delivered, avg_delivery_days, pct_within_5_days, share_of_period_orders_pct

InesQuery compiles and returns the expected columns. Submitting.

Ines calls submit

WITH elig_orders AS (
  SELECT o.id AS order_id, o.placed_at
  FROM orders o
  WHERE o.status = 'fulfilled'
    AND o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
),
fulfillment_counts AS (
  SELECT f.order_id, COUNT(*) AS n_fulfillments
  FROM fulfillments f
  JOIN elig_orders eo ON eo.order_id = f.order_id
  GROUP BY f.order_id
),
single_fulfillment AS (
  SELECT f.id AS fulfillment_id, f.order_id
  FROM fulfillments f
  JOIN fulfillment_counts fc ON fc.order_id = f.order_id AND fc.n_fulfillments = 1
),
shipment_counts AS (
  SELECT s.fulfillment_id, COUNT(*) AS n_shipments
  FROM shipments s
  JOIN single_fulfillment sf ON sf.fulfillment_id = s.fulfillment_id
  GROUP BY s.fulfillment_id
),
single_shipment AS (
  SELECT s.fulfillment_id, s.carrier, s.delivered_at
  FROM shipments s
  JOIN shipment_counts sc ON sc.fulfillment_id = s.fulfillment_id AND sc.n_shipments = 1
),
qualified AS (
  SELECT eo.order_id, eo.placed_at, ss.carrier, ss.delivered_at
  FROM elig_orders eo
  JOIN single_fulfillment sf ON sf.order_id = eo.order_id
  JOIN single_shipment ss ON ss.fulfillment_id = sf.fulfillment_id
  WHERE ss.delivered_at IS NOT NULL
),
labeled AS (
  SELECT
    order_id,
    CASE WHEN placed_at < DATE '2025-11-01' THEN 'baseline' ELSE 'collapse' END AS period,
    CASE WHEN carrier = 'Albion Parcel' THEN 'Albion Parcel' ELSE 'All other carriers' END AS carrier_group,
    EXTRACT(EPOCH FROM (delivered_at - placed_at)) / 86400.0 AS delivery_days
  FROM qualified
),
agg AS (
  SELECT
    period,
    carrier_group,
    COUNT(*) AS orders_fully_delivered,
    AVG(delivery_days) AS avg_delivery_days,
    100.0 * SUM(CASE WHEN delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*) AS pct_within_5_days
  FROM labeled
  GROUP BY period, carrier_group
)
SELECT
  carrier_group,
  period,
  orders_fully_delivered,
  ROUND(avg_delivery_days, 2) AS avg_delivery_days,
  ROUND(pct_within_5_days, 1) AS pct_within_5_days,
  ROUND(100.0 * orders_fully_delivered / SUM(orders_fully_delivered) OVER (PARTITION BY period), 1) AS share_of_period_orders_pct
FROM agg
ORDER BY period ASC, carrier_group ASC

The warehouse answers

Submitted to tonight's batch. Your work on this request is done.

InesSubmitted. The report computes, for each of the two 2025 periods (baseline Jan–Oct, collapse Nov–Dec), delivery metrics split into 'Albion Parcel' vs 'All other carriers', using orders that are fulfilled with exactly one fulfillment and exactly one shipment with a non-null delivered_at — matching the same single-carrier attribution rule as the prior weak-carriers-vs-rest report.

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

Ines's transcript (271 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)

Order-level delivery reliability: Albion Parcel vs rest, baseline vs collapse, 2025

Priya Raman writes to Lena Hartmann

Lena Hartmann plans the night

Ines writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q239

Everything, as recorded