Queries · q241 · Chief Operating Officer · week 2, night 4
“What is each carrier's order-level delivery reliability (orders placed to orders delivered, within 5 days), in one single consistent query I can use as the ground truth for a reliability ceiling calculation?”
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 1 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 · 3 dry runs on the empty schema, then the submission
SELECT carrier, orders_fully_delivered, avg_delivery_days, pct_within_5_days, share_of_orders_pct, 0 AS sort_rank FROM ( SELECT d.carrier AS carrier, COUNT(*) AS orders_fully_delivered, ROUND(CAST(AVG(d.delivery_days) AS numeric), 2) AS avg_delivery_days, ROUND(100.0 * SUM(CASE WHEN d.delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_5_days, ROUND(100.0 * COUNT(*) / ( SELECT COUNT(*) FROM ( SELECT o2.id AS order_id FROM orders o2 JOIN fulfillments f2a ON f2a.order_id = o2.id JOIN shipments s2a ON s2a.fulfillment_id = f2a.id WHERE o2.status = 'fulfilled' AND o2.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o2.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s2a.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments ff WHERE ff.order_id = o2.id) = 1 AND (SELECT COUNT(*) FROM shipments ss WHERE ss.fulfillment_id = f2a.id) = 1 ) all_d ), 1) AS share_of_orders_pct FROM ( SELECT o.id AS order_id, s.carrier AS carrier, (CAST(s.delivered_at AS DATE) - CAST(o.placed_at AS DATE)) AS delivery_days FROM orders o JOIN fulfillments f ON f.order_id = o.id JOIN shipments s ON s.fulfillment_id = f.id WHERE o.status = 'fulfilled' AND o.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments f2 WHERE f2.order_id = o.id) = 1 AND (SELECT COUNT(*) FROM shipments s2 WHERE s2.fulfillment_id = f.id) = 1 ) d GROUP BY d.carrier ) per_carrier UNION ALL SELECT 'ALL CARRIERS' AS carrier, orders_fully_delivered, avg_delivery_days, pct_within_5_days, 100.0 AS share_of_orders_pct, 1 AS sort_rank FROM ( SELECT COUNT(*) AS orders_fully_delivered, ROUND(CAST(AVG(delivery_days) AS numeric), 2) AS avg_delivery_days, ROUND(100.0 * SUM(CASE WHEN delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_5_days FROM ( SELECT (CAST(s.delivered_at AS DATE) - CAST(o.placed_at AS DATE)) AS delivery_days FROM orders o JOIN fulfillments f ON f.order_id = o.id JOIN shipments s ON s.fulfillment_id = f.id WHERE o.status = 'fulfilled' AND o.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments f2 WHERE f2.order_id = o.id) = 1 AND (SELECT COUNT(*) FROM shipments s2 WHERE s2.fulfillment_id = f.id) = 1 ) all_orders ) totals ORDER BY sort_rank, orders_fully_delivered DESC
SELECT carrier, orders_fully_delivered, avg_delivery_days, pct_within_5_days, share_of_orders_pct, 0 AS sort_rank share_of_orders_pctFROM ( SELECT d.carrier AS carrier, COUNT(*) AS orders_fully_delivered, ROUND(CAST(AVG(d.delivery_days) AS numeric), 2) AS avg_delivery_days, ROUND(100.0 * SUM(CASE WHEN d.delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_5_days, ROUND(100.0 * COUNT(*) / ( SELECT COUNT(*) FROM ( SELECT o2.id AS order_id FROM orders o2 JOIN fulfillments f2a ON f2a.order_id = o2.id JOIN shipments s2a ON s2a.fulfillment_id = f2a.id WHERE o2.status = 'fulfilled' AND o2.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o2.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s2a.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments ff WHERE ff.order_id = o2.id) = 1 AND (SELECT COUNT(*) FROM shipments ss WHERE ss.fulfillment_id = f2a.id) = 1 ) all_d ), 1) AS share_of_orders_pct FROM ( SELECT o.id AS order_id, s.carrier AS carrier, (CAST(s.delivered_at AS DATE) - CAST(o.placed_at AS DATE)) AS delivery_days FROM orders o JOIN fulfillments f ON f.order_id = o.id JOIN shipments s ON s.fulfillment_id = f.id WHERE o.status = 'fulfilled' AND o.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments f2 WHERE f2.order_id = o.id) = 1 AND (SELECT COUNT(*) FROM shipments s2 WHERE s2.fulfillment_id = f.id) = 1 ) d GROUP BY d.carrier) per_carrierUNION ALLSELECT 'ALL CARRIERS' AS carrier, orders_fully_delivered, avg_delivery_days, pct_within_5_days, 100.0 AS share_of_orders_pct, 1 AS sort_rank 100.0 AS share_of_orders_pctFROM ( SELECT COUNT(*) AS orders_fully_delivered, ROUND(CAST(AVG(delivery_days) AS numeric), 2) AS avg_delivery_days, ROUND(100.0 * SUM(CASE WHEN delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_5_days FROM ( SELECT (CAST(s.delivered_at AS DATE) - CAST(o.placed_at AS DATE)) AS delivery_days FROM orders o JOIN fulfillments f ON f.order_id = o.id JOIN shipments s ON s.fulfillment_id = f.id WHERE o.status = 'fulfilled' AND o.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments f2 WHERE f2.order_id = o.id) = 1 AND (SELECT COUNT(*) FROM shipments s2 WHERE s2.fulfillment_id = f.id) = 1 ) all_orders) totalsORDER BY sort_rank, orders_fully_delivered DESCORDER BY (CASE WHEN carrier = 'ALL CARRIERS' THEN 1 ELSE 0 END), orders_fully_delivered DESC, carrier ASC
SELECT carrier, orders_fully_delivered, avg_delivery_days, pct_within_5_days, share_of_orders_pctSELECT carrier, orders_fully_delivered, avg_delivery_days, pct_within_5_days, share_of_orders_pctFROM ( SELECT d.carrier AS carrier, COUNT(*) AS orders_fully_delivered, ROUND(CAST(AVG(d.delivery_days) AS numeric), 2) AS avg_delivery_days, ROUND(100.0 * SUM(CASE WHEN d.delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_5_days, ROUND(100.0 * COUNT(*) / ( SELECT COUNT(*) FROM ( SELECT o2.id AS order_id FROM orders o2 JOIN fulfillments f2a ON f2a.order_id = o2.id JOIN shipments s2a ON s2a.fulfillment_id = f2a.id WHERE o2.status = 'fulfilled' AND o2.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o2.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s2a.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments ff WHERE ff.order_id = o2.id) = 1 AND (SELECT COUNT(*) FROM shipments ss WHERE ss.fulfillment_id = f2a.id) = 1 ) all_d ), 1) AS share_of_orders_pct carrier, orders_fully_delivered, avg_delivery_days, pct_within_5_days, share_of_orders_pct FROM ( SELECT o.id AS order_id, s.carrier AS carrier, (CAST(s.delivered_at AS DATE) - CAST(o.placed_at AS DATE)) AS delivery_days FROM orders o JOIN fulfillments f ON f.order_id = o.id JOIN shipments s ON s.fulfillment_id = f.id WHERE o.status = 'fulfilled' AND o.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments f2 WHERE f2.order_id = o.id) = 1 AND (SELECT COUNT(*) FROM shipments s2 WHERE s2.fulfillment_id = f.id) = 1 ) d GROUP BY d.carrier) per_carrier d.carrier AS carrier, COUNT(*) AS orders_fully_delivered, ROUND(CAST(AVG(d.delivery_days) AS numeric), 2) AS avg_delivery_days, ROUND(100.0 * SUM(CASE WHEN d.delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_5_days, ROUND(100.0 * COUNT(*) / ( SELECT COUNT(*) FROM ( SELECT o2.id AS order_id FROM orders o2 JOIN fulfillments f2a ON f2a.order_id = o2.id JOIN shipments s2a ON s2a.fulfillment_id = f2a.id WHERE o2.status = 'fulfilled' AND o2.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o2.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s2a.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments ff WHERE ff.order_id = o2.id) = 1 AND (SELECT COUNT(*) FROM shipments ss WHERE ss.fulfillment_id = f2a.id) = 1 ) all_d ), 1) AS share_of_orders_pct FROM ( SELECT o.id AS order_id, s.carrier AS carrier, (CAST(s.delivered_at AS DATE) - CAST(o.placed_at AS DATE)) AS delivery_days FROM orders o JOIN fulfillments f ON f.order_id = o.id JOIN shipments s ON s.fulfillment_id = f.id WHERE o.status = 'fulfilled' AND o.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments f2 WHERE f2.order_id = o.id) = 1 AND (SELECT COUNT(*) FROM shipments s2 WHERE s2.fulfillment_id = f.id) = 1 ) d GROUP BY d.carrier ) per_carrierUNION ALL UNION ALLSELECT 'ALL CARRIERS' AS carrier, orders_fully_delivered, avg_delivery_days, pct_within_5_days, 100.0 AS share_of_orders_pctFROM ( SELECT COUNT(*) AS orders_fully_delivered, ROUND(CAST(AVG(delivery_days) AS numeric), 2) AS avg_delivery_days, ROUND(100.0 * SUM(CASE WHEN delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_5_days 'ALL CARRIERS' AS carrier, orders_fully_delivered, avg_delivery_days, pct_within_5_days, 100.0 AS share_of_orders_pct FROM ( SELECT (CAST(s.delivered_at AS DATE) - CAST(o.placed_at AS DATE)) AS delivery_days FROM orders o JOIN fulfillments f ON f.order_id = o.id JOIN shipments s ON s.fulfillment_id = f.id WHERE o.status = 'fulfilled' AND o.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments f2 WHERE f2.order_id = o.id) = 1 AND (SELECT COUNT(*) FROM shipments s2 WHERE s2.fulfillment_id = f.id) = 1 ) all_orders) totals COUNT(*) AS orders_fully_delivered, ROUND(CAST(AVG(delivery_days) AS numeric), 2) AS avg_delivery_days, ROUND(100.0 * SUM(CASE WHEN delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_5_days FROM ( SELECT (CAST(s.delivered_at AS DATE) - CAST(o.placed_at AS DATE)) AS delivery_days FROM orders o JOIN fulfillments f ON f.order_id = o.id JOIN shipments s ON s.fulfillment_id = f.id WHERE o.status = 'fulfilled' AND o.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments f2 WHERE f2.order_id = o.id) = 1 AND (SELECT COUNT(*) FROM shipments s2 WHERE s2.fulfillment_id = f.id) = 1 ) all_orders ) totals) combinedORDER BY (CASE WHEN carrier = 'ALL CARRIERS' THEN 1 ELSE 0 END), orders_fully_delivered DESC, carrier ASC
“This compiles cleanly and produces exactly the requested columns and grain. Submitting now.”
“Submitted. Summary of what the query does:
- Population: orders with placed_at in 2025, status='fulfilled', exactly one fulfillments row, that fulfillment linked to exactly one shipments row, and that shipment's delivered_at not null — enforced via correlated-count subqueries so each order maps to exactly one carrier.
- delivery_days: date(delivered_at) - date(placed_at), matching DATEDIFF(day, ...) semantics.
- Per-carrier rows: count, avg delivery days (rounded 2dp), % within 5 days (rounded 1dp), and share of the total clean population (rounded 1dp).
- ALL CARRIERS row: same aggregation over the full clean population (not a sum of rounded parts), so it's a true sanity-check total, pinned last via a sort case expression, with carrier rows ordered by orders_fully_delivered descending and carrier name as a tiebreaker.”
As Lena Hartmann saw it, from the small dataset the agents worked on
carrier | orders_fully_delivered | avg_delivery_days | pct_within_5_days | share_of_orders_pct ---------------------+------------------------+-------------------+-------------------+-------------------- Strombogen Kurier | 2900 | 3.27 | 97.7 | 14.1 Albion Parcel | 2564 | 4.72 | 82.9 | 12.5 Lowlands Express | 2445 | 4.97 | 66.7 | 11.9 Liberty Ground | 2196 | 3.13 | 96.5 | 10.7 Alpenpaket | 1996 | 3.31 | 97.1 | 9.7 Landfall Freight | 1828 | 6.15 | 44.5 | 8.9 Trailhead Express | 1759 | 3.36 | 92.3 | 8.6 Skerrylane Logistics | 1075 | 3.83 | 87.5 | 5.2 Kennet Freight | 876 | 3.60 | 91.8 | 4.3 Polder Post | 855 | 4.17 | 80.6 | 4.2 Regent Parcels | 759 | 3.55 | 92.4 | 3.7 Prairie Line Express | 699 | 3.41 | 93.7 | 3.4 Liffey Couriers | 319 | 3.95 | 84.3 | 1.6 Gallia Colis | 262 | 4.18 | 81.7 | 1.3 ALL CARRIERS | 20533 | 4.02 | 84.5 | 100.0 (15 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
SELECT carrier, orders_fully_delivered, avg_delivery_days, pct_within_5_days, share_of_orders_pctFROM ( SELECT carrier, orders_fully_delivered, avg_delivery_days, pct_within_5_days, share_of_orders_pct FROM ( SELECT d.carrier AS carrier, COUNT(*) AS orders_fully_delivered, ROUND(CAST(AVG(d.delivery_days) AS numeric), 2) AS avg_delivery_days, ROUND(100.0 * SUM(CASE WHEN d.delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_5_days, ROUND(100.0 * COUNT(*) / ( SELECT COUNT(*) FROM ( SELECT o2.id AS order_id FROM orders o2 JOIN fulfillments f2a ON f2a.order_id = o2.id JOIN shipments s2a ON s2a.fulfillment_id = f2a.id WHERE o2.status = 'fulfilled' AND o2.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o2.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s2a.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments ff WHERE ff.order_id = o2.id) = 1 AND (SELECT COUNT(*) FROM shipments ss WHERE ss.fulfillment_id = f2a.id) = 1 ) all_d ), 1) AS share_of_orders_pct AVG(CAST(d.delivery_days AS NUMERIC(18,6))) AS avg_delivery_days, 100.0 * SUM(CASE WHEN d.delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*) AS pct_within_5_days, 100.0 * COUNT(*) / ( SELECT COUNT(*) FROM ( SELECT o2.id AS order_id FROM orders o2 JOIN fulfillments f2a ON f2a.order_id = o2.id JOIN shipments s2a ON s2a.fulfillment_id = f2a.id WHERE o2.status = 'fulfilled' AND o2.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o2.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s2a.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments ff WHERE ff.order_id = o2.id) = 1 AND (SELECT COUNT(*) FROM shipments ss WHERE ss.fulfillment_id = f2a.id) = 1 ) all_d) AS share_of_orders_pct FROM ( SELECT o.id AS order_id, s.carrier AS carrier, (CAST(s.delivered_at AS DATE) - CAST(o.placed_at AS DATE)) AS delivery_days {{date_diff_days(o.placed_at, s.delivered_at)}} AS delivery_days FROM orders o JOIN fulfillments f ON f.order_id = o.id JOIN shipments s ON s.fulfillment_id = f.id WHERE o.status = 'fulfilled' AND o.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments f2 WHERE f2.order_id = o.id) = 1 AND (SELECT COUNT(*) FROM shipments s2 WHERE s2.fulfillment_id = f.id) = 1 ) d GROUP BY d.carrier ) per_carrier UNION ALL SELECT 'ALL CARRIERS' AS carrier, orders_fully_delivered, avg_delivery_days, pct_within_5_days, 100.0 AS share_of_orders_pct FROM ( SELECT COUNT(*) AS orders_fully_delivered, ROUND(CAST(AVG(delivery_days) AS numeric), 2) AS avg_delivery_days, ROUND(100.0 * SUM(CASE WHEN delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_5_days AVG(CAST(delivery_days AS NUMERIC(18,6))) AS avg_delivery_days, 100.0 * SUM(CASE WHEN delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*) AS pct_within_5_days FROM ( SELECT (CAST(s.delivered_at AS DATE) - CAST(o.placed_at AS DATE)) AS delivery_days ({{date_diff_days(o.placed_at, s.delivered_at)}}) AS delivery_days FROM orders o JOIN fulfillments f ON f.order_id = o.id JOIN shipments s ON s.fulfillment_id = f.id WHERE o.status = 'fulfilled' AND o.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments f2 WHERE f2.order_id = o.id) = 1 AND (SELECT COUNT(*) FROM shipments s2 WHERE s2.fulfillment_id = f.id) = 1 ) all_orders ) totals) combinedORDER BY (CASE WHEN carrier = 'ALL CARRIERS' THEN 1 ELSE 0 END), orders_fully_delivered DESC, carrier ASC (CASE WHEN carrier = 'ALL CARRIERS' THEN 1 ELSE 0 END) NULLS LAST, orders_fully_delivered DESC NULLS FIRST, carrier ASC NULLS LAST
SELECT carrier, orders_fully_delivered, avg_delivery_days, pct_within_5_days, share_of_orders_pct FROM ( SELECT carrier, orders_fully_delivered, avg_delivery_days, pct_within_5_days, share_of_orders_pct FROM ( SELECT d.carrier AS carrier, COUNT(*) AS orders_fully_delivered, AVG(CAST(d.delivery_days AS NUMERIC(18,6))) AS avg_delivery_days, 100.0 * SUM(CASE WHEN d.delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*) AS pct_within_5_days, 100.0 * COUNT(*) / ( SELECT COUNT(*) FROM ( SELECT o2.id AS order_id FROM orders o2 JOIN fulfillments f2a ON f2a.order_id = o2.id JOIN shipments s2a ON s2a.fulfillment_id = f2a.id WHERE o2.status = 'fulfilled' AND o2.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o2.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s2a.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments ff WHERE ff.order_id = o2.id) = 1 AND (SELECT COUNT(*) FROM shipments ss WHERE ss.fulfillment_id = f2a.id) = 1 ) all_d ) AS share_of_orders_pct FROM ( SELECT o.id AS order_id, s.carrier AS carrier, DATE_DIFF('day', o.placed_at, s.delivered_at) AS delivery_days FROM orders o JOIN fulfillments f ON f.order_id = o.id JOIN shipments s ON s.fulfillment_id = f.id WHERE o.status = 'fulfilled' AND o.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments f2 WHERE f2.order_id = o.id) = 1 AND (SELECT COUNT(*) FROM shipments s2 WHERE s2.fulfillment_id = f.id) = 1 ) d GROUP BY d.carrier ) per_carrier UNION ALL SELECT 'ALL CARRIERS' AS carrier, orders_fully_delivered, avg_delivery_days, pct_within_5_days, 100.0 AS share_of_orders_pct FROM ( SELECT COUNT(*) AS orders_fully_delivered, AVG(CAST(delivery_days AS NUMERIC(18,6))) AS avg_delivery_days, 100.0 * SUM(CASE WHEN delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*) AS pct_within_5_days FROM ( SELECT (DATE_DIFF('day', o.placed_at, s.delivered_at)) AS delivery_days FROM orders o JOIN fulfillments f ON f.order_id = o.id JOIN shipments s ON s.fulfillment_id = f.id WHERE o.status = 'fulfilled' AND o.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments f2 WHERE f2.order_id = o.id) = 1 AND (SELECT COUNT(*) FROM shipments s2 WHERE s2.fulfillment_id = f.id) = 1 ) all_orders ) totals ) combined ORDER BY (CASE WHEN carrier = 'ALL CARRIERS' THEN 1 ELSE 0 END) NULLS LAST, orders_fully_delivered DESC NULLS FIRST, carrier ASC NULLS LAST
SELECT carrier, orders_fully_delivered, avg_delivery_days, pct_within_5_days, share_of_orders_pct FROM ( SELECT carrier, orders_fully_delivered, avg_delivery_days, pct_within_5_days, share_of_orders_pct FROM ( SELECT d.carrier AS carrier, COUNT(*) AS orders_fully_delivered, AVG(CAST(d.delivery_days AS NUMERIC(18,6))) AS avg_delivery_days, 100.0 * SUM(CASE WHEN d.delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*) AS pct_within_5_days, 100.0 * COUNT(*) / ( SELECT COUNT(*) FROM ( SELECT o2.id AS order_id FROM orders o2 JOIN fulfillments f2a ON f2a.order_id = o2.id JOIN shipments s2a ON s2a.fulfillment_id = f2a.id WHERE o2.status = 'fulfilled' AND o2.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o2.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s2a.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments ff WHERE ff.order_id = o2.id) = 1 AND (SELECT COUNT(*) FROM shipments ss WHERE ss.fulfillment_id = f2a.id) = 1 ) all_d ) AS share_of_orders_pct FROM ( SELECT o.id AS order_id, s.carrier AS carrier, (CAST(s.delivered_at AS DATE) - CAST(o.placed_at AS DATE)) AS delivery_days FROM orders o JOIN fulfillments f ON f.order_id = o.id JOIN shipments s ON s.fulfillment_id = f.id WHERE o.status = 'fulfilled' AND o.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments f2 WHERE f2.order_id = o.id) = 1 AND (SELECT COUNT(*) FROM shipments s2 WHERE s2.fulfillment_id = f.id) = 1 ) d GROUP BY d.carrier ) per_carrier UNION ALL SELECT 'ALL CARRIERS' AS carrier, orders_fully_delivered, avg_delivery_days, pct_within_5_days, 100.0 AS share_of_orders_pct FROM ( SELECT COUNT(*) AS orders_fully_delivered, AVG(CAST(delivery_days AS NUMERIC(18,6))) AS avg_delivery_days, 100.0 * SUM(CASE WHEN delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*) AS pct_within_5_days FROM ( SELECT ((CAST(s.delivered_at AS DATE) - CAST(o.placed_at AS DATE))) AS delivery_days FROM orders o JOIN fulfillments f ON f.order_id = o.id JOIN shipments s ON s.fulfillment_id = f.id WHERE o.status = 'fulfilled' AND o.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments f2 WHERE f2.order_id = o.id) = 1 AND (SELECT COUNT(*) FROM shipments s2 WHERE s2.fulfillment_id = f.id) = 1 ) all_orders ) totals ) combined ORDER BY (CASE WHEN carrier = 'ALL CARRIERS' THEN 1 ELSE 0 END) NULLS LAST, orders_fully_delivered DESC NULLS FIRST, carrier ASC NULLS LAST
SELECT carrier, orders_fully_delivered, avg_delivery_days, pct_within_5_days, share_of_orders_pct FROM ( SELECT carrier, orders_fully_delivered, avg_delivery_days, pct_within_5_days, share_of_orders_pct FROM ( SELECT d.carrier AS carrier, COUNT(*) AS orders_fully_delivered, AVG(CAST(d.delivery_days AS NUMERIC(18,6))) AS avg_delivery_days, 100.0 * SUM(CASE WHEN d.delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*) AS pct_within_5_days, 100.0 * COUNT(*) / ( SELECT COUNT(*) FROM ( SELECT o2.id AS order_id FROM orders o2 JOIN fulfillments f2a ON f2a.order_id = o2.id JOIN shipments s2a ON s2a.fulfillment_id = f2a.id WHERE o2.status = 'fulfilled' AND o2.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o2.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s2a.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments ff WHERE ff.order_id = o2.id) = 1 AND (SELECT COUNT(*) FROM shipments ss WHERE ss.fulfillment_id = f2a.id) = 1 ) all_d ) AS share_of_orders_pct FROM ( SELECT o.id AS order_id, s.carrier AS carrier, dateDiff('day', o.placed_at, s.delivered_at) AS delivery_days FROM orders o JOIN fulfillments f ON f.order_id = o.id JOIN shipments s ON s.fulfillment_id = f.id WHERE o.status = 'fulfilled' AND o.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments f2 WHERE f2.order_id = o.id) = 1 AND (SELECT COUNT(*) FROM shipments s2 WHERE s2.fulfillment_id = f.id) = 1 ) d GROUP BY d.carrier ) per_carrier UNION ALL SELECT 'ALL CARRIERS' AS carrier, orders_fully_delivered, avg_delivery_days, pct_within_5_days, 100.0 AS share_of_orders_pct FROM ( SELECT COUNT(*) AS orders_fully_delivered, AVG(CAST(delivery_days AS NUMERIC(18,6))) AS avg_delivery_days, 100.0 * SUM(CASE WHEN delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*) AS pct_within_5_days FROM ( SELECT (dateDiff('day', o.placed_at, s.delivered_at)) AS delivery_days FROM orders o JOIN fulfillments f ON f.order_id = o.id JOIN shipments s ON s.fulfillment_id = f.id WHERE o.status = 'fulfilled' AND o.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments f2 WHERE f2.order_id = o.id) = 1 AND (SELECT COUNT(*) FROM shipments s2 WHERE s2.fulfillment_id = f.id) = 1 ) all_orders ) totals ) combined ORDER BY (CASE WHEN carrier = 'ALL CARRIERS' THEN 1 ELSE 0 END) NULLS LAST, orders_fully_delivered 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: Order-level delivery reliability by carrier, full year 2025 (unified source) Question: What is each carrier's order-level delivery reliability (orders placed to orders delivered, within 5 days), in one single consistent query I can use as the ground truth for a reliability ceiling calculation? Grain: one row per carrier, full year 2025 Columns: - carrier - orders_fully_delivered - avg_delivery_days (placed_at to delivered_at) - pct_within_5_days - share_of_orders_pct Details: Scope: orders with placed_at between 2025-01-01 and 2025-12-31, status='fulfilled', restricted to orders with exactly one row in fulfillments and that fulfillment linked to exactly one row in shipments, with that shipment's delivered_at not null (one carrier per order, cleanly attributed). delivery_days = DATEDIFF(day, orders.placed_at, shipments.delivered_at). Group by shipments.carrier. Please also show the grand total as a final row labeled 'ALL CARRIERS' (sum of orders_fully_delivered across all carriers, and the overall pct_within_5_days across that full population) so I can sanity-check that individual carrier rows sum correctly. Round avg_delivery_days to 2 decimals, pct columns to 1 decimal. Order by orders_fully_delivered descending, with the 'ALL CARRIERS' total row last. Expect about 15 rows (14 carriers + 1 total). Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runSELECT carrier, orders_fully_delivered, avg_delivery_days, pct_within_5_days, share_of_orders_pct, 0 AS sort_rank FROM ( SELECT d.carrier AS carrier, COUNT(*) AS orders_fully_delivered, ROUND(CAST(AVG(d.delivery_days) AS numeric), 2) AS avg_delivery_days, ROUND(100.0 * SUM(CASE WHEN d.delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_5_days, ROUND(100.0 * COUNT(*) / ( SELECT COUNT(*) FROM ( SELECT o2.id AS order_id FROM orders o2 JOIN fulfillments f2a ON f2a.order_id = o2.id JOIN shipments s2a ON s2a.fulfillment_id = f2a.id WHERE o2.status = 'fulfilled' AND o2.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o2.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s2a.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments ff WHERE ff.order_id = o2.id) = 1 AND (SELECT COUNT(*) FROM shipments ss WHERE ss.fulfillment_id = f2a.id) = 1 ) all_d ), 1) AS share_of_orders_pct FROM ( SELECT o.id AS order_id, s.carrier AS carrier, (CAST(s.delivered_at AS DATE) - CAST(o.placed_at AS DATE)) AS delivery_days FROM orders o JOIN fulfillments f ON f.order_id = o.id JOIN shipments s ON s.fulfillment_id = f.id WHERE o.status = 'fulfilled' AND o.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments f2 WHERE f2.order_id = o.id) = 1 AND (SELECT COUNT(*) FROM shipments s2 WHERE s2.fulfillment_id = f.id) = 1 ) d GROUP BY d.carrier ) per_carrier UNION ALL SELECT 'ALL CARRIERS' AS carrier, orders_fully_delivered, avg_delivery_days, pct_within_5_days, 100.0 AS share_of_orders_pct, 1 AS sort_rank FROM ( SELECT COUNT(*) AS orders_fully_delivered, ROUND(CAST(AVG(delivery_days) AS numeric), 2) AS avg_delivery_days, ROUND(100.0 * SUM(CASE WHEN delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_5_days FROM ( SELECT (CAST(s.delivered_at AS DATE) - CAST(o.placed_at AS DATE)) AS delivery_days FROM orders o JOIN fulfillments f ON f.order_id = o.id JOIN shipments s ON s.fulfillment_id = f.id WHERE o.status = 'fulfilled' AND o.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments f2 WHERE f2.order_id = o.id) = 1 AND (SELECT COUNT(*) FROM shipments s2 WHERE s2.fulfillment_id = f.id) = 1 ) all_orders ) totals ORDER BY sort_rank, orders_fully_delivered DESC
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: carrier, orders_fully_delivered, avg_delivery_days, pct_within_5_days, share_of_orders_pct, sort_rank
dry_runSELECT carrier, orders_fully_delivered, avg_delivery_days, pct_within_5_days, share_of_orders_pct FROM ( SELECT d.carrier AS carrier, COUNT(*) AS orders_fully_delivered, ROUND(CAST(AVG(d.delivery_days) AS numeric), 2) AS avg_delivery_days, ROUND(100.0 * SUM(CASE WHEN d.delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_5_days, ROUND(100.0 * COUNT(*) / ( SELECT COUNT(*) FROM ( SELECT o2.id AS order_id FROM orders o2 JOIN fulfillments f2a ON f2a.order_id = o2.id JOIN shipments s2a ON s2a.fulfillment_id = f2a.id WHERE o2.status = 'fulfilled' AND o2.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o2.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s2a.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments ff WHERE ff.order_id = o2.id) = 1 AND (SELECT COUNT(*) FROM shipments ss WHERE ss.fulfillment_id = f2a.id) = 1 ) all_d ), 1) AS share_of_orders_pct FROM ( SELECT o.id AS order_id, s.carrier AS carrier, (CAST(s.delivered_at AS DATE) - CAST(o.placed_at AS DATE)) AS delivery_days FROM orders o JOIN fulfillments f ON f.order_id = o.id JOIN shipments s ON s.fulfillment_id = f.id WHERE o.status = 'fulfilled' AND o.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments f2 WHERE f2.order_id = o.id) = 1 AND (SELECT COUNT(*) FROM shipments s2 WHERE s2.fulfillment_id = f.id) = 1 ) d GROUP BY d.carrier ) per_carrier UNION ALL SELECT 'ALL CARRIERS' AS carrier, orders_fully_delivered, avg_delivery_days, pct_within_5_days, 100.0 AS share_of_orders_pct FROM ( SELECT COUNT(*) AS orders_fully_delivered, ROUND(CAST(AVG(delivery_days) AS numeric), 2) AS avg_delivery_days, ROUND(100.0 * SUM(CASE WHEN delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_5_days FROM ( SELECT (CAST(s.delivered_at AS DATE) - CAST(o.placed_at AS DATE)) AS delivery_days FROM orders o JOIN fulfillments f ON f.order_id = o.id JOIN shipments s ON s.fulfillment_id = f.id WHERE o.status = 'fulfilled' AND o.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments f2 WHERE f2.order_id = o.id) = 1 AND (SELECT COUNT(*) FROM shipments s2 WHERE s2.fulfillment_id = f.id) = 1 ) all_orders ) totals ORDER BY (CASE WHEN carrier = 'ALL CARRIERS' THEN 1 ELSE 0 END), orders_fully_delivered DESC, carrier ASC
STATUS: REJECTED - does not compile: Binder Error: Could not ORDER BY column "CASE WHEN ((per_carrier.carrier = 'ALL CARRIERS')) THEN (1) ELSE 0 END": add the expression/function to every SELECT, or move the UNION into a FROM clause.
dry_runSELECT carrier, orders_fully_delivered, avg_delivery_days, pct_within_5_days, share_of_orders_pct FROM ( SELECT carrier, orders_fully_delivered, avg_delivery_days, pct_within_5_days, share_of_orders_pct FROM ( SELECT d.carrier AS carrier, COUNT(*) AS orders_fully_delivered, ROUND(CAST(AVG(d.delivery_days) AS numeric), 2) AS avg_delivery_days, ROUND(100.0 * SUM(CASE WHEN d.delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_5_days, ROUND(100.0 * COUNT(*) / ( SELECT COUNT(*) FROM ( SELECT o2.id AS order_id FROM orders o2 JOIN fulfillments f2a ON f2a.order_id = o2.id JOIN shipments s2a ON s2a.fulfillment_id = f2a.id WHERE o2.status = 'fulfilled' AND o2.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o2.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s2a.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments ff WHERE ff.order_id = o2.id) = 1 AND (SELECT COUNT(*) FROM shipments ss WHERE ss.fulfillment_id = f2a.id) = 1 ) all_d ), 1) AS share_of_orders_pct FROM ( SELECT o.id AS order_id, s.carrier AS carrier, (CAST(s.delivered_at AS DATE) - CAST(o.placed_at AS DATE)) AS delivery_days FROM orders o JOIN fulfillments f ON f.order_id = o.id JOIN shipments s ON s.fulfillment_id = f.id WHERE o.status = 'fulfilled' AND o.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments f2 WHERE f2.order_id = o.id) = 1 AND (SELECT COUNT(*) FROM shipments s2 WHERE s2.fulfillment_id = f.id) = 1 ) d GROUP BY d.carrier ) per_carrier UNION ALL SELECT 'ALL CARRIERS' AS carrier, orders_fully_delivered, avg_delivery_days, pct_within_5_days, 100.0 AS share_of_orders_pct FROM ( SELECT COUNT(*) AS orders_fully_delivered, ROUND(CAST(AVG(delivery_days) AS numeric), 2) AS avg_delivery_days, ROUND(100.0 * SUM(CASE WHEN delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_5_days FROM ( SELECT (CAST(s.delivered_at AS DATE) - CAST(o.placed_at AS DATE)) AS delivery_days FROM orders o JOIN fulfillments f ON f.order_id = o.id JOIN shipments s ON s.fulfillment_id = f.id WHERE o.status = 'fulfilled' AND o.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments f2 WHERE f2.order_id = o.id) = 1 AND (SELECT COUNT(*) FROM shipments s2 WHERE s2.fulfillment_id = f.id) = 1 ) all_orders ) totals ) combined ORDER BY (CASE WHEN carrier = 'ALL CARRIERS' THEN 1 ELSE 0 END), orders_fully_delivered DESC, carrier ASC
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: carrier, orders_fully_delivered, avg_delivery_days, pct_within_5_days, share_of_orders_pct
submitSELECT carrier, orders_fully_delivered, avg_delivery_days, pct_within_5_days, share_of_orders_pct FROM ( SELECT carrier, orders_fully_delivered, avg_delivery_days, pct_within_5_days, share_of_orders_pct FROM ( SELECT d.carrier AS carrier, COUNT(*) AS orders_fully_delivered, ROUND(CAST(AVG(d.delivery_days) AS numeric), 2) AS avg_delivery_days, ROUND(100.0 * SUM(CASE WHEN d.delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_5_days, ROUND(100.0 * COUNT(*) / ( SELECT COUNT(*) FROM ( SELECT o2.id AS order_id FROM orders o2 JOIN fulfillments f2a ON f2a.order_id = o2.id JOIN shipments s2a ON s2a.fulfillment_id = f2a.id WHERE o2.status = 'fulfilled' AND o2.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o2.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s2a.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments ff WHERE ff.order_id = o2.id) = 1 AND (SELECT COUNT(*) FROM shipments ss WHERE ss.fulfillment_id = f2a.id) = 1 ) all_d ), 1) AS share_of_orders_pct FROM ( SELECT o.id AS order_id, s.carrier AS carrier, (CAST(s.delivered_at AS DATE) - CAST(o.placed_at AS DATE)) AS delivery_days FROM orders o JOIN fulfillments f ON f.order_id = o.id JOIN shipments s ON s.fulfillment_id = f.id WHERE o.status = 'fulfilled' AND o.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments f2 WHERE f2.order_id = o.id) = 1 AND (SELECT COUNT(*) FROM shipments s2 WHERE s2.fulfillment_id = f.id) = 1 ) d GROUP BY d.carrier ) per_carrier UNION ALL SELECT 'ALL CARRIERS' AS carrier, orders_fully_delivered, avg_delivery_days, pct_within_5_days, 100.0 AS share_of_orders_pct FROM ( SELECT COUNT(*) AS orders_fully_delivered, ROUND(CAST(AVG(delivery_days) AS numeric), 2) AS avg_delivery_days, ROUND(100.0 * SUM(CASE WHEN delivery_days <= 5 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_within_5_days FROM ( SELECT (CAST(s.delivered_at AS DATE) - CAST(o.placed_at AS DATE)) AS delivery_days FROM orders o JOIN fulfillments f ON f.order_id = o.id JOIN shipments s ON s.fulfillment_id = f.id WHERE o.status = 'fulfilled' AND o.placed_at >= TIMESTAMP '2025-01-01 00:00:00' AND o.placed_at < TIMESTAMP '2026-01-01 00:00:00' AND s.delivered_at IS NOT NULL AND (SELECT COUNT(*) FROM fulfillments f2 WHERE f2.order_id = o.id) = 1 AND (SELECT COUNT(*) FROM shipments s2 WHERE s2.fulfillment_id = f.id) = 1 ) all_orders ) totals ) combined ORDER BY (CASE WHEN carrier = 'ALL CARRIERS' THEN 1 ELSE 0 END), orders_fully_delivered DESC, carrier ASC
Submitted to tonight's batch. Your work on this request is done.