Queries · q026 · Chief Operating Officer · week 1, night 1
“Which merchants deliver reliably fast, and which are a coin toss, over the past year?”
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 1, request 2 of 3
Priya's top two priorities are (1) end-to-end delivery speed and its consistency/trend, and (2) how the last peak season held up. I'm spending all 3 slots tonight on those, leaving carriers/warehouses, inventory and returns for later nights once I've seen where the baseline numbers point. I'm defining "delivered" as order status = fulfilled with all linked shipments having a non-null delivered_at, and delivery time as placed_at -> last shipment delivered_at. Report 1 gives the 24-month trend + variability (median/p90/SLA-hit-rate) so I can see direction of travel and spread in one page. Report 2 cuts the same metrics by merchant for 2025 to see who is reliably fast vs. a coin toss. Report 3 looks at the most recent peak window (Oct-Dec 2025) weekly to see if speed degraded under volume and whether a backlog built up and cleared by year-end. I'll sanity-check all three against each other (e.g. does the peak-season weekly average line up with the Nov-Dec rows in the monthly trend) before going further.
The engineer · 1 dry run on the empty schema, then the submission
SELECT t.name AS tenant_name, t.industry, d.orders_delivered, ROUND(d.avg_delivery_days, 2) AS avg_delivery_days, ROUND(d.median_delivery_days, 2) AS median_delivery_days, ROUND(d.p90_delivery_days, 2) AS p90_delivery_days, ROUND(d.pct_within_5_days, 4) AS pct_within_5_days FROM tenants t JOIN ( SELECT o.tenant_id, COUNT(*) AS orders_delivered, AVG(EXTRACT(EPOCH FROM (o.delivered_at - o.placed_at)) / 86400.0) AS avg_delivery_days, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (o.delivered_at - o.placed_at)) / 86400.0) AS median_delivery_days, PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (o.delivered_at - o.placed_at)) / 86400.0) AS p90_delivery_days, AVG(CASE WHEN EXTRACT(EPOCH FROM (o.delivered_at - o.placed_at)) / 86400.0 <= 5 THEN 1.0 ELSE 0.0 END) AS pct_within_5_days FROM ( SELECT ord.id, ord.tenant_id, ord.placed_at, (SELECT MAX(s.delivered_at) FROM fulfillments f JOIN shipments s ON s.fulfillment_id = f.id WHERE f.order_id = ord.id) AS delivered_at FROM orders ord WHERE ord.status = 'fulfilled' AND ord.placed_at >= DATE '2025-01-01' AND ord.placed_at < DATE '2026-01-01' AND EXISTS ( SELECT 1 FROM fulfillments f JOIN shipments s ON s.fulfillment_id = f.id WHERE f.order_id = ord.id ) AND NOT EXISTS ( SELECT 1 FROM fulfillments f JOIN shipments s ON s.fulfillment_id = f.id WHERE f.order_id = ord.id AND s.delivered_at IS NULL ) ) o GROUP BY o.tenant_id HAVING COUNT(*) >= 50 ) d ON d.tenant_id = t.id ORDER BY d.orders_delivered DESC, t.name ASC LIMIT 50
“Query compiles and returns the requested columns. Submitting.”
“Submitted. The report computes delivery_days as placed_at → the latest delivered_at across all shipments linked to each order (via fulfillments), only counting orders where every linked shipment shows as delivered — consistent with the monthly trend report's definition. It filters to status = 'fulfilled' orders placed in 2025, groups by tenant with a minimum of 50 delivered orders, and surfaces average, median (p50), p90, and the share delivered within 5 days, ordered by volume then tenant name, capped at 50 rows.”
As Lena Hartmann saw it, from the small dataset the agents worked on
tenant_name | industry | orders_delivered | avg_delivery_days | median_delivery_days | p90_delivery_days | pct_within_5_days ------------------------------+---------------------------+------------------+-------------------+----------------------+-------------------+------------------ Brennquell Kaffee | Food & Coffee | 4071 | 3.48 | 3.22 | 5.15 | 0.8828 Cragsley Outfitters | Apparel & Footwear | 3219 | 3.39 | 3.13 | 4.96 | 0.9031 Marrowby & Vane | Apparel & Footwear | 1529 | 4.09 | 3.34 | 7.28 | 0.7933 Grind Collective | Food & Coffee | 1082 | 5.1 | 3.98 | 9.12 | 0.6636 Spice Route Box | Food & Coffee | 755 | 4.31 | 4.18 | 6.55 | 0.6742 Petalcourt Beauty | Beauty | 646 | 4.98 | 3.82 | 7.92 | 0.7183 Kibblewick | Pet Supplies | 644 | 4.72 | 3.88 | 7.98 | 0.7298 Honeycomb Larder | Food & Coffee | 592 | 3.75 | 3.56 | 5.78 | 0.8226 Studio Vrij | Apparel & Footwear | 532 | 4.92 | 4.02 | 8.96 | 0.6259 Ampfeld Elektronik | Consumer Electronics | 418 | 3.88 | 3.61 | 5.97 | 0.7919 Fennel & Flint | Home & Living | 360 | 5.36 | 4.0 | 10.09 | 0.6583 Ballycorran Industrial Supply | Industrial Supplies (B2B) | 338 | 4.74 | 4.14 | 6.94 | 0.6509 Cinder Peak Sports Co. | Sports & Outdoor | 303 | 3.91 | 3.63 | 6.24 | 0.7954 Sourdough Club | Food & Coffee | 298 | 5.03 | 4.24 | 8.77 | 0.6342 Wiesgrund Wohnen | Home & Living | 270 | 3.94 | 3.77 | 6.14 | 0.7704 Kontor24 Bürobedarf | Office Supplies (B2B) | 258 | 3.6 | 3.28 | 5.29 | 0.8295 Linden & Loom | Home & Living | 237 | 4.12 | 3.86 | 6.12 | 0.7679 Gratlinie Outdoor | Sports & Outdoor | 212 | 3.91 | 3.43 | 6.06 | 0.7925 Feather & Fin | Pet Supplies | 207 | 3.63 | 3.56 | 5.01 | 0.8937 Whisker Works | Pet Supplies | 172 | 3.89 | 3.72 | 6.18 | 0.7733 OfficeHub Nordic | Office Supplies (B2B) | 163 | 4.16 | 3.86 | 6.32 | 0.7301 Castell Fasteners | Industrial Supplies (B2B) | 155 | 3.47 | 3.33 | 5.11 | 0.8839 Rue Lafayette Studio | Apparel & Footwear | 155 | 4.13 | 3.94 | 6.69 | 0.7226 Juniper Apothecary | Beauty | 146 | 4.23 | 3.92 | 6.69 | 0.7055 Coastline Interiors | Home & Living | 143 | 4.2 | 4.1 | 6.14 | 0.7203 Midland Safety Wear | Industrial Supplies (B2B) | 139 | 4.0 | 3.86 | 6.27 | 0.7482 Oakhurst Tailoring | Apparel & Footwear | 139 | 4.32 | 4.1 | 6.35 | 0.7194 Wohnglück | Home & Living | 138 | 3.65 | 3.65 | 5.16 | 0.8478 Nordic Bloom Cosmetics | Beauty | 132 | 4.19 | 3.96 | 6.29 | 0.75 Velvet Ash | Apparel & Footwear | 114 | 4.26 | 3.96 | 6.91 | 0.693 Saffron & Sage | Beauty | 113 | 3.86 | 3.73 | 5.91 | 0.7876 Pfotenglück | Pet Supplies | 111 | 3.37 | 3.16 | 4.91 | 0.9189 Ostwind Mode | Apparel & Footwear | 108 | 3.85 | 3.49 | 6.01 | 0.75 Ridgeback Gear | Sports & Outdoor | 102 | 3.76 | 3.76 | 5.36 | 0.8627 The Balm Room | Beauty | 100 | 5.0 | 4.22 | 7.37 | 0.7 Chocolaterie Mireille | Food & Coffee | 99 | 4.9 | 4.78 | 7.2 | 0.5657 Kayak Corner | Sports & Outdoor | 96 | 5.22 | 4.06 | 7.3 | 0.6875 Sorella Skin | Beauty | 93 | 3.94 | 3.84 | 5.87 | 0.7957 Hautnah | Beauty | 90 | 4.0 | 3.78 | 6.17 | 0.7222 Willow & Wren | Home & Living | 90 | 4.66 | 3.68 | 6.81 | 0.7889 Tail Wag Supply | Pet Supplies | 87 | 3.6 | 3.55 | 5.43 | 0.8736 Thistlecombe | Apparel & Footwear | 84 | 6.0 | 4.75 | 11.51 | 0.5476 Deskline Supplies | Office Supplies (B2B) | 75 | 4.55 | 4.14 | 6.91 | 0.7067 Fell & Fjell | Sports & Outdoor | 72 | 4.05 | 3.98 | 6.02 | 0.7083 Purrfect Pantry | Pet Supplies | 72 | 5.92 | 4.37 | 10.92 | 0.5417 Hanse Industriebedarf | Industrial Supplies (B2B) | 71 | 4.01 | 3.99 | 6.05 | 0.7465 Mirelle | Beauty | 60 | 3.82 | 3.8 | 5.54 | 0.85 Ampere & Co. | Consumer Electronics | 56 | 4.22 | 4.0 | 6.2 | 0.6964 Kettle & Key | Home & Living | 55 | 4.48 | 4.16 | 6.52 | 0.6182 Polder Packaging | Industrial Supplies (B2B) | 55 | 4.56 | 4.25 | 6.79 | 0.6 (50 rows)
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
Changed only where the benchmark needs the same result on every system
SELECT t.name AS tenant_name, t.industry, d.orders_delivered, ROUND(d.avg_delivery_days, 2) AS avg_delivery_days, ROUND(d.median_delivery_days, 2) AS median_delivery_days, ROUND(d.p90_delivery_days, 2) AS p90_delivery_days, ROUND(d.pct_within_5_days, 4) AS pct_within_5_days t.industry AS industry, d.orders_delivered AS orders_delivered, d.avg_delivery_days AS avg_delivery_days, d.median_delivery_days AS median_delivery_days, d.p90_delivery_days AS p90_delivery_days, d.pct_within_5_days AS pct_within_5_daysFROM tenants tJOIN ( SELECT o.tenant_id, o.tenant_id AS tenant_id, COUNT(*) AS orders_delivered, AVG(EXTRACT(EPOCH FROM (o.delivered_at - o.placed_at)) / 86400.0) AS avg_delivery_days, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (o.delivered_at - o.placed_at)) / 86400.0) AS median_delivery_days, PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (o.delivered_at - o.placed_at)) / 86400.0) AS p90_delivery_days, AVG(CASE WHEN EXTRACT(EPOCH FROM (o.delivered_at - o.placed_at)) / 86400.0 <= 5 THEN 1.0 ELSE 0.0 END) AS pct_within_5_days AVG({{seconds_between(o.placed_at, o.delivered_at)}} / 86400.0) AS avg_delivery_days, {{percentile_cont(0.5, {{seconds_between(o.placed_at, o.delivered_at)}} / 86400.0)}} AS median_delivery_days, {{percentile_cont(0.9, {{seconds_between(o.placed_at, o.delivered_at)}} / 86400.0)}} AS p90_delivery_days, AVG(CASE WHEN {{seconds_between(o.placed_at, o.delivered_at)}} / 86400.0 <= 5 THEN 1.0 ELSE 0.0 END) AS pct_within_5_days FROM ( SELECT ord.id, ord.tenant_id, ord.placed_at, ord.id AS id, ord.tenant_id AS tenant_id, ord.placed_at AS placed_at, (SELECT MAX(s.delivered_at) FROM fulfillments f JOIN shipments s ON s.fulfillment_id = f.id WHERE f.order_id = ord.id) AS delivered_at FROM orders ord WHERE ord.status = 'fulfilled' AND ord.placed_at >= DATE '2025-01-01' AND ord.placed_at < DATE '2026-01-01' AND EXISTS ( SELECT 1 FROM fulfillments f JOIN shipments s ON s.fulfillment_id = f.id WHERE f.order_id = ord.id ) AND NOT EXISTS ( SELECT 1 FROM fulfillments f JOIN shipments s ON s.fulfillment_id = f.id WHERE f.order_id = ord.id AND s.delivered_at IS NULL ) ) o GROUP BY o.tenant_id HAVING COUNT(*) >= 50) d ON d.tenant_id = t.idORDER BY d.orders_delivered DESC, t.name ASCLIMIT 50ORDER BY d.orders_delivered DESC NULLS FIRST, t.name ASC NULLS LAST, t.id ASC NULLS LAST{{fetch_first(50)}}
SELECT t.name AS tenant_name, t.industry AS industry, d.orders_delivered AS orders_delivered, d.avg_delivery_days AS avg_delivery_days, d.median_delivery_days AS median_delivery_days, d.p90_delivery_days AS p90_delivery_days, d.pct_within_5_days AS pct_within_5_days FROM tenants t JOIN ( SELECT o.tenant_id AS tenant_id, COUNT(*) AS orders_delivered, AVG(CAST(EXTRACT(EPOCH FROM (o.delivered_at - o.placed_at)) AS NUMERIC(18,6)) / 86400.0) AS avg_delivery_days, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CAST(EXTRACT(EPOCH FROM (o.delivered_at - o.placed_at)) AS NUMERIC(18,6)) / 86400.0) AS median_delivery_days, PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY CAST(EXTRACT(EPOCH FROM (o.delivered_at - o.placed_at)) AS NUMERIC(18,6)) / 86400.0) AS p90_delivery_days, AVG(CASE WHEN CAST(EXTRACT(EPOCH FROM (o.delivered_at - o.placed_at)) AS NUMERIC(18,6)) / 86400.0 <= 5 THEN 1.0 ELSE 0.0 END) AS pct_within_5_days FROM ( SELECT ord.id AS id, ord.tenant_id AS tenant_id, ord.placed_at AS placed_at, (SELECT MAX(s.delivered_at) FROM fulfillments f JOIN shipments s ON s.fulfillment_id = f.id WHERE f.order_id = ord.id) AS delivered_at FROM orders ord WHERE ord.status = 'fulfilled' AND ord.placed_at >= DATE '2025-01-01' AND ord.placed_at < DATE '2026-01-01' AND EXISTS ( SELECT 1 FROM fulfillments f JOIN shipments s ON s.fulfillment_id = f.id WHERE f.order_id = ord.id ) AND NOT EXISTS ( SELECT 1 FROM fulfillments f JOIN shipments s ON s.fulfillment_id = f.id WHERE f.order_id = ord.id AND s.delivered_at IS NULL ) ) o GROUP BY o.tenant_id HAVING COUNT(*) >= 50 ) d ON d.tenant_id = t.id ORDER BY d.orders_delivered DESC NULLS FIRST, t.name ASC NULLS LAST, t.id ASC NULLS LAST FETCH FIRST 50 ROWS ONLY
SELECT t.name AS tenant_name, t.industry AS industry, d.orders_delivered AS orders_delivered, d.avg_delivery_days AS avg_delivery_days, d.median_delivery_days AS median_delivery_days, d.p90_delivery_days AS p90_delivery_days, d.pct_within_5_days AS pct_within_5_days FROM tenants t JOIN ( SELECT o.tenant_id AS tenant_id, COUNT(*) AS orders_delivered, AVG(CAST(EXTRACT(EPOCH FROM (o.delivered_at - o.placed_at)) AS NUMERIC(18,6)) / 86400.0) AS avg_delivery_days, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CAST(EXTRACT(EPOCH FROM (o.delivered_at - o.placed_at)) AS NUMERIC(18,6)) / 86400.0) AS median_delivery_days, PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY CAST(EXTRACT(EPOCH FROM (o.delivered_at - o.placed_at)) AS NUMERIC(18,6)) / 86400.0) AS p90_delivery_days, AVG(CASE WHEN CAST(EXTRACT(EPOCH FROM (o.delivered_at - o.placed_at)) AS NUMERIC(18,6)) / 86400.0 <= 5 THEN 1.0 ELSE 0.0 END) AS pct_within_5_days FROM ( SELECT ord.id AS id, ord.tenant_id AS tenant_id, ord.placed_at AS placed_at, (SELECT MAX(s.delivered_at) FROM fulfillments f JOIN shipments s ON s.fulfillment_id = f.id WHERE f.order_id = ord.id) AS delivered_at FROM orders ord WHERE ord.status = 'fulfilled' AND ord.placed_at >= DATE '2025-01-01' AND ord.placed_at < DATE '2026-01-01' AND EXISTS ( SELECT 1 FROM fulfillments f JOIN shipments s ON s.fulfillment_id = f.id WHERE f.order_id = ord.id ) AND NOT EXISTS ( SELECT 1 FROM fulfillments f JOIN shipments s ON s.fulfillment_id = f.id WHERE f.order_id = ord.id AND s.delivered_at IS NULL ) ) o GROUP BY o.tenant_id HAVING COUNT(*) >= 50 ) d ON d.tenant_id = t.id ORDER BY d.orders_delivered DESC NULLS FIRST, t.name ASC NULLS LAST, t.id ASC NULLS LAST LIMIT 50
SELECT t.name AS tenant_name, t.industry AS industry, d.orders_delivered AS orders_delivered, d.avg_delivery_days AS avg_delivery_days, d.median_delivery_days AS median_delivery_days, d.p90_delivery_days AS p90_delivery_days, d.pct_within_5_days AS pct_within_5_days FROM tenants t JOIN ( SELECT o.tenant_id AS tenant_id, COUNT(*) AS orders_delivered, AVG(CAST(EXTRACT(EPOCH FROM (o.delivered_at - o.placed_at)) AS NUMERIC(18,6)) / 86400.0) AS avg_delivery_days, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CAST(CAST(EXTRACT(EPOCH FROM (o.delivered_at - o.placed_at)) AS NUMERIC(18,6)) / 86400.0 AS DOUBLE PRECISION)) AS median_delivery_days, PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY CAST(CAST(EXTRACT(EPOCH FROM (o.delivered_at - o.placed_at)) AS NUMERIC(18,6)) / 86400.0 AS DOUBLE PRECISION)) AS p90_delivery_days, AVG(CASE WHEN CAST(EXTRACT(EPOCH FROM (o.delivered_at - o.placed_at)) AS NUMERIC(18,6)) / 86400.0 <= 5 THEN 1.0 ELSE 0.0 END) AS pct_within_5_days FROM ( SELECT ord.id AS id, ord.tenant_id AS tenant_id, ord.placed_at AS placed_at, (SELECT MAX(s.delivered_at) FROM fulfillments f JOIN shipments s ON s.fulfillment_id = f.id WHERE f.order_id = ord.id) AS delivered_at FROM orders ord WHERE ord.status = 'fulfilled' AND ord.placed_at >= DATE '2025-01-01' AND ord.placed_at < DATE '2026-01-01' AND EXISTS ( SELECT 1 FROM fulfillments f JOIN shipments s ON s.fulfillment_id = f.id WHERE f.order_id = ord.id ) AND NOT EXISTS ( SELECT 1 FROM fulfillments f JOIN shipments s ON s.fulfillment_id = f.id WHERE f.order_id = ord.id AND s.delivered_at IS NULL ) ) o GROUP BY o.tenant_id HAVING COUNT(*) >= 50 ) d ON d.tenant_id = t.id ORDER BY d.orders_delivered DESC NULLS FIRST, t.name ASC NULLS LAST, t.id ASC NULLS LAST FETCH FIRST 50 ROWS ONLY
SELECT t.name AS tenant_name, t.industry AS industry, d.orders_delivered AS orders_delivered, d.avg_delivery_days AS avg_delivery_days, d.median_delivery_days AS median_delivery_days, d.p90_delivery_days AS p90_delivery_days, d.pct_within_5_days AS pct_within_5_days FROM tenants t JOIN ( SELECT o.tenant_id AS tenant_id, COUNT(*) AS orders_delivered, AVG(CAST(divideDecimal(toDecimal128(dateDiff('microsecond', o.placed_at, o.delivered_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6))) / 86400.0) AS avg_delivery_days, quantileExactInclusive(0.5)(toFloat64(CAST(divideDecimal(toDecimal128(dateDiff('microsecond', o.placed_at, o.delivered_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6))) / 86400.0)) AS median_delivery_days, quantileExactInclusive(0.9)(toFloat64(CAST(divideDecimal(toDecimal128(dateDiff('microsecond', o.placed_at, o.delivered_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6))) / 86400.0)) AS p90_delivery_days, AVG(CASE WHEN CAST(divideDecimal(toDecimal128(dateDiff('microsecond', o.placed_at, o.delivered_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6))) / 86400.0 <= 5 THEN 1.0 ELSE 0.0 END) AS pct_within_5_days FROM ( SELECT ord.id AS id, ord.tenant_id AS tenant_id, ord.placed_at AS placed_at, (SELECT MAX(s.delivered_at) FROM fulfillments f JOIN shipments s ON s.fulfillment_id = f.id WHERE f.order_id = ord.id) AS delivered_at FROM orders ord WHERE ord.status = 'fulfilled' AND ord.placed_at >= DATE '2025-01-01' AND ord.placed_at < DATE '2026-01-01' AND EXISTS ( SELECT 1 FROM fulfillments f JOIN shipments s ON s.fulfillment_id = f.id WHERE f.order_id = ord.id ) AND NOT EXISTS ( SELECT 1 FROM fulfillments f JOIN shipments s ON s.fulfillment_id = f.id WHERE f.order_id = ord.id AND s.delivered_at IS NULL ) ) o GROUP BY o.tenant_id HAVING COUNT(*) >= 50 ) d ON d.tenant_id = t.id ORDER BY d.orders_delivered DESC NULLS FIRST, t.name ASC NULLS LAST, t.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: Delivery time by merchant, 2025 Question: Which merchants deliver reliably fast, and which are a coin toss, over the past year? Grain: one row per tenant (merchant) Columns: - tenant_name - industry - orders_delivered (count fully delivered in 2025) - avg_delivery_days - median_delivery_days (p50) - p90_delivery_days - pct_within_5_days (share of delivered orders where delivery_days <= 5) Details: Grain: one row per tenants.id. Scope: orders with placed_at in 2025-01-01 through 2025-12-31, status = 'fulfilled'. Same delivery_days definition as the monthly trend report (placed_at to max linked shipment.delivered_at via fulfillments, only orders where all linked shipments are delivered). Join tenants for tenant_name and industry. Include only tenants with at least 50 delivered orders in the window (to exclude noise from tiny merchants). Order by orders_delivered descending, tie-break by tenant_name ascending. Cap at 50 rows (should be well under given ~120 tenants total). Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runSELECT t.name AS tenant_name, t.industry, d.orders_delivered, ROUND(d.avg_delivery_days, 2) AS avg_delivery_days, ROUND(d.median_delivery_days, 2) AS median_delivery_days, ROUND(d.p90_delivery_days, 2) AS p90_delivery_days, ROUND(d.pct_within_5_days, 4) AS pct_within_5_days FROM tenants t JOIN ( SELECT o.tenant_id, COUNT(*) AS orders_delivered, AVG(EXTRACT(EPOCH FROM (o.delivered_at - o.placed_at)) / 86400.0) AS avg_delivery_days, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (o.delivered_at - o.placed_at)) / 86400.0) AS median_delivery_days, PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (o.delivered_at - o.placed_at)) / 86400.0) AS p90_delivery_days, AVG(CASE WHEN EXTRACT(EPOCH FROM (o.delivered_at - o.placed_at)) / 86400.0 <= 5 THEN 1.0 ELSE 0.0 END) AS pct_within_5_days FROM ( SELECT ord.id, ord.tenant_id, ord.placed_at, (SELECT MAX(s.delivered_at) FROM fulfillments f JOIN shipments s ON s.fulfillment_id = f.id WHERE f.order_id = ord.id) AS delivered_at FROM orders ord WHERE ord.status = 'fulfilled' AND ord.placed_at >= DATE '2025-01-01' AND ord.placed_at < DATE '2026-01-01' AND EXISTS ( SELECT 1 FROM fulfillments f JOIN shipments s ON s.fulfillment_id = f.id WHERE f.order_id = ord.id ) AND NOT EXISTS ( SELECT 1 FROM fulfillments f JOIN shipments s ON s.fulfillment_id = f.id WHERE f.order_id = ord.id AND s.delivered_at IS NULL ) ) o GROUP BY o.tenant_id HAVING COUNT(*) >= 50 ) d ON d.tenant_id = t.id ORDER BY d.orders_delivered DESC, t.name ASC LIMIT 50
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: tenant_name, industry, orders_delivered, avg_delivery_days, median_delivery_days, p90_delivery_days, pct_within_5_days
submitSELECT t.name AS tenant_name, t.industry, d.orders_delivered, ROUND(d.avg_delivery_days, 2) AS avg_delivery_days, ROUND(d.median_delivery_days, 2) AS median_delivery_days, ROUND(d.p90_delivery_days, 2) AS p90_delivery_days, ROUND(d.pct_within_5_days, 4) AS pct_within_5_days FROM tenants t JOIN ( SELECT o.tenant_id, COUNT(*) AS orders_delivered, AVG(EXTRACT(EPOCH FROM (o.delivered_at - o.placed_at)) / 86400.0) AS avg_delivery_days, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (o.delivered_at - o.placed_at)) / 86400.0) AS median_delivery_days, PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (o.delivered_at - o.placed_at)) / 86400.0) AS p90_delivery_days, AVG(CASE WHEN EXTRACT(EPOCH FROM (o.delivered_at - o.placed_at)) / 86400.0 <= 5 THEN 1.0 ELSE 0.0 END) AS pct_within_5_days FROM ( SELECT ord.id, ord.tenant_id, ord.placed_at, (SELECT MAX(s.delivered_at) FROM fulfillments f JOIN shipments s ON s.fulfillment_id = f.id WHERE f.order_id = ord.id) AS delivered_at FROM orders ord WHERE ord.status = 'fulfilled' AND ord.placed_at >= DATE '2025-01-01' AND ord.placed_at < DATE '2026-01-01' AND EXISTS ( SELECT 1 FROM fulfillments f JOIN shipments s ON s.fulfillment_id = f.id WHERE f.order_id = ord.id ) AND NOT EXISTS ( SELECT 1 FROM fulfillments f JOIN shipments s ON s.fulfillment_id = f.id WHERE f.order_id = ord.id AND s.delivered_at IS NULL ) ) o GROUP BY o.tenant_id HAVING COUNT(*) >= 50 ) d ON d.tenant_id = t.id ORDER BY d.orders_delivered DESC, t.name ASC LIMIT 50
Submitted to tonight's batch. Your work on this request is done.