Queries · q034 · Chief Operating Officer · week 1, night 4
“Which suppliers are chronically slow to deliver against purchase orders, relative to what they've promised?”
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 1
Subject: Getting my bearings on fulfillment — questions for this week
Hi Lena,
Thanks for taking these on. I'm new here and coming from the logistics side, so my first priority is understanding whether the delivery promise we make to shoppers actually holds up — that's the lens for most of what's below. Given the slot budget, here's what matters most, roughly in priority order:
What I'll use this for: I'm forming my first impressions of operational health in this area and want to walk into conversations with merchants and carrier partners armed with facts, not assumptions. I'd rather have a smaller number of really clear answers than a lot of surface-level ones — so if you need to cut scope to fit your slots this week, prioritize #1 and #2, and we can pick up the rest next week.
Thanks — looking forward to Friday's memo.
Priya
The analyst · week 1, night 4, request 1 of 3
Night 3 nailed down priority #3 cleanly, so I'm done there and pivoting all of tonight's slots to priority #4 (inventory/replenishment), which I haven't touched yet. Key night-3 findings for the memo: (a) the complete Q4 carrier table confirms three chronically-weak carriers -- Landfall Freight (13.6% within 3 days, worst all year), Lowlands Express (44.6%), and now Albion Parcel -- together carrying ~32% of Q4 volume; (b) Albion Parcel's monthly breakdown shows it was perfectly fine through October (75.7% within 3 days) then catastrophically broke in November (28.2%) and got worse, not better, in December (p90 blew out to 20.7 days, pct_delivered slipped to 94.2%) -- this is an acute, ongoing carrier failure, not a peak blip that self-resolved, and is the single most actionable finding for a carrier conversation; (c) the carrier-mix report confirms the merchant-level variance from Night 1 is almost entirely explained by carrier assignment, not the merchant or warehouse itself -- e.g. Grind Collective's Kennet Freight leg hits 88.7% within 3 days while its Landfall Freight leg on the very same business hits 19.4%; the "best" merchants simply route nearly all volume through the strong carriers (Strombogen Kurier, Liberty Ground, Alpenpaket, Trailhead Express), while "worst" merchants have meaningful share stuck on the weak ones. That resolves priority #3 well enough to write up without further queries. Tonight (last night) I'm spending all 3 slots on priority #4: whether replenishment from suppliers keeps pace (purchase order lead times, by supplier and by quarter to check for a peak-season backlog) and a current snapshot of stockout exposure by warehouse. I'm leaving returns (#5) for a future week per Priya's explicit prioritization.
The engineer · 1 dry run on the empty schema, then the submission
SELECT s.name AS supplier_name, s.lead_time_days AS lead_time_days_contracted, agg.pos_ordered, agg.pos_received, ROUND(agg.avg_actual_lead_days, 1) AS avg_actual_lead_days, ROUND(agg.pct_received_late, 1) AS pct_received_late, ROUND(agg.avg_days_late, 1) AS avg_days_late FROM suppliers s JOIN ( SELECT po.supplier_id, COUNT(*) AS pos_ordered, COUNT(po.received_at) AS pos_received, AVG(CASE WHEN po.received_at IS NOT NULL THEN (po.received_at::date - po.ordered_at::date) END) AS avg_actual_lead_days, 100.0 * SUM(CASE WHEN po.expected_at IS NOT NULL AND po.received_at IS NOT NULL AND po.received_at > po.expected_at THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN po.expected_at IS NOT NULL AND po.received_at IS NOT NULL THEN 1 ELSE 0 END), 0) AS pct_received_late, AVG(CASE WHEN po.expected_at IS NOT NULL AND po.received_at IS NOT NULL AND po.received_at > po.expected_at THEN (po.received_at::date - po.expected_at::date) END) AS avg_days_late FROM purchase_orders po WHERE po.ordered_at >= DATE '2025-01-01' AND po.ordered_at < DATE '2026-01-01' GROUP BY po.supplier_id HAVING COUNT(*) >= 10 ) agg ON agg.supplier_id = s.id ORDER BY agg.pos_ordered DESC, s.name ASC LIMIT 50
“Query compiles and returns the expected columns. Submitting.”
“Submitted. The report returns one row per supplier (suppliers with ≥10 POs ordered in 2025), with contracted lead time, PO volume, actual vs. contracted lead time, and the late-delivery rate/severity — ordered by PO volume so the highest-traffic suppliers surface first, tie-broken alphabetically.”
As Lena Hartmann saw it, from the small dataset the agents worked on
supplier_name | lead_time_days_contracted | pos_ordered | pos_received | avg_actual_lead_days | pct_received_late | avg_days_late ---------------------------------+---------------------------+-------------+--------------+----------------------+-------------------+-------------- Dalsgaard Roasters GmbH | 8 | 52 | 51 | 7.5 | 52.9 | 0.9 Hanbury Textiles Inc. | 7 | 52 | 51 | 7.5 | 52.9 | 1.2 Kuntai Textiles Co., Ltd. | 34 | 52 | 47 | 37.0 | 55.3 | 9.2 Norvik Roasters Ltd | 6 | 52 | 51 | 6.2 | 43.1 | 0.5 Tessaro Roasters GmbH | 8 | 52 | 51 | 7.7 | 47.1 | 0.8 Tessaro Textiles GmbH | 15 | 52 | 50 | 16.9 | 64.0 | 2.8 Weiyang Roasters Co., Ltd. | 37 | 52 | 45 | 40.6 | 46.7 | 12.0 Castelo Roasters GmbH | 14 | 51 | 50 | 16.4 | 56.0 | 3.6 Kuntai Roasters Sp. z o.o. | 12 | 51 | 49 | 14.3 | 46.9 | 3.0 Kuntai Textiles S.p.A. | 15 | 51 | 49 | 18.2 | 55.1 | 2.2 Weiyang Textiles Inc. | 4 | 51 | 50 | 4.8 | 74.0 | 0.8 Castelo Roasters B.V. | 8 | 50 | 48 | 8.6 | 60.4 | 0.8 Weiyang Textiles Co., Ltd. | 37 | 50 | 43 | 42.5 | 46.5 | 5.9 Oakmere Roasters Inc. | 7 | 48 | 47 | 6.9 | 55.3 | 0.5 Dalsgaard Textiles S.p.A. | 8 | 47 | 46 | 8.7 | 43.5 | 2.7 Tessaro Textiles Ltd | 9 | 47 | 46 | 8.6 | 50.0 | 0.8 Vireo Textiles Co., Ltd. | 28 | 47 | 40 | 39.9 | 80.0 | 16.0 Oakmere Cosmetics Ltd | 5 | 46 | 45 | 5.3 | 53.3 | 0.5 Tessaro Roasters Co., Ltd. | 43 | 46 | 38 | 52.8 | 39.5 | 11.2 Vireo Cosmetics Co., Ltd. | 42 | 46 | 39 | 50.0 | 41.0 | 8.1 Vireo Textiles Sp. z o.o. | 15 | 46 | 43 | 16.8 | 46.5 | 3.0 Dalsgaard Cosmetics GmbH | 14 | 45 | 44 | 14.7 | 50.0 | 3.5 Dalsgaard Textiles Ltd | 8 | 45 | 44 | 8.1 | 59.1 | 0.3 Montval Pet Foods Co., Ltd. | 31 | 45 | 40 | 35.5 | 42.5 | 7.6 Hanbury Roasters Co., Ltd. | 42 | 44 | 39 | 43.1 | 41.0 | 7.7 Oakmere Textiles B.V. | 4 | 44 | 44 | 4.4 | 40.9 | 0.8 Weiyang Cosmetics Ltd | 6 | 43 | 42 | 6.4 | 35.7 | 0.9 Haizhou Pet Foods GmbH | 9 | 42 | 42 | 11.0 | 52.4 | 1.6 Norvik Industrial Co., Ltd. | 32 | 42 | 38 | 37.7 | 52.6 | 5.4 Haizhou Roasters Co., Ltd. | 28 | 40 | 34 | 37.9 | 76.5 | 13.7 Hanbury Roasters B.V. | 15 | 40 | 37 | 16.6 | 48.6 | 2.1 Lucerna Pet Foods Ltd | 8 | 40 | 39 | 6.8 | 51.3 | 0.5 Lucerna Roasters Co., Ltd. | 35 | 40 | 35 | 41.7 | 51.4 | 6.8 Selwyn Homeware Co., Ltd. | 35 | 40 | 33 | 43.6 | 84.8 | 11.1 Tanaka-Ri Industrial S.p.A. | 16 | 39 | 37 | 18.4 | 45.9 | 2.8 Lucerna Textiles Co., Ltd. | 34 | 38 | 32 | 43.6 | 40.6 | 13.3 Oakmere Office Products B.V. | 11 | 38 | 36 | 14.1 | 77.8 | 4.7 Oakmere Homeware Co., Ltd. | 31 | 37 | 31 | 34.2 | 51.6 | 6.9 Tessaro Electronics GmbH | 6 | 37 | 37 | 7.0 | 70.3 | 1.0 Vireo Office Products Sp. z o.o. | 14 | 37 | 34 | 19.1 | 55.9 | 3.3 Weiyang Outdoor Gear Sp. z o.o. | 8 | 37 | 36 | 8.4 | 38.9 | 1.9 Dalsgaard Industrial Sp. z o.o. | 12 | 36 | 35 | 12.7 | 42.9 | 2.6 Selwyn Industrial Ltd | 5 | 36 | 35 | 4.5 | 42.9 | 0.9 Brenholt Textiles GmbH | 8 | 35 | 35 | 9.3 | 40.0 | 3.9 Vireo Homeware GmbH | 12 | 35 | 33 | 12.3 | 57.6 | 1.4 Dalsgaard Outdoor Gear Co., Ltd. | 28 | 34 | 31 | 34.1 | 51.6 | 4.4 Haizhou Textiles B.V. | 4 | 34 | 34 | 4.4 | 44.1 | 0.4 Hanbury Electronics Co., Ltd. | 43 | 34 | 28 | 48.1 | 67.9 | 11.2 Lucerna Roasters B.V. | 13 | 34 | 32 | 16.8 | 75.0 | 5.6 Vireo Office Products Ltd | 4 | 34 | 34 | 4.3 | 38.2 | 0.7 (50 rows)
Changed only where the benchmark needs the same result on every system
SELECT s.name AS supplier_name, s.lead_time_days AS lead_time_days_contracted, agg.pos_ordered, agg.pos_received, ROUND(agg.avg_actual_lead_days, 1) AS avg_actual_lead_days, ROUND(agg.pct_received_late, 1) AS pct_received_late, ROUND(agg.avg_days_late, 1) AS avg_days_late agg.pos_ordered AS pos_ordered, agg.pos_received AS pos_received, agg.avg_actual_lead_days AS avg_actual_lead_days, agg.pct_received_late AS pct_received_late, agg.avg_days_late AS avg_days_lateFROM suppliers sJOIN ( SELECT po.supplier_id, po.supplier_id AS supplier_id, COUNT(*) AS pos_ordered, COUNT(po.received_at) AS pos_received, AVG(CASE WHEN po.received_at IS NOT NULL THEN (po.received_at::date - po.ordered_at::date) END) AS avg_actual_lead_days, AVG(CAST(CASE WHEN po.received_at IS NOT NULL THEN ({{date_diff_days(po.ordered_at, po.received_at)}}) END AS NUMERIC(18,6))) AS avg_actual_lead_days, 100.0 * SUM(CASE WHEN po.expected_at IS NOT NULL AND po.received_at IS NOT NULL AND po.received_at > po.expected_at THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN po.expected_at IS NOT NULL AND po.received_at IS NOT NULL THEN 1 ELSE 0 END), 0) AS pct_received_late, AVG(CASE WHEN po.expected_at IS NOT NULL AND po.received_at IS NOT NULL AND po.received_at > po.expected_at THEN (po.received_at::date - po.expected_at::date) END) AS avg_days_late AVG(CAST(CASE WHEN po.expected_at IS NOT NULL AND po.received_at IS NOT NULL AND po.received_at > po.expected_at THEN ({{date_diff_days(po.expected_at, po.received_at)}}) END AS NUMERIC(18,6))) AS avg_days_late FROM purchase_orders po WHERE po.ordered_at >= DATE '2025-01-01' AND po.ordered_at < DATE '2026-01-01' GROUP BY po.supplier_id HAVING COUNT(*) >= 10) agg ON agg.supplier_id = s.idORDER BY agg.pos_ordered DESC, s.name ASCLIMIT 50ORDER BY agg.pos_ordered DESC NULLS FIRST, s.name ASC NULLS LAST, s.id NULLS LAST{{fetch_first(50)}}
SELECT s.name AS supplier_name, s.lead_time_days AS lead_time_days_contracted, agg.pos_ordered AS pos_ordered, agg.pos_received AS pos_received, agg.avg_actual_lead_days AS avg_actual_lead_days, agg.pct_received_late AS pct_received_late, agg.avg_days_late AS avg_days_late FROM suppliers s JOIN ( SELECT po.supplier_id AS supplier_id, COUNT(*) AS pos_ordered, COUNT(po.received_at) AS pos_received, AVG(CAST(CASE WHEN po.received_at IS NOT NULL THEN (DATE_DIFF('day', po.ordered_at, po.received_at)) END AS NUMERIC(18,6))) AS avg_actual_lead_days, 100.0 * SUM(CASE WHEN po.expected_at IS NOT NULL AND po.received_at IS NOT NULL AND po.received_at > po.expected_at THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN po.expected_at IS NOT NULL AND po.received_at IS NOT NULL THEN 1 ELSE 0 END), 0) AS pct_received_late, AVG(CAST(CASE WHEN po.expected_at IS NOT NULL AND po.received_at IS NOT NULL AND po.received_at > po.expected_at THEN (DATE_DIFF('day', po.expected_at, po.received_at)) END AS NUMERIC(18,6))) AS avg_days_late FROM purchase_orders po WHERE po.ordered_at >= DATE '2025-01-01' AND po.ordered_at < DATE '2026-01-01' GROUP BY po.supplier_id HAVING COUNT(*) >= 10 ) agg ON agg.supplier_id = s.id ORDER BY agg.pos_ordered DESC NULLS FIRST, s.name ASC NULLS LAST, s.id NULLS LAST FETCH FIRST 50 ROWS ONLY
SELECT s.name AS supplier_name, s.lead_time_days AS lead_time_days_contracted, agg.pos_ordered AS pos_ordered, agg.pos_received AS pos_received, agg.avg_actual_lead_days AS avg_actual_lead_days, agg.pct_received_late AS pct_received_late, agg.avg_days_late AS avg_days_late FROM suppliers s JOIN ( SELECT po.supplier_id AS supplier_id, COUNT(*) AS pos_ordered, COUNT(po.received_at) AS pos_received, AVG(CAST(CASE WHEN po.received_at IS NOT NULL THEN ((CAST(po.received_at AS DATE) - CAST(po.ordered_at AS DATE))) END AS NUMERIC(18,6))) AS avg_actual_lead_days, 100.0 * SUM(CASE WHEN po.expected_at IS NOT NULL AND po.received_at IS NOT NULL AND po.received_at > po.expected_at THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN po.expected_at IS NOT NULL AND po.received_at IS NOT NULL THEN 1 ELSE 0 END), 0) AS pct_received_late, AVG(CAST(CASE WHEN po.expected_at IS NOT NULL AND po.received_at IS NOT NULL AND po.received_at > po.expected_at THEN ((CAST(po.received_at AS DATE) - CAST(po.expected_at AS DATE))) END AS NUMERIC(18,6))) AS avg_days_late FROM purchase_orders po WHERE po.ordered_at >= DATE '2025-01-01' AND po.ordered_at < DATE '2026-01-01' GROUP BY po.supplier_id HAVING COUNT(*) >= 10 ) agg ON agg.supplier_id = s.id ORDER BY agg.pos_ordered DESC NULLS FIRST, s.name ASC NULLS LAST, s.id NULLS LAST FETCH FIRST 50 ROWS ONLY
SELECT s.name AS supplier_name, s.lead_time_days AS lead_time_days_contracted, agg.pos_ordered AS pos_ordered, agg.pos_received AS pos_received, agg.avg_actual_lead_days AS avg_actual_lead_days, agg.pct_received_late AS pct_received_late, agg.avg_days_late AS avg_days_late FROM suppliers s JOIN ( SELECT po.supplier_id AS supplier_id, COUNT(*) AS pos_ordered, COUNT(po.received_at) AS pos_received, AVG(CAST(CASE WHEN po.received_at IS NOT NULL THEN ((CAST(po.received_at AS DATE) - CAST(po.ordered_at AS DATE))) END AS NUMERIC(18,6))) AS avg_actual_lead_days, 100.0 * SUM(CASE WHEN po.expected_at IS NOT NULL AND po.received_at IS NOT NULL AND po.received_at > po.expected_at THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN po.expected_at IS NOT NULL AND po.received_at IS NOT NULL THEN 1 ELSE 0 END), 0) AS pct_received_late, AVG(CAST(CASE WHEN po.expected_at IS NOT NULL AND po.received_at IS NOT NULL AND po.received_at > po.expected_at THEN ((CAST(po.received_at AS DATE) - CAST(po.expected_at AS DATE))) END AS NUMERIC(18,6))) AS avg_days_late FROM purchase_orders po WHERE po.ordered_at >= DATE '2025-01-01' AND po.ordered_at < DATE '2026-01-01' GROUP BY po.supplier_id HAVING COUNT(*) >= 10 ) agg ON agg.supplier_id = s.id ORDER BY agg.pos_ordered DESC NULLS FIRST, s.name ASC NULLS LAST, s.id NULLS LAST LIMIT 50
SELECT s.name AS supplier_name, s.lead_time_days AS lead_time_days_contracted, agg.pos_ordered AS pos_ordered, agg.pos_received AS pos_received, agg.avg_actual_lead_days AS avg_actual_lead_days, agg.pct_received_late AS pct_received_late, agg.avg_days_late AS avg_days_late FROM suppliers s JOIN ( SELECT po.supplier_id AS supplier_id, COUNT(*) AS pos_ordered, COUNT(po.received_at) AS pos_received, AVG(CAST(CASE WHEN po.received_at IS NOT NULL THEN (dateDiff('day', po.ordered_at, po.received_at)) END AS NUMERIC(18,6))) AS avg_actual_lead_days, 100.0 * SUM(CASE WHEN po.expected_at IS NOT NULL AND po.received_at IS NOT NULL AND po.received_at > po.expected_at THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN po.expected_at IS NOT NULL AND po.received_at IS NOT NULL THEN 1 ELSE 0 END), 0) AS pct_received_late, AVG(CAST(CASE WHEN po.expected_at IS NOT NULL AND po.received_at IS NOT NULL AND po.received_at > po.expected_at THEN (dateDiff('day', po.expected_at, po.received_at)) END AS NUMERIC(18,6))) AS avg_days_late FROM purchase_orders po WHERE po.ordered_at >= DATE '2025-01-01' AND po.ordered_at < DATE '2026-01-01' GROUP BY po.supplier_id HAVING COUNT(*) >= 10 ) agg ON agg.supplier_id = s.id ORDER BY agg.pos_ordered DESC NULLS FIRST, s.name ASC NULLS LAST, s.id NULLS LAST FETCH FIRST 50 ROWS ONLY
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: Purchase order replenishment performance by supplier, 2025 Question: Which suppliers are chronically slow to deliver against purchase orders, relative to what they've promised? Grain: one row per supplier Columns: - supplier_name - lead_time_days_contracted (suppliers.lead_time_days) - pos_ordered (count of purchase_orders with ordered_at in 2025) - pos_received (of those, count with received_at not null) - avg_actual_lead_days (DATEDIFF(day, ordered_at, received_at), received subset only) - pct_received_late (share of POs with both expected_at and received_at set, where received_at > expected_at) - avg_days_late (average of DATEDIFF(day, expected_at, received_at) for the late subset only) Details: Tables: purchase_orders (supplier_id, ordered_at, expected_at, received_at, status) joined to suppliers (name, lead_time_days). Scope: purchase_orders.ordered_at between 2025-01-01 and 2025-12-31. Round avg_actual_lead_days and avg_days_late to 1 decimal, pct_received_late to 1 decimal (as %). Include only suppliers with at least 10 POs ordered in the window. Order by pos_ordered descending, tie-break by supplier_name ascending. Cap at 50 rows. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runSELECT s.name AS supplier_name, s.lead_time_days AS lead_time_days_contracted, agg.pos_ordered, agg.pos_received, ROUND(agg.avg_actual_lead_days, 1) AS avg_actual_lead_days, ROUND(agg.pct_received_late, 1) AS pct_received_late, ROUND(agg.avg_days_late, 1) AS avg_days_late FROM suppliers s JOIN ( SELECT po.supplier_id, COUNT(*) AS pos_ordered, COUNT(po.received_at) AS pos_received, AVG(CASE WHEN po.received_at IS NOT NULL THEN (po.received_at::date - po.ordered_at::date) END) AS avg_actual_lead_days, 100.0 * SUM(CASE WHEN po.expected_at IS NOT NULL AND po.received_at IS NOT NULL AND po.received_at > po.expected_at THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN po.expected_at IS NOT NULL AND po.received_at IS NOT NULL THEN 1 ELSE 0 END), 0) AS pct_received_late, AVG(CASE WHEN po.expected_at IS NOT NULL AND po.received_at IS NOT NULL AND po.received_at > po.expected_at THEN (po.received_at::date - po.expected_at::date) END) AS avg_days_late FROM purchase_orders po WHERE po.ordered_at >= DATE '2025-01-01' AND po.ordered_at < DATE '2026-01-01' GROUP BY po.supplier_id HAVING COUNT(*) >= 10 ) agg ON agg.supplier_id = s.id ORDER BY agg.pos_ordered DESC, s.name ASC LIMIT 50
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: supplier_name, lead_time_days_contracted, pos_ordered, pos_received, avg_actual_lead_days, pct_received_late, avg_days_late
submitSELECT s.name AS supplier_name, s.lead_time_days AS lead_time_days_contracted, agg.pos_ordered, agg.pos_received, ROUND(agg.avg_actual_lead_days, 1) AS avg_actual_lead_days, ROUND(agg.pct_received_late, 1) AS pct_received_late, ROUND(agg.avg_days_late, 1) AS avg_days_late FROM suppliers s JOIN ( SELECT po.supplier_id, COUNT(*) AS pos_ordered, COUNT(po.received_at) AS pos_received, AVG(CASE WHEN po.received_at IS NOT NULL THEN (po.received_at::date - po.ordered_at::date) END) AS avg_actual_lead_days, 100.0 * SUM(CASE WHEN po.expected_at IS NOT NULL AND po.received_at IS NOT NULL AND po.received_at > po.expected_at THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN po.expected_at IS NOT NULL AND po.received_at IS NOT NULL THEN 1 ELSE 0 END), 0) AS pct_received_late, AVG(CASE WHEN po.expected_at IS NOT NULL AND po.received_at IS NOT NULL AND po.received_at > po.expected_at THEN (po.received_at::date - po.expected_at::date) END) AS avg_days_late FROM purchase_orders po WHERE po.ordered_at >= DATE '2025-01-01' AND po.ordered_at < DATE '2026-01-01' GROUP BY po.supplier_id HAVING COUNT(*) >= 10 ) agg ON agg.supplier_id = s.id ORDER BY agg.pos_ordered DESC, s.name ASC LIMIT 50
Submitted to tonight's batch. Your work on this request is done.