Queries · q242 · Chief Operating Officer · week 2, night 4
“Did Albion Parcel's reliability break down specifically in Q4 while Landfall Freight and Lowlands Express stayed consistently weak all year, when measured in one single consistent query?”
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 4, request 2 of 3
Before I write anything, I cross-checked the three night-3 order-level reports against each other by hand, and they don't reconcile: Report 1 says the three weak carriers blend to 66.8% within 5 days; Report 2's Albion baseline/collapse figures imply Albion alone blends to ~80.1%, which would force Landfall+Lowlands down to ~58.8% to match Report 1's total. But Report 3's "all other carriers" (82.0%, 18,088 orders) combined with Report 1's "good eleven" (93.4%, 13,696 orders) and Report 2's Albion (~80.1%, 2,564 orders) overshoots the reported total on its own -- it would require Landfall's own rate to be negative, which is impossible. So at least one of these three reports has an inconsistency (likely a scoping or computation difference across the three separate queries), and I'm not willing to hand Priya a "ceiling" number or a "fix Lowlands' lanes" recommendation until it's resolved -- this is exactly the kind of number I'd expect her or Daniel's/Tomasz's teams to poke at. Tonight (last night) I'm spending all 3 slots getting a single, internally-consistent source of truth in one query each, rather than stitching across reports: (1) one full-year carrier-level breakdown to anchor the ceiling calculation, (2) one carrier-group-by-quarter breakdown (Albion / Landfall / Lowlands / everyone else) to nail down the timing story cleanly, and (3) one Lowlands-by-lane breakdown done the same consistent way, so the lane-reassignment number is trustworthy. I'll reconcile against this week's earlier reports once these are back and flag any remaining discrepancy explicitly in the memo rather than paper over it.
The engineer · 1 dry run on the empty schema, then the submission
WITH q_orders AS ( SELECT o.id AS order_id, o.tenant_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, f.tenant_id, COUNT(*) AS fulfillment_count FROM fulfillments f JOIN q_orders qo ON qo.order_id = f.order_id AND qo.tenant_id = f.tenant_id GROUP BY f.order_id, f.tenant_id ), single_fulfillment AS ( SELECT fc.order_id, fc.tenant_id FROM fulfillment_counts fc WHERE fc.fulfillment_count = 1 ), fulfillment_shipment_counts AS ( SELECT f.id AS fulfillment_id, f.order_id, f.tenant_id, COUNT(s.id) AS shipment_count FROM fulfillments f JOIN single_fulfillment sf ON sf.order_id = f.order_id AND sf.tenant_id = f.tenant_id LEFT JOIN shipments s ON s.fulfillment_id = f.id AND s.tenant_id = f.tenant_id GROUP BY f.id, f.order_id, f.tenant_id ), qualifying_fulfillments AS ( SELECT fsc.fulfillment_id, fsc.order_id, fsc.tenant_id FROM fulfillment_shipment_counts fsc WHERE fsc.shipment_count = 1 ), order_shipment AS ( SELECT qf.order_id, qf.tenant_id, s.carrier, s.delivered_at FROM qualifying_fulfillments qf JOIN shipments s ON s.fulfillment_id = qf.fulfillment_id AND s.tenant_id = qf.tenant_id WHERE s.delivered_at IS NOT NULL ), order_detail AS ( SELECT qo.order_id, qo.tenant_id, qo.placed_at, os.carrier, os.delivered_at, EXTRACT(EPOCH FROM (os.delivered_at - qo.placed_at)) / 86400.0 AS delivery_days FROM q_orders qo JOIN order_shipment os ON os.order_id = qo.order_id AND os.tenant_id = qo.tenant_id ), order_detail_grouped AS ( SELECT od.order_id, od.tenant_id, od.placed_at, od.delivery_days, CASE WHEN od.carrier IN ('Albion Parcel', 'Landfall Freight', 'Lowlands Express') THEN od.carrier ELSE 'All other carriers' END AS carrier_group FROM order_detail od ), with_quarter AS ( SELECT odg.order_id, odg.delivery_days, odg.carrier_group, cd.year_num, cd.quarter_num FROM order_detail_grouped odg JOIN calendar_days cd ON cd.calendar_date = CAST(odg.placed_at AS DATE) ), quarter_totals AS ( SELECT year_num, quarter_num, COUNT(*) AS total_orders FROM with_quarter GROUP BY year_num, quarter_num ), group_agg AS ( SELECT year_num, quarter_num, carrier_group, COUNT(*) AS orders_fully_delivered, AVG(delivery_days) AS avg_delivery_days, AVG(CASE WHEN delivery_days <= 5 THEN 1.0 ELSE 0.0 END) * 100 AS pct_within_5_days FROM with_quarter GROUP BY year_num, quarter_num, carrier_group ) SELECT ga.carrier_group AS carrier_group, CONCAT(CAST(ga.year_num AS VARCHAR), '-Q', CAST(ga.quarter_num AS VARCHAR)) AS quarter, ga.orders_fully_delivered AS orders_fully_delivered, ROUND(CAST(ga.avg_delivery_days AS NUMERIC), 2) AS avg_delivery_days, ROUND(CAST(ga.pct_within_5_days AS NUMERIC), 1) AS pct_within_5_days, ROUND(CAST(ga.orders_fully_delivered * 100.0 / qt.total_orders AS NUMERIC), 1) AS share_of_quarter_orders_pct FROM group_agg ga JOIN quarter_totals qt ON qt.year_num = ga.year_num AND qt.quarter_num = ga.quarter_num ORDER BY ga.quarter_num ASC, CASE ga.carrier_group WHEN 'Albion Parcel' THEN 1 WHEN 'Landfall Freight' THEN 2 WHEN 'Lowlands Express' THEN 3 ELSE 4 END ASC
“The query compiles and produces the expected columns at the right grain. Submitting.”
“Submitted. The query computes order-level delivery reliability per carrier group (Albion Parcel, Landfall Freight, Lowlands Express, and "All other carriers" combined) by calendar quarter of placed_at for 2025, using the same scoping rule as the full-year report (fulfilled orders with exactly one fulfillment containing exactly one delivered shipment). It reports order counts, average delivery days, % delivered within 5 days, and each group's share of that quarter's qualifying orders — all in one consistent query, as requested, so the analyst can directly compare Albion Parcel's Q4 pattern against the steadier Landfall Freight/Lowlands Express trends.”
As Lena Hartmann saw it, from the small dataset the agents worked on
carrier_group | quarter | orders_fully_delivered | avg_delivery_days | pct_within_5_days | share_of_quarter_orders_pct -------------------+---------+------------------------+-------------------+-------------------+---------------------------- Albion Parcel | 2025-Q1 | 614 | 3.18 | 95.0 | 13.4 Landfall Freight | 2025-Q1 | 430 | 5.76 | 42.6 | 9.4 Lowlands Express | 2025-Q1 | 567 | 4.61 | 64.2 | 12.4 All other carriers | 2025-Q1 | 2980 | 3.35 | 91.4 | 64.9 Albion Parcel | 2025-Q2 | 587 | 3.58 | 88.8 | 13.1 Landfall Freight | 2025-Q2 | 366 | 6.08 | 38.8 | 8.2 Lowlands Express | 2025-Q2 | 560 | 5.27 | 54.1 | 12.5 All other carriers | 2025-Q2 | 2957 | 3.51 | 87.6 | 66.2 Albion Parcel | 2025-Q3 | 612 | 3.40 | 93.1 | 13.2 Landfall Freight | 2025-Q3 | 408 | 6.24 | 30.6 | 8.8 Lowlands Express | 2025-Q3 | 547 | 4.82 | 60.1 | 11.8 All other carriers | 2025-Q3 | 3066 | 3.60 | 86.8 | 66.2 Albion Parcel | 2025-Q4 | 751 | 7.80 | 50.5 | 11.0 Landfall Freight | 2025-Q4 | 624 | 6.31 | 31.6 | 9.1 Lowlands Express | 2025-Q4 | 771 | 4.95 | 58.5 | 11.3 All other carriers | 2025-Q4 | 4693 | 3.46 | 88.6 | 68.6 (16 rows)
The memo and the queries that followed; links found by title, so inferred
It builds on:
Changed only where the benchmark needs the same result on every system
WITH q_orders AS ( SELECT o.id AS order_id, o.tenant_id, o.placed_at SELECT o.id AS order_id, o.tenant_id AS tenant_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, f.tenant_id, COUNT(*) AS fulfillment_count SELECT f.order_id AS order_id, f.tenant_id AS tenant_id, COUNT(*) AS fulfillment_count FROM fulfillments f JOIN q_orders qo ON qo.order_id = f.order_id AND qo.tenant_id = f.tenant_id GROUP BY f.order_id, f.tenant_id),single_fulfillment AS ( SELECT fc.order_id, fc.tenant_id SELECT fc.order_id AS order_id, fc.tenant_id AS tenant_id FROM fulfillment_counts fc WHERE fc.fulfillment_count = 1),fulfillment_shipment_counts AS ( SELECT f.id AS fulfillment_id, f.order_id, f.tenant_id, COUNT(s.id) AS shipment_count SELECT f.id AS fulfillment_id, f.order_id AS order_id, f.tenant_id AS tenant_id, COUNT(s.id) AS shipment_count FROM fulfillments f JOIN single_fulfillment sf ON sf.order_id = f.order_id AND sf.tenant_id = f.tenant_id LEFT JOIN shipments s ON s.fulfillment_id = f.id AND s.tenant_id = f.tenant_id GROUP BY f.id, f.order_id, f.tenant_id),qualifying_fulfillments AS ( SELECT fsc.fulfillment_id, fsc.order_id, fsc.tenant_id SELECT fsc.fulfillment_id AS fulfillment_id, fsc.order_id AS order_id, fsc.tenant_id AS tenant_id FROM fulfillment_shipment_counts fsc WHERE fsc.shipment_count = 1),order_shipment AS ( SELECT qf.order_id, qf.tenant_id, s.carrier, s.delivered_at SELECT qf.order_id AS order_id, qf.tenant_id AS tenant_id, s.carrier AS carrier, s.delivered_at AS delivered_at FROM qualifying_fulfillments qf JOIN shipments s ON s.fulfillment_id = qf.fulfillment_id AND s.tenant_id = qf.tenant_id WHERE s.delivered_at IS NOT NULL),order_detail AS ( SELECT qo.order_id, qo.tenant_id, qo.placed_at, os.carrier, os.delivered_at, EXTRACT(EPOCH FROM (os.delivered_at - qo.placed_at)) / 86400.0 AS delivery_days qo.order_id AS order_id, qo.tenant_id AS tenant_id, qo.placed_at AS placed_at, os.carrier AS carrier, os.delivered_at AS delivered_at, {{seconds_between(qo.placed_at, os.delivered_at)}} / 86400.0 AS delivery_days FROM q_orders qo JOIN order_shipment os ON os.order_id = qo.order_id AND os.tenant_id = qo.tenant_id),order_detail_grouped AS ( SELECT od.order_id, od.tenant_id, od.placed_at, od.delivery_days, od.order_id AS order_id, od.tenant_id AS tenant_id, od.placed_at AS placed_at, od.delivery_days AS delivery_days, CASE WHEN od.carrier IN ('Albion Parcel', 'Landfall Freight', 'Lowlands Express') THEN od.carrier ELSE 'All other carriers' END AS carrier_group FROM order_detail od),with_quarter AS ( SELECT odg.order_id, odg.delivery_days, odg.carrier_group, cd.year_num, cd.quarter_num odg.order_id AS order_id, odg.delivery_days AS delivery_days, odg.carrier_group AS carrier_group, cd.year_num AS year_num, cd.quarter_num AS quarter_num FROM order_detail_grouped odg JOIN calendar_days cd ON cd.calendar_date = CAST(odg.placed_at AS DATE)),quarter_totals AS ( SELECT year_num, quarter_num, COUNT(*) AS total_orders FROM with_quarter GROUP BY year_num, quarter_num),group_agg AS ( SELECT year_num, quarter_num, carrier_group, COUNT(*) AS orders_fully_delivered, AVG(delivery_days) AS avg_delivery_days, AVG(CASE WHEN delivery_days <= 5 THEN 1.0 ELSE 0.0 END) * 100 AS pct_within_5_days FROM with_quarter GROUP BY year_num, quarter_num, carrier_group)SELECT ga.carrier_group AS carrier_group, CONCAT(CAST(ga.year_num AS VARCHAR), '-Q', CAST(ga.quarter_num AS VARCHAR)) AS quarter, ga.orders_fully_delivered AS orders_fully_delivered, ROUND(CAST(ga.avg_delivery_days AS NUMERIC), 2) AS avg_delivery_days, ROUND(CAST(ga.pct_within_5_days AS NUMERIC), 1) AS pct_within_5_days, ROUND(CAST(ga.orders_fully_delivered * 100.0 / qt.total_orders AS NUMERIC), 1) AS share_of_quarter_orders_pct ga.avg_delivery_days AS avg_delivery_days, ga.pct_within_5_days AS pct_within_5_days, ga.orders_fully_delivered * 100.0 / qt.total_orders AS share_of_quarter_orders_pctFROM group_agg gaJOIN quarter_totals qt ON qt.year_num = ga.year_num AND qt.quarter_num = ga.quarter_numORDER BY ga.quarter_num ASC, ga.quarter_num ASC NULLS LAST, CASE ga.carrier_group WHEN 'Albion Parcel' THEN 1 WHEN 'Landfall Freight' THEN 2 WHEN 'Lowlands Express' THEN 3 ELSE 4 END ASC END ASC NULLS LAST, ga.year_num NULLS LAST
WITH q_orders AS ( SELECT o.id AS order_id, o.tenant_id AS tenant_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, f.tenant_id AS tenant_id, COUNT(*) AS fulfillment_count FROM fulfillments f JOIN q_orders qo ON qo.order_id = f.order_id AND qo.tenant_id = f.tenant_id GROUP BY f.order_id, f.tenant_id ), single_fulfillment AS ( SELECT fc.order_id AS order_id, fc.tenant_id AS tenant_id FROM fulfillment_counts fc WHERE fc.fulfillment_count = 1 ), fulfillment_shipment_counts AS ( SELECT f.id AS fulfillment_id, f.order_id AS order_id, f.tenant_id AS tenant_id, COUNT(s.id) AS shipment_count FROM fulfillments f JOIN single_fulfillment sf ON sf.order_id = f.order_id AND sf.tenant_id = f.tenant_id LEFT JOIN shipments s ON s.fulfillment_id = f.id AND s.tenant_id = f.tenant_id GROUP BY f.id, f.order_id, f.tenant_id ), qualifying_fulfillments AS ( SELECT fsc.fulfillment_id AS fulfillment_id, fsc.order_id AS order_id, fsc.tenant_id AS tenant_id FROM fulfillment_shipment_counts fsc WHERE fsc.shipment_count = 1 ), order_shipment AS ( SELECT qf.order_id AS order_id, qf.tenant_id AS tenant_id, s.carrier AS carrier, s.delivered_at AS delivered_at FROM qualifying_fulfillments qf JOIN shipments s ON s.fulfillment_id = qf.fulfillment_id AND s.tenant_id = qf.tenant_id WHERE s.delivered_at IS NOT NULL ), order_detail AS ( SELECT qo.order_id AS order_id, qo.tenant_id AS tenant_id, qo.placed_at AS placed_at, os.carrier AS carrier, os.delivered_at AS delivered_at, CAST(EXTRACT(EPOCH FROM (os.delivered_at - qo.placed_at)) AS NUMERIC(18,6)) / 86400.0 AS delivery_days FROM q_orders qo JOIN order_shipment os ON os.order_id = qo.order_id AND os.tenant_id = qo.tenant_id ), order_detail_grouped AS ( SELECT od.order_id AS order_id, od.tenant_id AS tenant_id, od.placed_at AS placed_at, od.delivery_days AS delivery_days, CASE WHEN od.carrier IN ('Albion Parcel', 'Landfall Freight', 'Lowlands Express') THEN od.carrier ELSE 'All other carriers' END AS carrier_group FROM order_detail od ), with_quarter AS ( SELECT odg.order_id AS order_id, odg.delivery_days AS delivery_days, odg.carrier_group AS carrier_group, cd.year_num AS year_num, cd.quarter_num AS quarter_num FROM order_detail_grouped odg JOIN calendar_days cd ON cd.calendar_date = CAST(odg.placed_at AS DATE) ), quarter_totals AS ( SELECT year_num, quarter_num, COUNT(*) AS total_orders FROM with_quarter GROUP BY year_num, quarter_num ), group_agg AS ( SELECT year_num, quarter_num, carrier_group, COUNT(*) AS orders_fully_delivered, AVG(delivery_days) AS avg_delivery_days, AVG(CASE WHEN delivery_days <= 5 THEN 1.0 ELSE 0.0 END) * 100 AS pct_within_5_days FROM with_quarter GROUP BY year_num, quarter_num, carrier_group ) SELECT ga.carrier_group AS carrier_group, CONCAT(CAST(ga.year_num AS VARCHAR), '-Q', CAST(ga.quarter_num AS VARCHAR)) AS quarter, ga.orders_fully_delivered AS orders_fully_delivered, ga.avg_delivery_days AS avg_delivery_days, ga.pct_within_5_days AS pct_within_5_days, ga.orders_fully_delivered * 100.0 / qt.total_orders AS share_of_quarter_orders_pct FROM group_agg ga JOIN quarter_totals qt ON qt.year_num = ga.year_num AND qt.quarter_num = ga.quarter_num ORDER BY ga.quarter_num ASC NULLS LAST, CASE ga.carrier_group WHEN 'Albion Parcel' THEN 1 WHEN 'Landfall Freight' THEN 2 WHEN 'Lowlands Express' THEN 3 ELSE 4 END ASC NULLS LAST, ga.year_num NULLS LAST
WITH q_orders AS ( SELECT o.id AS order_id, o.tenant_id AS tenant_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, f.tenant_id AS tenant_id, COUNT(*) AS fulfillment_count FROM fulfillments f JOIN q_orders qo ON qo.order_id = f.order_id AND qo.tenant_id = f.tenant_id GROUP BY f.order_id, f.tenant_id ), single_fulfillment AS ( SELECT fc.order_id AS order_id, fc.tenant_id AS tenant_id FROM fulfillment_counts fc WHERE fc.fulfillment_count = 1 ), fulfillment_shipment_counts AS ( SELECT f.id AS fulfillment_id, f.order_id AS order_id, f.tenant_id AS tenant_id, COUNT(s.id) AS shipment_count FROM fulfillments f JOIN single_fulfillment sf ON sf.order_id = f.order_id AND sf.tenant_id = f.tenant_id LEFT JOIN shipments s ON s.fulfillment_id = f.id AND s.tenant_id = f.tenant_id GROUP BY f.id, f.order_id, f.tenant_id ), qualifying_fulfillments AS ( SELECT fsc.fulfillment_id AS fulfillment_id, fsc.order_id AS order_id, fsc.tenant_id AS tenant_id FROM fulfillment_shipment_counts fsc WHERE fsc.shipment_count = 1 ), order_shipment AS ( SELECT qf.order_id AS order_id, qf.tenant_id AS tenant_id, s.carrier AS carrier, s.delivered_at AS delivered_at FROM qualifying_fulfillments qf JOIN shipments s ON s.fulfillment_id = qf.fulfillment_id AND s.tenant_id = qf.tenant_id WHERE s.delivered_at IS NOT NULL ), order_detail AS ( SELECT qo.order_id AS order_id, qo.tenant_id AS tenant_id, qo.placed_at AS placed_at, os.carrier AS carrier, os.delivered_at AS delivered_at, CAST(divideDecimal(toDecimal128(dateDiff('microsecond', qo.placed_at, os.delivered_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6))) / 86400.0 AS delivery_days FROM q_orders qo JOIN order_shipment os ON os.order_id = qo.order_id AND os.tenant_id = qo.tenant_id ), order_detail_grouped AS ( SELECT od.order_id AS order_id, od.tenant_id AS tenant_id, od.placed_at AS placed_at, od.delivery_days AS delivery_days, CASE WHEN od.carrier IN ('Albion Parcel', 'Landfall Freight', 'Lowlands Express') THEN od.carrier ELSE 'All other carriers' END AS carrier_group FROM order_detail od ), with_quarter AS ( SELECT odg.order_id AS order_id, odg.delivery_days AS delivery_days, odg.carrier_group AS carrier_group, cd.year_num AS year_num, cd.quarter_num AS quarter_num FROM order_detail_grouped odg JOIN calendar_days cd ON cd.calendar_date = CAST(odg.placed_at AS DATE) ), quarter_totals AS ( SELECT year_num, quarter_num, COUNT(*) AS total_orders FROM with_quarter GROUP BY year_num, quarter_num ), group_agg AS ( SELECT year_num, quarter_num, carrier_group, COUNT(*) AS orders_fully_delivered, AVG(delivery_days) AS avg_delivery_days, AVG(CASE WHEN delivery_days <= 5 THEN 1.0 ELSE 0.0 END) * 100 AS pct_within_5_days FROM with_quarter GROUP BY year_num, quarter_num, carrier_group ) SELECT ga.carrier_group AS carrier_group, CONCAT(CAST(ga.year_num AS VARCHAR), '-Q', CAST(ga.quarter_num AS VARCHAR)) AS quarter, ga.orders_fully_delivered AS orders_fully_delivered, ga.avg_delivery_days AS avg_delivery_days, ga.pct_within_5_days AS pct_within_5_days, ga.orders_fully_delivered * 100.0 / qt.total_orders AS share_of_quarter_orders_pct FROM group_agg ga JOIN quarter_totals qt ON qt.year_num = ga.year_num AND qt.quarter_num = ga.quarter_num ORDER BY ga.quarter_num ASC NULLS LAST, CASE ga.carrier_group WHEN 'Albion Parcel' THEN 1 WHEN 'Landfall Freight' THEN 2 WHEN 'Lowlands Express' THEN 3 ELSE 4 END ASC NULLS LAST, ga.year_num 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: Order-level delivery reliability by carrier group and quarter, 2025 (unified source)
Question: Did Albion Parcel's reliability break down specifically in Q4 while Landfall Freight and Lowlands Express stayed consistently weak all year, when measured in one single consistent query?
Grain: one row per carrier_group and calendar quarter of placed_at, 2025
Columns:
- carrier_group ('Albion Parcel', 'Landfall Freight', 'Lowlands Express', or 'All other carriers')
- quarter (e.g. 2025-Q1)
- orders_fully_delivered
- avg_delivery_days
- pct_within_5_days
- share_of_quarter_orders_pct
Details: Same scoping and single-carrier attribution rule as the full-year carrier report (orders placed_at in 2025, status='fulfilled', exactly one fulfillment with exactly one shipment, delivered_at not null). Group by quarter of orders.placed_at (use calendar_days.quarter_num/quarter_start) and by carrier_group, where carrier_group = the shipment's carrier name if it is 'Albion Parcel', 'Landfall Freight', or 'Lowlands Express', else 'All other carriers' (all remaining carriers summed together). Round avg_delivery_days to 2 decimals, pct columns to 1 decimal. Order by quarter ascending, then carrier_group in this fixed order: Albion Parcel, Landfall Freight, Lowlands Express, All other carriers. Expect 16 rows (4 quarters x 4 groups).
Check your query with the warehouse's dry_run tool, then hand it in with submit.dry_runWITH q_orders AS ( SELECT o.id AS order_id, o.tenant_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, f.tenant_id, COUNT(*) AS fulfillment_count FROM fulfillments f JOIN q_orders qo ON qo.order_id = f.order_id AND qo.tenant_id = f.tenant_id GROUP BY f.order_id, f.tenant_id ), single_fulfillment AS ( SELECT fc.order_id, fc.tenant_id FROM fulfillment_counts fc WHERE fc.fulfillment_count = 1 ), fulfillment_shipment_counts AS ( SELECT f.id AS fulfillment_id, f.order_id, f.tenant_id, COUNT(s.id) AS shipment_count FROM fulfillments f JOIN single_fulfillment sf ON sf.order_id = f.order_id AND sf.tenant_id = f.tenant_id LEFT JOIN shipments s ON s.fulfillment_id = f.id AND s.tenant_id = f.tenant_id GROUP BY f.id, f.order_id, f.tenant_id ), qualifying_fulfillments AS ( SELECT fsc.fulfillment_id, fsc.order_id, fsc.tenant_id FROM fulfillment_shipment_counts fsc WHERE fsc.shipment_count = 1 ), order_shipment AS ( SELECT qf.order_id, qf.tenant_id, s.carrier, s.delivered_at FROM qualifying_fulfillments qf JOIN shipments s ON s.fulfillment_id = qf.fulfillment_id AND s.tenant_id = qf.tenant_id WHERE s.delivered_at IS NOT NULL ), order_detail AS ( SELECT qo.order_id, qo.tenant_id, qo.placed_at, os.carrier, os.delivered_at, EXTRACT(EPOCH FROM (os.delivered_at - qo.placed_at)) / 86400.0 AS delivery_days FROM q_orders qo JOIN order_shipment os ON os.order_id = qo.order_id AND os.tenant_id = qo.tenant_id ), order_detail_grouped AS ( SELECT od.order_id, od.tenant_id, od.placed_at, od.delivery_days, CASE WHEN od.carrier IN ('Albion Parcel', 'Landfall Freight', 'Lowlands Express') THEN od.carrier ELSE 'All other carriers' END AS carrier_group FROM order_detail od ), with_quarter AS ( SELECT odg.order_id, odg.delivery_days, odg.carrier_group, cd.year_num, cd.quarter_num FROM order_detail_grouped odg JOIN calendar_days cd ON cd.calendar_date = CAST(odg.placed_at AS DATE) ), quarter_totals AS ( SELECT year_num, quarter_num, COUNT(*) AS total_orders FROM with_quarter GROUP BY year_num, quarter_num ), group_agg AS ( SELECT year_num, quarter_num, carrier_group, COUNT(*) AS orders_fully_delivered, AVG(delivery_days) AS avg_delivery_days, AVG(CASE WHEN delivery_days <= 5 THEN 1.0 ELSE 0.0 END) * 100 AS pct_within_5_days FROM with_quarter GROUP BY year_num, quarter_num, carrier_group ) SELECT ga.carrier_group AS carrier_group, CONCAT(CAST(ga.year_num AS VARCHAR), '-Q', CAST(ga.quarter_num AS VARCHAR)) AS quarter, ga.orders_fully_delivered AS orders_fully_delivered, ROUND(CAST(ga.avg_delivery_days AS NUMERIC), 2) AS avg_delivery_days, ROUND(CAST(ga.pct_within_5_days AS NUMERIC), 1) AS pct_within_5_days, ROUND(CAST(ga.orders_fully_delivered * 100.0 / qt.total_orders AS NUMERIC), 1) AS share_of_quarter_orders_pct FROM group_agg ga JOIN quarter_totals qt ON qt.year_num = ga.year_num AND qt.quarter_num = ga.quarter_num ORDER BY ga.quarter_num ASC, CASE ga.carrier_group WHEN 'Albion Parcel' THEN 1 WHEN 'Landfall Freight' THEN 2 WHEN 'Lowlands Express' THEN 3 ELSE 4 END ASC
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: carrier_group, quarter, orders_fully_delivered, avg_delivery_days, pct_within_5_days, share_of_quarter_orders_pct
submitWITH q_orders AS ( SELECT o.id AS order_id, o.tenant_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, f.tenant_id, COUNT(*) AS fulfillment_count FROM fulfillments f JOIN q_orders qo ON qo.order_id = f.order_id AND qo.tenant_id = f.tenant_id GROUP BY f.order_id, f.tenant_id ), single_fulfillment AS ( SELECT fc.order_id, fc.tenant_id FROM fulfillment_counts fc WHERE fc.fulfillment_count = 1 ), fulfillment_shipment_counts AS ( SELECT f.id AS fulfillment_id, f.order_id, f.tenant_id, COUNT(s.id) AS shipment_count FROM fulfillments f JOIN single_fulfillment sf ON sf.order_id = f.order_id AND sf.tenant_id = f.tenant_id LEFT JOIN shipments s ON s.fulfillment_id = f.id AND s.tenant_id = f.tenant_id GROUP BY f.id, f.order_id, f.tenant_id ), qualifying_fulfillments AS ( SELECT fsc.fulfillment_id, fsc.order_id, fsc.tenant_id FROM fulfillment_shipment_counts fsc WHERE fsc.shipment_count = 1 ), order_shipment AS ( SELECT qf.order_id, qf.tenant_id, s.carrier, s.delivered_at FROM qualifying_fulfillments qf JOIN shipments s ON s.fulfillment_id = qf.fulfillment_id AND s.tenant_id = qf.tenant_id WHERE s.delivered_at IS NOT NULL ), order_detail AS ( SELECT qo.order_id, qo.tenant_id, qo.placed_at, os.carrier, os.delivered_at, EXTRACT(EPOCH FROM (os.delivered_at - qo.placed_at)) / 86400.0 AS delivery_days FROM q_orders qo JOIN order_shipment os ON os.order_id = qo.order_id AND os.tenant_id = qo.tenant_id ), order_detail_grouped AS ( SELECT od.order_id, od.tenant_id, od.placed_at, od.delivery_days, CASE WHEN od.carrier IN ('Albion Parcel', 'Landfall Freight', 'Lowlands Express') THEN od.carrier ELSE 'All other carriers' END AS carrier_group FROM order_detail od ), with_quarter AS ( SELECT odg.order_id, odg.delivery_days, odg.carrier_group, cd.year_num, cd.quarter_num FROM order_detail_grouped odg JOIN calendar_days cd ON cd.calendar_date = CAST(odg.placed_at AS DATE) ), quarter_totals AS ( SELECT year_num, quarter_num, COUNT(*) AS total_orders FROM with_quarter GROUP BY year_num, quarter_num ), group_agg AS ( SELECT year_num, quarter_num, carrier_group, COUNT(*) AS orders_fully_delivered, AVG(delivery_days) AS avg_delivery_days, AVG(CASE WHEN delivery_days <= 5 THEN 1.0 ELSE 0.0 END) * 100 AS pct_within_5_days FROM with_quarter GROUP BY year_num, quarter_num, carrier_group ) SELECT ga.carrier_group AS carrier_group, CONCAT(CAST(ga.year_num AS VARCHAR), '-Q', CAST(ga.quarter_num AS VARCHAR)) AS quarter, ga.orders_fully_delivered AS orders_fully_delivered, ROUND(CAST(ga.avg_delivery_days AS NUMERIC), 2) AS avg_delivery_days, ROUND(CAST(ga.pct_within_5_days AS NUMERIC), 1) AS pct_within_5_days, ROUND(CAST(ga.orders_fully_delivered * 100.0 / qt.total_orders AS NUMERIC), 1) AS share_of_quarter_orders_pct FROM group_agg ga JOIN quarter_totals qt ON qt.year_num = ga.year_num AND qt.quarter_num = ga.quarter_num ORDER BY ga.quarter_num ASC, CASE ga.carrier_group WHEN 'Albion Parcel' THEN 1 WHEN 'Landfall Freight' THEN 2 WHEN 'Lowlands Express' THEN 3 ELSE 4 END ASC
Submitted to tonight's batch. Your work on this request is done.