Queries · q029 · Chief Operating Officer · week 1, night 2
“Are there specific fulfillment sites that are consistently slower to process or less reliable on final delivery time?”
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 2, request 2 of 3
Night 1 take-aways: (a) delivery times are broadly stable most of the year (avg ~3.6-4.1 days, median 3-4, ~83-90% within 5 days), but both Nov 2025 and Dec 2024 show clear peak-season degradation (Nov 2025 worst: avg 4.7, only 78.7% within 5 days) -- confirms Priya's stress-test hypothesis; (b) the Oct-Dec 2025 weekly report pinpoints the worst weeks as 2025-11-24 and 2025-12-01 (Black Friday/Cyber Monday through early Dec), where pct-within-5-days fell to 73.5% and 56.1% and backlog (undelivered) roughly tripled, before recovering to 80-93% by mid-to-late December -- so the operation did wobble under peak load but recovered within 1-2 weeks; (c) the merchant-level report shows huge spread in reliability (pct-within-5-days from ~0.92 down to ~0.54) that does NOT line up cleanly with vertical (Food & Coffee has both the best and some of the worst performers), which points to carrier or warehouse-specific causes rather than product-category causes -- this is the natural next question (Priya's priority #3) and also helps explain the peak-season wobble. Tonight I'm moving into carriers and warehouses, and decomposing total delivery time into warehouse processing time vs. carrier transit time so we know which stage is actually the bottleneck, especially during peak.
The engineer · 1 dry run on the empty schema, then the submission
WITH order_fulfillment_counts AS ( SELECT order_id, COUNT(*) AS fulfillment_count FROM fulfillments GROUP BY order_id ), qualifying_fulfillments AS ( SELECT o.id AS order_id, o.placed_at, f.warehouse_id, f.shipped_at, f.delivered_at FROM orders o JOIN order_fulfillment_counts ofc ON ofc.order_id = o.id AND ofc.fulfillment_count = 1 JOIN fulfillments f ON f.order_id = o.id WHERE o.status = 'fulfilled' AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' AND f.shipped_at IS NOT NULL AND f.delivered_at IS NOT NULL ), order_metrics AS ( SELECT warehouse_id, (shipped_at::date - placed_at::date) AS processing_days, (delivered_at::date - placed_at::date) AS delivery_days FROM qualifying_fulfillments ), warehouse_agg AS ( SELECT warehouse_id, COUNT(*) AS orders_shipped, AVG(processing_days) AS avg_processing_days, 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 order_metrics GROUP BY warehouse_id HAVING COUNT(*) >= 30 ) SELECT w.name AS warehouse_name, t.name AS tenant_name, wa.orders_shipped, ROUND(wa.avg_processing_days, 2) AS avg_processing_days, ROUND(wa.avg_delivery_days, 2) AS avg_delivery_days, ROUND(wa.pct_within_5_days, 1) AS pct_within_5_days FROM warehouse_agg wa JOIN warehouses w ON w.id = wa.warehouse_id JOIN tenants t ON t.id = w.tenant_id ORDER BY wa.orders_shipped DESC, w.name ASC LIMIT 50
“Query compiles and produces the requested columns. Submitting.”
“Submitted. The report returns one row per qualifying warehouse (≥30 orders meeting the single-fulfillment, fully-shipped-and-delivered criteria in 2025), ranked by volume, with processing/delivery day averages and the on-time-within-5-days share so the COO's analyst can spot consistently slow or unreliable sites.”
As Lena Hartmann saw it, from the small dataset the agents worked on
warehouse_name | tenant_name | orders_shipped | avg_processing_days | avg_delivery_days | pct_within_5_days --------------------------------------+-------------------------------+----------------+---------------------+-------------------+------------------ Köln Fulfilment Centre | Brennquell Kaffee | 4071 | 1.03 | 3.55 | 91.7 Atlanta Fulfilment Centre | Cragsley Outfitters | 3219 | 0.79 | 3.2 | 95.2 Reading Fulfilment Centre | Marrowby & Vane | 1529 | 1.02 | 4.13 | 82.5 Manchester Fulfilment Centre | Grind Collective | 1082 | 1.5 | 5.12 | 71.6 Liège Fulfilment Centre | Spice Route Box | 755 | 1.58 | 4.38 | 76.2 Bromley Fulfilment Centre | Petalcourt Beauty | 646 | 1.14 | 5.0 | 77.4 Bromley Fulfilment Centre | Kibblewick | 644 | 1.26 | 4.75 | 77.6 San Francisco Fulfilment Centre | Honeycomb Larder | 592 | 1.22 | 3.59 | 88.5 Den Haag Fulfilment Centre | Studio Vrij | 532 | 2.26 | 4.97 | 69.0 Hamburg Fulfilment Centre | Ampfeld Elektronik | 418 | 1.51 | 3.92 | 84.9 Liverpool Fulfilment Centre | Fennel & Flint | 360 | 1.4 | 5.41 | 72.2 Galway Fulfilment Centre | Ballycorran Industrial Supply | 338 | 1.11 | 4.69 | 73.1 San Francisco Fulfilment Centre | Cinder Peak Sports Co. | 303 | 1.25 | 3.74 | 84.5 Dublin Fulfilment Centre | Sourdough Club | 298 | 1.53 | 5.07 | 69.8 Berlin Fulfilment Centre | Wiesgrund Wohnen | 270 | 1.4 | 4.01 | 84.4 Berlin Fulfilment Centre | Kontor24 Bürobedarf | 258 | 1.09 | 3.55 | 91.1 Amsterdam Fulfilment Centre | Linden & Loom | 237 | 1.58 | 4.21 | 82.7 Dortmund Fulfilment Centre | Gratlinie Outdoor | 212 | 1.44 | 3.93 | 84.0 Houston Fulfilment Centre | Feather & Fin | 207 | 0.98 | 3.4 | 95.2 Amsterdam Fulfilment Centre | Whisker Works | 172 | 1.24 | 3.97 | 82.0 Oslo Fulfilment Centre | OfficeHub Nordic | 163 | 1.12 | 4.1 | 82.8 Paris Fulfilment Centre | Rue Lafayette Studio | 155 | 1.3 | 4.19 | 78.7 San Antonio Fulfilment Centre | Castell Fasteners | 155 | 1.06 | 3.45 | 96.8 Uppsala Fulfilment Centre | Juniper Apothecary | 146 | 1.49 | 4.29 | 74.7 Göteborg Fulfilment Centre | Coastline Interiors | 143 | 1.44 | 4.27 | 79.7 København Fulfilment Centre | Oakhurst Tailoring | 139 | 1.65 | 4.32 | 79.1 Rotterdam Fulfilment Centre | Midland Safety Wear | 139 | 1.19 | 3.96 | 80.6 Nürnberg Fulfilment Centre | Wohnglück | 138 | 1.41 | 3.72 | 93.5 Odense Fulfilment Centre | Nordic Bloom Cosmetics | 132 | 1.2 | 4.28 | 81.1 Uppsala Fulfilment Centre | Velvet Ash | 114 | 1.29 | 4.3 | 76.3 Lund Fulfilment Centre | Saffron & Sage | 113 | 1.23 | 3.93 | 83.2 Köln Fulfilment Centre | Pfotenglück | 111 | 1.3 | 3.5 | 95.5 München Fulfilment Centre | Ostwind Mode | 108 | 1.3 | 3.94 | 79.6 Rotterdam Fulfilment Centre | Ridgeback Gear | 102 | 1.38 | 3.84 | 92.2 Leeds Fulfilment Centre | The Balm Room | 100 | 1.4 | 5.02 | 78.0 Bordeaux Fulfilment Centre | Chocolaterie Mireille | 99 | 1.31 | 4.93 | 64.6 Exeter Fulfilment Centre | Kayak Corner | 96 | 1.42 | 5.25 | 75.0 Zaandam Fulfilment Centre | Sorella Skin | 93 | 1.32 | 4.03 | 82.8 Augsburg Fulfilment Centre | Hautnah | 90 | 1.46 | 4.06 | 82.2 Manchester Fulfilment Centre | Willow & Wren | 90 | 1.4 | 4.72 | 83.3 Houston Fulfilment Centre | Tail Wag Supply | 87 | 1.05 | 3.39 | 93.1 Cambridge Fulfilment Centre | Thistlecombe | 84 | 1.25 | 6.07 | 58.3 Newcastle upon Tyne Fulfilment Centre | Deskline Supplies | 75 | 1.13 | 4.52 | 76.0 Odense Fulfilment Centre | Fell & Fjell | 72 | 1.25 | 4.08 | 80.6 Oslo Fulfilment Centre | Purrfect Pantry | 72 | 1.14 | 6.0 | 58.3 Hannover Fulfilment Centre | Hanse Industriebedarf | 71 | 1.2 | 3.96 | 85.9 Paris Fulfilment Centre | Mirelle | 60 | 1.3 | 3.83 | 88.3 Amsterdam Fulfilment Centre | Ampere & Co. | 56 | 1.29 | 4.29 | 75.0 Amersfoort Fulfilment Centre | Kettle & Key | 55 | 1.29 | 4.55 | 65.5 Liège Fulfilment Centre | Polder Packaging | 55 | 1.13 | 4.49 | 72.7 (50 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
WITH order_fulfillment_counts AS ( SELECT order_id, COUNT(*) AS fulfillment_count FROM fulfillments GROUP BY order_id),qualifying_fulfillments AS ( SELECT o.id AS order_id, o.placed_at, f.warehouse_id, f.shipped_at, f.delivered_at o.placed_at AS placed_at, f.warehouse_id AS warehouse_id, f.shipped_at AS shipped_at, f.delivered_at AS delivered_at FROM orders o JOIN order_fulfillment_counts ofc ON ofc.order_id = o.id AND ofc.fulfillment_count = 1 JOIN fulfillments f ON f.order_id = o.id WHERE o.status = 'fulfilled' AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' AND f.shipped_at IS NOT NULL AND f.delivered_at IS NOT NULL),order_metrics AS ( SELECT warehouse_id, (shipped_at::date - placed_at::date) AS processing_days, (delivered_at::date - placed_at::date) AS delivery_days {{date_diff_days(placed_at, shipped_at)}} AS processing_days, {{date_diff_days(placed_at, delivered_at)}} AS delivery_days FROM qualifying_fulfillments),warehouse_agg AS ( SELECT warehouse_id, COUNT(*) AS orders_shipped, AVG(processing_days) AS avg_processing_days, AVG(delivery_days) AS avg_delivery_days, AVG(CAST(processing_days AS NUMERIC(18,6))) AS avg_processing_days, AVG(CAST(delivery_days AS NUMERIC(18,6))) AS avg_delivery_days, AVG(CASE WHEN delivery_days <= 5 THEN 1.0 ELSE 0.0 END) * 100 AS pct_within_5_days FROM order_metrics GROUP BY warehouse_id HAVING COUNT(*) >= 30)SELECT w.name AS warehouse_name, t.name AS tenant_name, wa.orders_shipped, ROUND(wa.avg_processing_days, 2) AS avg_processing_days, ROUND(wa.avg_delivery_days, 2) AS avg_delivery_days, ROUND(wa.pct_within_5_days, 1) AS pct_within_5_days wa.orders_shipped AS orders_shipped, wa.avg_processing_days AS avg_processing_days, wa.avg_delivery_days AS avg_delivery_days, wa.pct_within_5_days AS pct_within_5_daysFROM warehouse_agg waJOIN warehouses w ON w.id = wa.warehouse_idJOIN tenants t ON t.id = w.tenant_idORDER BY wa.orders_shipped DESC, w.name ASCLIMIT 50ORDER BY wa.orders_shipped DESC NULLS FIRST, w.name ASC NULLS LAST, w.id ASC NULLS LAST{{fetch_first(50)}}
WITH order_fulfillment_counts AS ( SELECT order_id, COUNT(*) AS fulfillment_count FROM fulfillments GROUP BY order_id ), qualifying_fulfillments AS ( SELECT o.id AS order_id, o.placed_at AS placed_at, f.warehouse_id AS warehouse_id, f.shipped_at AS shipped_at, f.delivered_at AS delivered_at FROM orders o JOIN order_fulfillment_counts ofc ON ofc.order_id = o.id AND ofc.fulfillment_count = 1 JOIN fulfillments f ON f.order_id = o.id WHERE o.status = 'fulfilled' AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' AND f.shipped_at IS NOT NULL AND f.delivered_at IS NOT NULL ), order_metrics AS ( SELECT warehouse_id, DATE_DIFF('day', placed_at, shipped_at) AS processing_days, DATE_DIFF('day', placed_at, delivered_at) AS delivery_days FROM qualifying_fulfillments ), warehouse_agg AS ( SELECT warehouse_id, COUNT(*) AS orders_shipped, AVG(CAST(processing_days AS NUMERIC(18,6))) AS avg_processing_days, AVG(CAST(delivery_days AS NUMERIC(18,6))) AS avg_delivery_days, AVG(CASE WHEN delivery_days <= 5 THEN 1.0 ELSE 0.0 END) * 100 AS pct_within_5_days FROM order_metrics GROUP BY warehouse_id HAVING COUNT(*) >= 30 ) SELECT w.name AS warehouse_name, t.name AS tenant_name, wa.orders_shipped AS orders_shipped, wa.avg_processing_days AS avg_processing_days, wa.avg_delivery_days AS avg_delivery_days, wa.pct_within_5_days AS pct_within_5_days FROM warehouse_agg wa JOIN warehouses w ON w.id = wa.warehouse_id JOIN tenants t ON t.id = w.tenant_id ORDER BY wa.orders_shipped DESC NULLS FIRST, w.name ASC NULLS LAST, w.id ASC NULLS LAST FETCH FIRST 50 ROWS ONLY
WITH order_fulfillment_counts AS ( SELECT order_id, COUNT(*) AS fulfillment_count FROM fulfillments GROUP BY order_id ), qualifying_fulfillments AS ( SELECT o.id AS order_id, o.placed_at AS placed_at, f.warehouse_id AS warehouse_id, f.shipped_at AS shipped_at, f.delivered_at AS delivered_at FROM orders o JOIN order_fulfillment_counts ofc ON ofc.order_id = o.id AND ofc.fulfillment_count = 1 JOIN fulfillments f ON f.order_id = o.id WHERE o.status = 'fulfilled' AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' AND f.shipped_at IS NOT NULL AND f.delivered_at IS NOT NULL ), order_metrics AS ( SELECT warehouse_id, (CAST(shipped_at AS DATE) - CAST(placed_at AS DATE)) AS processing_days, (CAST(delivered_at AS DATE) - CAST(placed_at AS DATE)) AS delivery_days FROM qualifying_fulfillments ), warehouse_agg AS ( SELECT warehouse_id, COUNT(*) AS orders_shipped, AVG(CAST(processing_days AS NUMERIC(18,6))) AS avg_processing_days, AVG(CAST(delivery_days AS NUMERIC(18,6))) AS avg_delivery_days, AVG(CASE WHEN delivery_days <= 5 THEN 1.0 ELSE 0.0 END) * 100 AS pct_within_5_days FROM order_metrics GROUP BY warehouse_id HAVING COUNT(*) >= 30 ) SELECT w.name AS warehouse_name, t.name AS tenant_name, wa.orders_shipped AS orders_shipped, wa.avg_processing_days AS avg_processing_days, wa.avg_delivery_days AS avg_delivery_days, wa.pct_within_5_days AS pct_within_5_days FROM warehouse_agg wa JOIN warehouses w ON w.id = wa.warehouse_id JOIN tenants t ON t.id = w.tenant_id ORDER BY wa.orders_shipped DESC NULLS FIRST, w.name ASC NULLS LAST, w.id ASC NULLS LAST FETCH FIRST 50 ROWS ONLY
WITH order_fulfillment_counts AS ( SELECT order_id, COUNT(*) AS fulfillment_count FROM fulfillments GROUP BY order_id ), qualifying_fulfillments AS ( SELECT o.id AS order_id, o.placed_at AS placed_at, f.warehouse_id AS warehouse_id, f.shipped_at AS shipped_at, f.delivered_at AS delivered_at FROM orders o JOIN order_fulfillment_counts ofc ON ofc.order_id = o.id AND ofc.fulfillment_count = 1 JOIN fulfillments f ON f.order_id = o.id WHERE o.status = 'fulfilled' AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' AND f.shipped_at IS NOT NULL AND f.delivered_at IS NOT NULL ), order_metrics AS ( SELECT warehouse_id, (CAST(shipped_at AS DATE) - CAST(placed_at AS DATE)) AS processing_days, (CAST(delivered_at AS DATE) - CAST(placed_at AS DATE)) AS delivery_days FROM qualifying_fulfillments ), warehouse_agg AS ( SELECT warehouse_id, COUNT(*) AS orders_shipped, AVG(CAST(processing_days AS NUMERIC(18,6))) AS avg_processing_days, AVG(CAST(delivery_days AS NUMERIC(18,6))) AS avg_delivery_days, AVG(CASE WHEN delivery_days <= 5 THEN 1.0 ELSE 0.0 END) * 100 AS pct_within_5_days FROM order_metrics GROUP BY warehouse_id HAVING COUNT(*) >= 30 ) SELECT w.name AS warehouse_name, t.name AS tenant_name, wa.orders_shipped AS orders_shipped, wa.avg_processing_days AS avg_processing_days, wa.avg_delivery_days AS avg_delivery_days, wa.pct_within_5_days AS pct_within_5_days FROM warehouse_agg wa JOIN warehouses w ON w.id = wa.warehouse_id JOIN tenants t ON t.id = w.tenant_id ORDER BY wa.orders_shipped DESC NULLS FIRST, w.name ASC NULLS LAST, w.id ASC NULLS LAST LIMIT 50
WITH order_fulfillment_counts AS ( SELECT order_id, COUNT(*) AS fulfillment_count FROM fulfillments GROUP BY order_id ), qualifying_fulfillments AS ( SELECT o.id AS order_id, o.placed_at AS placed_at, f.warehouse_id AS warehouse_id, f.shipped_at AS shipped_at, f.delivered_at AS delivered_at FROM orders o JOIN order_fulfillment_counts ofc ON ofc.order_id = o.id AND ofc.fulfillment_count = 1 JOIN fulfillments f ON f.order_id = o.id WHERE o.status = 'fulfilled' AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' AND f.shipped_at IS NOT NULL AND f.delivered_at IS NOT NULL ), order_metrics AS ( SELECT warehouse_id, dateDiff('day', placed_at, shipped_at) AS processing_days, dateDiff('day', placed_at, delivered_at) AS delivery_days FROM qualifying_fulfillments ), warehouse_agg AS ( SELECT warehouse_id, COUNT(*) AS orders_shipped, AVG(CAST(processing_days AS NUMERIC(18,6))) AS avg_processing_days, AVG(CAST(delivery_days AS NUMERIC(18,6))) AS avg_delivery_days, AVG(CASE WHEN delivery_days <= 5 THEN 1.0 ELSE 0.0 END) * 100 AS pct_within_5_days FROM order_metrics GROUP BY warehouse_id HAVING COUNT(*) >= 30 ) SELECT w.name AS warehouse_name, t.name AS tenant_name, wa.orders_shipped AS orders_shipped, wa.avg_processing_days AS avg_processing_days, wa.avg_delivery_days AS avg_delivery_days, wa.pct_within_5_days AS pct_within_5_days FROM warehouse_agg wa JOIN warehouses w ON w.id = wa.warehouse_id JOIN tenants t ON t.id = w.tenant_id ORDER BY wa.orders_shipped DESC NULLS FIRST, w.name ASC NULLS LAST, w.id ASC 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: Fulfillment performance by warehouse, 2025 Question: Are there specific fulfillment sites that are consistently slower to process or less reliable on final delivery time? Grain: one row per warehouse Columns: - warehouse_name - tenant_name - orders_shipped (count in 2025) - avg_processing_days (placed_at to fulfillment.shipped_at) - avg_delivery_days (placed_at to fulfillment.delivered_at) - pct_within_5_days (share with delivery_days <= 5) Details: Scope: orders with placed_at in 2025-01-01 through 2025-12-31, status = 'fulfilled', restricted to orders that have exactly one row in fulfillments (so the order can be cleanly attributed to a single warehouse) with both shipped_at and delivered_at not null. processing_days = DATEDIFF(day, orders.placed_at, fulfillments.shipped_at); delivery_days = DATEDIFF(day, orders.placed_at, fulfillments.delivered_at). Join fulfillments.warehouse_id -> warehouses, and warehouses.tenant_id -> tenants for tenant_name. Round avg_processing_days and avg_delivery_days to 2 decimals, pct_within_5_days to 1 decimal (as %). Include only warehouses with at least 30 qualifying orders. Order by orders_shipped descending, tie-break by warehouse_name ascending. Cap at 50 rows. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runWITH order_fulfillment_counts AS ( SELECT order_id, COUNT(*) AS fulfillment_count FROM fulfillments GROUP BY order_id ), qualifying_fulfillments AS ( SELECT o.id AS order_id, o.placed_at, f.warehouse_id, f.shipped_at, f.delivered_at FROM orders o JOIN order_fulfillment_counts ofc ON ofc.order_id = o.id AND ofc.fulfillment_count = 1 JOIN fulfillments f ON f.order_id = o.id WHERE o.status = 'fulfilled' AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' AND f.shipped_at IS NOT NULL AND f.delivered_at IS NOT NULL ), order_metrics AS ( SELECT warehouse_id, (shipped_at::date - placed_at::date) AS processing_days, (delivered_at::date - placed_at::date) AS delivery_days FROM qualifying_fulfillments ), warehouse_agg AS ( SELECT warehouse_id, COUNT(*) AS orders_shipped, AVG(processing_days) AS avg_processing_days, 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 order_metrics GROUP BY warehouse_id HAVING COUNT(*) >= 30 ) SELECT w.name AS warehouse_name, t.name AS tenant_name, wa.orders_shipped, ROUND(wa.avg_processing_days, 2) AS avg_processing_days, ROUND(wa.avg_delivery_days, 2) AS avg_delivery_days, ROUND(wa.pct_within_5_days, 1) AS pct_within_5_days FROM warehouse_agg wa JOIN warehouses w ON w.id = wa.warehouse_id JOIN tenants t ON t.id = w.tenant_id ORDER BY wa.orders_shipped DESC, w.name ASC LIMIT 50
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: warehouse_name, tenant_name, orders_shipped, avg_processing_days, avg_delivery_days, pct_within_5_days
submitWITH order_fulfillment_counts AS ( SELECT order_id, COUNT(*) AS fulfillment_count FROM fulfillments GROUP BY order_id ), qualifying_fulfillments AS ( SELECT o.id AS order_id, o.placed_at, f.warehouse_id, f.shipped_at, f.delivered_at FROM orders o JOIN order_fulfillment_counts ofc ON ofc.order_id = o.id AND ofc.fulfillment_count = 1 JOIN fulfillments f ON f.order_id = o.id WHERE o.status = 'fulfilled' AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' AND f.shipped_at IS NOT NULL AND f.delivered_at IS NOT NULL ), order_metrics AS ( SELECT warehouse_id, (shipped_at::date - placed_at::date) AS processing_days, (delivered_at::date - placed_at::date) AS delivery_days FROM qualifying_fulfillments ), warehouse_agg AS ( SELECT warehouse_id, COUNT(*) AS orders_shipped, AVG(processing_days) AS avg_processing_days, 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 order_metrics GROUP BY warehouse_id HAVING COUNT(*) >= 30 ) SELECT w.name AS warehouse_name, t.name AS tenant_name, wa.orders_shipped, ROUND(wa.avg_processing_days, 2) AS avg_processing_days, ROUND(wa.avg_delivery_days, 2) AS avg_delivery_days, ROUND(wa.pct_within_5_days, 1) AS pct_within_5_days FROM warehouse_agg wa JOIN warehouses w ON w.id = wa.warehouse_id JOIN tenants t ON t.id = w.tenant_id ORDER BY wa.orders_shipped DESC, w.name ASC LIMIT 50
Submitted to tonight's batch. Your work on this request is done.