Queries · q158 · Head of Pricing and Promotions · week 1, night 4

Aggregate within-customer basket-size lift by promotion family, 2025

“Across all tenants running each major marketing promotion, do customers buy bigger baskets (pre-discount subtotal) when using the code, stated as one clean weighted number per promotion family?”

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

   Show the whole note
   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 4, request 2 of 3

   Last night before the memo, so I'm closing out the three loose threads that actually change what I tell Marco and Tobias, and skipping anything merely confirmatory:

   1. The orphan subscription-delivery report came back strange: 0% of orphan customers were classified as "lapsed within 30 days" and 0% as "no subscription record at all" — yet 25-46% of orders are orphan. That means essentially all orphan customers have a subscription record, just not one whose status/date my query treated as active-and-overlapping. The likely candidates are 'paused' or 'incomplete' subscriptions, or cancellations that happened well before the 30-day window I checked. I need the actual status distribution before I can tell Tobias whether this is "paused customers still getting free boxes" (a real control gap) or something more benign.
   2. The corrected (pre-discount subtotal) lift report is strikingly consistent — every one of the 50 worst rows shows negative lift, and the pattern looks structural, not noise. But it's fragmented into 219 rows at the per-tenant-instance grain, which is too granular for the memo's headline number. I want one clean, order-weighted summary per promotion family (Welcome10 / Black Friday / January Sale / Summer Sale) so Marco and Tobias get a single defensible "no incremental lift, if anything smaller baskets" figure rather than a page of tenant fragments.
   3. The returns/refunds report surfaced two outsized ratios — a Welcome10 instance refunding 189% of its own discount cost, and a Volume-tier-10+ instance refunding 162% of its discount cost. Before stating "this discount is actively loss-making after refunds" in the memo, I want to check whether that's driven by a handful of large refunds (an outlier/concentration story, possibly one big account) versus broad-based returns across many orders (a genuine pattern worth capping the discount over).

   These three reports close out Marco's Q3/Q4 and Tobias's Q1/Q2 with concrete, checked numbers for Friday.

   Read the whole plan
   Aggregate within-customer basket-size lift by promotion family, 2025

   Question

   Across all tenants running each major marketing promotion, do customers buy bigger baskets (pre-discount subtotal) when using the code, stated as one clean weighted number per promotion family?

   One row per

   promotion family name: 'Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', 'Summer Sale 2025'

   Columns

   • promotion name
   • discount instances aggregated (distinct discounts.id count across tenants)
   • discounted order count (sum across all instances)
   • distinct customers (sum across instances, not deduplicated across tenants)
   • avg discounted order subtotal, order-weighted (EUR)
   • avg comparison order subtotal (same customers' other non-discounted 2025 orders), order-weighted (EUR)
   • subtotal lift (EUR)
   • subtotal lift (%)
   • % of discounted customers who had at least one comparison order

4. Viktor 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 promo_names AS (
     SELECT name FROM (VALUES
       ('Welcome 10% off'),
       ('Black Friday 2025'),
       ('January Sale 2025'),
       ('Summer Sale 2025')
     ) AS v(name)
   ),
   discounted AS (
     SELECT d.name AS promo_name,
            d.id AS discount_id,
            o.tenant_id,
            o.user_id,
            o.id AS order_id,
            o.subtotal / er.rate AS subtotal_eur
     FROM order_discounts od
     JOIN discounts d ON d.id = od.discount_id AND d.tenant_id = od.tenant_id
     JOIN orders o ON o.id = od.order_id AND o.tenant_id = od.tenant_id
     JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = DATE(o.placed_at)
     WHERE d.name IN (SELECT name FROM promo_names)
       AND o.placed_at >= DATE '2025-01-01'
       AND o.placed_at < DATE '2026-01-01'
       AND o.status <> 'cancelled'
   ),
   discounted_orders AS (
     SELECT promo_name, tenant_id, user_id, order_id, MAX(subtotal_eur) AS subtotal_eur
     FROM discounted
     GROUP BY promo_name, tenant_id, user_id, order_id
   ),
   instance_customers AS (
     SELECT promo_name, discount_id, COUNT(DISTINCT tenant_id || ':' || user_id) AS customers_in_instance
     FROM discounted
     GROUP BY promo_name, discount_id
   ),
   discounted_agg AS (
     SELECT promo_name,
            COUNT(*) AS discounted_order_count,
            SUM(subtotal_eur) AS discounted_subtotal_sum
     FROM discounted_orders
     GROUP BY promo_name
   ),
   instance_agg AS (
     SELECT promo_name,
            COUNT(DISTINCT discount_id) AS discount_instances,
            SUM(customers_in_instance) AS distinct_customers_summed
     FROM instance_customers
     GROUP BY promo_name
   ),
   customer_set AS (
     SELECT DISTINCT promo_name, tenant_id, user_id FROM discounted_orders
   ),
   customer_set_agg AS (
     SELECT promo_name, COUNT(*) AS customer_set_count FROM customer_set GROUP BY promo_name
   ),
   comparison_orders AS (
     SELECT cs.promo_name, cs.tenant_id, cs.user_id, o.id AS order_id,
            o.subtotal / er.rate AS subtotal_eur
     FROM customer_set cs
     JOIN orders o ON o.tenant_id = cs.tenant_id AND o.user_id = cs.user_id
     JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = DATE(o.placed_at)
     WHERE o.placed_at >= DATE '2025-01-01'
       AND o.placed_at < DATE '2026-01-01'
       AND o.status <> 'cancelled'
       AND NOT EXISTS (
         SELECT 1 FROM order_discounts od2
         WHERE od2.tenant_id = o.tenant_id AND od2.order_id = o.id
       )
   ),
   comparison_agg AS (
     SELECT promo_name,
            COUNT(*) AS comparison_order_count,
            SUM(subtotal_eur) AS comparison_subtotal_sum,
            COUNT(DISTINCT tenant_id || ':' || user_id) AS customers_with_comparison
     FROM comparison_orders
     GROUP BY promo_name
   )
   SELECT
     pn.name AS promotion_name,
     COALESCE(ia.discount_instances, 0) AS discount_instances,
     COALESCE(da.discounted_order_count, 0) AS discounted_order_count,
     COALESCE(ia.distinct_customers_summed, 0) AS distinct_customers,
     ROUND(da.discounted_subtotal_sum / NULLIF(da.discounted_order_count,0), 2) AS avg_discounted_order_subtotal_eur,
     ROUND(ca.comparison_subtotal_sum / NULLIF(ca.comparison_order_count,0), 2) AS avg_comparison_order_subtotal_eur,
     ROUND(
       (da.discounted_subtotal_sum / NULLIF(da.discounted_order_count,0))
       - (ca.comparison_subtotal_sum / NULLIF(ca.comparison_order_count,0))
     , 2) AS subtotal_lift_eur,
     ROUND(
       100.0 * (
         (da.discounted_subtotal_sum / NULLIF(da.discounted_order_count,0))
         - (ca.comparison_subtotal_sum / NULLIF(ca.comparison_order_count,0))
       ) / NULLIF(ca.comparison_subtotal_sum / NULLIF(ca.comparison_order_count,0), 0)
     , 1) AS subtotal_lift_pct,
     ROUND(100.0 * COALESCE(ca.customers_with_comparison,0) / NULLIF(cs.customer_set_count,0), 1) AS pct_customers_with_comparison_order
   FROM promo_names pn
   LEFT JOIN instance_agg ia ON ia.promo_name = pn.name
   LEFT JOIN discounted_agg da ON da.promo_name = pn.name
   LEFT JOIN customer_set_agg cs ON cs.promo_name = pn.name
   LEFT JOIN comparison_agg ca ON ca.promo_name = pn.name
   ORDER BY subtotal_lift_pct ASC NULLS LAST, pn.name

   “The query compiles and produces the 9 requested 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: - Discounted orders: order_discounts → discounts (filtered to the 4 promo names) → orders (2025, non-cancelled), deduplicated to one row per (tenant, order) so multi-line discounts don't double-count an order; subtotal converted to EUR via same-day exchange_rates. - Discount instances / distinct customers: counted per discount row (instance) within each promo name, then summed, per the request's "sum across instances" wording. - Comparison orders: for the deduplicated (tenant, customer) set behind each promo, all their other 2025 non-cancelled orders that have no order_discounts row at all (clean, undiscounted baskets). - Lift metrics: order-weighted EUR totals divided by order counts on each side, with lift in EUR and %, plus the share of discounted customers who had at least one comparison order. Noted in the submit note where the "distinct customers" metric (summed per instance) differs from the tenant-deduplicated set used for the comparison-order coverage %, and clarified the "no row in order_discounts" exclusion applies to any discount, not just the promotion in question.”

5. The report comes back overnight

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

   promotion_name    | discount_instances | discounted_order_count | distinct_customers | avg_discounted_order_subtotal_eur | avg_comparison_order_subtotal_eur | subtotal_lift_eur | subtotal_lift_pct | pct_customers_with_comparison_order
   ------------------+--------------------+------------------------+--------------------+-----------------------------------+-----------------------------------+-------------------+-------------------+------------------------------------
   Summer Sale 2025  |                 17 |                    306 |                273 |                            202.92 |                            155.47 |             47.45 |              30.5 |                                84.2
   Black Friday 2025 |                 29 |                    249 |                226 |                            149.79 |                            111.99 |              37.8 |              33.8 |                                73.5
   January Sale 2025 |                 14 |                    209 |                180 |                            184.78 |                            109.65 |             75.13 |              68.5 |                                78.3
   Welcome 10% off   |                 50 |                    714 |                714 |                            343.99 |                            166.09 |             177.9 |             107.1 |                                49.2
   (4 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:

   • q363Incrementality re-cut: order value matched by basket size (item count), 2025

   It builds on:

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

7. It becomes benchmark query q158

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

   • spellingdate cast (2×)
   • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
   • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
   • 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 (5×)
   • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results
   The benchmark text, against the submitted SQL

   WITH promo_names AS (  SELECT name FROM (VALUES    ('Welcome 10% off'),    ('Black Friday 2025'),    ('January Sale 2025'),    ('Summer Sale 2025')  ) AS v(name)),discounted AS (  SELECT d.name AS promo_name,         d.id AS discount_id,         o.tenant_id,         o.user_id,         o.tenant_id AS tenant_id,         o.user_id AS user_id,         o.id AS order_id,         o.subtotal / er.rate AS subtotal_eur         CAST(o.subtotal / er.rate AS NUMERIC(18,6)) AS subtotal_eur  FROM order_discounts od  JOIN discounts d ON d.id = od.discount_id AND d.tenant_id = od.tenant_id  JOIN orders o ON o.id = od.order_id AND o.tenant_id = od.tenant_id  JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = DATE(o.placed_at)  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 d.name IN (SELECT name FROM promo_names)    AND o.placed_at >= DATE '2025-01-01'    AND o.placed_at < DATE '2026-01-01'    AND o.status <> 'cancelled'),discounted_orders AS (  SELECT promo_name, tenant_id, user_id, order_id, MAX(subtotal_eur) AS subtotal_eur  FROM discounted  GROUP BY promo_name, tenant_id, user_id, order_id),instance_customers AS (  SELECT promo_name, discount_id, COUNT(DISTINCT tenant_id || ':' || user_id) AS customers_in_instance  FROM discounted  GROUP BY promo_name, discount_id),discounted_agg AS (  SELECT promo_name,         COUNT(*) AS discounted_order_count,         SUM(subtotal_eur) AS discounted_subtotal_sum  FROM discounted_orders  GROUP BY promo_name),instance_agg AS (  SELECT promo_name,         COUNT(DISTINCT discount_id) AS discount_instances,         SUM(customers_in_instance) AS distinct_customers_summed  FROM instance_customers  GROUP BY promo_name),customer_set AS (  SELECT DISTINCT promo_name, tenant_id, user_id FROM discounted_orders),customer_set_agg AS (  SELECT promo_name, COUNT(*) AS customer_set_count FROM customer_set GROUP BY promo_name),comparison_orders AS (  SELECT cs.promo_name, cs.tenant_id, cs.user_id, o.id AS order_id,         o.subtotal / er.rate AS subtotal_eur  SELECT cs.promo_name AS promo_name, cs.tenant_id AS tenant_id, cs.user_id AS user_id, o.id AS order_id,         CAST(o.subtotal / er.rate AS NUMERIC(18,6)) AS subtotal_eur  FROM customer_set cs  JOIN orders o ON o.tenant_id = cs.tenant_id AND o.user_id = cs.user_id  JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = DATE(o.placed_at)  JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS DATE)  WHERE o.placed_at >= DATE '2025-01-01'    AND o.placed_at < DATE '2026-01-01'    AND o.status <> 'cancelled'    AND NOT EXISTS (      SELECT 1 FROM order_discounts od2      WHERE od2.tenant_id = o.tenant_id AND od2.order_id = o.id    )),comparison_agg AS (  SELECT promo_name,         COUNT(*) AS comparison_order_count,         SUM(subtotal_eur) AS comparison_subtotal_sum,         COUNT(DISTINCT tenant_id || ':' || user_id) AS customers_with_comparison  FROM comparison_orders  GROUP BY promo_name)SELECT  pn.name AS promotion_name,  COALESCE(ia.discount_instances, 0) AS discount_instances,  COALESCE(da.discounted_order_count, 0) AS discounted_order_count,  COALESCE(ia.distinct_customers_summed, 0) AS distinct_customers,  ROUND(da.discounted_subtotal_sum / NULLIF(da.discounted_order_count,0), 2) AS avg_discounted_order_subtotal_eur,  ROUND(ca.comparison_subtotal_sum / NULLIF(ca.comparison_order_count,0), 2) AS avg_comparison_order_subtotal_eur,  ROUND(  da.discounted_subtotal_sum / NULLIF(da.discounted_order_count,0) AS avg_discounted_order_subtotal_eur,  ca.comparison_subtotal_sum / NULLIF(ca.comparison_order_count,0) AS avg_comparison_order_subtotal_eur,  (da.discounted_subtotal_sum / NULLIF(da.discounted_order_count,0))  - (ca.comparison_subtotal_sum / NULLIF(ca.comparison_order_count,0)) AS subtotal_lift_eur,  100.0 * (    (da.discounted_subtotal_sum / NULLIF(da.discounted_order_count,0))    - (ca.comparison_subtotal_sum / NULLIF(ca.comparison_order_count,0))  , 2) AS subtotal_lift_eur,  ROUND(    100.0 * (      (da.discounted_subtotal_sum / NULLIF(da.discounted_order_count,0))      - (ca.comparison_subtotal_sum / NULLIF(ca.comparison_order_count,0))    ) / NULLIF(ca.comparison_subtotal_sum / NULLIF(ca.comparison_order_count,0), 0)  , 1) AS subtotal_lift_pct,  ROUND(100.0 * COALESCE(ca.customers_with_comparison,0) / NULLIF(cs.customer_set_count,0), 1) AS pct_customers_with_comparison_order  ) / NULLIF(ca.comparison_subtotal_sum / NULLIF(ca.comparison_order_count,0), 0) AS subtotal_lift_pct,  100.0 * COALESCE(ca.customers_with_comparison,0) / NULLIF(cs.customer_set_count,0) AS pct_customers_with_comparison_orderFROM promo_names pnLEFT JOIN instance_agg ia ON ia.promo_name = pn.nameLEFT JOIN discounted_agg da ON da.promo_name = pn.nameLEFT JOIN customer_set_agg cs ON cs.promo_name = pn.nameLEFT JOIN comparison_agg ca ON ca.promo_name = pn.nameORDER BY subtotal_lift_pct ASC NULLS LAST, pn.nameORDER BY subtotal_lift_pct ASC NULLS LAST, pn.name NULLS LAST

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

   DuckDB · Hyper · GizmoSQL · CedarDB · pg_duckdb · PostgreSQL · DataFusion · Firebolt · ClickHouse · chDB · clickhouse_brew

   WITH promo_names AS (
     SELECT name FROM (VALUES
       ('Welcome 10% off'),
       ('Black Friday 2025'),
       ('January Sale 2025'),
       ('Summer Sale 2025')
     ) AS v(name)
   ),
   discounted AS (
     SELECT d.name AS promo_name,
            d.id AS discount_id,
            o.tenant_id AS tenant_id,
            o.user_id AS user_id,
            o.id AS order_id,
            CAST(o.subtotal / er.rate AS NUMERIC(18,6)) AS subtotal_eur
     FROM order_discounts od
     JOIN discounts d ON d.id = od.discount_id AND d.tenant_id = od.tenant_id
     JOIN orders o ON o.id = od.order_id AND o.tenant_id = od.tenant_id
     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 d.name IN (SELECT name FROM promo_names)
       AND o.placed_at >= DATE '2025-01-01'
       AND o.placed_at < DATE '2026-01-01'
       AND o.status <> 'cancelled'
   ),
   discounted_orders AS (
     SELECT promo_name, tenant_id, user_id, order_id, MAX(subtotal_eur) AS subtotal_eur
     FROM discounted
     GROUP BY promo_name, tenant_id, user_id, order_id
   ),
   instance_customers AS (
     SELECT promo_name, discount_id, COUNT(DISTINCT tenant_id || ':' || user_id) AS customers_in_instance
     FROM discounted
     GROUP BY promo_name, discount_id
   ),
   discounted_agg AS (
     SELECT promo_name,
            COUNT(*) AS discounted_order_count,
            SUM(subtotal_eur) AS discounted_subtotal_sum
     FROM discounted_orders
     GROUP BY promo_name
   ),
   instance_agg AS (
     SELECT promo_name,
            COUNT(DISTINCT discount_id) AS discount_instances,
            SUM(customers_in_instance) AS distinct_customers_summed
     FROM instance_customers
     GROUP BY promo_name
   ),
   customer_set AS (
     SELECT DISTINCT promo_name, tenant_id, user_id FROM discounted_orders
   ),
   customer_set_agg AS (
     SELECT promo_name, COUNT(*) AS customer_set_count FROM customer_set GROUP BY promo_name
   ),
   comparison_orders AS (
     SELECT cs.promo_name AS promo_name, cs.tenant_id AS tenant_id, cs.user_id AS user_id, o.id AS order_id,
            CAST(o.subtotal / er.rate AS NUMERIC(18,6)) AS subtotal_eur
     FROM customer_set cs
     JOIN orders o ON o.tenant_id = cs.tenant_id AND o.user_id = cs.user_id
     JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS DATE)
     WHERE o.placed_at >= DATE '2025-01-01'
       AND o.placed_at < DATE '2026-01-01'
       AND o.status <> 'cancelled'
       AND NOT EXISTS (
         SELECT 1 FROM order_discounts od2
         WHERE od2.tenant_id = o.tenant_id AND od2.order_id = o.id
       )
   ),
   comparison_agg AS (
     SELECT promo_name,
            COUNT(*) AS comparison_order_count,
            SUM(subtotal_eur) AS comparison_subtotal_sum,
            COUNT(DISTINCT tenant_id || ':' || user_id) AS customers_with_comparison
     FROM comparison_orders
     GROUP BY promo_name
   )
   SELECT
     pn.name AS promotion_name,
     COALESCE(ia.discount_instances, 0) AS discount_instances,
     COALESCE(da.discounted_order_count, 0) AS discounted_order_count,
     COALESCE(ia.distinct_customers_summed, 0) AS distinct_customers,
     da.discounted_subtotal_sum / NULLIF(da.discounted_order_count,0) AS avg_discounted_order_subtotal_eur,
     ca.comparison_subtotal_sum / NULLIF(ca.comparison_order_count,0) AS avg_comparison_order_subtotal_eur,
     (da.discounted_subtotal_sum / NULLIF(da.discounted_order_count,0))
     - (ca.comparison_subtotal_sum / NULLIF(ca.comparison_order_count,0)) AS subtotal_lift_eur,
     100.0 * (
       (da.discounted_subtotal_sum / NULLIF(da.discounted_order_count,0))
       - (ca.comparison_subtotal_sum / NULLIF(ca.comparison_order_count,0))
     ) / NULLIF(ca.comparison_subtotal_sum / NULLIF(ca.comparison_order_count,0), 0) AS subtotal_lift_pct,
     100.0 * COALESCE(ca.customers_with_comparison,0) / NULLIF(cs.customer_set_count,0) AS pct_customers_with_comparison_order
   FROM promo_names pn
   LEFT JOIN instance_agg ia ON ia.promo_name = pn.name
   LEFT JOIN discounted_agg da ON da.promo_name = pn.name
   LEFT JOIN customer_set_agg cs ON cs.promo_name = pn.name
   LEFT JOIN comparison_agg ca ON ca.promo_name = pn.name
   ORDER BY subtotal_lift_pct ASC NULLS LAST, pn.name NULLS LAST

   • Firebolt{ "errors": [ { "description": "Line 15, Column 26: 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": "7bb3a664-c99f-4384-b471-cf37a23db8f5", "query_lab
   • ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table20.subtotal Decimal(14, 4) Decimal64(size = 0), __table21.rate Decimal(12, 6) Decimal64(size = 0). (ARGUMENT_OUT_OF_BOUND)

   Scale factor 1000, c8a.24xlarge, after the last load. q158 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

   Viktor's whole session, 7 turns

   • The request, as Viktor received it

     Report request from Oliver Grant (analyst for the Head of Pricing and Promotions):
     
     Title: Aggregate within-customer basket-size lift by promotion family, 2025
     Question: Across all tenants running each major marketing promotion, do customers buy bigger baskets (pre-discount subtotal) when using the code, stated as one clean weighted number per promotion family?
     Grain: one row per promotion family name: 'Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', 'Summer Sale 2025'
     Columns:
     - promotion name
     - discount instances aggregated (distinct discounts.id count across tenants)
     - discounted order count (sum across all instances)
     - distinct customers (sum across instances, not deduplicated across tenants)
     - avg discounted order subtotal, order-weighted (EUR)
     - avg comparison order subtotal (same customers' other non-discounted 2025 orders), order-weighted (EUR)
     - subtotal lift (EUR)
     - subtotal lift (%)
     - % of discounted customers who had at least one comparison order
     Details: Source: order_discounts + discounts (name in the four promotion names above, across all tenants/instances) + orders (subtotal, currency_code, placed_at in 2025, status != 'cancelled', user_id, tenant_id). For each promotion name, sum subtotal_eur and count across all discounted orders tenant-wide to get the order-weighted average (i.e. total EUR / total orders, not an average of per-tenant averages). For comparison, for every (user_id, tenant_id) that appears as a discounted customer under that promotion name, pull all their other 2025 orders for that tenant with no row in order_discounts, and similarly sum subtotal_eur and count across all of them tenant-wide for the order-weighted comparison average. Convert to EUR via exchange_rates (base_code='EUR', quote_code=currency_code, as_of=placed_at). Order by subtotal lift (%) ascending. Print all 4 rows.
     
     Check your query with the warehouse's dry_run tool, then hand it in with submit.

   • Viktor calls dry_run

     WITH promo_names AS (
       SELECT name FROM (VALUES
         ('Welcome 10% off'),
         ('Black Friday 2025'),
         ('January Sale 2025'),
         ('Summer Sale 2025')
       ) AS v(name)
     ),
     discounted AS (
       SELECT d.name AS promo_name,
              d.id AS discount_id,
              o.tenant_id,
              o.user_id,
              o.id AS order_id,
              o.subtotal / er.rate AS subtotal_eur
       FROM order_discounts od
       JOIN discounts d ON d.id = od.discount_id AND d.tenant_id = od.tenant_id
       JOIN orders o ON o.id = od.order_id AND o.tenant_id = od.tenant_id
       JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = DATE(o.placed_at)
       WHERE d.name IN (SELECT name FROM promo_names)
         AND o.placed_at >= DATE '2025-01-01'
         AND o.placed_at < DATE '2026-01-01'
         AND o.status <> 'cancelled'
     ),
     discounted_orders AS (
       SELECT promo_name, tenant_id, user_id, order_id, MAX(subtotal_eur) AS subtotal_eur
       FROM discounted
       GROUP BY promo_name, tenant_id, user_id, order_id
     ),
     instance_customers AS (
       SELECT promo_name, discount_id, COUNT(DISTINCT tenant_id || ':' || user_id) AS customers_in_instance
       FROM discounted
       GROUP BY promo_name, discount_id
     ),
     discounted_agg AS (
       SELECT promo_name,
              COUNT(*) AS discounted_order_count,
              SUM(subtotal_eur) AS discounted_subtotal_sum
       FROM discounted_orders
       GROUP BY promo_name
     ),
     instance_agg AS (
       SELECT promo_name,
              COUNT(DISTINCT discount_id) AS discount_instances,
              SUM(customers_in_instance) AS distinct_customers_summed
       FROM instance_customers
       GROUP BY promo_name
     ),
     customer_set AS (
       SELECT DISTINCT promo_name, tenant_id, user_id FROM discounted_orders
     ),
     customer_set_agg AS (
       SELECT promo_name, COUNT(*) AS customer_set_count FROM customer_set GROUP BY promo_name
     ),
     comparison_orders AS (
       SELECT cs.promo_name, cs.tenant_id, cs.user_id, o.id AS order_id,
              o.subtotal / er.rate AS subtotal_eur
       FROM customer_set cs
       JOIN orders o ON o.tenant_id = cs.tenant_id AND o.user_id = cs.user_id
       JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = DATE(o.placed_at)
       WHERE o.placed_at >= DATE '2025-01-01'
         AND o.placed_at < DATE '2026-01-01'
         AND o.status <> 'cancelled'
         AND NOT EXISTS (
           SELECT 1 FROM order_discounts od2
           WHERE od2.tenant_id = o.tenant_id AND od2.order_id = o.id
         )
     ),
     comparison_agg AS (
       SELECT promo_name,
              COUNT(*) AS comparison_order_count,
              SUM(subtotal_eur) AS comparison_subtotal_sum,
              COUNT(DISTINCT tenant_id || ':' || user_id) AS customers_with_comparison
       FROM comparison_orders
       GROUP BY promo_name
     )
     SELECT
       pn.name AS promotion_name,
       COALESCE(ia.discount_instances, 0) AS discount_instances,
       COALESCE(da.discounted_order_count, 0) AS discounted_order_count,
       COALESCE(ia.distinct_customers_summed, 0) AS distinct_customers,
       ROUND(da.discounted_subtotal_sum / NULLIF(da.discounted_order_count,0), 2) AS avg_discounted_order_subtotal_eur,
       ROUND(ca.comparison_subtotal_sum / NULLIF(ca.comparison_order_count,0), 2) AS avg_comparison_order_subtotal_eur,
       ROUND(
         (da.discounted_subtotal_sum / NULLIF(da.discounted_order_count,0))
         - (ca.comparison_subtotal_sum / NULLIF(ca.comparison_order_count,0))
       , 2) AS subtotal_lift_eur,
       ROUND(
         100.0 * (
           (da.discounted_subtotal_sum / NULLIF(da.discounted_order_count,0))
           - (ca.comparison_subtotal_sum / NULLIF(ca.comparison_order_count,0))
         ) / NULLIF(ca.comparison_subtotal_sum / NULLIF(ca.comparison_order_count,0), 0)
       , 1) AS subtotal_lift_pct,
       ROUND(100.0 * COALESCE(ca.customers_with_comparison,0) / NULLIF(cs.customer_set_count,0), 1) AS pct_customers_with_comparison_order
     FROM promo_names pn
     LEFT JOIN instance_agg ia ON ia.promo_name = pn.name
     LEFT JOIN discounted_agg da ON da.promo_name = pn.name
     LEFT JOIN customer_set_agg cs ON cs.promo_name = pn.name
     LEFT JOIN comparison_agg ca ON ca.promo_name = pn.name
     ORDER BY subtotal_lift_pct ASC NULLS LAST, pn.name

   • The warehouse answers

     STATUS: COMPILES (dry run on the empty schema, no data read)
     COLUMNS: promotion_name, discount_instances, discounted_order_count, distinct_customers, avg_discounted_order_subtotal_eur, avg_comparison_order_subtotal_eur, subtotal_lift_eur, subtotal_lift_pct, pct_customers_with_comparison_order

   • ViktorThe query compiles and produces the 9 requested columns. Submitting.
   • Viktor calls submit

     WITH promo_names AS (
       SELECT name FROM (VALUES
         ('Welcome 10% off'),
         ('Black Friday 2025'),
         ('January Sale 2025'),
         ('Summer Sale 2025')
       ) AS v(name)
     ),
     discounted AS (
       SELECT d.name AS promo_name,
              d.id AS discount_id,
              o.tenant_id,
              o.user_id,
              o.id AS order_id,
              o.subtotal / er.rate AS subtotal_eur
       FROM order_discounts od
       JOIN discounts d ON d.id = od.discount_id AND d.tenant_id = od.tenant_id
       JOIN orders o ON o.id = od.order_id AND o.tenant_id = od.tenant_id
       JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = DATE(o.placed_at)
       WHERE d.name IN (SELECT name FROM promo_names)
         AND o.placed_at >= DATE '2025-01-01'
         AND o.placed_at < DATE '2026-01-01'
         AND o.status <> 'cancelled'
     ),
     discounted_orders AS (
       SELECT promo_name, tenant_id, user_id, order_id, MAX(subtotal_eur) AS subtotal_eur
       FROM discounted
       GROUP BY promo_name, tenant_id, user_id, order_id
     ),
     instance_customers AS (
       SELECT promo_name, discount_id, COUNT(DISTINCT tenant_id || ':' || user_id) AS customers_in_instance
       FROM discounted
       GROUP BY promo_name, discount_id
     ),
     discounted_agg AS (
       SELECT promo_name,
              COUNT(*) AS discounted_order_count,
              SUM(subtotal_eur) AS discounted_subtotal_sum
       FROM discounted_orders
       GROUP BY promo_name
     ),
     instance_agg AS (
       SELECT promo_name,
              COUNT(DISTINCT discount_id) AS discount_instances,
              SUM(customers_in_instance) AS distinct_customers_summed
       FROM instance_customers
       GROUP BY promo_name
     ),
     customer_set AS (
       SELECT DISTINCT promo_name, tenant_id, user_id FROM discounted_orders
     ),
     customer_set_agg AS (
       SELECT promo_name, COUNT(*) AS customer_set_count FROM customer_set GROUP BY promo_name
     ),
     comparison_orders AS (
       SELECT cs.promo_name, cs.tenant_id, cs.user_id, o.id AS order_id,
              o.subtotal / er.rate AS subtotal_eur
       FROM customer_set cs
       JOIN orders o ON o.tenant_id = cs.tenant_id AND o.user_id = cs.user_id
       JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = DATE(o.placed_at)
       WHERE o.placed_at >= DATE '2025-01-01'
         AND o.placed_at < DATE '2026-01-01'
         AND o.status <> 'cancelled'
         AND NOT EXISTS (
           SELECT 1 FROM order_discounts od2
           WHERE od2.tenant_id = o.tenant_id AND od2.order_id = o.id
         )
     ),
     comparison_agg AS (
       SELECT promo_name,
              COUNT(*) AS comparison_order_count,
              SUM(subtotal_eur) AS comparison_subtotal_sum,
              COUNT(DISTINCT tenant_id || ':' || user_id) AS customers_with_comparison
       FROM comparison_orders
       GROUP BY promo_name
     )
     SELECT
       pn.name AS promotion_name,
       COALESCE(ia.discount_instances, 0) AS discount_instances,
       COALESCE(da.discounted_order_count, 0) AS discounted_order_count,
       COALESCE(ia.distinct_customers_summed, 0) AS distinct_customers,
       ROUND(da.discounted_subtotal_sum / NULLIF(da.discounted_order_count,0), 2) AS avg_discounted_order_subtotal_eur,
       ROUND(ca.comparison_subtotal_sum / NULLIF(ca.comparison_order_count,0), 2) AS avg_comparison_order_subtotal_eur,
       ROUND(
         (da.discounted_subtotal_sum / NULLIF(da.discounted_order_count,0))
         - (ca.comparison_subtotal_sum / NULLIF(ca.comparison_order_count,0))
       , 2) AS subtotal_lift_eur,
       ROUND(
         100.0 * (
           (da.discounted_subtotal_sum / NULLIF(da.discounted_order_count,0))
           - (ca.comparison_subtotal_sum / NULLIF(ca.comparison_order_count,0))
         ) / NULLIF(ca.comparison_subtotal_sum / NULLIF(ca.comparison_order_count,0), 0)
       , 1) AS subtotal_lift_pct,
       ROUND(100.0 * COALESCE(ca.customers_with_comparison,0) / NULLIF(cs.customer_set_count,0), 1) AS pct_customers_with_comparison_order
     FROM promo_names pn
     LEFT JOIN instance_agg ia ON ia.promo_name = pn.name
     LEFT JOIN discounted_agg da ON da.promo_name = pn.name
     LEFT JOIN customer_set_agg cs ON cs.promo_name = pn.name
     LEFT JOIN comparison_agg ca ON ca.promo_name = pn.name
     ORDER BY subtotal_lift_pct ASC NULLS LAST, pn.name

   • The warehouse answers

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

   • ViktorSubmitted. Summary of the approach: - **Discounted orders**: `order_discounts` → `discounts` (filtered to the 4 promo names) → `orders` (2025, non-cancelled), deduplicated to one row per (tenant, order) so multi-line discounts don't double-count an order; subtotal converted to EUR via same-day `exchange_rates`. - **Discount instances / distinct customers**: counted per discount row (instance) within each promo name, then summed, per the request's "sum across instances" wording. - **Comparison orders**: for the deduplicated (tenant, customer) set behind each promo, all their other 2025 non-cancelled orders that have **no** `order_discounts` row at all (clean, undiscounted baskets). - **Lift metrics**: order-weighted EUR totals divided by order counts on each side, with lift in EUR and %, plus the share of discounted customers who had at least one comparison order. Noted in the submit note where the "distinct customers" metric (summed per instance) differs from the tenant-deduplicated set used for the comparison-order coverage %, and clarified the "no row in order_discounts" exclusion applies to any discount, not just the promotion in question.

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

   Viktor's transcript (298 KB) · Oliver Grant's transcript (972 KB) · Marco Bianchi's transcript (259 KB) · the night's plan (7 KB) · the night's reports (5 KB) · the week's memo (3 KB)