Queries · q335 · VP Platform Engineering · week 2, night 1
“How many merchants have ERP/WMS/Accounting sync webhooks stuck in retry, and is this concentrated in our biggest accounts or spread across the long tail?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
Week 1; shared with Samuel Whitcombe, Head of Merchant Success
Leadership wants a review of platform reliability in 2025: failed and slow background jobs, health check failures, rate limiting, webhook delivery problems, and which merchants felt them.
The manager, at the start of week 2
Subject: Re: 2025 Platform Reliability Review — good start, a few things I need tightened up
Lucas,
Thanks — this is useful and more honest than I expected on the limits of what we can see. A few things stood out and need more work before Friday's memo goes to leadership.
First, the retention gap is itself a finding, not just a caveat. We're flying with 90 days of job history and 60 days of health-check history — that means we genuinely don't know how 2025 looked before October. I want that stated plainly and separately in the memo as a reliability/observability gap in its own right, not buried as a footnote under the carts.remind story.
Second, the ERP/WMS/Accounting webhook stuck-retry pattern is, to me, the most important and actionable thing in here — more than the one-week carts.remind incident. I want that properly validated and sized, not eyeballed, before we say anything about it to leadership.
Questions for this week:
On rate limiting: agreed, drop it for this cycle — don't spend a slot trying to force an answer out of config data alone. Just flag it as a 2026 instrumentation ask, as you already have.
Priority for your remaining slots: #1 and #2 above. The webhook/sync issue is the one I can take into a fix-it conversation with engineering next week if the numbers hold up — I'd rather have that nailed down than more polish on the carts.remind narrative.
Diego
The analyst · week 2, night 1, request 1 of 3
Diego wants the webhook stuck-retry finding properly validated before it goes to leadership, and that's explicitly his #1 and #2 priority this week, so two of tonight's three slots go there: first, a correct tenant-level view (fixing last week's broken join) showing exactly how many merchants have ERP/WMS/Accounting sync webhooks stuck above a real threshold and what revenue/order volume sits behind them, so we can say concentrated-in-big-accounts vs long-tail; second, a full-year monthly trend of that same stuck-rate (webhook_deliveries has clean 12-month coverage, unlike job_runs/health_checks) to tell if this is longstanding or a recent regression. I'm using my third slot on Diego's #3 (carts.remind confidence) since it's a cheap, high-value pull: the actual error_message on the 20 failed runs will tell us in one query whether this was one shared root cause (supports his 'latent risk that could recur' concern being containable) or scattered causes (supports a load/capacity read). I'm deferring Diego's #4 (checking if tickets from the 10 affected tenants actually mention sync/reminders/carts) to night 2 since it needs careful text-search scoping and isn't his top priority this week.
The engineer · 1 dry run on the empty schema, then the submission
WITH sync_webhooks AS ( SELECT id, tenant_id FROM webhooks WHERE lower(name) LIKE '%erp%sync%' OR lower(name) LIKE '%wms%sync%' OR lower(name) LIKE '%accounting%sync%' ), distinct_sync_tenants AS ( SELECT DISTINCT tenant_id FROM sync_webhooks ), sync_deliveries_2025 AS ( SELECT wd.tenant_id, wd.status FROM webhook_deliveries wd JOIN sync_webhooks sw ON sw.id = wd.webhook_id WHERE wd.created_at >= DATE '2025-01-01' AND wd.created_at < DATE '2026-01-01' ), delivery_stats AS ( SELECT tenant_id, COUNT(*) AS total_sync_deliveries_2025, ROUND(100.0 * SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) / COUNT(*), 2) AS success_rate_pct, ROUND(100.0 * SUM(CASE WHEN status IN ('pending','retrying') THEN 1 ELSE 0 END) / COUNT(*), 2) AS pending_or_retrying_rate_pct FROM sync_deliveries_2025 GROUP BY tenant_id ), order_eur_amounts AS ( SELECT o.tenant_id, o.id AS order_id, CASE WHEN o.currency_code = 'EUR' THEN o.grand_total ELSE o.grand_total / er.rate END AS revenue_eur FROM orders o LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS DATE) WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' ), tenant_revenue_all AS ( SELECT tenant_id, COUNT(*) AS total_orders_2025, ROUND(SUM(revenue_eur), 2) AS total_revenue_eur_2025 FROM order_eur_amounts GROUP BY tenant_id ), revenue_ranked AS ( SELECT tenant_id, total_orders_2025, total_revenue_eur_2025, DENSE_RANK() OVER (ORDER BY total_revenue_eur_2025 DESC) AS revenue_rank FROM tenant_revenue_all ) SELECT t.name AS tenant_name, COALESCE(ds.total_sync_deliveries_2025, 0) AS total_sync_deliveries_2025, ds.success_rate_pct, ds.pending_or_retrying_rate_pct, COALESCE(rr.total_orders_2025, 0) AS total_orders_2025, rr.total_revenue_eur_2025, rr.revenue_rank FROM distinct_sync_tenants dst JOIN tenants t ON t.id = dst.tenant_id LEFT JOIN delivery_stats ds ON ds.tenant_id = dst.tenant_id LEFT JOIN revenue_ranked rr ON rr.tenant_id = dst.tenant_id ORDER BY ds.pending_or_retrying_rate_pct DESC NULLS LAST, t.name ASC
“Query compiles cleanly with the requested columns. Submitting.”
“Submitted. Summary of what the query does: - Identifies tenants with webhooks whose name indicates ERP/WMS/Accounting sync (case-insensitive substring match, since I couldn't peek at actual data via dry_run). - Aggregates all their 2025 webhook deliveries into total count, success rate %, and pending/retrying rate %. - Separately computes 2025 order counts and EUR-converted revenue for all tenants, dense-ranks them by revenue, then joins that rank onto the sync-affected subset so you can see where they sit platform-wide. - Orders by pending/retrying rate descending (the "stuck" tenants first), tie-broken by tenant name. Flagged in the submit note: the webhook name matching is a best-effort pattern match (not a confirmed exact/variant list, since dry_run can't show data), and tenants with no 2025 deliveries/orders/FX match surface as 0/NULL rather than being excluded.”
As Lucas Moreau saw it, from the small dataset the agents worked on
tenant_name | total_sync_deliveries_2025 | success_rate_pct | pending_or_retrying_rate_pct | total_orders_2025 | total_revenue_eur_2025 | revenue_rank ------------------------------+----------------------------+------------------+------------------------------+-------------------+------------------------+------------- Teapot Society | 2 | 50.0 | 50.0 | 1 | 78.81 | 112 Kaffeekontor Nord | 13 | 61.54 | 38.46 | 8 | 405.82 | 105 Hautnah | 120 | 80.0 | 20.0 | 92 | 9190.62 | 51 Lumen Collective | 89 | 82.02 | 17.98 | 52 | 6391.23 | 62 Hanse Industriebedarf | 97 | 83.51 | 16.49 | 72 | 113320.38 | 13 Seilschaft | 25 | 84.0 | 16.0 | 17 | 6759.28 | 60 Thistlecombe | 140 | 84.29 | 15.71 | 92 | 12570.49 | 44 Folio & Fern | 58 | 86.21 | 13.79 | 46 | 2148.33 | 88 Brennquell Kaffee | 14149 | 88.87 | 11.12 | 4195 | 114554.65 | 11 Ampfeld Elektronik | 1583 | 89.83 | 10.17 | 449 | 512006.37 | 4 Linden & Loom | 614 | 91.21 | 8.79 | 263 | 38475.39 | 20 Wohnglück | 341 | 91.79 | 8.21 | 151 | 22859.8 | 32 Kibble Crate | 19 | 94.74 | 5.26 | 18 | 1211.91 | 95 Cragsley Outfitters | 14104 | 95.04 | 4.96 | 3635 | 724835.93 | 2 Basecamp Nord | 49 | 95.92 | 4.08 | 43 | 10657.51 | 46 Purrfect Pantry | 81 | 96.3 | 3.7 | 74 | 3583.86 | 74 Kettle & Key | 68 | 97.06 | 2.94 | 60 | 7581.57 | 54 Velo Noord | 39 | 97.44 | 2.56 | 31 | 9780.02 | 49 Noordzee Surfwear | 88 | 97.73 | 2.27 | 65 | 10894.01 | 45 Wiesgrund Wohnen | 642 | 97.98 | 2.02 | 296 | 38433.09 | 21 Sorella Skin | 105 | 98.1 | 1.9 | 99 | 10495.53 | 47 Mirelle | 68 | 98.53 | 1.47 | 63 | 2920.13 | 78 Studio Vrij | 2396 | 98.62 | 1.38 | 631 | 113540.14 | 12 Gratlinie Outdoor | 291 | 98.63 | 1.37 | 232 | 96277.59 | 14 Marrowby & Vane | 6425 | 98.63 | 1.37 | 1758 | 288909.93 | 8 Kibblewick | 1349 | 98.74 | 1.26 | 672 | 23801.41 | 30 Fjordline Wear | 81 | 98.77 | 1.23 | 57 | 8615.11 | 52 Nordic Bloom Cosmetics | 163 | 98.77 | 1.23 | 151 | 19074.7 | 36 Ostwind Mode | 162 | 98.77 | 1.23 | 122 | 21621.0 | 33 Atelier Nouveau | 87 | 98.85 | 1.15 | 64 | 9406.81 | 50 Grind Collective | 1136 | 98.94 | 1.06 | 1111 | 25610.52 | 27 Kontor24 Bürobedarf | 534 | 99.06 | 0.94 | 264 | 840409.53 | 1 Chocolaterie Mireille | 108 | 99.07 | 0.93 | 104 | 4566.99 | 67 Spice Route Box | 796 | 99.12 | 0.88 | 780 | 17113.71 | 39 Ballycorran Industrial Supply | 380 | 99.21 | 0.79 | 344 | 559149.73 | 3 Cinder Peak Sports Co. | 748 | 99.33 | 0.67 | 338 | 124756.84 | 10 Coastline Interiors | 179 | 99.44 | 0.56 | 155 | 21468.31 | 34 Fennel & Flint | 814 | 99.51 | 0.49 | 389 | 65764.13 | 16 OfficeHub Nordic | 350 | 99.71 | 0.29 | 170 | 428472.93 | 5 Petalcourt Beauty | 775 | 99.74 | 0.26 | 706 | 63508.22 | 17 Bijoux Lune | 23 | 100.0 | 0.0 | 21 | 3794.91 | 72 Copper Lane | 8 | 100.0 | 0.0 | 6 | 1003.56 | 98 Courtside Collective | 23 | 100.0 | 0.0 | 20 | 8514.48 | 53 Dewdrop Studio | 5 | 100.0 | 0.0 | 5 | 271.34 | 108 Fern & Fable | 48 | 100.0 | 0.0 | 42 | 5564.1 | 64 Glasshouse Grooming | 10 | 100.0 | 0.0 | 10 | 1455.2 | 93 Northstar Watches | 14 | 100.0 | 0.0 | 11 | 2655.08 | 85 Olive & Oak Pantry | 1 | 100.0 | 0.0 | 1 | 118.55 | 111 Ondine Botanicals | 27 | 100.0 | 0.0 | 25 | 1694.77 | 90 Papeterie Grossiste | 20 | 100.0 | 0.0 | 18 | 41189.21 | 18 (56 rows, first 50 printed)
The memo and the queries that followed; links found by title, so inferred
Lucas Moreau's memo to Diego Morales at the end of the week:
6. Webhook sync stuck-retries (your top priority): confirmed concentrated and longstanding, not new. ~91% of all stuck ERP/WMS/Accounting sync deliveries sit with 4 tenants (Brennquell Kaffee ~1,574, Cragsley Outfitters ~700, Ampfeld Elektronik ~161, Marrowby & Vane ~88 of ~2,774 total). Their monthly stuck rates have been stable all year (Brennquell Kaffee 9.7-13.7%, Cragsley Outfitters 3.9-6.5%, Ampfeld Elektronik noisy 3-17% around ~9%) — this is a lived-with condition, not a recent regression. Sources: "Merchants Affected by the Sync-Webhook Stuck-Retry Pattern," "Monthly Sync-Webhook Stuck Rate for the 4 Highest-Impact Tenants." Last week's "~15%" figure was a selection-biased view of only the worst endpoints — the true platform average is a flat ~5.5%/month all year.
Later queries that build on this report:
It builds on:
Changed only where the benchmark needs the same result on every system
WITH sync_webhooks AS ( SELECT id, tenant_id FROM webhooks WHERE lower(name) LIKE '%erp%sync%' OR lower(name) LIKE '%wms%sync%' OR lower(name) LIKE '%accounting%sync%'),distinct_sync_tenants AS ( SELECT DISTINCT tenant_id FROM sync_webhooks),sync_deliveries_2025 AS ( SELECT wd.tenant_id, wd.status SELECT wd.tenant_id AS tenant_id, wd.status AS status FROM webhook_deliveries wd JOIN sync_webhooks sw ON sw.id = wd.webhook_id WHERE wd.created_at >= DATE '2025-01-01' AND wd.created_at < DATE '2026-01-01'),delivery_stats AS ( SELECT tenant_id, COUNT(*) AS total_sync_deliveries_2025, ROUND(100.0 * SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) / COUNT(*), 2) AS success_rate_pct, ROUND(100.0 * SUM(CASE WHEN status IN ('pending','retrying') THEN 1 ELSE 0 END) / COUNT(*), 2) AS pending_or_retrying_rate_pct 100.0 * SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) / COUNT(*) AS success_rate_pct, 100.0 * SUM(CASE WHEN status IN ('pending','retrying') THEN 1 ELSE 0 END) / COUNT(*) AS pending_or_retrying_rate_pct FROM sync_deliveries_2025 GROUP BY tenant_id),order_eur_amounts AS ( SELECT o.tenant_id, o.tenant_id AS tenant_id, o.id AS order_id, CASE WHEN o.currency_code = 'EUR' THEN o.grand_total ELSE o.grand_total / er.rate END AS revenue_eur ELSE CAST(o.grand_total / er.rate AS NUMERIC(18,6)) END AS revenue_eur FROM orders o LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS DATE) WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'),tenant_revenue_all AS ( SELECT tenant_id, COUNT(*) AS total_orders_2025, ROUND(SUM(revenue_eur), 2) AS total_revenue_eur_2025 SUM(revenue_eur) AS total_revenue_eur_2025 FROM order_eur_amounts GROUP BY tenant_id),revenue_ranked AS ( SELECT tenant_id, total_orders_2025, total_revenue_eur_2025, DENSE_RANK() OVER (ORDER BY total_revenue_eur_2025 DESC) AS revenue_rank DENSE_RANK() OVER (ORDER BY total_revenue_eur_2025 DESC NULLS FIRST) AS revenue_rank FROM tenant_revenue_all)SELECT t.name AS tenant_name, COALESCE(ds.total_sync_deliveries_2025, 0) AS total_sync_deliveries_2025, ds.success_rate_pct, ds.pending_or_retrying_rate_pct, ds.success_rate_pct AS success_rate_pct, ds.pending_or_retrying_rate_pct AS pending_or_retrying_rate_pct, COALESCE(rr.total_orders_2025, 0) AS total_orders_2025, rr.total_revenue_eur_2025, rr.revenue_rank rr.total_revenue_eur_2025 AS total_revenue_eur_2025, rr.revenue_rank AS revenue_rankFROM distinct_sync_tenants dstJOIN tenants t ON t.id = dst.tenant_idLEFT JOIN delivery_stats ds ON ds.tenant_id = dst.tenant_idLEFT JOIN revenue_ranked rr ON rr.tenant_id = dst.tenant_idORDER BY ds.pending_or_retrying_rate_pct DESC NULLS LAST, t.name ASCORDER BY ds.pending_or_retrying_rate_pct DESC NULLS LAST, t.name ASC NULLS LAST, t.id NULLS LAST
WITH sync_webhooks AS ( SELECT id, tenant_id FROM webhooks WHERE lower(name) LIKE '%erp%sync%' OR lower(name) LIKE '%wms%sync%' OR lower(name) LIKE '%accounting%sync%' ), distinct_sync_tenants AS ( SELECT DISTINCT tenant_id FROM sync_webhooks ), sync_deliveries_2025 AS ( SELECT wd.tenant_id AS tenant_id, wd.status AS status FROM webhook_deliveries wd JOIN sync_webhooks sw ON sw.id = wd.webhook_id WHERE wd.created_at >= DATE '2025-01-01' AND wd.created_at < DATE '2026-01-01' ), delivery_stats AS ( SELECT tenant_id, COUNT(*) AS total_sync_deliveries_2025, 100.0 * SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) / COUNT(*) AS success_rate_pct, 100.0 * SUM(CASE WHEN status IN ('pending','retrying') THEN 1 ELSE 0 END) / COUNT(*) AS pending_or_retrying_rate_pct FROM sync_deliveries_2025 GROUP BY tenant_id ), order_eur_amounts AS ( SELECT o.tenant_id AS tenant_id, o.id AS order_id, CASE WHEN o.currency_code = 'EUR' THEN o.grand_total ELSE CAST(o.grand_total / er.rate AS NUMERIC(18,6)) END AS revenue_eur FROM orders o LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS DATE) WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' ), tenant_revenue_all AS ( SELECT tenant_id, COUNT(*) AS total_orders_2025, SUM(revenue_eur) AS total_revenue_eur_2025 FROM order_eur_amounts GROUP BY tenant_id ), revenue_ranked AS ( SELECT tenant_id, total_orders_2025, total_revenue_eur_2025, DENSE_RANK() OVER (ORDER BY total_revenue_eur_2025 DESC NULLS FIRST) AS revenue_rank FROM tenant_revenue_all ) SELECT t.name AS tenant_name, COALESCE(ds.total_sync_deliveries_2025, 0) AS total_sync_deliveries_2025, ds.success_rate_pct AS success_rate_pct, ds.pending_or_retrying_rate_pct AS pending_or_retrying_rate_pct, COALESCE(rr.total_orders_2025, 0) AS total_orders_2025, rr.total_revenue_eur_2025 AS total_revenue_eur_2025, rr.revenue_rank AS revenue_rank FROM distinct_sync_tenants dst JOIN tenants t ON t.id = dst.tenant_id LEFT JOIN delivery_stats ds ON ds.tenant_id = dst.tenant_id LEFT JOIN revenue_ranked rr ON rr.tenant_id = dst.tenant_id ORDER BY ds.pending_or_retrying_rate_pct DESC NULLS LAST, t.name ASC NULLS LAST, t.id 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 Lucas Moreau (analyst for the VP Platform Engineering):
Title: Merchants Affected by the Sync-Webhook Stuck-Retry Pattern and Their Size
Question: How many merchants have ERP/WMS/Accounting sync webhooks stuck in retry, and is this concentrated in our biggest accounts or spread across the long tail?
Grain: one row per tenant that has at least one ERP sync, WMS sync, or ACCOUNTING sync webhook
Columns:
- tenant_name
- total_sync_deliveries_2025
- success_rate_pct
- pending_or_retrying_rate_pct
- total_orders_2025
- total_revenue_eur_2025
- revenue_rank
Details: Tables: webhook_deliveries joined to webhooks (match webhooks.name in the sync-integration set actually present in the data - 'ERP sync', 'WMS sync', 'ACCOUNTING sync', confirm exact distinct values and include any close variants) joined to tenants. Filter created_at in 2025. Aggregate all of a tenant's sync-type webhooks together: total_sync_deliveries_2025 = count of deliveries; success_rate_pct = share with status='success'; pending_or_retrying_rate_pct = share with status in ('pending','retrying'), both rounded 2dp. total_orders_2025 = count of orders with placed_at in 2025; total_revenue_eur_2025 = sum of orders.grand_total converted to EUR via exchange_rates (base_code='EUR', quote_code=orders.currency_code, as_of=date(placed_at)), rounded to 2dp. revenue_rank = dense rank of ALL tenants (not just this subset) by total_revenue_eur_2025 descending, so we can see where these merchants sit platform-wide. Order by pending_or_retrying_rate_pct descending, tie-break tenant_name ascending. Return all qualifying tenants (note total count in the report even if under 50).
Check your query with the warehouse's dry_run tool, then hand it in with submit.dry_runWITH sync_webhooks AS ( SELECT id, tenant_id FROM webhooks WHERE lower(name) LIKE '%erp%sync%' OR lower(name) LIKE '%wms%sync%' OR lower(name) LIKE '%accounting%sync%' ), distinct_sync_tenants AS ( SELECT DISTINCT tenant_id FROM sync_webhooks ), sync_deliveries_2025 AS ( SELECT wd.tenant_id, wd.status FROM webhook_deliveries wd JOIN sync_webhooks sw ON sw.id = wd.webhook_id WHERE wd.created_at >= DATE '2025-01-01' AND wd.created_at < DATE '2026-01-01' ), delivery_stats AS ( SELECT tenant_id, COUNT(*) AS total_sync_deliveries_2025, ROUND(100.0 * SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) / COUNT(*), 2) AS success_rate_pct, ROUND(100.0 * SUM(CASE WHEN status IN ('pending','retrying') THEN 1 ELSE 0 END) / COUNT(*), 2) AS pending_or_retrying_rate_pct FROM sync_deliveries_2025 GROUP BY tenant_id ), order_eur_amounts AS ( SELECT o.tenant_id, o.id AS order_id, CASE WHEN o.currency_code = 'EUR' THEN o.grand_total ELSE o.grand_total / er.rate END AS revenue_eur FROM orders o LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS DATE) WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' ), tenant_revenue_all AS ( SELECT tenant_id, COUNT(*) AS total_orders_2025, ROUND(SUM(revenue_eur), 2) AS total_revenue_eur_2025 FROM order_eur_amounts GROUP BY tenant_id ), revenue_ranked AS ( SELECT tenant_id, total_orders_2025, total_revenue_eur_2025, DENSE_RANK() OVER (ORDER BY total_revenue_eur_2025 DESC) AS revenue_rank FROM tenant_revenue_all ) SELECT t.name AS tenant_name, COALESCE(ds.total_sync_deliveries_2025, 0) AS total_sync_deliveries_2025, ds.success_rate_pct, ds.pending_or_retrying_rate_pct, COALESCE(rr.total_orders_2025, 0) AS total_orders_2025, rr.total_revenue_eur_2025, rr.revenue_rank FROM distinct_sync_tenants dst JOIN tenants t ON t.id = dst.tenant_id LEFT JOIN delivery_stats ds ON ds.tenant_id = dst.tenant_id LEFT JOIN revenue_ranked rr ON rr.tenant_id = dst.tenant_id ORDER BY ds.pending_or_retrying_rate_pct DESC NULLS LAST, t.name ASC
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: tenant_name, total_sync_deliveries_2025, success_rate_pct, pending_or_retrying_rate_pct, total_orders_2025, total_revenue_eur_2025, revenue_rank
submitWITH sync_webhooks AS ( SELECT id, tenant_id FROM webhooks WHERE lower(name) LIKE '%erp%sync%' OR lower(name) LIKE '%wms%sync%' OR lower(name) LIKE '%accounting%sync%' ), distinct_sync_tenants AS ( SELECT DISTINCT tenant_id FROM sync_webhooks ), sync_deliveries_2025 AS ( SELECT wd.tenant_id, wd.status FROM webhook_deliveries wd JOIN sync_webhooks sw ON sw.id = wd.webhook_id WHERE wd.created_at >= DATE '2025-01-01' AND wd.created_at < DATE '2026-01-01' ), delivery_stats AS ( SELECT tenant_id, COUNT(*) AS total_sync_deliveries_2025, ROUND(100.0 * SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) / COUNT(*), 2) AS success_rate_pct, ROUND(100.0 * SUM(CASE WHEN status IN ('pending','retrying') THEN 1 ELSE 0 END) / COUNT(*), 2) AS pending_or_retrying_rate_pct FROM sync_deliveries_2025 GROUP BY tenant_id ), order_eur_amounts AS ( SELECT o.tenant_id, o.id AS order_id, CASE WHEN o.currency_code = 'EUR' THEN o.grand_total ELSE o.grand_total / er.rate END AS revenue_eur FROM orders o LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS DATE) WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01' ), tenant_revenue_all AS ( SELECT tenant_id, COUNT(*) AS total_orders_2025, ROUND(SUM(revenue_eur), 2) AS total_revenue_eur_2025 FROM order_eur_amounts GROUP BY tenant_id ), revenue_ranked AS ( SELECT tenant_id, total_orders_2025, total_revenue_eur_2025, DENSE_RANK() OVER (ORDER BY total_revenue_eur_2025 DESC) AS revenue_rank FROM tenant_revenue_all ) SELECT t.name AS tenant_name, COALESCE(ds.total_sync_deliveries_2025, 0) AS total_sync_deliveries_2025, ds.success_rate_pct, ds.pending_or_retrying_rate_pct, COALESCE(rr.total_orders_2025, 0) AS total_orders_2025, rr.total_revenue_eur_2025, rr.revenue_rank FROM distinct_sync_tenants dst JOIN tenants t ON t.id = dst.tenant_id LEFT JOIN delivery_stats ds ON ds.tenant_id = dst.tenant_id LEFT JOIN revenue_ranked rr ON rr.tenant_id = dst.tenant_id ORDER BY ds.pending_or_retrying_rate_pct DESC NULLS LAST, t.name ASC
Submitted to tonight's batch. Your work on this request is done.