Queries · q151 · Head of Pricing and Promotions · week 1, night 2
“Is the 'Subscription delivery (prepaid)' discount a real zero-margin giveaway, or does the €0 order revenue reflect that these customers actually pay via a separate subscription invoice?”
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 Tobias Lindqvist, Chief Financial Officer
Finance questions the cost of discounts. Which promotions and coupons brought additional sales, which only gave away margin, and is anything being misused?
The manager, at the start of week 1
Subject: Priorities for this week's discount cost-review with Tobias
Oliver,
Tobias and I need a credible answer to "are our promotions and coupons earning their keep," in time for Friday's memo. This feeds a joint decision on which promotions/coupons we keep, retire, or cap going into next quarter — so I'd rather have four solid, well-checked answers than a pile of rushed ones. Please pick the nights' reports accordingly and flag anything suspicious you find along the way rather than burying it in a footnote.
Here's what I actually want to know, in my own words:
Keep each report to one clear question — I'd rather get three clean answers this week than one bundled one that gets bounced. Let's sync briefly before you submit Thursday's batch in case the first results change what we ask.
Thanks, Marco
Subject: Finance's angle on the promotions/coupons review
Oliver,
As you and Marco dig into discount performance this week, a few things I'd like the analysis to keep an eye on from the finance side:
Keep it tight; I mainly need the "so what" for the annual accounts and the 2026 budget assumptions on discount cost.
Thanks, Tobias
The analyst · week 1, night 2, request 1 of 3
Two things from last night change tonight's plan:
(1) The single biggest cost line in Report 1 — "Subscription delivery (prepaid)" — shows real discount spend (up to ~€24k on one tenant instance, and it recurs across ~6 rows/tenants) but €0.0 associated order revenue every single time, which also explains the absurd discount_pct_of_revenue values in Report 2 (Grind Collective 99.92%, Honeycomb Larder 96.93%, The Pencil Case 781.56%). Before I rank "biggest cost, no lift" discounts for Marco, I need to know whether this is a real zero-margin giveaway or a billing-model artifact where the actual revenue is collected via subscription invoices rather than the order itself. Getting this wrong would make me tell Marco to kill a discount that isn't actually losing money, or miss one that genuinely is.
(2) Report 3's abuse framing was broken: usage_limit and per_customer_limit were NULL for every coupon shown, so "max redemptions by a customer > limit" could never actually fire — we only surfaced raw repeat-redemption counts, not real breaches. I want to redo this narrowly on coupons that actually have a configured cap, which is the only place "abuse" is well-defined.
I'm also starting the hard incrementality question (Marco/Tobias Q2) now rather than waiting, using a within-customer before/after comparison (discounted order value vs. the same customers' other, non-discounted orders) as an honest, approximate read — this needs iteration so it's better to see a first pass early.
All three tonight are independent and don't block each other.
The engineer · 2 dry runs on the empty schema, then the submission
WITH disc_orders AS ( SELECT o.id AS order_id, o.tenant_id, o.user_id, o.currency_code, o.placed_at, o.subtotal, o.discount_total, o.tax_total, o.shipping_total, o.grand_total FROM order_discounts od JOIN discounts d ON d.id = od.discount_id AND d.tenant_id = od.tenant_id JOIN orders o ON o.id = od.order_id AND o.tenant_id = od.tenant_id WHERE d.name = 'Subscription delivery (prepaid)' AND o.status <> 'cancelled' AND o.placed_at >= TIMESTAMP '2025-01-01' AND o.placed_at < TIMESTAMP '2026-01-01' ), rated AS ( SELECT do.*, er.rate, do.subtotal / er.rate AS subtotal_eur, do.discount_total / er.rate AS discount_total_eur, do.tax_total / er.rate AS tax_total_eur, do.shipping_total / er.rate AS shipping_total_eur, do.grand_total / er.rate AS grand_total_eur, CASE WHEN EXISTS ( SELECT 1 FROM subscriptions s WHERE s.tenant_id = do.tenant_id AND s.user_id = do.user_id AND s.status IN ('trialing','active','past_due') AND s.started_at <= do.placed_at AND (s.ended_at IS NULL OR s.ended_at >= do.placed_at) ) THEN 1 ELSE 0 END AS has_active_sub FROM disc_orders do JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = do.currency_code AND er.as_of = CAST(do.placed_at AS date) ) SELECT t.name AS tenant_name, COUNT(*) AS order_count, COUNT(DISTINCT r.user_id) AS distinct_customers, ROUND(AVG(r.subtotal_eur),2) AS avg_subtotal_eur, ROUND(AVG(r.discount_total_eur),2) AS avg_discount_total_eur, ROUND(AVG(r.shipping_total_eur),2) AS avg_shipping_total_eur, ROUND(AVG(r.tax_total_eur),2) AS avg_tax_total_eur, ROUND(AVG(r.grand_total_eur),2) AS avg_grand_total_eur, ROUND(100.0 * SUM(CASE WHEN r.grand_total = 0 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_zero_grand_total, ROUND(100.0 * SUM(r.has_active_sub) / COUNT(*), 1) AS pct_orders_with_active_subscription FROM rated r JOIN tenants t ON t.id = r.tenant_id GROUP BY t.name ORDER BY order_count DESC, tenant_name ASC
WITH disc_orders AS ( SELECT o.id AS order_id, o.tenant_id, o.user_id, o.currency_code, o.placed_at, o.subtotal, o.discount_total, o.tax_total, o.shipping_total, o.grand_total FROM order_discounts od JOIN discounts d ON d.id = od.discount_id AND d.tenant_id = od.tenant_id JOIN orders o ON o.id = od.order_id AND o.tenant_id = od.tenant_id WHERE d.name = 'Subscription delivery (prepaid)' AND o.status <> 'cancelled' AND o.placed_at >= TIMESTAMP '2025-01-01' AND o.placed_at < TIMESTAMP '2026-01-01'),rated AS ( SELECT do.*, SELECT dso.*, er.rate, do.subtotal / er.rate AS subtotal_eur, do.discount_total / er.rate AS discount_total_eur, do.tax_total / er.rate AS tax_total_eur, do.shipping_total / er.rate AS shipping_total_eur, do.grand_total / er.rate AS grand_total_eur, dso.subtotal / er.rate AS subtotal_eur, dso.discount_total / er.rate AS discount_total_eur, dso.tax_total / er.rate AS tax_total_eur, dso.shipping_total / er.rate AS shipping_total_eur, dso.grand_total / er.rate AS grand_total_eur, CASE WHEN EXISTS ( SELECT 1 FROM subscriptions s WHERE s.tenant_id = do.tenant_id AND s.user_id = do.user_id WHERE s.tenant_id = dso.tenant_id AND s.user_id = dso.user_id AND s.status IN ('trialing','active','past_due') AND s.started_at <= do.placed_at AND (s.ended_at IS NULL OR s.ended_at >= do.placed_at) AND s.started_at <= dso.placed_at AND (s.ended_at IS NULL OR s.ended_at >= dso.placed_at) ) THEN 1 ELSE 0 END AS has_active_sub FROM disc_orders do FROM disc_orders dso JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = do.currency_code AND er.as_of = CAST(do.placed_at AS date) AND er.quote_code = dso.currency_code AND er.as_of = CAST(dso.placed_at AS date))SELECT t.name AS tenant_name, COUNT(*) AS order_count, COUNT(DISTINCT r.user_id) AS distinct_customers, ROUND(AVG(r.subtotal_eur),2) AS avg_subtotal_eur, ROUND(AVG(r.discount_total_eur),2) AS avg_discount_total_eur, ROUND(AVG(r.shipping_total_eur),2) AS avg_shipping_total_eur, ROUND(AVG(r.tax_total_eur),2) AS avg_tax_total_eur, ROUND(AVG(r.grand_total_eur),2) AS avg_grand_total_eur, ROUND(100.0 * SUM(CASE WHEN r.grand_total = 0 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_zero_grand_total, ROUND(100.0 * SUM(r.has_active_sub) / COUNT(*), 1) AS pct_orders_with_active_subscriptionFROM rated rJOIN tenants t ON t.id = r.tenant_idGROUP BY t.nameORDER BY order_count DESC, tenant_name ASC
“Query compiles with the expected 10 columns. Submitting now.”
“Submitted. Summary of what the report will show: - Grain: one row per tenant, limited to orders carrying the 'Subscription delivery (prepaid)' discount, placed between 2025‑01‑01 and 2025‑12‑31, excluding cancelled orders. - Columns: tenant name, order count, distinct customers, avg subtotal/discount/shipping/tax/grand total in EUR (converted via the day's EUR→currency exchange rate), % of orders with grand_total = 0, and % of orders where the customer had an active subscription (trialing/active/past_due) overlapping the order's placed_at date. - Note to analyst: the subscription-overlap percentage is computed per order (not per unique customer), since a customer's subscription status can differ across their own orders depending on the date.”
As Oliver Grant saw it, from the small dataset the agents worked on
tenant_name | order_count | distinct_customers | avg_subtotal_eur | avg_discount_total_eur | avg_shipping_total_eur | avg_tax_total_eur | avg_grand_total_eur | pct_zero_grand_total | pct_orders_with_active_subscription -----------------+-------------+--------------------+------------------+------------------------+------------------------+-------------------+---------------------+----------------------+------------------------------------ Grind Collective | 650 | 74 | 37.31 | 37.31 | 0.0 | 0.0 | 0.0 | 100.0 | 74.3 Honeycomb Larder | 354 | 51 | 46.68 | 46.68 | 0.0 | 0.0 | 0.0 | 100.0 | 64.1 Kibblewick | 326 | 57 | 36.2 | 36.2 | 0.0 | 0.0 | 0.0 | 100.0 | 69.3 Feather & Fin | 106 | 21 | 52.79 | 52.79 | 0.0 | 0.0 | 0.0 | 100.0 | 53.8 Saffron & Sage | 43 | 8 | 105.61 | 105.61 | 0.0 | 0.0 | 0.0 | 100.0 | 60.5 The Balm Room | 16 | 4 | 97.13 | 97.13 | 0.0 | 0.0 | 0.0 | 100.0 | 93.8 The Pencil Case | 3 | 1 | 67.61 | 67.61 | 0.0 | 0.0 | 0.0 | 100.0 | 100.0 Snout & Sons | 1 | 1 | 34.81 | 34.81 | 0.0 | 0.0 | 0.0 | 100.0 | 100.0 (8 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 disc_orders AS ( SELECT o.id AS order_id, o.tenant_id, o.user_id, o.currency_code, o.placed_at, o.subtotal, o.discount_total, o.tax_total, o.shipping_total, o.grand_total SELECT o.id AS order_id, o.tenant_id AS tenant_id, o.user_id AS user_id, o.currency_code AS currency_code, o.placed_at AS placed_at, o.subtotal AS subtotal, o.discount_total AS discount_total, o.tax_total AS tax_total, o.shipping_total AS shipping_total, o.grand_total AS grand_total FROM order_discounts od JOIN discounts d ON d.id = od.discount_id AND d.tenant_id = od.tenant_id JOIN orders o ON o.id = od.order_id AND o.tenant_id = od.tenant_id WHERE d.name = 'Subscription delivery (prepaid)' AND o.status <> 'cancelled' AND o.placed_at >= TIMESTAMP '2025-01-01' AND o.placed_at < TIMESTAMP '2026-01-01'),rated AS ( SELECT dso.*, er.rate, dso.subtotal / er.rate AS subtotal_eur, dso.discount_total / er.rate AS discount_total_eur, dso.tax_total / er.rate AS tax_total_eur, dso.shipping_total / er.rate AS shipping_total_eur, dso.grand_total / er.rate AS grand_total_eur, er.rate AS rate, CAST(dso.subtotal / er.rate AS NUMERIC(18,6)) AS subtotal_eur, CAST(dso.discount_total / er.rate AS NUMERIC(18,6)) AS discount_total_eur, CAST(dso.tax_total / er.rate AS NUMERIC(18,6)) AS tax_total_eur, CAST(dso.shipping_total / er.rate AS NUMERIC(18,6)) AS shipping_total_eur, CAST(dso.grand_total / er.rate AS NUMERIC(18,6)) AS grand_total_eur, CASE WHEN EXISTS ( SELECT 1 FROM subscriptions s WHERE s.tenant_id = dso.tenant_id AND s.user_id = dso.user_id AND s.status IN ('trialing','active','past_due') AND s.started_at <= dso.placed_at AND (s.ended_at IS NULL OR s.ended_at >= dso.placed_at) ) THEN 1 ELSE 0 END AS has_active_sub FROM disc_orders dso JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = dso.currency_code AND er.as_of = CAST(dso.placed_at AS date))SELECT t.name AS tenant_name, COUNT(*) AS order_count, COUNT(DISTINCT r.user_id) AS distinct_customers, ROUND(AVG(r.subtotal_eur),2) AS avg_subtotal_eur, ROUND(AVG(r.discount_total_eur),2) AS avg_discount_total_eur, ROUND(AVG(r.shipping_total_eur),2) AS avg_shipping_total_eur, ROUND(AVG(r.tax_total_eur),2) AS avg_tax_total_eur, ROUND(AVG(r.grand_total_eur),2) AS avg_grand_total_eur, ROUND(100.0 * SUM(CASE WHEN r.grand_total = 0 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_zero_grand_total, ROUND(100.0 * SUM(r.has_active_sub) / COUNT(*), 1) AS pct_orders_with_active_subscription AVG(r.subtotal_eur) AS avg_subtotal_eur, AVG(r.discount_total_eur) AS avg_discount_total_eur, AVG(r.shipping_total_eur) AS avg_shipping_total_eur, AVG(r.tax_total_eur) AS avg_tax_total_eur, AVG(r.grand_total_eur) AS avg_grand_total_eur, 100.0 * SUM(CASE WHEN r.grand_total = 0 THEN 1 ELSE 0 END) / COUNT(*) AS pct_zero_grand_total, 100.0 * SUM(r.has_active_sub) / COUNT(*) AS pct_orders_with_active_subscriptionFROM rated rJOIN tenants t ON t.id = r.tenant_idGROUP BY t.nameORDER BY order_count DESC, tenant_name ASCORDER BY order_count DESC NULLS FIRST, tenant_name ASC NULLS LAST
WITH disc_orders AS ( SELECT o.id AS order_id, o.tenant_id AS tenant_id, o.user_id AS user_id, o.currency_code AS currency_code, o.placed_at AS placed_at, o.subtotal AS subtotal, o.discount_total AS discount_total, o.tax_total AS tax_total, o.shipping_total AS shipping_total, o.grand_total AS grand_total FROM order_discounts od JOIN discounts d ON d.id = od.discount_id AND d.tenant_id = od.tenant_id JOIN orders o ON o.id = od.order_id AND o.tenant_id = od.tenant_id WHERE d.name = 'Subscription delivery (prepaid)' AND o.status <> 'cancelled' AND o.placed_at >= TIMESTAMP '2025-01-01' AND o.placed_at < TIMESTAMP '2026-01-01' ), rated AS ( SELECT dso.*, er.rate AS rate, CAST(dso.subtotal / er.rate AS NUMERIC(18,6)) AS subtotal_eur, CAST(dso.discount_total / er.rate AS NUMERIC(18,6)) AS discount_total_eur, CAST(dso.tax_total / er.rate AS NUMERIC(18,6)) AS tax_total_eur, CAST(dso.shipping_total / er.rate AS NUMERIC(18,6)) AS shipping_total_eur, CAST(dso.grand_total / er.rate AS NUMERIC(18,6)) AS grand_total_eur, CASE WHEN EXISTS ( SELECT 1 FROM subscriptions s WHERE s.tenant_id = dso.tenant_id AND s.user_id = dso.user_id AND s.status IN ('trialing','active','past_due') AND s.started_at <= dso.placed_at AND (s.ended_at IS NULL OR s.ended_at >= dso.placed_at) ) THEN 1 ELSE 0 END AS has_active_sub FROM disc_orders dso JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = dso.currency_code AND er.as_of = CAST(dso.placed_at AS date) ) SELECT t.name AS tenant_name, COUNT(*) AS order_count, COUNT(DISTINCT r.user_id) AS distinct_customers, AVG(r.subtotal_eur) AS avg_subtotal_eur, AVG(r.discount_total_eur) AS avg_discount_total_eur, AVG(r.shipping_total_eur) AS avg_shipping_total_eur, AVG(r.tax_total_eur) AS avg_tax_total_eur, AVG(r.grand_total_eur) AS avg_grand_total_eur, 100.0 * SUM(CASE WHEN r.grand_total = 0 THEN 1 ELSE 0 END) / COUNT(*) AS pct_zero_grand_total, 100.0 * SUM(r.has_active_sub) / COUNT(*) AS pct_orders_with_active_subscription FROM rated r JOIN tenants t ON t.id = r.tenant_id GROUP BY t.name ORDER BY order_count DESC NULLS FIRST, tenant_name 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 Oliver Grant (analyst for the Head of Pricing and Promotions):
Title: Order economics behind the 'Subscription delivery (prepaid)' discount
Question: Is the 'Subscription delivery (prepaid)' discount a real zero-margin giveaway, or does the €0 order revenue reflect that these customers actually pay via a separate subscription invoice?
Grain: one row per tenant, restricted to orders carrying the 'Subscription delivery (prepaid)' discount
Columns:
- tenant name
- order count
- distinct customers
- avg order subtotal (EUR)
- avg discount_total on the order (EUR)
- avg shipping_total (EUR)
- avg tax_total (EUR)
- avg grand_total (EUR)
- % of these orders with grand_total = 0
- % of these customers with an active subscription (subscriptions.status in ('trialing','active','past_due')) overlapping the order's placed_at date
Details: Source: order_discounts joined to discounts (filter discounts.name = 'Subscription delivery (prepaid)') and orders (subtotal, discount_total, tax_total, shipping_total, grand_total, placed_at, currency_code, user_id, tenant_id, status != 'cancelled'), tenants for name. Time window: orders.placed_at in 2025-01-01 to 2025-12-31. Convert all money columns to EUR via exchange_rates (base_code='EUR', quote_code=currency_code, as_of=placed_at date). For the subscription-overlap check, join subscriptions on user_id and tenant_id where subscriptions.started_at <= order.placed_at and (ended_at is null or ended_at >= order.placed_at). Order by order_count descending, tenant name as tie-break. Note total row count (expect ~6-10 tenants); print all.
Check your query with the warehouse's dry_run tool, then hand it in with submit.dry_runWITH disc_orders AS ( SELECT o.id AS order_id, o.tenant_id, o.user_id, o.currency_code, o.placed_at, o.subtotal, o.discount_total, o.tax_total, o.shipping_total, o.grand_total FROM order_discounts od JOIN discounts d ON d.id = od.discount_id AND d.tenant_id = od.tenant_id JOIN orders o ON o.id = od.order_id AND o.tenant_id = od.tenant_id WHERE d.name = 'Subscription delivery (prepaid)' AND o.status <> 'cancelled' AND o.placed_at >= TIMESTAMP '2025-01-01' AND o.placed_at < TIMESTAMP '2026-01-01' ), rated AS ( SELECT do.*, er.rate, do.subtotal / er.rate AS subtotal_eur, do.discount_total / er.rate AS discount_total_eur, do.tax_total / er.rate AS tax_total_eur, do.shipping_total / er.rate AS shipping_total_eur, do.grand_total / er.rate AS grand_total_eur, CASE WHEN EXISTS ( SELECT 1 FROM subscriptions s WHERE s.tenant_id = do.tenant_id AND s.user_id = do.user_id AND s.status IN ('trialing','active','past_due') AND s.started_at <= do.placed_at AND (s.ended_at IS NULL OR s.ended_at >= do.placed_at) ) THEN 1 ELSE 0 END AS has_active_sub FROM disc_orders do JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = do.currency_code AND er.as_of = CAST(do.placed_at AS date) ) SELECT t.name AS tenant_name, COUNT(*) AS order_count, COUNT(DISTINCT r.user_id) AS distinct_customers, ROUND(AVG(r.subtotal_eur),2) AS avg_subtotal_eur, ROUND(AVG(r.discount_total_eur),2) AS avg_discount_total_eur, ROUND(AVG(r.shipping_total_eur),2) AS avg_shipping_total_eur, ROUND(AVG(r.tax_total_eur),2) AS avg_tax_total_eur, ROUND(AVG(r.grand_total_eur),2) AS avg_grand_total_eur, ROUND(100.0 * SUM(CASE WHEN r.grand_total = 0 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_zero_grand_total, ROUND(100.0 * SUM(r.has_active_sub) / COUNT(*), 1) AS pct_orders_with_active_subscription FROM rated r JOIN tenants t ON t.id = r.tenant_id GROUP BY t.name ORDER BY order_count DESC, tenant_name ASC
STATUS: REJECTED - not a single SELECT statement: syntax error at or near "do"
dry_runWITH disc_orders AS ( SELECT o.id AS order_id, o.tenant_id, o.user_id, o.currency_code, o.placed_at, o.subtotal, o.discount_total, o.tax_total, o.shipping_total, o.grand_total FROM order_discounts od JOIN discounts d ON d.id = od.discount_id AND d.tenant_id = od.tenant_id JOIN orders o ON o.id = od.order_id AND o.tenant_id = od.tenant_id WHERE d.name = 'Subscription delivery (prepaid)' AND o.status <> 'cancelled' AND o.placed_at >= TIMESTAMP '2025-01-01' AND o.placed_at < TIMESTAMP '2026-01-01' ), rated AS ( SELECT dso.*, er.rate, dso.subtotal / er.rate AS subtotal_eur, dso.discount_total / er.rate AS discount_total_eur, dso.tax_total / er.rate AS tax_total_eur, dso.shipping_total / er.rate AS shipping_total_eur, dso.grand_total / er.rate AS grand_total_eur, CASE WHEN EXISTS ( SELECT 1 FROM subscriptions s WHERE s.tenant_id = dso.tenant_id AND s.user_id = dso.user_id AND s.status IN ('trialing','active','past_due') AND s.started_at <= dso.placed_at AND (s.ended_at IS NULL OR s.ended_at >= dso.placed_at) ) THEN 1 ELSE 0 END AS has_active_sub FROM disc_orders dso JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = dso.currency_code AND er.as_of = CAST(dso.placed_at AS date) ) SELECT t.name AS tenant_name, COUNT(*) AS order_count, COUNT(DISTINCT r.user_id) AS distinct_customers, ROUND(AVG(r.subtotal_eur),2) AS avg_subtotal_eur, ROUND(AVG(r.discount_total_eur),2) AS avg_discount_total_eur, ROUND(AVG(r.shipping_total_eur),2) AS avg_shipping_total_eur, ROUND(AVG(r.tax_total_eur),2) AS avg_tax_total_eur, ROUND(AVG(r.grand_total_eur),2) AS avg_grand_total_eur, ROUND(100.0 * SUM(CASE WHEN r.grand_total = 0 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_zero_grand_total, ROUND(100.0 * SUM(r.has_active_sub) / COUNT(*), 1) AS pct_orders_with_active_subscription FROM rated r JOIN tenants t ON t.id = r.tenant_id GROUP BY t.name ORDER BY order_count DESC, tenant_name ASC
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: tenant_name, order_count, distinct_customers, avg_subtotal_eur, avg_discount_total_eur, avg_shipping_total_eur, avg_tax_total_eur, avg_grand_total_eur, pct_zero_grand_total, pct_orders_with_active_subscription
submitWITH disc_orders AS ( SELECT o.id AS order_id, o.tenant_id, o.user_id, o.currency_code, o.placed_at, o.subtotal, o.discount_total, o.tax_total, o.shipping_total, o.grand_total FROM order_discounts od JOIN discounts d ON d.id = od.discount_id AND d.tenant_id = od.tenant_id JOIN orders o ON o.id = od.order_id AND o.tenant_id = od.tenant_id WHERE d.name = 'Subscription delivery (prepaid)' AND o.status <> 'cancelled' AND o.placed_at >= TIMESTAMP '2025-01-01' AND o.placed_at < TIMESTAMP '2026-01-01' ), rated AS ( SELECT dso.*, er.rate, dso.subtotal / er.rate AS subtotal_eur, dso.discount_total / er.rate AS discount_total_eur, dso.tax_total / er.rate AS tax_total_eur, dso.shipping_total / er.rate AS shipping_total_eur, dso.grand_total / er.rate AS grand_total_eur, CASE WHEN EXISTS ( SELECT 1 FROM subscriptions s WHERE s.tenant_id = dso.tenant_id AND s.user_id = dso.user_id AND s.status IN ('trialing','active','past_due') AND s.started_at <= dso.placed_at AND (s.ended_at IS NULL OR s.ended_at >= dso.placed_at) ) THEN 1 ELSE 0 END AS has_active_sub FROM disc_orders dso JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = dso.currency_code AND er.as_of = CAST(dso.placed_at AS date) ) SELECT t.name AS tenant_name, COUNT(*) AS order_count, COUNT(DISTINCT r.user_id) AS distinct_customers, ROUND(AVG(r.subtotal_eur),2) AS avg_subtotal_eur, ROUND(AVG(r.discount_total_eur),2) AS avg_discount_total_eur, ROUND(AVG(r.shipping_total_eur),2) AS avg_shipping_total_eur, ROUND(AVG(r.tax_total_eur),2) AS avg_tax_total_eur, ROUND(AVG(r.grand_total_eur),2) AS avg_grand_total_eur, ROUND(100.0 * SUM(CASE WHEN r.grand_total = 0 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_zero_grand_total, ROUND(100.0 * SUM(r.has_active_sub) / COUNT(*), 1) AS pct_orders_with_active_subscription FROM rated r JOIN tenants t ON t.id = r.tenant_id GROUP BY t.name ORDER BY order_count DESC, tenant_name ASC
Submitted to tonight's batch. Your work on this request is done.