DB Olympics

Queries · q153 · Head of Pricing and Promotions · week 1, night 2

Within-customer order value lift on top discount codes, 2025

“For our top discounts, do the same customers spend more per order when using the discount than they do on their own other, non-discounted orders — our best approximate read on incrementality?”

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 SQLBeaWrites SQL the way the company's BI tool generates it: verbose fully qualified aliases (t0, t1, ...), a subquery per measure joined back on the grain keys, COALESCE everywhere.

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

  1. 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

  2. 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

  3. Oliver Grant plans the night

    The analyst · week 1, night 2, request 3 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.

    Within-customer order value lift on top discount codes, 2025
    Question
    For our top discounts, do the same customers spend more per order when using the discount than they do on their own other, non-discounted orders — our best approximate read on incrementality?
    One row per
    discount (discounts.id), restricted to the top discounts by 2025 discount spend excluding 'Subscription delivery (prepaid)'
    Columns
    • discount name
    • coupon code (or 'automatic')
    • order count with this discount
    • distinct customers using this discount
    • avg order value on discounted orders (grand_total, EUR)
    • avg order value on the same customers' other non-discounted orders in 2025 (EUR)
    • lift (EUR, discounted minus other)
    • lift (%)
    • % of these customers who placed at least one other order in 2025 with no discount applied

  4. Bea 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)
    WITH discount_order_lines AS (
    SELECT
        t1.id AS order_discount_id,
        t1.tenant_id AS tenant_id,
        t1.order_id AS order_id,
        t1.discount_id AS discount_id,
        t1.code AS coupon_code,
        t1.amount AS discount_amount,
        t1.currency_code AS discount_currency_code,
        t2.user_id AS user_id,
        t2.placed_at AS placed_at,
        t2.currency_code AS order_currency_code,
        t2.grand_total AS order_grand_total,
        t3.name AS discount_name
    FROM order_discounts t1
    JOIN orders t2
        ON t2.id = t1.order_id
       AND t2.tenant_id = t1.tenant_id
    JOIN discounts t3
        ON t3.id = t1.discount_id
       AND t3.tenant_id = t1.tenant_id
    WHERE t2.status <> 'cancelled'
      AND t2.placed_at >= DATE '2025-01-01'
      AND t2.placed_at < DATE '2026-01-01'
      AND t3.name <> 'Subscription delivery (prepaid)'
),
discount_order_lines_eur AS (
    SELECT
        t4.*,
        t4.discount_amount / NULLIF(t5.rate, 0) AS discount_amount_eur,
        t4.order_grand_total / NULLIF(t6.rate, 0) AS order_grand_total_eur
    FROM discount_order_lines t4
    LEFT JOIN exchange_rates t5
        ON t5.base_code = 'EUR'
       AND t5.quote_code = t4.discount_currency_code
       AND t5.as_of = CAST(t4.placed_at AS DATE)
    LEFT JOIN exchange_rates t6
        ON t6.base_code = 'EUR'
       AND t6.quote_code = t4.order_currency_code
       AND t6.as_of = CAST(t4.placed_at AS DATE)
),
discounted_orders_distinct AS (
    SELECT DISTINCT
        discount_id,
        discount_name,
        tenant_id,
        order_id,
        user_id,
        order_grand_total_eur
    FROM discount_order_lines_eur
),
discount_spend AS (
    SELECT
        discount_id,
        discount_name,
        SUM(discount_amount_eur) AS total_discount_spend_eur
    FROM discount_order_lines_eur
    GROUP BY discount_id, discount_name
),
top_discounts AS (
    SELECT discount_id, discount_name, total_discount_spend_eur
    FROM discount_spend
    ORDER BY total_discount_spend_eur DESC
    LIMIT 15
),
discount_order_count AS (
    SELECT discount_id, COUNT(DISTINCT order_id) AS order_count
    FROM discounted_orders_distinct
    GROUP BY discount_id
),
discount_customer_count AS (
    SELECT discount_id, COUNT(DISTINCT user_id) AS distinct_customers
    FROM discounted_orders_distinct
    GROUP BY discount_id
),
discount_avg_value AS (
    SELECT discount_id, AVG(order_grand_total_eur) AS avg_discounted_order_value_eur
    FROM discounted_orders_distinct
    GROUP BY discount_id
),
discount_coupon_code AS (
    SELECT discount_id, MAX(coupon_code) AS coupon_code
    FROM discount_order_lines_eur
    GROUP BY discount_id
),
customer_discount_pairs AS (
    SELECT DISTINCT discount_id, tenant_id, user_id
    FROM discounted_orders_distinct
    WHERE discount_id IN (SELECT discount_id FROM top_discounts)
),
non_discounted_orders AS (
    SELECT
        t7.id AS order_id,
        t7.tenant_id AS tenant_id,
        t7.user_id AS user_id,
        t7.placed_at AS placed_at,
        t7.currency_code AS currency_code,
        t7.grand_total AS grand_total
    FROM orders t7
    WHERE t7.status <> 'cancelled'
      AND t7.placed_at >= DATE '2025-01-01'
      AND t7.placed_at < DATE '2026-01-01'
      AND NOT EXISTS (
          SELECT 1 FROM order_discounts t8
          WHERE t8.order_id = t7.id AND t8.tenant_id = t7.tenant_id
      )
),
non_discounted_orders_eur AS (
    SELECT
        t9.*,
        t9.grand_total / NULLIF(t10.rate, 0) AS grand_total_eur
    FROM non_discounted_orders t9
    LEFT JOIN exchange_rates t10
        ON t10.base_code = 'EUR'
       AND t10.quote_code = t9.currency_code
       AND t10.as_of = CAST(t9.placed_at AS DATE)
),
comparison_orders AS (
    SELECT
        t11.discount_id AS discount_id,
        t11.user_id AS user_id,
        t12.order_id AS order_id,
        t12.grand_total_eur AS grand_total_eur
    FROM customer_discount_pairs t11
    JOIN non_discounted_orders_eur t12
        ON t12.tenant_id = t11.tenant_id
       AND t12.user_id = t11.user_id
),
discount_comparison_avg AS (
    SELECT discount_id, AVG(grand_total_eur) AS avg_other_order_value_eur
    FROM comparison_orders
    GROUP BY discount_id
),
discount_comparison_customers AS (
    SELECT discount_id, COUNT(DISTINCT user_id) AS customers_with_other_orders
    FROM comparison_orders
    GROUP BY discount_id
)
SELECT
    t0.discount_name AS discount_name,
    COALESCE(t13.coupon_code, 'automatic') AS coupon_code,
    COALESCE(t14.order_count, 0) AS discounted_order_count,
    COALESCE(t15.distinct_customers, 0) AS distinct_customers,
    ROUND(COALESCE(t16.avg_discounted_order_value_eur, 0), 2) AS avg_discounted_order_value_eur,
    ROUND(t17.avg_other_order_value_eur, 2) AS avg_other_order_value_eur,
    ROUND(COALESCE(t16.avg_discounted_order_value_eur, 0) - t17.avg_other_order_value_eur, 2) AS lift_eur,
    ROUND(
        (COALESCE(t16.avg_discounted_order_value_eur, 0) - t17.avg_other_order_value_eur)
        / NULLIF(t17.avg_other_order_value_eur, 0) * 100, 1
    ) AS lift_pct,
    ROUND(
        CAST(COALESCE(t18.customers_with_other_orders, 0) AS DECIMAL)
        / NULLIF(t15.distinct_customers, 0) * 100, 1
    ) AS pct_customers_with_other_nondiscounted_order
FROM top_discounts t0
LEFT JOIN discount_coupon_code t13 ON t13.discount_id = t0.discount_id
LEFT JOIN discount_order_count t14 ON t14.discount_id = t0.discount_id
LEFT JOIN discount_customer_count t15 ON t15.discount_id = t0.discount_id
LEFT JOIN discount_avg_value t16 ON t16.discount_id = t0.discount_id
LEFT JOIN discount_comparison_avg t17 ON t17.discount_id = t0.discount_id
LEFT JOIN discount_comparison_customers t18 ON t18.discount_id = t0.discount_id
ORDER BY lift_pct ASC NULLS LAST, discount_name ASC

    “Query compiles and returns the expected 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 what the query does: - Grain: one row per discount (top 15 by 2025 EUR discount spend, excluding 'Subscription delivery (prepaid)'). - Discounted side: order_discounts → orders → discounts, filtered to non-cancelled 2025 orders, converted to EUR via exchange_rates at the order's placed_at date. - Comparison side: for each (tenant, user) pair found on those discounted orders, their other 2025 non-cancelled orders that have no order_discounts row at all, averaged in EUR the same way. - Metrics: order count, distinct customers, avg discounted order value, avg comparison order value, lift (EUR and %), and the % of those customers who had at least one qualifying non-discounted order (which doubles as the caveat signal — low values there mean the lift estimate rests on few/no comparison orders). - Ordered by lift % ascending (weakest/no-lift cases first), tie-broken by discount name, as requested.”

  5. 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_order_value_eur | avg_other_order_value_eur | lift_eur | lift_pct | pct_customers_with_other_nondiscounted_o
------------------+-------------+------------------------+--------------------+--------------------------------+---------------------------+----------+----------+-----------------------------------------
Summer Sale 2025  | automatic   |                     67 |                 60 |                         120.91 |                    155.77 |   -34.87 |    -22.4 |                                     78.3
Summer Sale 2025  | automatic   |                    146 |                134 |                         159.92 |                    197.84 |   -37.92 |    -19.2 |                                     85.8
January Sale 2025 | automatic   |                    124 |                101 |                         164.95 |                    179.88 |   -14.93 |     -8.3 |                                     73.3
Welcome 10% off   | WELCOME10   |                    140 |                140 |                         169.16 |                    184.03 |   -14.87 |     -8.1 |                                     47.1
Welcome 10% off   | WELCOME10   |                    260 |                260 |                         210.99 |                     207.8 |      3.2 |      1.5 |                                     53.1
Black Friday 2025 | BF2530      |                     23 |                 22 |                         362.74 |                    271.44 |     91.3 |     33.6 |                                     77.3
Summer Sale 2025  | automatic   |                     24 |                 19 |                         556.99 |                    267.17 |   289.81 |    108.5 |                                     78.9
Volume tier 10+   | automatic   |                    104 |                 28 |                         2943.7 |                   1106.97 |  1836.73 |    165.9 |                                     89.3
Welcome 10% off   | WELCOME10   |                      7 |                  7 |                        3368.82 |                   1127.42 |  2241.39 |    198.8 |                                     42.9
Volume tier 10+   | automatic   |                    102 |                 32 |                        1934.17 |                    628.77 |  1305.41 |    207.6 |                                     65.6
Volume tier 50+   | automatic   |                      5 |                  4 |                        6118.39 |                   1414.43 |  4703.96 |    332.6 |                                    100.0
Volume tier 10+   | automatic   |                     38 |                 12 |                        4939.06 |                     924.9 |  4014.16 |    434.0 |                                     83.3
Volume tier 50+   | automatic   |                     15 |                 13 |                        6693.34 |                    551.86 |  6141.48 |   1112.9 |                                     84.6
Welcome 10% off   | WELCOME10   |                      7 |                  7 |                        8487.44 |                    585.53 |  7901.91 |   1349.5 |                                     42.9
Welcome 10% off   | WELCOME10   |                      4 |                  4 |                       10253.76 |                    542.69 |  9711.07 |   1789.4 |                                    100.0
(15 rows)

  6. What came of it

    The memo and the queries that followed; links found by title, so inferred

    Later queries that build on this report:

    It builds on:

  7. It becomes benchmark query q153

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

    • spellingfetch first
    • determinismtie-breaker at the FETCH 15 cut: discount_id is the group key
    • determinismtie-breaker: discount names are not unique
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision; bare NUMERIC cast that only served the rounding removed
    • numericsdocs/NUMERICS.md: x / exchange rate cast to NUMERIC(18,6) (3×)
    • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (4×)
    • placeholderfetch first
    The benchmark text, against the submitted SQL
    WITH discount_order_lines AS (    SELECT        t1.id AS order_discount_id,        t1.tenant_id AS tenant_id,        t1.order_id AS order_id,        t1.discount_id AS discount_id,        t1.code AS coupon_code,        t1.amount AS discount_amount,        t1.currency_code AS discount_currency_code,        t2.user_id AS user_id,        t2.placed_at AS placed_at,        t2.currency_code AS order_currency_code,        t2.grand_total AS order_grand_total,        t3.name AS discount_name    FROM order_discounts t1    JOIN orders t2        ON t2.id = t1.order_id       AND t2.tenant_id = t1.tenant_id    JOIN discounts t3        ON t3.id = t1.discount_id       AND t3.tenant_id = t1.tenant_id    WHERE t2.status <> 'cancelled'      AND t2.placed_at >= DATE '2025-01-01'      AND t2.placed_at < DATE '2026-01-01'      AND t3.name <> 'Subscription delivery (prepaid)'),discount_order_lines_eur AS (    SELECT        t4.*,        t4.discount_amount / NULLIF(t5.rate, 0) AS discount_amount_eur,        t4.order_grand_total / NULLIF(t6.rate, 0) AS order_grand_total_eur        CAST(t4.discount_amount / NULLIF(t5.rate, 0) AS NUMERIC(18,6)) AS discount_amount_eur,        CAST(t4.order_grand_total / NULLIF(t6.rate, 0) AS NUMERIC(18,6)) AS order_grand_total_eur    FROM discount_order_lines t4    LEFT JOIN exchange_rates t5        ON t5.base_code = 'EUR'       AND t5.quote_code = t4.discount_currency_code       AND t5.as_of = CAST(t4.placed_at AS DATE)    LEFT JOIN exchange_rates t6        ON t6.base_code = 'EUR'       AND t6.quote_code = t4.order_currency_code       AND t6.as_of = CAST(t4.placed_at AS DATE)),discounted_orders_distinct AS (    SELECT DISTINCT        discount_id,        discount_name,        tenant_id,        order_id,        user_id,        order_grand_total_eur    FROM discount_order_lines_eur),discount_spend AS (    SELECT        discount_id,        discount_name,        SUM(discount_amount_eur) AS total_discount_spend_eur    FROM discount_order_lines_eur    GROUP BY discount_id, discount_name),top_discounts AS (    SELECT discount_id, discount_name, total_discount_spend_eur    FROM discount_spend    ORDER BY total_discount_spend_eur DESC    LIMIT 15    ORDER BY total_discount_spend_eur DESC NULLS FIRST, discount_id NULLS LAST    {{fetch_first(15)}}),discount_order_count AS (    SELECT discount_id, COUNT(DISTINCT order_id) AS order_count    FROM discounted_orders_distinct    GROUP BY discount_id),discount_customer_count AS (    SELECT discount_id, COUNT(DISTINCT user_id) AS distinct_customers    FROM discounted_orders_distinct    GROUP BY discount_id),discount_avg_value AS (    SELECT discount_id, AVG(order_grand_total_eur) AS avg_discounted_order_value_eur    FROM discounted_orders_distinct    GROUP BY discount_id),discount_coupon_code AS (    SELECT discount_id, MAX(coupon_code) AS coupon_code    FROM discount_order_lines_eur    GROUP BY discount_id),customer_discount_pairs AS (    SELECT DISTINCT discount_id, tenant_id, user_id    FROM discounted_orders_distinct    WHERE discount_id IN (SELECT discount_id FROM top_discounts)),non_discounted_orders AS (    SELECT        t7.id AS order_id,        t7.tenant_id AS tenant_id,        t7.user_id AS user_id,        t7.placed_at AS placed_at,        t7.currency_code AS currency_code,        t7.grand_total AS grand_total    FROM orders t7    WHERE t7.status <> 'cancelled'      AND t7.placed_at >= DATE '2025-01-01'      AND t7.placed_at < DATE '2026-01-01'      AND NOT EXISTS (          SELECT 1 FROM order_discounts t8          WHERE t8.order_id = t7.id AND t8.tenant_id = t7.tenant_id      )),non_discounted_orders_eur AS (    SELECT        t9.*,        t9.grand_total / NULLIF(t10.rate, 0) AS grand_total_eur        CAST(t9.grand_total / NULLIF(t10.rate, 0) AS NUMERIC(18,6)) AS grand_total_eur    FROM non_discounted_orders t9    LEFT JOIN exchange_rates t10        ON t10.base_code = 'EUR'       AND t10.quote_code = t9.currency_code       AND t10.as_of = CAST(t9.placed_at AS DATE)),comparison_orders AS (    SELECT        t11.discount_id AS discount_id,        t11.user_id AS user_id,        t12.order_id AS order_id,        t12.grand_total_eur AS grand_total_eur    FROM customer_discount_pairs t11    JOIN non_discounted_orders_eur t12        ON t12.tenant_id = t11.tenant_id       AND t12.user_id = t11.user_id),discount_comparison_avg AS (    SELECT discount_id, AVG(grand_total_eur) AS avg_other_order_value_eur    FROM comparison_orders    GROUP BY discount_id),discount_comparison_customers AS (    SELECT discount_id, COUNT(DISTINCT user_id) AS customers_with_other_orders    FROM comparison_orders    GROUP BY discount_id)SELECT    t0.discount_name AS discount_name,    COALESCE(t13.coupon_code, 'automatic') AS coupon_code,    COALESCE(t14.order_count, 0) AS discounted_order_count,    COALESCE(t15.distinct_customers, 0) AS distinct_customers,    ROUND(COALESCE(t16.avg_discounted_order_value_eur, 0), 2) AS avg_discounted_order_value_eur,    ROUND(t17.avg_other_order_value_eur, 2) AS avg_other_order_value_eur,    ROUND(COALESCE(t16.avg_discounted_order_value_eur, 0) - t17.avg_other_order_value_eur, 2) AS lift_eur,    ROUND(        (COALESCE(t16.avg_discounted_order_value_eur, 0) - t17.avg_other_order_value_eur)        / NULLIF(t17.avg_other_order_value_eur, 0) * 100, 1    ) AS lift_pct,    ROUND(        CAST(COALESCE(t18.customers_with_other_orders, 0) AS DECIMAL)        / NULLIF(t15.distinct_customers, 0) * 100, 1    ) AS pct_customers_with_other_nondiscounted_order    COALESCE(t16.avg_discounted_order_value_eur, 0) AS avg_discounted_order_value_eur,    t17.avg_other_order_value_eur AS avg_other_order_value_eur,    COALESCE(t16.avg_discounted_order_value_eur, 0) - t17.avg_other_order_value_eur AS lift_eur,    (COALESCE(t16.avg_discounted_order_value_eur, 0) - t17.avg_other_order_value_eur)    / NULLIF(t17.avg_other_order_value_eur, 0) * 100 AS lift_pct,    CAST(COALESCE(t18.customers_with_other_orders, 0) AS DECIMAL(18,6))    / NULLIF(t15.distinct_customers, 0) * 100 AS pct_customers_with_other_nondiscounted_orderFROM top_discounts t0LEFT JOIN discount_coupon_code t13 ON t13.discount_id = t0.discount_idLEFT JOIN discount_order_count t14 ON t14.discount_id = t0.discount_idLEFT JOIN discount_customer_count t15 ON t15.discount_id = t0.discount_idLEFT JOIN discount_avg_value t16 ON t16.discount_id = t0.discount_idLEFT JOIN discount_comparison_avg t17 ON t17.discount_id = t0.discount_idLEFT JOIN discount_comparison_customers t18 ON t18.discount_id = t0.discount_idORDER BY lift_pct ASC NULLS LAST, discount_name ASCORDER BY lift_pct ASC NULLS LAST, discount_name ASC NULLS LAST, t0.discount_id ASC NULLS LAST

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

    WITH discount_order_lines AS (
    SELECT
        t1.id AS order_discount_id,
        t1.tenant_id AS tenant_id,
        t1.order_id AS order_id,
        t1.discount_id AS discount_id,
        t1.code AS coupon_code,
        t1.amount AS discount_amount,
        t1.currency_code AS discount_currency_code,
        t2.user_id AS user_id,
        t2.placed_at AS placed_at,
        t2.currency_code AS order_currency_code,
        t2.grand_total AS order_grand_total,
        t3.name AS discount_name
    FROM order_discounts t1
    JOIN orders t2
        ON t2.id = t1.order_id
       AND t2.tenant_id = t1.tenant_id
    JOIN discounts t3
        ON t3.id = t1.discount_id
       AND t3.tenant_id = t1.tenant_id
    WHERE t2.status <> 'cancelled'
      AND t2.placed_at >= DATE '2025-01-01'
      AND t2.placed_at < DATE '2026-01-01'
      AND t3.name <> 'Subscription delivery (prepaid)'
),
discount_order_lines_eur AS (
    SELECT
        t4.*,
        CAST(t4.discount_amount / NULLIF(t5.rate, 0) AS NUMERIC(18,6)) AS discount_amount_eur,
        CAST(t4.order_grand_total / NULLIF(t6.rate, 0) AS NUMERIC(18,6)) AS order_grand_total_eur
    FROM discount_order_lines t4
    LEFT JOIN exchange_rates t5
        ON t5.base_code = 'EUR'
       AND t5.quote_code = t4.discount_currency_code
       AND t5.as_of = CAST(t4.placed_at AS DATE)
    LEFT JOIN exchange_rates t6
        ON t6.base_code = 'EUR'
       AND t6.quote_code = t4.order_currency_code
       AND t6.as_of = CAST(t4.placed_at AS DATE)
),
discounted_orders_distinct AS (
    SELECT DISTINCT
        discount_id,
        discount_name,
        tenant_id,
        order_id,
        user_id,
        order_grand_total_eur
    FROM discount_order_lines_eur
),
discount_spend AS (
    SELECT
        discount_id,
        discount_name,
        SUM(discount_amount_eur) AS total_discount_spend_eur
    FROM discount_order_lines_eur
    GROUP BY discount_id, discount_name
),
top_discounts AS (
    SELECT discount_id, discount_name, total_discount_spend_eur
    FROM discount_spend
    ORDER BY total_discount_spend_eur DESC NULLS FIRST, discount_id NULLS LAST
    FETCH FIRST 15 ROWS ONLY
),
discount_order_count AS (
    SELECT discount_id, COUNT(DISTINCT order_id) AS order_count
    FROM discounted_orders_distinct
    GROUP BY discount_id
),
discount_customer_count AS (
    SELECT discount_id, COUNT(DISTINCT user_id) AS distinct_customers
    FROM discounted_orders_distinct
    GROUP BY discount_id
),
discount_avg_value AS (
    SELECT discount_id, AVG(order_grand_total_eur) AS avg_discounted_order_value_eur
    FROM discounted_orders_distinct
    GROUP BY discount_id
),
discount_coupon_code AS (
    SELECT discount_id, MAX(coupon_code) AS coupon_code
    FROM discount_order_lines_eur
    GROUP BY discount_id
),
customer_discount_pairs AS (
    SELECT DISTINCT discount_id, tenant_id, user_id
    FROM discounted_orders_distinct
    WHERE discount_id IN (SELECT discount_id FROM top_discounts)
),
non_discounted_orders AS (
    SELECT
        t7.id AS order_id,
        t7.tenant_id AS tenant_id,
        t7.user_id AS user_id,
        t7.placed_at AS placed_at,
        t7.currency_code AS currency_code,
        t7.grand_total AS grand_total
    FROM orders t7
    WHERE t7.status <> 'cancelled'
      AND t7.placed_at >= DATE '2025-01-01'
      AND t7.placed_at < DATE '2026-01-01'
      AND NOT EXISTS (
          SELECT 1 FROM order_discounts t8
          WHERE t8.order_id = t7.id AND t8.tenant_id = t7.tenant_id
      )
),
non_discounted_orders_eur AS (
    SELECT
        t9.*,
        CAST(t9.grand_total / NULLIF(t10.rate, 0) AS NUMERIC(18,6)) AS grand_total_eur
    FROM non_discounted_orders t9
    LEFT JOIN exchange_rates t10
        ON t10.base_code = 'EUR'
       AND t10.quote_code = t9.currency_code
       AND t10.as_of = CAST(t9.placed_at AS DATE)
),
comparison_orders AS (
    SELECT
        t11.discount_id AS discount_id,
        t11.user_id AS user_id,
        t12.order_id AS order_id,
        t12.grand_total_eur AS grand_total_eur
    FROM customer_discount_pairs t11
    JOIN non_discounted_orders_eur t12
        ON t12.tenant_id = t11.tenant_id
       AND t12.user_id = t11.user_id
),
discount_comparison_avg AS (
    SELECT discount_id, AVG(grand_total_eur) AS avg_other_order_value_eur
    FROM comparison_orders
    GROUP BY discount_id
),
discount_comparison_customers AS (
    SELECT discount_id, COUNT(DISTINCT user_id) AS customers_with_other_orders
    FROM comparison_orders
    GROUP BY discount_id
)
SELECT
    t0.discount_name AS discount_name,
    COALESCE(t13.coupon_code, 'automatic') AS coupon_code,
    COALESCE(t14.order_count, 0) AS discounted_order_count,
    COALESCE(t15.distinct_customers, 0) AS distinct_customers,
    COALESCE(t16.avg_discounted_order_value_eur, 0) AS avg_discounted_order_value_eur,
    t17.avg_other_order_value_eur AS avg_other_order_value_eur,
    COALESCE(t16.avg_discounted_order_value_eur, 0) - t17.avg_other_order_value_eur AS lift_eur,
    (COALESCE(t16.avg_discounted_order_value_eur, 0) - t17.avg_other_order_value_eur)
    / NULLIF(t17.avg_other_order_value_eur, 0) * 100 AS lift_pct,
    CAST(COALESCE(t18.customers_with_other_orders, 0) AS DECIMAL(18,6))
    / NULLIF(t15.distinct_customers, 0) * 100 AS pct_customers_with_other_nondiscounted_order
FROM top_discounts t0
LEFT JOIN discount_coupon_code t13 ON t13.discount_id = t0.discount_id
LEFT JOIN discount_order_count t14 ON t14.discount_id = t0.discount_id
LEFT JOIN discount_customer_count t15 ON t15.discount_id = t0.discount_id
LEFT JOIN discount_avg_value t16 ON t16.discount_id = t0.discount_id
LEFT JOIN discount_comparison_avg t17 ON t17.discount_id = t0.discount_id
LEFT JOIN discount_comparison_customers t18 ON t18.discount_id = t0.discount_id
ORDER BY lift_pct ASC NULLS LAST, discount_name ASC NULLS LAST, t0.discount_id ASC NULLS LAST
    100 ms1 s10 sHyper354 msDuckDB1.12 sGizmoSQL1.11 sclickhouse_brew1.84 sFirebolterrorDataFusion3.90 sClickHouseerror
    • Firebolt{ "errors": [ { "description": "Line 30, Column 33: 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": "57480755-7bba-4de7-a3ee-828a0e0de63a", "query_lab
    • ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table4.discount_amount Decimal(14, 4) Decimal64(size = 0), nullIf(__table8.rate, 0_UInt8) Nullable(Decimal(12, 6)) Nullable(size = 0, Decimal64(size = 0), UInt8(size = 0)). (AR

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

  8. 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

    Bea's whole session, 7 turns
    • The request, as Bea received it
      Report request from Oliver Grant (analyst for the Head of Pricing and Promotions):

Title: Within-customer order value lift on top discount codes, 2025
Question: For our top discounts, do the same customers spend more per order when using the discount than they do on their own other, non-discounted orders — our best approximate read on incrementality?
Grain: one row per discount (discounts.id), restricted to the top discounts by 2025 discount spend excluding 'Subscription delivery (prepaid)'
Columns:
- discount name
- coupon code (or 'automatic')
- order count with this discount
- distinct customers using this discount
- avg order value on discounted orders (grand_total, EUR)
- avg order value on the same customers' other non-discounted orders in 2025 (EUR)
- lift (EUR, discounted minus other)
- lift (%)
- % of these customers who placed at least one other order in 2025 with no discount applied
Details: Source: order_discounts + discounts + orders for the discounted-order side (orders.status != 'cancelled', placed_at in 2025, discount name != 'Subscription delivery (prepaid)'); for the comparison side, for each user_id found above, pull their other 2025 orders (same tenant, status != 'cancelled') that have no row in order_discounts, and average grand_total (EUR) across those. Convert all amounts to EUR via exchange_rates (base_code='EUR', quote_code=currency_code, as_of=placed_at date). Restrict to the top 15 discounts by total 2025 discount_amount_eur (from the existing ranking, excluding the subscription-delivery discount) to keep this focused and affordable. Order by lift (%) ascending (so we see 'no discernible lift' cases first, as that's the more actionable finding), discount name as tie-break. Note total discounts considered (15) and how many customers had no comparison orders available (flag as a caveat if material).

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Bea calls dry_run
      WITH discount_order_lines AS (
    SELECT
        t1.id AS order_discount_id,
        t1.tenant_id AS tenant_id,
        t1.order_id AS order_id,
        t1.discount_id AS discount_id,
        t1.code AS coupon_code,
        t1.amount AS discount_amount,
        t1.currency_code AS discount_currency_code,
        t2.user_id AS user_id,
        t2.placed_at AS placed_at,
        t2.currency_code AS order_currency_code,
        t2.grand_total AS order_grand_total,
        t3.name AS discount_name
    FROM order_discounts t1
    JOIN orders t2
        ON t2.id = t1.order_id
       AND t2.tenant_id = t1.tenant_id
    JOIN discounts t3
        ON t3.id = t1.discount_id
       AND t3.tenant_id = t1.tenant_id
    WHERE t2.status <> 'cancelled'
      AND t2.placed_at >= DATE '2025-01-01'
      AND t2.placed_at < DATE '2026-01-01'
      AND t3.name <> 'Subscription delivery (prepaid)'
),
discount_order_lines_eur AS (
    SELECT
        t4.*,
        t4.discount_amount / NULLIF(t5.rate, 0) AS discount_amount_eur,
        t4.order_grand_total / NULLIF(t6.rate, 0) AS order_grand_total_eur
    FROM discount_order_lines t4
    LEFT JOIN exchange_rates t5
        ON t5.base_code = 'EUR'
       AND t5.quote_code = t4.discount_currency_code
       AND t5.as_of = CAST(t4.placed_at AS DATE)
    LEFT JOIN exchange_rates t6
        ON t6.base_code = 'EUR'
       AND t6.quote_code = t4.order_currency_code
       AND t6.as_of = CAST(t4.placed_at AS DATE)
),
discounted_orders_distinct AS (
    SELECT DISTINCT
        discount_id,
        discount_name,
        tenant_id,
        order_id,
        user_id,
        order_grand_total_eur
    FROM discount_order_lines_eur
),
discount_spend AS (
    SELECT
        discount_id,
        discount_name,
        SUM(discount_amount_eur) AS total_discount_spend_eur
    FROM discount_order_lines_eur
    GROUP BY discount_id, discount_name
),
top_discounts AS (
    SELECT discount_id, discount_name, total_discount_spend_eur
    FROM discount_spend
    ORDER BY total_discount_spend_eur DESC
    LIMIT 15
),
discount_order_count AS (
    SELECT discount_id, COUNT(DISTINCT order_id) AS order_count
    FROM discounted_orders_distinct
    GROUP BY discount_id
),
discount_customer_count AS (
    SELECT discount_id, COUNT(DISTINCT user_id) AS distinct_customers
    FROM discounted_orders_distinct
    GROUP BY discount_id
),
discount_avg_value AS (
    SELECT discount_id, AVG(order_grand_total_eur) AS avg_discounted_order_value_eur
    FROM discounted_orders_distinct
    GROUP BY discount_id
),
discount_coupon_code AS (
    SELECT discount_id, MAX(coupon_code) AS coupon_code
    FROM discount_order_lines_eur
    GROUP BY discount_id
),
customer_discount_pairs AS (
    SELECT DISTINCT discount_id, tenant_id, user_id
    FROM discounted_orders_distinct
    WHERE discount_id IN (SELECT discount_id FROM top_discounts)
),
non_discounted_orders AS (
    SELECT
        t7.id AS order_id,
        t7.tenant_id AS tenant_id,
        t7.user_id AS user_id,
        t7.placed_at AS placed_at,
        t7.currency_code AS currency_code,
        t7.grand_total AS grand_total
    FROM orders t7
    WHERE t7.status <> 'cancelled'
      AND t7.placed_at >= DATE '2025-01-01'
      AND t7.placed_at < DATE '2026-01-01'
      AND NOT EXISTS (
          SELECT 1 FROM order_discounts t8
          WHERE t8.order_id = t7.id AND t8.tenant_id = t7.tenant_id
      )
),
non_discounted_orders_eur AS (
    SELECT
        t9.*,
        t9.grand_total / NULLIF(t10.rate, 0) AS grand_total_eur
    FROM non_discounted_orders t9
    LEFT JOIN exchange_rates t10
        ON t10.base_code = 'EUR'
       AND t10.quote_code = t9.currency_code
       AND t10.as_of = CAST(t9.placed_at AS DATE)
),
comparison_orders AS (
    SELECT
        t11.discount_id AS discount_id,
        t11.user_id AS user_id,
        t12.order_id AS order_id,
        t12.grand_total_eur AS grand_total_eur
    FROM customer_discount_pairs t11
    JOIN non_discounted_orders_eur t12
        ON t12.tenant_id = t11.tenant_id
       AND t12.user_id = t11.user_id
),
discount_comparison_avg AS (
    SELECT discount_id, AVG(grand_total_eur) AS avg_other_order_value_eur
    FROM comparison_orders
    GROUP BY discount_id
),
discount_comparison_customers AS (
    SELECT discount_id, COUNT(DISTINCT user_id) AS customers_with_other_orders
    FROM comparison_orders
    GROUP BY discount_id
)
SELECT
    t0.discount_name AS discount_name,
    COALESCE(t13.coupon_code, 'automatic') AS coupon_code,
    COALESCE(t14.order_count, 0) AS discounted_order_count,
    COALESCE(t15.distinct_customers, 0) AS distinct_customers,
    ROUND(COALESCE(t16.avg_discounted_order_value_eur, 0), 2) AS avg_discounted_order_value_eur,
    ROUND(t17.avg_other_order_value_eur, 2) AS avg_other_order_value_eur,
    ROUND(COALESCE(t16.avg_discounted_order_value_eur, 0) - t17.avg_other_order_value_eur, 2) AS lift_eur,
    ROUND(
        (COALESCE(t16.avg_discounted_order_value_eur, 0) - t17.avg_other_order_value_eur)
        / NULLIF(t17.avg_other_order_value_eur, 0) * 100, 1
    ) AS lift_pct,
    ROUND(
        CAST(COALESCE(t18.customers_with_other_orders, 0) AS DECIMAL)
        / NULLIF(t15.distinct_customers, 0) * 100, 1
    ) AS pct_customers_with_other_nondiscounted_order
FROM top_discounts t0
LEFT JOIN discount_coupon_code t13 ON t13.discount_id = t0.discount_id
LEFT JOIN discount_order_count t14 ON t14.discount_id = t0.discount_id
LEFT JOIN discount_customer_count t15 ON t15.discount_id = t0.discount_id
LEFT JOIN discount_avg_value t16 ON t16.discount_id = t0.discount_id
LEFT JOIN discount_comparison_avg t17 ON t17.discount_id = t0.discount_id
LEFT JOIN discount_comparison_customers t18 ON t18.discount_id = t0.discount_id
ORDER BY lift_pct ASC NULLS LAST, discount_name 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_order_value_eur, avg_other_order_value_eur, lift_eur, lift_pct, pct_customers_with_other_nondiscounted_order
    • BeaQuery compiles and returns the expected columns. Submitting.
    • Bea calls submit
      WITH discount_order_lines AS (
    SELECT
        t1.id AS order_discount_id,
        t1.tenant_id AS tenant_id,
        t1.order_id AS order_id,
        t1.discount_id AS discount_id,
        t1.code AS coupon_code,
        t1.amount AS discount_amount,
        t1.currency_code AS discount_currency_code,
        t2.user_id AS user_id,
        t2.placed_at AS placed_at,
        t2.currency_code AS order_currency_code,
        t2.grand_total AS order_grand_total,
        t3.name AS discount_name
    FROM order_discounts t1
    JOIN orders t2
        ON t2.id = t1.order_id
       AND t2.tenant_id = t1.tenant_id
    JOIN discounts t3
        ON t3.id = t1.discount_id
       AND t3.tenant_id = t1.tenant_id
    WHERE t2.status <> 'cancelled'
      AND t2.placed_at >= DATE '2025-01-01'
      AND t2.placed_at < DATE '2026-01-01'
      AND t3.name <> 'Subscription delivery (prepaid)'
),
discount_order_lines_eur AS (
    SELECT
        t4.*,
        t4.discount_amount / NULLIF(t5.rate, 0) AS discount_amount_eur,
        t4.order_grand_total / NULLIF(t6.rate, 0) AS order_grand_total_eur
    FROM discount_order_lines t4
    LEFT JOIN exchange_rates t5
        ON t5.base_code = 'EUR'
       AND t5.quote_code = t4.discount_currency_code
       AND t5.as_of = CAST(t4.placed_at AS DATE)
    LEFT JOIN exchange_rates t6
        ON t6.base_code = 'EUR'
       AND t6.quote_code = t4.order_currency_code
       AND t6.as_of = CAST(t4.placed_at AS DATE)
),
discounted_orders_distinct AS (
    SELECT DISTINCT
        discount_id,
        discount_name,
        tenant_id,
        order_id,
        user_id,
        order_grand_total_eur
    FROM discount_order_lines_eur
),
discount_spend AS (
    SELECT
        discount_id,
        discount_name,
        SUM(discount_amount_eur) AS total_discount_spend_eur
    FROM discount_order_lines_eur
    GROUP BY discount_id, discount_name
),
top_discounts AS (
    SELECT discount_id, discount_name, total_discount_spend_eur
    FROM discount_spend
    ORDER BY total_discount_spend_eur DESC
    LIMIT 15
),
discount_order_count AS (
    SELECT discount_id, COUNT(DISTINCT order_id) AS order_count
    FROM discounted_orders_distinct
    GROUP BY discount_id
),
discount_customer_count AS (
    SELECT discount_id, COUNT(DISTINCT user_id) AS distinct_customers
    FROM discounted_orders_distinct
    GROUP BY discount_id
),
discount_avg_value AS (
    SELECT discount_id, AVG(order_grand_total_eur) AS avg_discounted_order_value_eur
    FROM discounted_orders_distinct
    GROUP BY discount_id
),
discount_coupon_code AS (
    SELECT discount_id, MAX(coupon_code) AS coupon_code
    FROM discount_order_lines_eur
    GROUP BY discount_id
),
customer_discount_pairs AS (
    SELECT DISTINCT discount_id, tenant_id, user_id
    FROM discounted_orders_distinct
    WHERE discount_id IN (SELECT discount_id FROM top_discounts)
),
non_discounted_orders AS (
    SELECT
        t7.id AS order_id,
        t7.tenant_id AS tenant_id,
        t7.user_id AS user_id,
        t7.placed_at AS placed_at,
        t7.currency_code AS currency_code,
        t7.grand_total AS grand_total
    FROM orders t7
    WHERE t7.status <> 'cancelled'
      AND t7.placed_at >= DATE '2025-01-01'
      AND t7.placed_at < DATE '2026-01-01'
      AND NOT EXISTS (
          SELECT 1 FROM order_discounts t8
          WHERE t8.order_id = t7.id AND t8.tenant_id = t7.tenant_id
      )
),
non_discounted_orders_eur AS (
    SELECT
        t9.*,
        t9.grand_total / NULLIF(t10.rate, 0) AS grand_total_eur
    FROM non_discounted_orders t9
    LEFT JOIN exchange_rates t10
        ON t10.base_code = 'EUR'
       AND t10.quote_code = t9.currency_code
       AND t10.as_of = CAST(t9.placed_at AS DATE)
),
comparison_orders AS (
    SELECT
        t11.discount_id AS discount_id,
        t11.user_id AS user_id,
        t12.order_id AS order_id,
        t12.grand_total_eur AS grand_total_eur
    FROM customer_discount_pairs t11
    JOIN non_discounted_orders_eur t12
        ON t12.tenant_id = t11.tenant_id
       AND t12.user_id = t11.user_id
),
discount_comparison_avg AS (
    SELECT discount_id, AVG(grand_total_eur) AS avg_other_order_value_eur
    FROM comparison_orders
    GROUP BY discount_id
),
discount_comparison_customers AS (
    SELECT discount_id, COUNT(DISTINCT user_id) AS customers_with_other_orders
    FROM comparison_orders
    GROUP BY discount_id
)
SELECT
    t0.discount_name AS discount_name,
    COALESCE(t13.coupon_code, 'automatic') AS coupon_code,
    COALESCE(t14.order_count, 0) AS discounted_order_count,
    COALESCE(t15.distinct_customers, 0) AS distinct_customers,
    ROUND(COALESCE(t16.avg_discounted_order_value_eur, 0), 2) AS avg_discounted_order_value_eur,
    ROUND(t17.avg_other_order_value_eur, 2) AS avg_other_order_value_eur,
    ROUND(COALESCE(t16.avg_discounted_order_value_eur, 0) - t17.avg_other_order_value_eur, 2) AS lift_eur,
    ROUND(
        (COALESCE(t16.avg_discounted_order_value_eur, 0) - t17.avg_other_order_value_eur)
        / NULLIF(t17.avg_other_order_value_eur, 0) * 100, 1
    ) AS lift_pct,
    ROUND(
        CAST(COALESCE(t18.customers_with_other_orders, 0) AS DECIMAL)
        / NULLIF(t15.distinct_customers, 0) * 100, 1
    ) AS pct_customers_with_other_nondiscounted_order
FROM top_discounts t0
LEFT JOIN discount_coupon_code t13 ON t13.discount_id = t0.discount_id
LEFT JOIN discount_order_count t14 ON t14.discount_id = t0.discount_id
LEFT JOIN discount_customer_count t15 ON t15.discount_id = t0.discount_id
LEFT JOIN discount_avg_value t16 ON t16.discount_id = t0.discount_id
LEFT JOIN discount_comparison_avg t17 ON t17.discount_id = t0.discount_id
LEFT JOIN discount_comparison_customers t18 ON t18.discount_id = t0.discount_id
ORDER BY lift_pct ASC NULLS LAST, discount_name ASC
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • BeaSubmitted. Summary of what the query does: - **Grain:** one row per discount (top 15 by 2025 EUR discount spend, excluding 'Subscription delivery (prepaid)'). - **Discounted side:** `order_discounts` → `orders` → `discounts`, filtered to non-cancelled 2025 orders, converted to EUR via `exchange_rates` at the order's placed_at date. - **Comparison side:** for each (tenant, user) pair found on those discounted orders, their other 2025 non-cancelled orders that have **no** `order_discounts` row at all, averaged in EUR the same way. - **Metrics:** order count, distinct customers, avg discounted order value, avg comparison order value, lift (EUR and %), and the % of those customers who had at least one qualifying non-discounted order (which doubles as the caveat signal — low values there mean the lift estimate rests on few/no comparison orders). - Ordered by lift % ascending (weakest/no-lift cases first), tie-broken by discount name, as requested.

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

    Bea's transcript (312 KB) · Oliver Grant's transcript (972 KB) · Marco Bianchi's transcript (259 KB) · the night's plan (6 KB) · the night's reports (8 KB) · the week's memo (3 KB)