Queries · q043 · VP Customer Experience · week 1, night 3
“Did Studio Vrij's actual shipping/delivery performance deteriorate in May-June 2025, explaining the spike in their delivery-related support tickets, or was it a support-side issue only?”
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: Where I'd like to start — support & shopper experience
Hi Mateo,
Good to have you on this with me. I'm still building my map of the business, so I want to keep this week broad — I'd rather get a solid first read on a few big questions than a deep dive on one. Here's what's on my mind, roughly in priority order:
What I need these for: I'm meeting merchant success and the exec team soon and want a credible, numbers-backed view of shopper experience — not just war stories. I also want to start spotting the two or three things that, if we fixed them, would move the needle most broadly across merchants, rather than chasing individual complaints.
Given the slot constraints, let's prioritize #1 through #3 this week if we can't fit all five — those feel most urgent to me. Happy to talk through any of this before you lock in requests.
Thanks, Daniel
The analyst · week 1, night 3, request 1 of 3
Report 1 (night 2) shows the May-Jun 2025 anomaly is strongly concentrated in delivery-tagged tickets specifically (response/resolution hours for delivery tickets roughly 2-3x worse than 'other' tickets in those two months, whereas Nov/Dec spikes hit both groups evenly - that looks like genuine seasonal volume, not a delivery-specific problem). Report 2 pins the May-Jun spike almost entirely on one merchant: Studio Vrij had 86 tickets vs an expected ~16 (5.38x their normal rate), 83.7% delivery-tagged, and it was also the slowest-resolving merchant in the whole-year peer table from night 1. Every other merchant with a high spike ratio is tiny in absolute volume (noise), and the platform's other big merchants (Cragsley, Marrowby) were actually below their own baseline rate in May-Jun. So this looks like a single-merchant fulfillment incident, not a platform-wide issue - I want to confirm that with real delivery/shipment data before I write it into the memo as a finding. Report 3 (returns trend) is reassuring: resolve time is flat around ~8 days and rejection rate flat regardless of volume swings; the return-rate spikes track the same Nov/Dec seasonality as everything else. I want one merchant-level peer cut on returns (mirroring the support-speed peer report) to see if any merchant stands out the way Studio Vrij did on tickets. With priorities #1-3 now well covered, I'm using the third slot tonight to open priority #4 (CSAT) so there's something in hand for Friday's memo if priority #5 doesn't fit.
The engineer · 2 dry runs on the empty schema, then the submission
SELECT to_char(o.placed_at, 'YYYY-MM') as month, SUM(CASE WHEN s.tenant_id = sv.id THEN 1 ELSE 0 END) as sv_shipments_delivered, ROUND(AVG(CASE WHEN s.tenant_id = sv.id THEN extract(epoch from (s.delivered_at - o.placed_at))/86400 END)::numeric,1) as sv_avg_days_placed_to_delivered, ROUND(100.0 * SUM(CASE WHEN s.tenant_id = sv.id AND extract(epoch from (s.delivered_at - o.placed_at))/86400 > 7 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN s.tenant_id = sv.id THEN 1 ELSE 0 END),0), 1) as sv_pct_over_7_days, SUM(CASE WHEN s.tenant_id <> sv.id THEN 1 ELSE 0 END) as platform_shipments_delivered, ROUND(AVG(CASE WHEN s.tenant_id <> sv.id THEN extract(epoch from (s.delivered_at - o.placed_at))/86400 END)::numeric,1) as platform_avg_days_placed_to_delivered, ROUND(100.0 * SUM(CASE WHEN s.tenant_id <> sv.id AND extract(epoch from (s.delivered_at - o.placed_at))/86400 > 7 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN s.tenant_id <> sv.id THEN 1 ELSE 0 END),0), 1) as platform_pct_over_7_days FROM shipments s JOIN fulfillments f ON f.id = s.fulfillment_id AND f.tenant_id = s.tenant_id JOIN orders o ON o.id = f.order_id AND o.tenant_id = f.tenant_id CROSS JOIN (SELECT id FROM tenants WHERE name = 'Studio Vrij') sv WHERE s.delivered_at IS NOT NULL AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' GROUP BY month ORDER BY month
SELECT to_char(o.placed_at, 'YYYY-MM') as month, strftime(o.placed_at, '%Y-%m') as month, SUM(CASE WHEN s.tenant_id = sv.id THEN 1 ELSE 0 END) as sv_shipments_delivered, ROUND(AVG(CASE WHEN s.tenant_id = sv.id THEN extract(epoch from (s.delivered_at - o.placed_at))/86400 END)::numeric,1) as sv_avg_days_placed_to_delivered, ROUND(100.0 * SUM(CASE WHEN s.tenant_id = sv.id AND extract(epoch from (s.delivered_at - o.placed_at))/86400 > 7 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN s.tenant_id = sv.id THEN 1 ELSE 0 END),0), 1) as sv_pct_over_7_days, SUM(CASE WHEN s.tenant_id <> sv.id THEN 1 ELSE 0 END) as platform_shipments_delivered, ROUND(AVG(CASE WHEN s.tenant_id <> sv.id THEN extract(epoch from (s.delivered_at - o.placed_at))/86400 END)::numeric,1) as platform_avg_days_placed_to_delivered, ROUND(100.0 * SUM(CASE WHEN s.tenant_id <> sv.id AND extract(epoch from (s.delivered_at - o.placed_at))/86400 > 7 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN s.tenant_id <> sv.id THEN 1 ELSE 0 END),0), 1) as platform_pct_over_7_daysFROM shipments sJOIN fulfillments f ON f.id = s.fulfillment_id AND f.tenant_id = s.tenant_idJOIN orders o ON o.id = f.order_id AND o.tenant_id = f.tenant_idCROSS JOIN (SELECT id FROM tenants WHERE name = 'Studio Vrij') svWHERE s.delivered_at IS NOT NULL AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'GROUP BY monthORDER BY month
“Query compiles with the expected columns. Submitting now.”
“Submitted. The report pairs Studio Vrij vs. the rest of the platform by month for 2025, with delivered-shipment counts, average placed-to-delivered days, and the share over 7 days for each group — which should let Mateo see directly whether Studio Vrij's actual delivery times/late-share worsened in May/June relative to the platform baseline, or stayed flat (pointing to a support-side-only issue).”
As Mateo Ruiz saw it, from the small dataset the agents worked on
month | sv_shipments_delivered | sv_avg_days_placed_to_delivered | sv_pct_over_7_days | platform_shipments_delivered | platform_avg_days_placed_to_delivered | platform_pct_over_7_days --------+------------------------+---------------------------------+--------------------+------------------------------+---------------------------------------+------------------------- 2025-01 | 40 | 3.2 | 0.0 | 1728 | 3.6 | 3.1 2025-02 | 20 | 3.2 | 0.0 | 1350 | 3.7 | 3.4 2025-03 | 42 | 4.7 | 16.7 | 1544 | 3.8 | 4.1 2025-04 | 31 | 3.7 | 3.2 | 1489 | 3.7 | 2.8 2025-05 | 107 | 9.3 | 70.1 | 1526 | 4.0 | 4.8 2025-06 | 29 | 4.9 | 20.7 | 1432 | 3.8 | 4.7 2025-07 | 41 | 4.0 | 4.9 | 1555 | 3.9 | 4.4 2025-08 | 31 | 3.9 | 3.2 | 1471 | 3.8 | 4.1 2025-09 | 31 | 4.0 | 0.0 | 1628 | 4.1 | 5.5 2025-10 | 56 | 4.8 | 10.7 | 1772 | 4.1 | 7.1 2025-11 | 75 | 3.7 | 1.3 | 2475 | 4.7 | 12.1 2025-12 | 85 | 4.0 | 3.5 | 2630 | 4.3 | 9.9 (12 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 strftime(o.placed_at, '%Y-%m') as month, {{year_month(o.placed_at)}} as month, SUM(CASE WHEN s.tenant_id = sv.id THEN 1 ELSE 0 END) as sv_shipments_delivered, ROUND(AVG(CASE WHEN s.tenant_id = sv.id THEN extract(epoch from (s.delivered_at - o.placed_at))/86400 END)::numeric,1) as sv_avg_days_placed_to_delivered, ROUND(100.0 * SUM(CASE WHEN s.tenant_id = sv.id AND extract(epoch from (s.delivered_at - o.placed_at))/86400 > 7 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN s.tenant_id = sv.id THEN 1 ELSE 0 END),0), 1) as sv_pct_over_7_days, AVG(CASE WHEN s.tenant_id = sv.id THEN {{seconds_between(o.placed_at, s.delivered_at)}}/86400 END) as sv_avg_days_placed_to_delivered, 100.0 * SUM(CASE WHEN s.tenant_id = sv.id AND {{seconds_between(o.placed_at, s.delivered_at)}}/86400 > 7 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN s.tenant_id = sv.id THEN 1 ELSE 0 END),0) as sv_pct_over_7_days, SUM(CASE WHEN s.tenant_id <> sv.id THEN 1 ELSE 0 END) as platform_shipments_delivered, ROUND(AVG(CASE WHEN s.tenant_id <> sv.id THEN extract(epoch from (s.delivered_at - o.placed_at))/86400 END)::numeric,1) as platform_avg_days_placed_to_delivered, ROUND(100.0 * SUM(CASE WHEN s.tenant_id <> sv.id AND extract(epoch from (s.delivered_at - o.placed_at))/86400 > 7 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN s.tenant_id <> sv.id THEN 1 ELSE 0 END),0), 1) as platform_pct_over_7_days AVG(CASE WHEN s.tenant_id <> sv.id THEN {{seconds_between(o.placed_at, s.delivered_at)}}/86400 END) as platform_avg_days_placed_to_delivered, 100.0 * SUM(CASE WHEN s.tenant_id <> sv.id AND {{seconds_between(o.placed_at, s.delivered_at)}}/86400 > 7 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN s.tenant_id <> sv.id THEN 1 ELSE 0 END),0) as platform_pct_over_7_daysFROM shipments sJOIN fulfillments f ON f.id = s.fulfillment_id AND f.tenant_id = s.tenant_idJOIN orders o ON o.id = f.order_id AND o.tenant_id = f.tenant_idCROSS JOIN (SELECT id FROM tenants WHERE name = 'Studio Vrij') svWHERE s.delivered_at IS NOT NULL AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'GROUP BY monthORDER BY monthORDER BY month NULLS LAST
SELECT strftime(o.placed_at, '%Y-%m') as month, SUM(CASE WHEN s.tenant_id = sv.id THEN 1 ELSE 0 END) as sv_shipments_delivered, AVG(CASE WHEN s.tenant_id = sv.id THEN CAST(EXTRACT(EPOCH FROM (s.delivered_at - o.placed_at)) AS NUMERIC(18,6))/86400 END) as sv_avg_days_placed_to_delivered, 100.0 * SUM(CASE WHEN s.tenant_id = sv.id AND CAST(EXTRACT(EPOCH FROM (s.delivered_at - o.placed_at)) AS NUMERIC(18,6))/86400 > 7 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN s.tenant_id = sv.id THEN 1 ELSE 0 END),0) as sv_pct_over_7_days, SUM(CASE WHEN s.tenant_id <> sv.id THEN 1 ELSE 0 END) as platform_shipments_delivered, AVG(CASE WHEN s.tenant_id <> sv.id THEN CAST(EXTRACT(EPOCH FROM (s.delivered_at - o.placed_at)) AS NUMERIC(18,6))/86400 END) as platform_avg_days_placed_to_delivered, 100.0 * SUM(CASE WHEN s.tenant_id <> sv.id AND CAST(EXTRACT(EPOCH FROM (s.delivered_at - o.placed_at)) AS NUMERIC(18,6))/86400 > 7 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN s.tenant_id <> sv.id THEN 1 ELSE 0 END),0) as platform_pct_over_7_days FROM shipments s JOIN fulfillments f ON f.id = s.fulfillment_id AND f.tenant_id = s.tenant_id JOIN orders o ON o.id = f.order_id AND o.tenant_id = f.tenant_id CROSS JOIN (SELECT id FROM tenants WHERE name = 'Studio Vrij') sv WHERE s.delivered_at IS NOT NULL AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' GROUP BY month ORDER BY month NULLS LAST
SELECT to_char(o.placed_at, 'YYYY-MM') as month, SUM(CASE WHEN s.tenant_id = sv.id THEN 1 ELSE 0 END) as sv_shipments_delivered, AVG(CASE WHEN s.tenant_id = sv.id THEN CAST(EXTRACT(EPOCH FROM (s.delivered_at - o.placed_at)) AS NUMERIC(18,6))/86400 END) as sv_avg_days_placed_to_delivered, 100.0 * SUM(CASE WHEN s.tenant_id = sv.id AND CAST(EXTRACT(EPOCH FROM (s.delivered_at - o.placed_at)) AS NUMERIC(18,6))/86400 > 7 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN s.tenant_id = sv.id THEN 1 ELSE 0 END),0) as sv_pct_over_7_days, SUM(CASE WHEN s.tenant_id <> sv.id THEN 1 ELSE 0 END) as platform_shipments_delivered, AVG(CASE WHEN s.tenant_id <> sv.id THEN CAST(EXTRACT(EPOCH FROM (s.delivered_at - o.placed_at)) AS NUMERIC(18,6))/86400 END) as platform_avg_days_placed_to_delivered, 100.0 * SUM(CASE WHEN s.tenant_id <> sv.id AND CAST(EXTRACT(EPOCH FROM (s.delivered_at - o.placed_at)) AS NUMERIC(18,6))/86400 > 7 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN s.tenant_id <> sv.id THEN 1 ELSE 0 END),0) as platform_pct_over_7_days FROM shipments s JOIN fulfillments f ON f.id = s.fulfillment_id AND f.tenant_id = s.tenant_id JOIN orders o ON o.id = f.order_id AND o.tenant_id = f.tenant_id CROSS JOIN (SELECT id FROM tenants WHERE name = 'Studio Vrij') sv WHERE s.delivered_at IS NOT NULL AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' GROUP BY month ORDER BY month NULLS LAST
SELECT to_char(CAST(o.placed_at AS timestamp), 'YYYY-MM') as month, SUM(CASE WHEN s.tenant_id = sv.id THEN 1 ELSE 0 END) as sv_shipments_delivered, AVG(CASE WHEN s.tenant_id = sv.id THEN CAST(EXTRACT(EPOCH FROM (s.delivered_at - o.placed_at)) AS NUMERIC(18,6))/86400 END) as sv_avg_days_placed_to_delivered, 100.0 * SUM(CASE WHEN s.tenant_id = sv.id AND CAST(EXTRACT(EPOCH FROM (s.delivered_at - o.placed_at)) AS NUMERIC(18,6))/86400 > 7 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN s.tenant_id = sv.id THEN 1 ELSE 0 END),0) as sv_pct_over_7_days, SUM(CASE WHEN s.tenant_id <> sv.id THEN 1 ELSE 0 END) as platform_shipments_delivered, AVG(CASE WHEN s.tenant_id <> sv.id THEN CAST(EXTRACT(EPOCH FROM (s.delivered_at - o.placed_at)) AS NUMERIC(18,6))/86400 END) as platform_avg_days_placed_to_delivered, 100.0 * SUM(CASE WHEN s.tenant_id <> sv.id AND CAST(EXTRACT(EPOCH FROM (s.delivered_at - o.placed_at)) AS NUMERIC(18,6))/86400 > 7 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN s.tenant_id <> sv.id THEN 1 ELSE 0 END),0) as platform_pct_over_7_days FROM shipments s JOIN fulfillments f ON f.id = s.fulfillment_id AND f.tenant_id = s.tenant_id JOIN orders o ON o.id = f.order_id AND o.tenant_id = f.tenant_id CROSS JOIN (SELECT id FROM tenants WHERE name = 'Studio Vrij') sv WHERE s.delivered_at IS NOT NULL AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' GROUP BY month ORDER BY month NULLS LAST
SELECT formatDateTime(o.placed_at, '%Y-%m') as month, SUM(CASE WHEN s.tenant_id = sv.id THEN 1 ELSE 0 END) as sv_shipments_delivered, AVG(CASE WHEN s.tenant_id = sv.id THEN CAST(divideDecimal(toDecimal128(dateDiff('microsecond', o.placed_at, s.delivered_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6)))/86400 END) as sv_avg_days_placed_to_delivered, 100.0 * SUM(CASE WHEN s.tenant_id = sv.id AND CAST(divideDecimal(toDecimal128(dateDiff('microsecond', o.placed_at, s.delivered_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6)))/86400 > 7 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN s.tenant_id = sv.id THEN 1 ELSE 0 END),0) as sv_pct_over_7_days, SUM(CASE WHEN s.tenant_id <> sv.id THEN 1 ELSE 0 END) as platform_shipments_delivered, AVG(CASE WHEN s.tenant_id <> sv.id THEN CAST(divideDecimal(toDecimal128(dateDiff('microsecond', o.placed_at, s.delivered_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6)))/86400 END) as platform_avg_days_placed_to_delivered, 100.0 * SUM(CASE WHEN s.tenant_id <> sv.id AND CAST(divideDecimal(toDecimal128(dateDiff('microsecond', o.placed_at, s.delivered_at), 0), toDecimal128(1000000, 0), 6) AS Nullable(Decimal(18, 6)))/86400 > 7 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN s.tenant_id <> sv.id THEN 1 ELSE 0 END),0) as platform_pct_over_7_days FROM shipments s JOIN fulfillments f ON f.id = s.fulfillment_id AND f.tenant_id = s.tenant_id JOIN orders o ON o.id = f.order_id AND o.tenant_id = f.tenant_id CROSS JOIN (SELECT id FROM tenants WHERE name = 'Studio Vrij') sv WHERE s.delivered_at IS NOT NULL AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' GROUP BY month ORDER BY month 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 Mateo Ruiz (analyst for the VP Customer Experience): Title: Delivery performance: Studio Vrij vs platform, 2025 Question: Did Studio Vrij's actual shipping/delivery performance deteriorate in May-June 2025, explaining the spike in their delivery-related support tickets, or was it a support-side issue only? Grain: one calendar month in 2025, with paired columns for Studio Vrij and the rest of the platform Columns: - month (YYYY-MM) - Studio Vrij shipments delivered - Studio Vrij avg days placed-to-delivered - Studio Vrij % of shipments over 7 days placed-to-delivered - platform (ex-Studio Vrij) shipments delivered - platform (ex-Studio Vrij) avg days placed-to-delivered - platform (ex-Studio Vrij) % of shipments over 7 days placed-to-delivered Details: Source: shipments joined to fulfillments (for order_id and tenant scoping) joined to orders (for placed_at). Only include shipments with delivered_at not null. 'Days placed-to-delivered' = extract(epoch from shipments.delivered_at - orders.placed_at)/86400. Grain the month on orders.placed_at (2025-01 through 2025-12). Split into two groups: tenant = the 'Studio Vrij' tenant (join tenants on name to find its id) vs all other tenants combined. '% over 7 days' = share of that group/month's delivered shipments where days placed-to-delivered > 7, as a percentage rounded to 1 decimal. Round avg days to 1 decimal. Note in the report that Nov/Dec 2025 cohorts will be incomplete since some orders placed late in the year may not show a delivered shipment yet (right-censoring), similar to the earlier ticket-resolution caveat. Order by month ascending. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runSELECT to_char(o.placed_at, 'YYYY-MM') as month, SUM(CASE WHEN s.tenant_id = sv.id THEN 1 ELSE 0 END) as sv_shipments_delivered, ROUND(AVG(CASE WHEN s.tenant_id = sv.id THEN extract(epoch from (s.delivered_at - o.placed_at))/86400 END)::numeric,1) as sv_avg_days_placed_to_delivered, ROUND(100.0 * SUM(CASE WHEN s.tenant_id = sv.id AND extract(epoch from (s.delivered_at - o.placed_at))/86400 > 7 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN s.tenant_id = sv.id THEN 1 ELSE 0 END),0), 1) as sv_pct_over_7_days, SUM(CASE WHEN s.tenant_id <> sv.id THEN 1 ELSE 0 END) as platform_shipments_delivered, ROUND(AVG(CASE WHEN s.tenant_id <> sv.id THEN extract(epoch from (s.delivered_at - o.placed_at))/86400 END)::numeric,1) as platform_avg_days_placed_to_delivered, ROUND(100.0 * SUM(CASE WHEN s.tenant_id <> sv.id AND extract(epoch from (s.delivered_at - o.placed_at))/86400 > 7 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN s.tenant_id <> sv.id THEN 1 ELSE 0 END),0), 1) as platform_pct_over_7_days FROM shipments s JOIN fulfillments f ON f.id = s.fulfillment_id AND f.tenant_id = s.tenant_id JOIN orders o ON o.id = f.order_id AND o.tenant_id = f.tenant_id CROSS JOIN (SELECT id FROM tenants WHERE name = 'Studio Vrij') sv WHERE s.delivered_at IS NOT NULL AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' GROUP BY month ORDER BY month
STATUS: REJECTED - does not compile: Catalog Error: Scalar Function with name to_char does not exist!
dry_runSELECT strftime(o.placed_at, '%Y-%m') as month, SUM(CASE WHEN s.tenant_id = sv.id THEN 1 ELSE 0 END) as sv_shipments_delivered, ROUND(AVG(CASE WHEN s.tenant_id = sv.id THEN extract(epoch from (s.delivered_at - o.placed_at))/86400 END)::numeric,1) as sv_avg_days_placed_to_delivered, ROUND(100.0 * SUM(CASE WHEN s.tenant_id = sv.id AND extract(epoch from (s.delivered_at - o.placed_at))/86400 > 7 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN s.tenant_id = sv.id THEN 1 ELSE 0 END),0), 1) as sv_pct_over_7_days, SUM(CASE WHEN s.tenant_id <> sv.id THEN 1 ELSE 0 END) as platform_shipments_delivered, ROUND(AVG(CASE WHEN s.tenant_id <> sv.id THEN extract(epoch from (s.delivered_at - o.placed_at))/86400 END)::numeric,1) as platform_avg_days_placed_to_delivered, ROUND(100.0 * SUM(CASE WHEN s.tenant_id <> sv.id AND extract(epoch from (s.delivered_at - o.placed_at))/86400 > 7 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN s.tenant_id <> sv.id THEN 1 ELSE 0 END),0), 1) as platform_pct_over_7_days FROM shipments s JOIN fulfillments f ON f.id = s.fulfillment_id AND f.tenant_id = s.tenant_id JOIN orders o ON o.id = f.order_id AND o.tenant_id = f.tenant_id CROSS JOIN (SELECT id FROM tenants WHERE name = 'Studio Vrij') sv WHERE s.delivered_at IS NOT NULL AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' GROUP BY month ORDER BY month
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: month, sv_shipments_delivered, sv_avg_days_placed_to_delivered, sv_pct_over_7_days, platform_shipments_delivered, platform_avg_days_placed_to_delivered, platform_pct_over_7_days
submitSELECT strftime(o.placed_at, '%Y-%m') as month, SUM(CASE WHEN s.tenant_id = sv.id THEN 1 ELSE 0 END) as sv_shipments_delivered, ROUND(AVG(CASE WHEN s.tenant_id = sv.id THEN extract(epoch from (s.delivered_at - o.placed_at))/86400 END)::numeric,1) as sv_avg_days_placed_to_delivered, ROUND(100.0 * SUM(CASE WHEN s.tenant_id = sv.id AND extract(epoch from (s.delivered_at - o.placed_at))/86400 > 7 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN s.tenant_id = sv.id THEN 1 ELSE 0 END),0), 1) as sv_pct_over_7_days, SUM(CASE WHEN s.tenant_id <> sv.id THEN 1 ELSE 0 END) as platform_shipments_delivered, ROUND(AVG(CASE WHEN s.tenant_id <> sv.id THEN extract(epoch from (s.delivered_at - o.placed_at))/86400 END)::numeric,1) as platform_avg_days_placed_to_delivered, ROUND(100.0 * SUM(CASE WHEN s.tenant_id <> sv.id AND extract(epoch from (s.delivered_at - o.placed_at))/86400 > 7 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN s.tenant_id <> sv.id THEN 1 ELSE 0 END),0), 1) as platform_pct_over_7_days FROM shipments s JOIN fulfillments f ON f.id = s.fulfillment_id AND f.tenant_id = s.tenant_id JOIN orders o ON o.id = f.order_id AND o.tenant_id = f.tenant_id CROSS JOIN (SELECT id FROM tenants WHERE name = 'Studio Vrij') sv WHERE s.delivered_at IS NOT NULL AND o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' GROUP BY month ORDER BY month
Submitted to tonight's batch. Your work on this request is done.