Queries · q155 · Head of Pricing and Promotions · week 1, night 3

Corrected incrementality read: pre-discount basket size on top marketing discounts, 2025

“Using basket size before the discount is applied (subtotal), do customers actually buy bigger baskets when using our top marketing discount codes, or was the earlier post-discount comparison just showing the mechanical effect of the discount itself?”

Manager · asked the questionMarco BianchiHead of Pricing and Promotions. Previously in revenue management at a travel company.

Analyst · planned the reportOliver GrantPeer-minded: compares each merchant with peers of similar size and vertical.

Engineer · wrote the SQLFelixJunior engineer who nests subqueries in FROM and WHERE (IN, EXISTS, correlated subqueries) rather than CTEs.

Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told

A business event lands on the Head of Pricing and Promotions's desk
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?
One of the 35 events we wrote for the role play; it shaped the manager's first note. How events arise · All the events
Marco Bianchi writes to Oliver Grant
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:
1. Which promotions and coupons are costing us the most, and is that cost buying us anything? I want to see discount cost alongside the orders/revenue they sit on, so we can tell "big spend, big lift" apart from "big spend, no lift."
2. For our top discounts, do customers actually spend more because of them, or are we just discounting purchases that would have happened anyway? I know this is the hard one — I just want our best honest read on it, even if approximate.
3. Are any coupons being overused or abused — the same customers redeeming repeatedly, usage patterns that look like stacking or sharing of codes meant to be one-time or limited, or codes that are far outliving the campaign they were meant for?
4. Which discounts are quietly eating into margin on top of their price reduction — for example cases where returns or refunds cluster around discounted orders, so the real cost is higher than the discount line alone suggests.
5. (If there's a slot left) How does this differ by merchant or vertical — are one or two tenants responsible for most of the discount spend, which would change how we approach this.
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
A note from the Chief Financial Officer, Tobias Lindqvist
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:
1. Are discounted orders actually incremental, or just margin we're giving away on sales that would have happened anyway? I care less about redemption volume and more about whether promotions are growing net revenue after the discount, versus simply lowering what we collect on existing demand.
2. Is any of this exposed as cost we haven't fully absorbed yet? Where coupons stack with refunds or returns, I want to understand whether we're left carrying a loss after the fact, especially heading into year-end close.
3. Is there any pattern of abuse or concentration — the same accounts or a narrow set of merchants repeatedly running the heaviest discounts — that's quietly eating into margin rather than driving genuine growth?
Keep it tight; I mainly need the "so what" for the annual accounts and the 2026 budget assumptions on discount cost.

Thanks, Tobias
Oliver Grant plans the night
The analyst · week 1, night 3, request 2 of 3
Night 2 sharpened three things:
1. Report 1 confirmed 'Subscription delivery (prepaid)' orders are 100%-discounted (subtotal = discount_total exactly, shipping/tax = 0) — so it's not a data artifact, it's a deliberate full comp. But only 54-100% of these free orders overlap an active subscription (as low as 53.8% at Feather & Fin, 60.5% at Saffron & Sage). That means a meaningful share of "free delivery" orders have no active subscription on record at the time — either lapsed subscribers still getting shipped, or a real leakage pattern. This is squarely Tobias's "cost we haven't fully absorbed" question and needs a direct look before I call it fine.
2. Report 2 came back with zero rows: no coupon in the warehouse has usage_limit or per_customer_limit set at all. That's a clean, if surprising, answer — there is no configured cap anywhere, so "breach" isn't the right abuse framing; the right framing is simply uncapped repeat use (which night 1's report already quantified). I'm treating this as settled and won't spend another slot re-asking it.
3. Report 3's "lift" is confounded: it compares post-discount grand_total, so a percentage-off code mechanically shows a lower total even with an identical basket — that's why seasonal Sales show "negative lift" and Volume-tier discounts (which only trigger on already-large orders) show absurd 300-1000%+ "lift." I need to redo this on pre-discount subtotal (basket size) and keep structural volume-tier discounts separate from true marketing codes, or the read is misleading.
Tonight's 3 reports: (A) drill into the subscription-delivery orders with no overlapping active subscription — concentration and whether it traces to lapsed subs or true giveaways; (B) a corrected incrementality read on true marketing discounts using pre-discount subtotal; (C) the still-untouched margin question — do discounted orders return/refund at a higher rate than baseline, eating further into margin. This covers the open pieces of Marco's and Tobias's questions before Thursday's sync.
Corrected incrementality read: pre-discount basket size on top marketing discounts, 2025
Question
Using basket size before the discount is applied (subtotal), do customers actually buy bigger baskets when using our top marketing discount codes, or was the earlier post-discount comparison just showing the mechanical effect of the discount itself?
One row per
discount (discounts.id), restricted to the top marketing-type discounts by 2025 spend: 'Welcome 10% off', 'Black Friday 2025' (all coupon variants), 'January Sale 2025', 'Summer Sale 2025' — explicitly excluding 'Subscription delivery (prepaid)' and 'Volume tier 10+/50+' (those are a prepaid billing artifact and a structural large-order discount respectively, not marketing incentives)
Columns
- discount name
- coupon code (or 'automatic')
- discounted order count
- distinct customers
- avg order subtotal on discounted orders, pre-discount (EUR)
- avg order subtotal on the same customers' other non-discounted 2025 orders (EUR)
- subtotal lift (EUR)
- subtotal lift (%)
- % of these customers who placed at least one other order in 2025 with no discount applied

Felix writes the SQL

The engineer · 1 dry run on the empty schema, then the submission

Dry run 1 · STATUS: COMPILES (dry run on the empty schema, no data read)

SELECT
  d.name AS discount_name,
  COALESCE(cc.coupon_code, 'automatic') AS coupon_code,
  disc.order_count AS discounted_order_count,
  disc.customer_count AS distinct_customers,
  ROUND(disc.avg_subtotal_eur, 2) AS avg_discounted_subtotal_eur,
  ROUND(cmp.avg_subtotal_eur, 2) AS avg_comparison_subtotal_eur,
  ROUND(disc.avg_subtotal_eur - cmp.avg_subtotal_eur, 2) AS subtotal_lift_eur,
  ROUND(CASE WHEN cmp.avg_subtotal_eur IS NULL OR cmp.avg_subtotal_eur = 0 THEN NULL
             ELSE (disc.avg_subtotal_eur - cmp.avg_subtotal_eur) / cmp.avg_subtotal_eur * 100 END, 1) AS subtotal_lift_pct,
  ROUND(CASE WHEN disc.customer_count = 0 THEN NULL
             ELSE 100.0 * cmp.customers_with_comparison / disc.customer_count END, 1) AS pct_customers_with_comparison_order,
  (disc.customer_count - cmp.customers_with_comparison) AS customers_with_no_comparison_order
FROM discounts d
JOIN (
  SELECT dconv.discount_id,
         COUNT(*) AS order_count,
         COUNT(DISTINCT dconv.user_id) AS customer_count,
         AVG(dconv.subtotal_eur) AS avg_subtotal_eur
  FROM (
    SELECT DISTINCT od.discount_id, o.id AS order_id, o.user_id,
           CASE WHEN o.currency_code = 'EUR' THEN o.subtotal ELSE o.subtotal / er.rate END AS subtotal_eur
    FROM order_discounts od
    JOIN orders o ON o.id = od.order_id AND o.tenant_id = od.tenant_id
    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.status <> 'cancelled'
      AND o.placed_at >= DATE '2025-01-01'
      AND o.placed_at < DATE '2026-01-01'
      AND od.discount_id IN (
        SELECT id FROM discounts
        WHERE name IN ('Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', 'Summer Sale 2025')
      )
  ) dconv
  GROUP BY dconv.discount_id
) disc ON disc.discount_id = d.id
JOIN (
  SELECT dcust.discount_id,
         COUNT(cp.user_id) AS customers_with_comparison,
         SUM(cp.cmp_subtotal_sum) / NULLIF(SUM(cp.cmp_order_count), 0) AS avg_subtotal_eur
  FROM (
    SELECT DISTINCT od.discount_id, o.tenant_id, o.user_id
    FROM order_discounts od
    JOIN orders o ON o.id = od.order_id AND o.tenant_id = od.tenant_id
    WHERE o.status <> 'cancelled'
      AND o.placed_at >= DATE '2025-01-01'
      AND o.placed_at < DATE '2026-01-01'
      AND od.discount_id IN (
        SELECT id FROM discounts
        WHERE name IN ('Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', 'Summer Sale 2025')
      )
  ) dcust
  LEFT JOIN (
    SELECT o2.tenant_id, o2.user_id,
           COUNT(*) AS cmp_order_count,
           SUM(CASE WHEN o2.currency_code = 'EUR' THEN o2.subtotal ELSE o2.subtotal / er2.rate END) AS cmp_subtotal_sum
    FROM orders o2
    LEFT JOIN exchange_rates er2 ON er2.base_code = 'EUR'
           AND er2.quote_code = o2.currency_code
           AND er2.as_of = CAST(o2.placed_at AS date)
    WHERE o2.status <> 'cancelled'
      AND o2.placed_at >= DATE '2025-01-01'
      AND o2.placed_at < DATE '2026-01-01'
      AND NOT EXISTS (
        SELECT 1 FROM order_discounts od2 WHERE od2.order_id = o2.id
      )
    GROUP BY o2.tenant_id, o2.user_id
  ) cp ON cp.tenant_id = dcust.tenant_id AND cp.user_id = dcust.user_id
  GROUP BY dcust.discount_id
) cmp ON cmp.discount_id = d.id
LEFT JOIN (
  SELECT discount_id, STRING_AGG(DISTINCT code, ', ') AS coupon_code
  FROM order_discounts
  WHERE code IS NOT NULL
  GROUP BY discount_id
) cc ON cc.discount_id = d.id
WHERE d.name IN ('Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', 'Summer Sale 2025')
ORDER BY subtotal_lift_pct ASC, discount_name ASC, d.id ASC

“Query compiles cleanly with the expected 10 columns. Submitting.”

Submitted · the SQL of the last dry run, unchanged · Submitted to tonight's batch. Your work on this request is done.

“Submitted. Summary of the approach: - Grain: one row per discounts.id, filtered to name IN ('Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', 'Summer Sale 2025') — this naturally picks up every tenant/coupon-variant row under those names while excluding the two discounts the analyst called out. - Discounted side: pre-discount basket size = orders.subtotal (not grand_total), converted to EUR via exchange_rates, averaged over distinct non-cancelled 2025 orders carrying that discount. - Comparison side: for the same (tenant, customer) pairs, pooled average subtotal (EUR) over their other 2025 non-cancelled orders that have zero rows in order_discounts at all (truly undiscounted, not just missing this specific discount). - Lift computed in EUR and %, plus the share of customers who had any such undiscounted order, and a raw count of customers with no comparison order available at all. - Noted in the submit message that the "top 15 by spend" list from the prior report couldn't be re-verified since dry runs don't expose data — I relied on the explicit name list instead, which should match given the exclusions were already spelled out.”

The report comes back overnight

As Oliver Grant saw it, from the small dataset the agents worked on

discount_name     | coupon_code | discounted_order_count | distinct_customers | avg_discounted_subtotal_eur | avg_comparison_subtotal_eur | subtotal_lift_eur | subtotal_lift_pct | pct_customers_with_comparison_order | customers_with_no_comparison_order
------------------+-------------+------------------------+--------------------+-----------------------------+-----------------------------+-------------------+-------------------+-------------------------------------+-----------------------------------
January Sale 2025 | automatic   |                      1 |                  1 |                         7.5 |                      149.02 |           -141.52 |             -95.0 |                               100.0 |                                  0
Black Friday 2025 | BF2520      |                      2 |                  2 |                      203.52 |                     3433.38 |          -3229.86 |             -94.1 |                                50.0 |                                  1
Black Friday 2025 | BF2515      |                      1 |                  1 |                       64.36 |                      627.23 |           -562.87 |             -89.7 |                               100.0 |                                  0
Summer Sale 2025  | automatic   |                      1 |                  1 |                       15.25 |                      103.72 |            -88.47 |             -85.3 |                               100.0 |                                  0
Welcome 10% off   | WELCOME10   |                      1 |                  1 |                        6.24 |                       37.42 |            -31.18 |             -83.3 |                               100.0 |                                  0
Welcome 10% off   | WELCOME10   |                      4 |                  4 |                       11.66 |                       54.79 |            -43.14 |             -78.7 |                               100.0 |                                  0
Welcome 10% off   | WELCOME10   |                      2 |                  2 |                       78.48 |                      340.65 |           -262.17 |             -77.0 |                                50.0 |                                  1
Welcome 10% off   | WELCOME10   |                      6 |                  6 |                       68.12 |                      251.57 |           -183.45 |             -72.9 |                                66.7 |                                  2
Summer Sale 2025  | automatic   |                      4 |                  4 |                       93.57 |                      305.02 |           -211.45 |             -69.3 |                               100.0 |                                  0
January Sale 2025 | automatic   |                      2 |                  2 |                       45.44 |                      141.37 |            -95.93 |             -67.9 |                               100.0 |                                  0
January Sale 2025 | automatic   |                     22 |                 21 |                      366.38 |                     1114.62 |           -748.24 |             -67.1 |                                57.1 |                                  9
Black Friday 2025 | BF2530      |                      4 |                  3 |                       32.21 |                       84.69 |            -52.48 |             -62.0 |                               100.0 |                                  0
Summer Sale 2025  | automatic   |                      1 |                  1 |                        6.46 |                       16.73 |            -10.27 |             -61.4 |                               100.0 |                                  0
Black Friday 2025 | BF2515      |                      5 |                  5 |                       89.97 |                      219.26 |           -129.29 |             -59.0 |                                60.0 |                                  2
Black Friday 2025 | BF2515      |                      2 |                  2 |                       21.64 |                        51.9 |            -30.26 |             -58.3 |                               100.0 |                                  0
Welcome 10% off   | WELCOME10   |                      3 |                  3 |                      200.97 |                      462.74 |           -261.77 |             -56.6 |                                66.7 |                                  1
Welcome 10% off   | WELCOME10   |                      2 |                  2 |                       14.71 |                       32.26 |            -17.55 |             -54.4 |                               100.0 |                                  0
Welcome 10% off   | WELCOME10   |                      3 |                  3 |                      452.34 |                      962.16 |           -509.82 |             -53.0 |                               100.0 |                                  0
Welcome 10% off   | WELCOME10   |                      2 |                  2 |                      326.18 |                      692.15 |           -365.97 |             -52.9 |                                50.0 |                                  1
Summer Sale 2025  | automatic   |                      2 |                  2 |                       74.72 |                      157.55 |            -82.83 |             -52.6 |                               100.0 |                                  0
Summer Sale 2025  | automatic   |                      5 |                  3 |                       77.62 |                      162.57 |            -84.95 |             -52.3 |                               100.0 |                                  0
January Sale 2025 | automatic   |                      3 |                  2 |                       117.6 |                      232.44 |           -114.84 |             -49.4 |                                50.0 |                                  1
Black Friday 2025 | BF2520      |                      6 |                  5 |                      197.71 |                      360.44 |           -162.74 |             -45.1 |                                60.0 |                                  2
Welcome 10% off   | WELCOME10   |                      2 |                  2 |                       28.67 |                       52.06 |             -23.4 |             -44.9 |                                50.0 |                                  1
Welcome 10% off   | WELCOME10   |                      5 |                  5 |                       59.52 |                      107.76 |            -48.24 |             -44.8 |                                60.0 |                                  2
Summer Sale 2025  | automatic   |                      8 |                  8 |                      156.01 |                      271.78 |           -115.76 |             -42.6 |                                87.5 |                                  1
Welcome 10% off   | WELCOME10   |                      8 |                  8 |                       76.83 |                       128.7 |            -51.87 |             -40.3 |                                37.5 |                                  5
Black Friday 2025 | BF2515      |                      8 |                  8 |                       78.28 |                       129.0 |            -50.71 |             -39.3 |                                62.5 |                                  3
Black Friday 2025 | BF2525      |                      6 |                  6 |                      141.02 |                      228.03 |            -87.01 |             -38.2 |                               100.0 |                                  0
Welcome 10% off   | WELCOME10   |                      3 |                  3 |                        57.8 |                       93.18 |            -35.38 |             -38.0 |                               100.0 |                                  0
Welcome 10% off   | WELCOME10   |                     31 |                 31 |                      376.32 |                      602.82 |            -226.5 |             -37.6 |                                25.8 |                                 23
Welcome 10% off   | WELCOME10   |                      8 |                  8 |                       92.06 |                      142.61 |            -50.56 |             -35.5 |                                37.5 |                                  5
Welcome 10% off   | WELCOME10   |                      5 |                  5 |                      434.97 |                      666.93 |           -231.96 |             -34.8 |                                20.0 |                                  4
January Sale 2025 | automatic   |                      3 |                  3 |                      128.21 |                      194.96 |            -66.75 |             -34.2 |                               100.0 |                                  0
Black Friday 2025 | BF2520      |                      3 |                  3 |                      123.27 |                       177.7 |            -54.43 |             -30.6 |                                66.7 |                                  1
Welcome 10% off   | WELCOME10   |                      1 |                  1 |                       92.49 |                      131.95 |            -39.46 |             -29.9 |                               100.0 |                                  0
January Sale 2025 | automatic   |                      8 |                  7 |                      135.92 |                      193.46 |            -57.54 |             -29.7 |                                85.7 |                                  1
Black Friday 2025 | BF2530      |                      3 |                  3 |                       64.11 |                       90.81 |             -26.7 |             -29.4 |                                66.7 |                                  1
Black Friday 2025 | BF2530      |                      8 |                  6 |                       43.89 |                       61.02 |            -17.12 |             -28.1 |                               100.0 |                                  0
Summer Sale 2025  | automatic   |                      5 |                  4 |                       101.5 |                       138.5 |            -36.99 |             -26.7 |                                75.0 |                                  1
Black Friday 2025 | BF2525      |                      7 |                  5 |                       72.44 |                       98.63 |            -26.19 |             -26.6 |                               100.0 |                                  0
Summer Sale 2025  | automatic   |                      8 |                  7 |                      144.85 |                      195.13 |            -50.28 |             -25.8 |                                57.1 |                                  3
January Sale 2025 | automatic   |                      2 |                  2 |                       49.33 |                        66.3 |            -16.97 |             -25.6 |                               100.0 |                                  0
Welcome 10% off   | WELCOME10   |                     15 |                 15 |                       77.33 |                      103.64 |            -26.31 |             -25.4 |                                46.7 |                                  8
Welcome 10% off   | WELCOME10   |                      3 |                  3 |                       36.71 |                       47.41 |            -10.71 |             -22.6 |                                66.7 |                                  1
Welcome 10% off   | WELCOME10   |                      3 |                  3 |                      192.79 |                      248.85 |            -56.06 |             -22.5 |                                66.7 |                                  1
Black Friday 2025 | BF2525      |                      2 |                  2 |                       77.49 |                       99.71 |            -22.22 |             -22.3 |                               100.0 |                                  0
Welcome 10% off   | WELCOME10   |                      4 |                  4 |                      130.91 |                      165.52 |            -34.62 |             -20.9 |                                50.0 |                                  2
Black Friday 2025 | BF2515      |                      1 |                  1 |                      157.81 |                      198.75 |            -40.94 |             -20.6 |                               100.0 |                                  0
Black Friday 2025 | BF2525      |                      2 |                  2 |                      203.36 |                      243.75 |             -40.4 |             -16.6 |                               100.0 |                                  0
(219 rows, first 50 printed)

What came of it
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
- q158Aggregate within-customer basket-size lift by promotion family, 2025
It builds on:
- q153Within-customer order value lift on top discount codes, 2025

It becomes benchmark query q155

Changed only where the benchmark needs the same result on every system

determinismorder the aggregated codes
numericsdocs/NUMERICS.md: rounding removed; results keep full precision; multiplied before the division, so an engine that keeps few decimals in the quotient does not scale its rounding up (it tied two rows of the sort key at SF10)
numericsdocs/NUMERICS.md: x / exchange rate cast to NUMERIC(18,6) (2×)
made explicita selected t.col gets AS col, its standard name made explicit (9×)
placeholderstring agg
determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (3×)

The benchmark text, against the submitted SQL

SELECT  d.name AS discount_name,  COALESCE(cc.coupon_code, 'automatic') AS coupon_code,  disc.order_count AS discounted_order_count,  disc.customer_count AS distinct_customers,  ROUND(disc.avg_subtotal_eur, 2) AS avg_discounted_subtotal_eur,  ROUND(cmp.avg_subtotal_eur, 2) AS avg_comparison_subtotal_eur,  ROUND(disc.avg_subtotal_eur - cmp.avg_subtotal_eur, 2) AS subtotal_lift_eur,  ROUND(CASE WHEN cmp.avg_subtotal_eur IS NULL OR cmp.avg_subtotal_eur = 0 THEN NULL             ELSE (disc.avg_subtotal_eur - cmp.avg_subtotal_eur) / cmp.avg_subtotal_eur * 100 END, 1) AS subtotal_lift_pct,  ROUND(CASE WHEN disc.customer_count = 0 THEN NULL             ELSE 100.0 * cmp.customers_with_comparison / disc.customer_count END, 1) AS pct_customers_with_comparison_order,  disc.avg_subtotal_eur AS avg_discounted_subtotal_eur,  cmp.avg_subtotal_eur AS avg_comparison_subtotal_eur,  disc.avg_subtotal_eur - cmp.avg_subtotal_eur AS subtotal_lift_eur,  CASE WHEN cmp.avg_subtotal_eur IS NULL OR cmp.avg_subtotal_eur = 0 THEN NULL       ELSE (disc.avg_subtotal_eur - cmp.avg_subtotal_eur) * 100 / cmp.avg_subtotal_eur END AS subtotal_lift_pct,  CASE WHEN disc.customer_count = 0 THEN NULL       ELSE 100.0 * cmp.customers_with_comparison / disc.customer_count END AS pct_customers_with_comparison_order,  (disc.customer_count - cmp.customers_with_comparison) AS customers_with_no_comparison_orderFROM discounts dJOIN (  SELECT dconv.discount_id,  SELECT dconv.discount_id AS discount_id,         COUNT(*) AS order_count,         COUNT(DISTINCT dconv.user_id) AS customer_count,         AVG(dconv.subtotal_eur) AS avg_subtotal_eur  FROM (    SELECT DISTINCT od.discount_id, o.id AS order_id, o.user_id,           CASE WHEN o.currency_code = 'EUR' THEN o.subtotal ELSE o.subtotal / er.rate END AS subtotal_eur    SELECT DISTINCT od.discount_id AS discount_id, o.id AS order_id, o.user_id AS user_id,           CASE WHEN o.currency_code = 'EUR' THEN o.subtotal ELSE CAST(o.subtotal / er.rate AS NUMERIC(18,6)) END AS subtotal_eur    FROM order_discounts od    JOIN orders o ON o.id = od.order_id AND o.tenant_id = od.tenant_id    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.status <> 'cancelled'      AND o.placed_at >= DATE '2025-01-01'      AND o.placed_at < DATE '2026-01-01'      AND od.discount_id IN (        SELECT id FROM discounts        WHERE name IN ('Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', 'Summer Sale 2025')      )  ) dconv  GROUP BY dconv.discount_id) disc ON disc.discount_id = d.idJOIN (  SELECT dcust.discount_id,  SELECT dcust.discount_id AS discount_id,         COUNT(cp.user_id) AS customers_with_comparison,         SUM(cp.cmp_subtotal_sum) / NULLIF(SUM(cp.cmp_order_count), 0) AS avg_subtotal_eur  FROM (    SELECT DISTINCT od.discount_id, o.tenant_id, o.user_id    SELECT DISTINCT od.discount_id AS discount_id, o.tenant_id AS tenant_id, o.user_id AS user_id    FROM order_discounts od    JOIN orders o ON o.id = od.order_id AND o.tenant_id = od.tenant_id    WHERE o.status <> 'cancelled'      AND o.placed_at >= DATE '2025-01-01'      AND o.placed_at < DATE '2026-01-01'      AND od.discount_id IN (        SELECT id FROM discounts        WHERE name IN ('Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', 'Summer Sale 2025')      )  ) dcust  LEFT JOIN (    SELECT o2.tenant_id, o2.user_id,    SELECT o2.tenant_id AS tenant_id, o2.user_id AS user_id,           COUNT(*) AS cmp_order_count,           SUM(CASE WHEN o2.currency_code = 'EUR' THEN o2.subtotal ELSE o2.subtotal / er2.rate END) AS cmp_subtotal_sum           SUM(CASE WHEN o2.currency_code = 'EUR' THEN o2.subtotal ELSE CAST(o2.subtotal / er2.rate AS NUMERIC(18,6)) END) AS cmp_subtotal_sum    FROM orders o2    LEFT JOIN exchange_rates er2 ON er2.base_code = 'EUR'           AND er2.quote_code = o2.currency_code           AND er2.as_of = CAST(o2.placed_at AS date)    WHERE o2.status <> 'cancelled'      AND o2.placed_at >= DATE '2025-01-01'      AND o2.placed_at < DATE '2026-01-01'      AND NOT EXISTS (        SELECT 1 FROM order_discounts od2 WHERE od2.order_id = o2.id      )    GROUP BY o2.tenant_id, o2.user_id  ) cp ON cp.tenant_id = dcust.tenant_id AND cp.user_id = dcust.user_id  GROUP BY dcust.discount_id) cmp ON cmp.discount_id = d.idLEFT JOIN (  SELECT discount_id, STRING_AGG(DISTINCT code, ', ') AS coupon_code  SELECT discount_id, {{string_agg_distinct_sorted(code, ', ')}} AS coupon_code  FROM order_discounts  WHERE code IS NOT NULL  GROUP BY discount_id) cc ON cc.discount_id = d.idWHERE d.name IN ('Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', 'Summer Sale 2025')ORDER BY subtotal_lift_pct ASC, discount_name ASC, d.id ASCORDER BY subtotal_lift_pct ASC NULLS LAST, discount_name ASC NULLS LAST, d.id ASC NULLS LAST

As each system received it; highlighted, the parts spelled for that system

SELECT
  d.name AS discount_name,
  COALESCE(cc.coupon_code, 'automatic') AS coupon_code,
  disc.order_count AS discounted_order_count,
  disc.customer_count AS distinct_customers,
  disc.avg_subtotal_eur AS avg_discounted_subtotal_eur,
  cmp.avg_subtotal_eur AS avg_comparison_subtotal_eur,
  disc.avg_subtotal_eur - cmp.avg_subtotal_eur AS subtotal_lift_eur,
  CASE WHEN cmp.avg_subtotal_eur IS NULL OR cmp.avg_subtotal_eur = 0 THEN NULL
       ELSE (disc.avg_subtotal_eur - cmp.avg_subtotal_eur) * 100 / cmp.avg_subtotal_eur END AS subtotal_lift_pct,
  CASE WHEN disc.customer_count = 0 THEN NULL
       ELSE 100.0 * cmp.customers_with_comparison / disc.customer_count END AS pct_customers_with_comparison_order,
  (disc.customer_count - cmp.customers_with_comparison) AS customers_with_no_comparison_order
FROM discounts d
JOIN (
  SELECT dconv.discount_id AS discount_id,
         COUNT(*) AS order_count,
         COUNT(DISTINCT dconv.user_id) AS customer_count,
         AVG(dconv.subtotal_eur) AS avg_subtotal_eur
  FROM (
    SELECT DISTINCT od.discount_id AS discount_id, o.id AS order_id, o.user_id AS user_id,
           CASE WHEN o.currency_code = 'EUR' THEN o.subtotal ELSE CAST(o.subtotal / er.rate AS NUMERIC(18,6)) END AS subtotal_eur
    FROM order_discounts od
    JOIN orders o ON o.id = od.order_id AND o.tenant_id = od.tenant_id
    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.status <> 'cancelled'
      AND o.placed_at >= DATE '2025-01-01'
      AND o.placed_at < DATE '2026-01-01'
      AND od.discount_id IN (
        SELECT id FROM discounts
        WHERE name IN ('Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', 'Summer Sale 2025')
      )
  ) dconv
  GROUP BY dconv.discount_id
) disc ON disc.discount_id = d.id
JOIN (
  SELECT dcust.discount_id AS discount_id,
         COUNT(cp.user_id) AS customers_with_comparison,
         SUM(cp.cmp_subtotal_sum) / NULLIF(SUM(cp.cmp_order_count), 0) AS avg_subtotal_eur
  FROM (
    SELECT DISTINCT od.discount_id AS discount_id, o.tenant_id AS tenant_id, o.user_id AS user_id
    FROM order_discounts od
    JOIN orders o ON o.id = od.order_id AND o.tenant_id = od.tenant_id
    WHERE o.status <> 'cancelled'
      AND o.placed_at >= DATE '2025-01-01'
      AND o.placed_at < DATE '2026-01-01'
      AND od.discount_id IN (
        SELECT id FROM discounts
        WHERE name IN ('Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', 'Summer Sale 2025')
      )
  ) dcust
  LEFT JOIN (
    SELECT o2.tenant_id AS tenant_id, o2.user_id AS user_id,
           COUNT(*) AS cmp_order_count,
           SUM(CASE WHEN o2.currency_code = 'EUR' THEN o2.subtotal ELSE CAST(o2.subtotal / er2.rate AS NUMERIC(18,6)) END) AS cmp_subtotal_sum
    FROM orders o2
    LEFT JOIN exchange_rates er2 ON er2.base_code = 'EUR'
           AND er2.quote_code = o2.currency_code
           AND er2.as_of = CAST(o2.placed_at AS date)
    WHERE o2.status <> 'cancelled'
      AND o2.placed_at >= DATE '2025-01-01'
      AND o2.placed_at < DATE '2026-01-01'
      AND NOT EXISTS (
        SELECT 1 FROM order_discounts od2 WHERE od2.order_id = o2.id
      )
    GROUP BY o2.tenant_id, o2.user_id
  ) cp ON cp.tenant_id = dcust.tenant_id AND cp.user_id = dcust.user_id
  GROUP BY dcust.discount_id
) cmp ON cmp.discount_id = d.id
LEFT JOIN (
  SELECT discount_id, STRING_AGG(DISTINCT code, ', ' ORDER BY code) AS coupon_code
  FROM order_discounts
  WHERE code IS NOT NULL
  GROUP BY discount_id
) cc ON cc.discount_id = d.id
WHERE d.name IN ('Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', 'Summer Sale 2025')
ORDER BY subtotal_lift_pct ASC NULLS LAST, discount_name ASC NULLS LAST, d.id ASC NULLS LAST

SELECT
  d.name AS discount_name,
  COALESCE(cc.coupon_code, 'automatic') AS coupon_code,
  disc.order_count AS discounted_order_count,
  disc.customer_count AS distinct_customers,
  disc.avg_subtotal_eur AS avg_discounted_subtotal_eur,
  cmp.avg_subtotal_eur AS avg_comparison_subtotal_eur,
  disc.avg_subtotal_eur - cmp.avg_subtotal_eur AS subtotal_lift_eur,
  CASE WHEN cmp.avg_subtotal_eur IS NULL OR cmp.avg_subtotal_eur = 0 THEN NULL
       ELSE (disc.avg_subtotal_eur - cmp.avg_subtotal_eur) * 100 / cmp.avg_subtotal_eur END AS subtotal_lift_pct,
  CASE WHEN disc.customer_count = 0 THEN NULL
       ELSE 100.0 * cmp.customers_with_comparison / disc.customer_count END AS pct_customers_with_comparison_order,
  (disc.customer_count - cmp.customers_with_comparison) AS customers_with_no_comparison_order
FROM discounts d
JOIN (
  SELECT dconv.discount_id AS discount_id,
         COUNT(*) AS order_count,
         COUNT(DISTINCT dconv.user_id) AS customer_count,
         AVG(dconv.subtotal_eur) AS avg_subtotal_eur
  FROM (
    SELECT DISTINCT od.discount_id AS discount_id, o.id AS order_id, o.user_id AS user_id,
           CASE WHEN o.currency_code = 'EUR' THEN o.subtotal ELSE CAST(o.subtotal / er.rate AS NUMERIC(18,6)) END AS subtotal_eur
    FROM order_discounts od
    JOIN orders o ON o.id = od.order_id AND o.tenant_id = od.tenant_id
    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.status <> 'cancelled'
      AND o.placed_at >= DATE '2025-01-01'
      AND o.placed_at < DATE '2026-01-01'
      AND od.discount_id IN (
        SELECT id FROM discounts
        WHERE name IN ('Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', 'Summer Sale 2025')
      )
  ) dconv
  GROUP BY dconv.discount_id
) disc ON disc.discount_id = d.id
JOIN (
  SELECT dcust.discount_id AS discount_id,
         COUNT(cp.user_id) AS customers_with_comparison,
         SUM(cp.cmp_subtotal_sum) / NULLIF(SUM(cp.cmp_order_count), 0) AS avg_subtotal_eur
  FROM (
    SELECT DISTINCT od.discount_id AS discount_id, o.tenant_id AS tenant_id, o.user_id AS user_id
    FROM order_discounts od
    JOIN orders o ON o.id = od.order_id AND o.tenant_id = od.tenant_id
    WHERE o.status <> 'cancelled'
      AND o.placed_at >= DATE '2025-01-01'
      AND o.placed_at < DATE '2026-01-01'
      AND od.discount_id IN (
        SELECT id FROM discounts
        WHERE name IN ('Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', 'Summer Sale 2025')
      )
  ) dcust
  LEFT JOIN (
    SELECT o2.tenant_id AS tenant_id, o2.user_id AS user_id,
           COUNT(*) AS cmp_order_count,
           SUM(CASE WHEN o2.currency_code = 'EUR' THEN o2.subtotal ELSE CAST(o2.subtotal / er2.rate AS NUMERIC(18,6)) END) AS cmp_subtotal_sum
    FROM orders o2
    LEFT JOIN exchange_rates er2 ON er2.base_code = 'EUR'
           AND er2.quote_code = o2.currency_code
           AND er2.as_of = CAST(o2.placed_at AS date)
    WHERE o2.status <> 'cancelled'
      AND o2.placed_at >= DATE '2025-01-01'
      AND o2.placed_at < DATE '2026-01-01'
      AND NOT EXISTS (
        SELECT 1 FROM order_discounts od2 WHERE od2.order_id = o2.id
      )
    GROUP BY o2.tenant_id, o2.user_id
  ) cp ON cp.tenant_id = dcust.tenant_id AND cp.user_id = dcust.user_id
  GROUP BY dcust.discount_id
) cmp ON cmp.discount_id = d.id
LEFT JOIN (
  SELECT discount_id, CASE WHEN COUNT(code) = 0 THEN NULL ELSE ARRAY_TO_STRING(ARRAY_SORT(ARRAY_DISTINCT(ARRAY_AGG(code))), ', ') END AS coupon_code
  FROM order_discounts
  WHERE code IS NOT NULL
  GROUP BY discount_id
) cc ON cc.discount_id = d.id
WHERE d.name IN ('Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', 'Summer Sale 2025')
ORDER BY subtotal_lift_pct ASC NULLS LAST, discount_name ASC NULLS LAST, d.id ASC NULLS LAST

SELECT
  d.name AS discount_name,
  COALESCE(cc.coupon_code, 'automatic') AS coupon_code,
  disc.order_count AS discounted_order_count,
  disc.customer_count AS distinct_customers,
  disc.avg_subtotal_eur AS avg_discounted_subtotal_eur,
  cmp.avg_subtotal_eur AS avg_comparison_subtotal_eur,
  disc.avg_subtotal_eur - cmp.avg_subtotal_eur AS subtotal_lift_eur,
  CASE WHEN cmp.avg_subtotal_eur IS NULL OR cmp.avg_subtotal_eur = 0 THEN NULL
       ELSE (disc.avg_subtotal_eur - cmp.avg_subtotal_eur) * 100 / cmp.avg_subtotal_eur END AS subtotal_lift_pct,
  CASE WHEN disc.customer_count = 0 THEN NULL
       ELSE 100.0 * cmp.customers_with_comparison / disc.customer_count END AS pct_customers_with_comparison_order,
  (disc.customer_count - cmp.customers_with_comparison) AS customers_with_no_comparison_order
FROM discounts d
JOIN (
  SELECT dconv.discount_id AS discount_id,
         COUNT(*) AS order_count,
         COUNT(DISTINCT dconv.user_id) AS customer_count,
         AVG(dconv.subtotal_eur) AS avg_subtotal_eur
  FROM (
    SELECT DISTINCT od.discount_id AS discount_id, o.id AS order_id, o.user_id AS user_id,
           CASE WHEN o.currency_code = 'EUR' THEN o.subtotal ELSE CAST(o.subtotal / er.rate AS NUMERIC(18,6)) END AS subtotal_eur
    FROM order_discounts od
    JOIN orders o ON o.id = od.order_id AND o.tenant_id = od.tenant_id
    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.status <> 'cancelled'
      AND o.placed_at >= DATE '2025-01-01'
      AND o.placed_at < DATE '2026-01-01'
      AND od.discount_id IN (
        SELECT id FROM discounts
        WHERE name IN ('Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', 'Summer Sale 2025')
      )
  ) dconv
  GROUP BY dconv.discount_id
) disc ON disc.discount_id = d.id
JOIN (
  SELECT dcust.discount_id AS discount_id,
         COUNT(cp.user_id) AS customers_with_comparison,
         SUM(cp.cmp_subtotal_sum) / NULLIF(SUM(cp.cmp_order_count), 0) AS avg_subtotal_eur
  FROM (
    SELECT DISTINCT od.discount_id AS discount_id, o.tenant_id AS tenant_id, o.user_id AS user_id
    FROM order_discounts od
    JOIN orders o ON o.id = od.order_id AND o.tenant_id = od.tenant_id
    WHERE o.status <> 'cancelled'
      AND o.placed_at >= DATE '2025-01-01'
      AND o.placed_at < DATE '2026-01-01'
      AND od.discount_id IN (
        SELECT id FROM discounts
        WHERE name IN ('Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', 'Summer Sale 2025')
      )
  ) dcust
  LEFT JOIN (
    SELECT o2.tenant_id AS tenant_id, o2.user_id AS user_id,
           COUNT(*) AS cmp_order_count,
           SUM(CASE WHEN o2.currency_code = 'EUR' THEN o2.subtotal ELSE CAST(o2.subtotal / er2.rate AS NUMERIC(18,6)) END) AS cmp_subtotal_sum
    FROM orders o2
    LEFT JOIN exchange_rates er2 ON er2.base_code = 'EUR'
           AND er2.quote_code = o2.currency_code
           AND er2.as_of = CAST(o2.placed_at AS date)
    WHERE o2.status <> 'cancelled'
      AND o2.placed_at >= DATE '2025-01-01'
      AND o2.placed_at < DATE '2026-01-01'
      AND NOT EXISTS (
        SELECT 1 FROM order_discounts od2 WHERE od2.order_id = o2.id
      )
    GROUP BY o2.tenant_id, o2.user_id
  ) cp ON cp.tenant_id = dcust.tenant_id AND cp.user_id = dcust.user_id
  GROUP BY dcust.discount_id
) cmp ON cmp.discount_id = d.id
LEFT JOIN (
  SELECT discount_id, arrayStringConcat(arraySort(groupUniqArray(code)), ', ') AS coupon_code
  FROM order_discounts
  WHERE code IS NOT NULL
  GROUP BY discount_id
) cc ON cc.discount_id = d.id
WHERE d.name IN ('Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', 'Summer Sale 2025')
ORDER BY subtotal_lift_pct ASC NULLS LAST, discount_name ASC NULLS LAST, d.id ASC NULLS LAST

Firebolt{ "errors": [ { "description": "Line 22, Column 83: Operations between decimals with different precision and scale is not supported at the moment. Explicitly cast one of the decimals to other decimal precision and scale." } ], "query": { "query_id": "838fd0ab-40c2-441e-b806-86d81267b9f7", "query_lab
ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table5.subtotal Decimal(14, 4) Decimal64(size = 0), __table6.rate Nullable(Decimal(12, 6)) Nullable(size = 0, Decimal64(size = 0), UInt8(size = 0)). (ARGUMENT_OUT_OF_BOUND)

Scale factor 1000, c8a.24xlarge, after the last load. q155 on the start page, with every configuration.

Everything, as recorded

The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted

Felix's whole session, 7 turns

The request, as Felix received it

Report request from Oliver Grant (analyst for the Head of Pricing and Promotions):

Title: Corrected incrementality read: pre-discount basket size on top marketing discounts, 2025
Question: Using basket size before the discount is applied (subtotal), do customers actually buy bigger baskets when using our top marketing discount codes, or was the earlier post-discount comparison just showing the mechanical effect of the discount itself?
Grain: one row per discount (discounts.id), restricted to the top marketing-type discounts by 2025 spend: 'Welcome 10% off', 'Black Friday 2025' (all coupon variants), 'January Sale 2025', 'Summer Sale 2025' — explicitly excluding 'Subscription delivery (prepaid)' and 'Volume tier 10+/50+' (those are a prepaid billing artifact and a structural large-order discount respectively, not marketing incentives)
Columns:
- discount name
- coupon code (or 'automatic')
- discounted order count
- distinct customers
- avg order subtotal on discounted orders, pre-discount (EUR)
- avg order subtotal on the same customers' other non-discounted 2025 orders (EUR)
- subtotal lift (EUR)
- subtotal lift (%)
- % of these customers who placed at least one other order in 2025 with no discount applied
Details: Source: order_discounts + discounts (filtered to the named discounts above) + orders (subtotal, currency_code, placed_at, status != 'cancelled', user_id, tenant_id) for the discounted side; for the comparison side, same customers' (user_id, tenant_id) other 2025 orders with no row in order_discounts, averaging orders.subtotal. Convert to EUR via exchange_rates (base_code='EUR', quote_code=currency_code, as_of=placed_at). Use the same set of up to 15 discount rows (by discount_amount_eur) identified in the prior lift report, minus Volume tier and Subscription delivery, so results are directly comparable to that earlier (flawed) pass. Order by subtotal lift (%) ascending, discount name as tie-break. Note how many customers had no comparison orders available.

Check your query with the warehouse's dry_run tool, then hand it in with submit.

Felix calls dry_run

SELECT
  d.name AS discount_name,
  COALESCE(cc.coupon_code, 'automatic') AS coupon_code,
  disc.order_count AS discounted_order_count,
  disc.customer_count AS distinct_customers,
  ROUND(disc.avg_subtotal_eur, 2) AS avg_discounted_subtotal_eur,
  ROUND(cmp.avg_subtotal_eur, 2) AS avg_comparison_subtotal_eur,
  ROUND(disc.avg_subtotal_eur - cmp.avg_subtotal_eur, 2) AS subtotal_lift_eur,
  ROUND(CASE WHEN cmp.avg_subtotal_eur IS NULL OR cmp.avg_subtotal_eur = 0 THEN NULL
             ELSE (disc.avg_subtotal_eur - cmp.avg_subtotal_eur) / cmp.avg_subtotal_eur * 100 END, 1) AS subtotal_lift_pct,
  ROUND(CASE WHEN disc.customer_count = 0 THEN NULL
             ELSE 100.0 * cmp.customers_with_comparison / disc.customer_count END, 1) AS pct_customers_with_comparison_order,
  (disc.customer_count - cmp.customers_with_comparison) AS customers_with_no_comparison_order
FROM discounts d
JOIN (
  SELECT dconv.discount_id,
         COUNT(*) AS order_count,
         COUNT(DISTINCT dconv.user_id) AS customer_count,
         AVG(dconv.subtotal_eur) AS avg_subtotal_eur
  FROM (
    SELECT DISTINCT od.discount_id, o.id AS order_id, o.user_id,
           CASE WHEN o.currency_code = 'EUR' THEN o.subtotal ELSE o.subtotal / er.rate END AS subtotal_eur
    FROM order_discounts od
    JOIN orders o ON o.id = od.order_id AND o.tenant_id = od.tenant_id
    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.status <> 'cancelled'
      AND o.placed_at >= DATE '2025-01-01'
      AND o.placed_at < DATE '2026-01-01'
      AND od.discount_id IN (
        SELECT id FROM discounts
        WHERE name IN ('Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', 'Summer Sale 2025')
      )
  ) dconv
  GROUP BY dconv.discount_id
) disc ON disc.discount_id = d.id
JOIN (
  SELECT dcust.discount_id,
         COUNT(cp.user_id) AS customers_with_comparison,
         SUM(cp.cmp_subtotal_sum) / NULLIF(SUM(cp.cmp_order_count), 0) AS avg_subtotal_eur
  FROM (
    SELECT DISTINCT od.discount_id, o.tenant_id, o.user_id
    FROM order_discounts od
    JOIN orders o ON o.id = od.order_id AND o.tenant_id = od.tenant_id
    WHERE o.status <> 'cancelled'
      AND o.placed_at >= DATE '2025-01-01'
      AND o.placed_at < DATE '2026-01-01'
      AND od.discount_id IN (
        SELECT id FROM discounts
        WHERE name IN ('Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', 'Summer Sale 2025')
      )
  ) dcust
  LEFT JOIN (
    SELECT o2.tenant_id, o2.user_id,
           COUNT(*) AS cmp_order_count,
           SUM(CASE WHEN o2.currency_code = 'EUR' THEN o2.subtotal ELSE o2.subtotal / er2.rate END) AS cmp_subtotal_sum
    FROM orders o2
    LEFT JOIN exchange_rates er2 ON er2.base_code = 'EUR'
           AND er2.quote_code = o2.currency_code
           AND er2.as_of = CAST(o2.placed_at AS date)
    WHERE o2.status <> 'cancelled'
      AND o2.placed_at >= DATE '2025-01-01'
      AND o2.placed_at < DATE '2026-01-01'
      AND NOT EXISTS (
        SELECT 1 FROM order_discounts od2 WHERE od2.order_id = o2.id
      )
    GROUP BY o2.tenant_id, o2.user_id
  ) cp ON cp.tenant_id = dcust.tenant_id AND cp.user_id = dcust.user_id
  GROUP BY dcust.discount_id
) cmp ON cmp.discount_id = d.id
LEFT JOIN (
  SELECT discount_id, STRING_AGG(DISTINCT code, ', ') AS coupon_code
  FROM order_discounts
  WHERE code IS NOT NULL
  GROUP BY discount_id
) cc ON cc.discount_id = d.id
WHERE d.name IN ('Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', 'Summer Sale 2025')
ORDER BY subtotal_lift_pct ASC, discount_name ASC, d.id ASC

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: discount_name, coupon_code, discounted_order_count, distinct_customers, avg_discounted_subtotal_eur, avg_comparison_subtotal_eur, subtotal_lift_eur, subtotal_lift_pct, pct_customers_with_comparison_order, customers_with_no_comparison_order

FelixQuery compiles cleanly with the expected 10 columns. Submitting.

Felix calls submit

SELECT
  d.name AS discount_name,
  COALESCE(cc.coupon_code, 'automatic') AS coupon_code,
  disc.order_count AS discounted_order_count,
  disc.customer_count AS distinct_customers,
  ROUND(disc.avg_subtotal_eur, 2) AS avg_discounted_subtotal_eur,
  ROUND(cmp.avg_subtotal_eur, 2) AS avg_comparison_subtotal_eur,
  ROUND(disc.avg_subtotal_eur - cmp.avg_subtotal_eur, 2) AS subtotal_lift_eur,
  ROUND(CASE WHEN cmp.avg_subtotal_eur IS NULL OR cmp.avg_subtotal_eur = 0 THEN NULL
             ELSE (disc.avg_subtotal_eur - cmp.avg_subtotal_eur) / cmp.avg_subtotal_eur * 100 END, 1) AS subtotal_lift_pct,
  ROUND(CASE WHEN disc.customer_count = 0 THEN NULL
             ELSE 100.0 * cmp.customers_with_comparison / disc.customer_count END, 1) AS pct_customers_with_comparison_order,
  (disc.customer_count - cmp.customers_with_comparison) AS customers_with_no_comparison_order
FROM discounts d
JOIN (
  SELECT dconv.discount_id,
         COUNT(*) AS order_count,
         COUNT(DISTINCT dconv.user_id) AS customer_count,
         AVG(dconv.subtotal_eur) AS avg_subtotal_eur
  FROM (
    SELECT DISTINCT od.discount_id, o.id AS order_id, o.user_id,
           CASE WHEN o.currency_code = 'EUR' THEN o.subtotal ELSE o.subtotal / er.rate END AS subtotal_eur
    FROM order_discounts od
    JOIN orders o ON o.id = od.order_id AND o.tenant_id = od.tenant_id
    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.status <> 'cancelled'
      AND o.placed_at >= DATE '2025-01-01'
      AND o.placed_at < DATE '2026-01-01'
      AND od.discount_id IN (
        SELECT id FROM discounts
        WHERE name IN ('Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', 'Summer Sale 2025')
      )
  ) dconv
  GROUP BY dconv.discount_id
) disc ON disc.discount_id = d.id
JOIN (
  SELECT dcust.discount_id,
         COUNT(cp.user_id) AS customers_with_comparison,
         SUM(cp.cmp_subtotal_sum) / NULLIF(SUM(cp.cmp_order_count), 0) AS avg_subtotal_eur
  FROM (
    SELECT DISTINCT od.discount_id, o.tenant_id, o.user_id
    FROM order_discounts od
    JOIN orders o ON o.id = od.order_id AND o.tenant_id = od.tenant_id
    WHERE o.status <> 'cancelled'
      AND o.placed_at >= DATE '2025-01-01'
      AND o.placed_at < DATE '2026-01-01'
      AND od.discount_id IN (
        SELECT id FROM discounts
        WHERE name IN ('Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', 'Summer Sale 2025')
      )
  ) dcust
  LEFT JOIN (
    SELECT o2.tenant_id, o2.user_id,
           COUNT(*) AS cmp_order_count,
           SUM(CASE WHEN o2.currency_code = 'EUR' THEN o2.subtotal ELSE o2.subtotal / er2.rate END) AS cmp_subtotal_sum
    FROM orders o2
    LEFT JOIN exchange_rates er2 ON er2.base_code = 'EUR'
           AND er2.quote_code = o2.currency_code
           AND er2.as_of = CAST(o2.placed_at AS date)
    WHERE o2.status <> 'cancelled'
      AND o2.placed_at >= DATE '2025-01-01'
      AND o2.placed_at < DATE '2026-01-01'
      AND NOT EXISTS (
        SELECT 1 FROM order_discounts od2 WHERE od2.order_id = o2.id
      )
    GROUP BY o2.tenant_id, o2.user_id
  ) cp ON cp.tenant_id = dcust.tenant_id AND cp.user_id = dcust.user_id
  GROUP BY dcust.discount_id
) cmp ON cmp.discount_id = d.id
LEFT JOIN (
  SELECT discount_id, STRING_AGG(DISTINCT code, ', ') AS coupon_code
  FROM order_discounts
  WHERE code IS NOT NULL
  GROUP BY discount_id
) cc ON cc.discount_id = d.id
WHERE d.name IN ('Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', 'Summer Sale 2025')
ORDER BY subtotal_lift_pct ASC, discount_name ASC, d.id ASC

The warehouse answers

Submitted to tonight's batch. Your work on this request is done.

FelixSubmitted. Summary of the approach: - **Grain**: one row per `discounts.id`, filtered to `name IN ('Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', 'Summer Sale 2025')` — this naturally picks up every tenant/coupon-variant row under those names while excluding the two discounts the analyst called out. - **Discounted side**: pre-discount basket size = `orders.subtotal` (not grand_total), converted to EUR via `exchange_rates`, averaged over distinct non-cancelled 2025 orders carrying that discount. - **Comparison side**: for the same (tenant, customer) pairs, pooled average subtotal (EUR) over their *other* 2025 non-cancelled orders that have zero rows in `order_discounts` at all (truly undiscounted, not just missing this specific discount). - **Lift** computed in EUR and %, plus the share of customers who had any such undiscounted order, and a raw count of customers with no comparison order available at all. - Noted in the submit message that the "top 15 by spend" list from the prior report couldn't be re-verified since dry runs don't expose data — I relied on the explicit name list instead, which should match given the exclusions were already spelled out.

Not shown here: 240 KB of context the agent was given, its instructions and the schema documentation; it is in the raw transcript.

Felix's transcript (321 KB) · Oliver Grant's transcript (972 KB) · Marco Bianchi's transcript (259 KB) · the night's plan (8 KB) · the night's reports (20 KB) · the week's memo (3 KB)

Corrected incrementality read: pre-discount basket size on top marketing discounts, 2025

A business event lands on the Head of Pricing and Promotions's desk

Marco Bianchi writes to Oliver Grant

Oliver Grant plans the night

Felix writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q155

Everything, as recorded