Queries · q235 · Chief Operating Officer · week 2, night 2
“What is each carrier's full-year transit reliability and volume share, so I can estimate how much overall unreliability would disappear if the weak carriers matched the good ones?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
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:
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
The analyst · week 2, night 2, request 1 of 3
Night 1 resolved priority #1 cleanly, and the answer is more nuanced than "weak carrier = weak carrier": (a) Albion Parcel's collapse is NOT localized -- it hit both countries it serves (UK and Ireland) and literally every origin warehouse/merchant it ships for, uniformly, the moment November started (pct-within-3-days fell from ~70-93% to ~0-40% everywhere at once, transit days jumping from ~2-2.5 to 8-17 days across the board). That rules out "one bad depot" or "one bad merchant" and points to a systemic capacity failure at Albion itself -- useful to know before the carrier conversation, but it's a harder story (a carrier that broke under load everywhere), not an easy single-fix. (b) Landfall Freight is uniformly bad across all 18 countries it serves (14-38% within 3 days everywhere, no bright spots) -- genuinely a "this carrier can't be trusted" case. (c) Lowlands Express is the interesting exception: it's actually solid on its home turf (Netherlands 79.9%, Belgium 69.3%, France 63.0% within 3 days -- 64.5% of its volume) but poor everywhere else (15-36% within 3 days on the remaining 35.5%). That's a lane-reassignment fix, not a carrier breakup. Tonight I'm covering priority #2 (the reliability ceiling, using a clean full-year carrier table so I can compute the weighted improvement by hand) and starting on #3 (a non-censored PO view, restricting to purchase orders old enough to have fully played out) and #4 (whether the apparel/footwear picking discrepancies Fatima's team flagged show up in delivery shortfalls, not just inventory accounting).
The engineer · 1 dry run on the empty schema, then the submission
SELECT carrier, COUNT(*) AS shipments_shipped, COUNT(delivered_at) AS shipments_delivered, ROUND(AVG(CASE WHEN delivered_at IS NOT NULL THEN (delivered_at::date - shipped_at::date) END)::numeric, 2) AS avg_transit_days, ROUND((PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY (delivered_at::date - shipped_at::date)) FILTER (WHERE delivered_at IS NOT NULL))::numeric, 2) AS median_transit_days, ROUND(100.0 * SUM(CASE WHEN delivered_at IS NOT NULL AND (delivered_at::date - shipped_at::date) <= 3 THEN 1 ELSE 0 END) / NULLIF(COUNT(delivered_at), 0), 1) AS pct_within_3_days, ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS share_of_total_shipments_pct FROM shipments WHERE shipped_at >= DATE '2025-01-01' AND shipped_at < DATE '2026-01-01' GROUP BY carrier ORDER BY shipments_shipped DESC, carrier ASC
“Query compiles and produces exactly the requested columns. Submitting now.”
“Submitted. The report gives one row per carrier for full-year 2025 with shipped/delivered counts, average and median transit days, percent delivered within 3 days, and each carrier's share of total shipment volume — ordered by volume descending, ready for Lena to spot which weak carriers to benchmark against the best performers.”
As Lena Hartmann saw it, from the small dataset the agents worked on
carrier | shipments_shipped | shipments_delivered | avg_transit_days | median_transit_days | pct_within_3_days | share_of_total_shipments_pct ---------------------+-------------------+---------------------+------------------+---------------------+-------------------+----------------------------- Strombogen Kurier | 2972 | 2944 | 2.14 | 2.00 | 93.8 | 13.9 Albion Parcel | 2676 | 2661 | 3.46 | 2.00 | 80.7 | 12.5 Lowlands Express | 2561 | 2524 | 3.56 | 3.00 | 57.9 | 11.9 Liberty Ground | 2343 | 2319 | 2.23 | 2.00 | 90.1 | 10.9 Alpenpaket | 2022 | 2014 | 2.17 | 2.00 | 92.5 | 9.4 Landfall Freight | 1906 | 1878 | 4.89 | 5.00 | 25.7 | 8.9 Trailhead Express | 1879 | 1860 | 2.46 | 2.00 | 83.7 | 8.8 Skerrylane Logistics | 1124 | 1116 | 2.50 | 2.00 | 81.0 | 5.2 Kennet Freight | 909 | 898 | 2.31 | 2.00 | 88.1 | 4.2 Polder Post | 902 | 892 | 2.57 | 2.00 | 79.0 | 4.2 Regent Parcels | 798 | 789 | 2.30 | 2.00 | 88.7 | 3.7 Prairie Line Express | 748 | 741 | 2.54 | 2.00 | 81.5 | 3.5 Liffey Couriers | 323 | 322 | 2.71 | 2.00 | 76.1 | 1.5 Gallia Colis | 284 | 280 | 2.82 | 2.00 | 74.6 | 1.3 (14 rows)
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
It builds on:
Changed only where the benchmark needs the same result on every system
SELECT carrier, COUNT(*) AS shipments_shipped, COUNT(delivered_at) AS shipments_delivered, ROUND(AVG(CASE WHEN delivered_at IS NOT NULL THEN (delivered_at::date - shipped_at::date) END)::numeric, 2) AS avg_transit_days, ROUND((PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY (delivered_at::date - shipped_at::date)) FILTER (WHERE delivered_at IS NOT NULL))::numeric, 2) AS median_transit_days, ROUND(100.0 * SUM(CASE WHEN delivered_at IS NOT NULL AND (delivered_at::date - shipped_at::date) <= 3 THEN 1 ELSE 0 END) / NULLIF(COUNT(delivered_at), 0), 1) AS pct_within_3_days, ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS share_of_total_shipments_pct AVG(CAST(CASE WHEN delivered_at IS NOT NULL THEN ({{date_diff_days(shipped_at, delivered_at)}}) END AS NUMERIC(18,6))) AS avg_transit_days, ({{percentile_cont_filter(0.5, {{date_diff_days(shipped_at, delivered_at)}}, delivered_at IS NOT NULL)}}) AS median_transit_days, 100.0 * SUM(CASE WHEN delivered_at IS NOT NULL AND ({{date_diff_days(shipped_at, delivered_at)}}) <= 3 THEN 1 ELSE 0 END) / NULLIF(COUNT(delivered_at), 0) AS pct_within_3_days, 100.0 * COUNT(*) / SUM(COUNT(*)) OVER () AS share_of_total_shipments_pctFROM shipmentsWHERE shipped_at >= DATE '2025-01-01' AND shipped_at < DATE '2026-01-01'GROUP BY carrierORDER BY shipments_shipped DESC, carrier ASCORDER BY shipments_shipped DESC NULLS FIRST, carrier ASC NULLS LAST
SELECT carrier, COUNT(*) AS shipments_shipped, COUNT(delivered_at) AS shipments_delivered, AVG(CAST(CASE WHEN delivered_at IS NOT NULL THEN (DATE_DIFF('day', shipped_at, delivered_at)) END AS NUMERIC(18,6))) AS avg_transit_days, (PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY DATE_DIFF('day', shipped_at, delivered_at)) FILTER (WHERE delivered_at IS NOT NULL)) AS median_transit_days, 100.0 * SUM(CASE WHEN delivered_at IS NOT NULL AND (DATE_DIFF('day', shipped_at, delivered_at)) <= 3 THEN 1 ELSE 0 END) / NULLIF(COUNT(delivered_at), 0) AS pct_within_3_days, 100.0 * COUNT(*) / SUM(COUNT(*)) OVER () AS share_of_total_shipments_pct FROM shipments WHERE shipped_at >= DATE '2025-01-01' AND shipped_at < DATE '2026-01-01' GROUP BY carrier ORDER BY shipments_shipped DESC NULLS FIRST, carrier ASC NULLS LAST
SELECT carrier, COUNT(*) AS shipments_shipped, COUNT(delivered_at) AS shipments_delivered, AVG(CAST(CASE WHEN delivered_at IS NOT NULL THEN ((CAST(delivered_at AS DATE) - CAST(shipped_at AS DATE))) END AS NUMERIC(18,6))) AS avg_transit_days, (PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CASE WHEN delivered_at IS NOT NULL THEN (CAST(delivered_at AS DATE) - CAST(shipped_at AS DATE)) END)) AS median_transit_days, 100.0 * SUM(CASE WHEN delivered_at IS NOT NULL AND ((CAST(delivered_at AS DATE) - CAST(shipped_at AS DATE))) <= 3 THEN 1 ELSE 0 END) / NULLIF(COUNT(delivered_at), 0) AS pct_within_3_days, 100.0 * COUNT(*) / SUM(COUNT(*)) OVER () AS share_of_total_shipments_pct FROM shipments WHERE shipped_at >= DATE '2025-01-01' AND shipped_at < DATE '2026-01-01' GROUP BY carrier ORDER BY shipments_shipped DESC NULLS FIRST, carrier ASC NULLS LAST
SELECT carrier, COUNT(*) AS shipments_shipped, COUNT(delivered_at) AS shipments_delivered, AVG(CAST(CASE WHEN delivered_at IS NOT NULL THEN ((CAST(delivered_at AS DATE) - CAST(shipped_at AS DATE))) END AS NUMERIC(18,6))) AS avg_transit_days, (PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY (CAST(delivered_at AS DATE) - CAST(shipped_at AS DATE))) FILTER (WHERE delivered_at IS NOT NULL)) AS median_transit_days, 100.0 * SUM(CASE WHEN delivered_at IS NOT NULL AND ((CAST(delivered_at AS DATE) - CAST(shipped_at AS DATE))) <= 3 THEN 1 ELSE 0 END) / NULLIF(COUNT(delivered_at), 0) AS pct_within_3_days, 100.0 * COUNT(*) / SUM(COUNT(*)) OVER () AS share_of_total_shipments_pct FROM shipments WHERE shipped_at >= DATE '2025-01-01' AND shipped_at < DATE '2026-01-01' GROUP BY carrier ORDER BY shipments_shipped DESC NULLS FIRST, carrier ASC NULLS LAST
SELECT carrier, COUNT(*) AS shipments_shipped, COUNT(delivered_at) AS shipments_delivered, AVG(CAST(CASE WHEN delivered_at IS NOT NULL THEN ((CAST(delivered_at AS DATE) - CAST(shipped_at AS DATE))) END AS NUMERIC(18,6))) AS avg_transit_days, (PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CAST((CAST(delivered_at AS DATE) - CAST(shipped_at AS DATE)) AS DOUBLE PRECISION)) FILTER (WHERE delivered_at IS NOT NULL)) AS median_transit_days, 100.0 * SUM(CASE WHEN delivered_at IS NOT NULL AND ((CAST(delivered_at AS DATE) - CAST(shipped_at AS DATE))) <= 3 THEN 1 ELSE 0 END) / NULLIF(COUNT(delivered_at), 0) AS pct_within_3_days, 100.0 * COUNT(*) / SUM(COUNT(*)) OVER () AS share_of_total_shipments_pct FROM shipments WHERE shipped_at >= DATE '2025-01-01' AND shipped_at < DATE '2026-01-01' GROUP BY carrier ORDER BY shipments_shipped DESC NULLS FIRST, carrier ASC NULLS LAST
SELECT carrier, COUNT(*) AS shipments_shipped, COUNT(delivered_at) AS shipments_delivered, AVG(CAST(CASE WHEN delivered_at IS NOT NULL THEN (dateDiff('day', shipped_at, delivered_at)) END AS NUMERIC(18,6))) AS avg_transit_days, (quantileExactInclusiveIf(0.5)(toFloat64(dateDiff('day', shipped_at, delivered_at)), delivered_at IS NOT NULL)) AS median_transit_days, 100.0 * SUM(CASE WHEN delivered_at IS NOT NULL AND (dateDiff('day', shipped_at, delivered_at)) <= 3 THEN 1 ELSE 0 END) / NULLIF(COUNT(delivered_at), 0) AS pct_within_3_days, 100.0 * COUNT(*) / SUM(COUNT(*)) OVER () AS share_of_total_shipments_pct FROM shipments WHERE shipped_at >= DATE '2025-01-01' AND shipped_at < DATE '2026-01-01' GROUP BY carrier ORDER BY shipments_shipped DESC NULLS FIRST, carrier ASC NULLS LAST
The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted
Report request from Lena Hartmann (analyst for the Chief Operating Officer): Title: Carrier performance, full year 2025 (complete) Question: What is each carrier's full-year transit reliability and volume share, so I can estimate how much overall unreliability would disappear if the weak carriers matched the good ones? Grain: one row per carrier, full year 2025 Columns: - carrier - shipments_shipped (shipped_at in 2025) - shipments_delivered - avg_transit_days - median_transit_days - pct_within_3_days - share_of_total_shipments_pct (this carrier's shipments / all carriers' shipments in 2025, x100) Details: Table: shipments. Scope: shipped_at between 2025-01-01 and 2025-12-31, all carriers, grouped by carrier (no row cap needed -- return every carrier, expect ~14 rows). transit_days = DATEDIFF(day, shipped_at, delivered_at), delivered subset only. Round day metrics to 2 decimals, pct columns to 1 decimal. Order by shipments_shipped descending, tie-break by carrier ascending. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runSELECT carrier, COUNT(*) AS shipments_shipped, COUNT(delivered_at) AS shipments_delivered, ROUND(AVG(CASE WHEN delivered_at IS NOT NULL THEN (delivered_at::date - shipped_at::date) END)::numeric, 2) AS avg_transit_days, ROUND((PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY (delivered_at::date - shipped_at::date)) FILTER (WHERE delivered_at IS NOT NULL))::numeric, 2) AS median_transit_days, ROUND(100.0 * SUM(CASE WHEN delivered_at IS NOT NULL AND (delivered_at::date - shipped_at::date) <= 3 THEN 1 ELSE 0 END) / NULLIF(COUNT(delivered_at), 0), 1) AS pct_within_3_days, ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS share_of_total_shipments_pct FROM shipments WHERE shipped_at >= DATE '2025-01-01' AND shipped_at < DATE '2026-01-01' GROUP BY carrier ORDER BY shipments_shipped DESC, carrier ASC
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: carrier, shipments_shipped, shipments_delivered, avg_transit_days, median_transit_days, pct_within_3_days, share_of_total_shipments_pct
submitSELECT carrier, COUNT(*) AS shipments_shipped, COUNT(delivered_at) AS shipments_delivered, ROUND(AVG(CASE WHEN delivered_at IS NOT NULL THEN (delivered_at::date - shipped_at::date) END)::numeric, 2) AS avg_transit_days, ROUND((PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY (delivered_at::date - shipped_at::date)) FILTER (WHERE delivered_at IS NOT NULL))::numeric, 2) AS median_transit_days, ROUND(100.0 * SUM(CASE WHEN delivered_at IS NOT NULL AND (delivered_at::date - shipped_at::date) <= 3 THEN 1 ELSE 0 END) / NULLIF(COUNT(delivered_at), 0), 1) AS pct_within_3_days, ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS share_of_total_shipments_pct FROM shipments WHERE shipped_at >= DATE '2025-01-01' AND shipped_at < DATE '2026-01-01' GROUP BY carrier ORDER BY shipments_shipped DESC, carrier ASC
Submitted to tonight's batch. Your work on this request is done.