Queries · q363 · Head of Pricing and Promotions · week 2, night 2

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

“Controlling for how many distinct items are in the order, do discounted orders (our four main marketing promotions) still show a higher average order value than non-discounted orders, or does the earlier lift mostly reflect that discounts land on already-bigger baskets?”

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 2
Subject: Good work — here's where I want to go next

Oliver,

This is exactly the kind of memo I needed — it separates real findings from artifacts, which is rarer than it should be. A few reactions and where I want to push next week.

The lapsed-subscriber giveaway (~€57k) is now the headline for Tobias, not the promo/coupon story — good catch pulling it out. Before I take that number upstairs, I want it airtight. And the Welcome10 broad refund pattern is the one piece of "margin erosion" I think is genuinely actionable rather than a sample-size illusion, so I want to go a layer deeper there before we talk about capping anything.

On incrementality — I accept we may never get a clean causal answer here, but I'd like one more honest attempt before I write it off as "probably just bigger carts clear the threshold anyway."

Questions for this week:
1. Is the active-but-orphan 18% a timing bug or a real gap? I need to know whether the full €57k-ish exposure is confirmed leakage or whether part of it shrinks once the overlap check is fixed properly.
2. What's actually driving Welcome10's refund pattern? Is it concentrated in particular products, categories, or merchants, or does it look like people discount-shopping and then returning — i.e., is this a fit/expectation problem or a "discount attracts returners" problem?
3. Can we get a cleaner incrementality read by comparing similar-sized orders with and without the discount, rather than comparing discounted vs. all orders? I'd rather have a more honest "we still can't tell" than a flattering but confounded number.
4. For the lapsed-subscriber leakage, how far back does it go and is it accelerating or steady? I want to know if this is a slow-burning historical gap or something that's gotten worse recently, since that changes how urgently we need to fix the underlying process versus just write off the past.
No need to re-litigate the B2B volume-tier outlier — just keep it excluded as you planned. Let's talk Wednesday before the last batch so I can steer based on what the orphan-subscription check turns up.

Thanks, Marco
Oliver Grant plans the night
The analyst · week 2, night 2, request 2 of 3
Night 1 raised one real problem and gave two solid answers:

Report 1 (the 'active' bucket breakdown) came back broken: only 1 of the 3 categories printed (88 orders, 30.2%), leaving 203 of the 291 orders unaccounted for entirely rather than showing 0-count rows. That smells like an inner-join dropping orders that had no matching 'older subscription' row — i.e. the 'preceded first-ever subscription' case was silently excluded rather than counted as zero. I can't hand Marco a number built on a query that's missing 70% of its own population, so this is priority one tonight: re-run with explicit NOT EXISTS/LEFT JOIN logic and a self-check total that must sum to 291.

Report 2 is a genuinely good, clean answer to Marco's trend question: leakage rose from ~€200/month in 2023 to a 2024 peak (~€2,000-3,300/month, Feb-Oct 2024), then has declined every month since, down to €819 in Nov 2025 and €0 in Dec 2025. This is the opposite of accelerating — it looks like a historical, now-shrinking gap. I want one more cut — by tenant — before I tell Marco it's broadly resolved, in case the decline is really just one or two tenants fixing something while others stay flat (which would change the "how urgent" answer).

Report 3 (Welcome10 drivers) shows returns spread across many apparel/footwear categories (Outerwear and Boots are the largest single contributors but no runaway concentration), with 9-19 day order-to-return gaps — consistent with normal apparel fit/try-and-return behavior, not quick-flip abuse. Combined with last week's finding that this instance's return rate is only +2.3pp above this tenant's own baseline (which is already ~37%), I think the right framing is shifting: refund-exceeding-discount-cost looks largely mechanical for any % discount at a high-baseline-return apparel merchant, not something the coupon itself is causing. I don't think this needs another report — I'll fold it into the memo with that caveat.

Tonight's three slots: (1) fix the broken categorization query, (2) the size-matched incrementality re-cut Marco asked for, (3) a tenant-level cut of the leakage trend to confirm the decline is broad-based before I call it resolved.
Incrementality re-cut: order value matched by basket size (item count), 2025
Question
Controlling for how many distinct items are in the order, do discounted orders (our four main marketing promotions) still show a higher average order value than non-discounted orders, or does the earlier lift mostly reflect that discounts land on already-bigger baskets?
One row per
item-count bucket (1 item, 2 items, 3 items, 4 items, 5+ items)
Columns
- item count bucket
- discounted order count (orders in 2025 carrying 'Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', or 'Summer Sale 2025')
- avg discounted order subtotal, pre-discount (EUR)
- non-discounted order count (same tenants' other 2025 orders with no row in order_discounts)
- avg non-discounted order subtotal (EUR)
- lift (EUR)
- lift (%)

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
  CASE
    WHEN buckets.bucket_num = 1 THEN '1 item'
    WHEN buckets.bucket_num = 2 THEN '2 items'
    WHEN buckets.bucket_num = 3 THEN '3 items'
    WHEN buckets.bucket_num = 4 THEN '4 items'
    ELSE '5+ items'
  END AS item_count_bucket,
  COALESCE(disc.discounted_order_count, 0) AS discounted_order_count,
  ROUND(disc.avg_subtotal_eur, 2) AS avg_discounted_subtotal_eur_pre_discount,
  COALESCE(nondisc.nondiscounted_order_count, 0) AS nondiscounted_order_count,
  ROUND(nondisc.avg_subtotal_eur, 2) AS avg_nondiscounted_subtotal_eur,
  ROUND(disc.avg_subtotal_eur - nondisc.avg_subtotal_eur, 2) AS lift_eur,
  ROUND(100.0 * (disc.avg_subtotal_eur - nondisc.avg_subtotal_eur) / NULLIF(nondisc.avg_subtotal_eur, 0), 2) AS lift_pct
FROM (SELECT 1 AS bucket_num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) buckets
LEFT JOIN (
  SELECT
    CASE WHEN item_count >= 5 THEN 5 ELSE item_count END AS bucket_num,
    COUNT(*) AS discounted_order_count,
    AVG(eur_subtotal) AS avg_subtotal_eur
  FROM (
    SELECT
      o.id,
      (SELECT COUNT(*) FROM order_items oi WHERE oi.order_id = o.id) AS item_count,
      CASE WHEN o.currency_code = 'EUR' THEN o.subtotal
           ELSE o.subtotal / (SELECT er.rate FROM exchange_rates er WHERE er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS DATE))
      END AS eur_subtotal
    FROM orders o
    WHERE o.status <> 'cancelled'
      AND o.placed_at >= DATE '2025-01-01'
      AND o.placed_at < DATE '2026-01-01'
      AND EXISTS (
        SELECT 1
        FROM order_discounts od
        LEFT JOIN discounts d1 ON od.discount_id = d1.id
        LEFT JOIN coupons cp ON od.coupon_id = cp.id
        LEFT JOIN discounts d2 ON cp.discount_id = d2.id
        WHERE od.order_id = o.id
          AND COALESCE(d1.name, d2.name) IN ('Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', 'Summer Sale 2025')
      )
  ) disc_orders
  GROUP BY CASE WHEN item_count >= 5 THEN 5 ELSE item_count END
) disc ON disc.bucket_num = buckets.bucket_num
LEFT JOIN (
  SELECT
    CASE WHEN item_count >= 5 THEN 5 ELSE item_count END AS bucket_num,
    COUNT(*) AS nondiscounted_order_count,
    AVG(eur_subtotal) AS avg_subtotal_eur
  FROM (
    SELECT
      o.id,
      (SELECT COUNT(*) FROM order_items oi WHERE oi.order_id = o.id) AS item_count,
      CASE WHEN o.currency_code = 'EUR' THEN o.subtotal
           ELSE o.subtotal / (SELECT er.rate FROM exchange_rates er WHERE er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS DATE))
      END AS eur_subtotal
    FROM orders o
    WHERE o.status <> 'cancelled'
      AND o.placed_at >= DATE '2025-01-01'
      AND o.placed_at < DATE '2026-01-01'
      AND o.tenant_id IN (
        SELECT DISTINCT o2.tenant_id
        FROM orders o2
        WHERE o2.status <> 'cancelled'
          AND o2.placed_at >= DATE '2025-01-01'
          AND o2.placed_at < DATE '2026-01-01'
          AND EXISTS (
            SELECT 1
            FROM order_discounts od2
            LEFT JOIN discounts d1b ON od2.discount_id = d1b.id
            LEFT JOIN coupons cp2 ON od2.coupon_id = cp2.id
            LEFT JOIN discounts d2b ON cp2.discount_id = d2b.id
            WHERE od2.order_id = o2.id
              AND COALESCE(d1b.name, d2b.name) IN ('Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', 'Summer Sale 2025')
          )
      )
      AND NOT EXISTS (
        SELECT 1 FROM order_discounts od3 WHERE od3.order_id = o.id
      )
  ) nondisc_orders
  GROUP BY CASE WHEN item_count >= 5 THEN 5 ELSE item_count END
) nondisc ON nondisc.bucket_num = buckets.bucket_num
ORDER BY buckets.bucket_num

“Query compiles with 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: - Discounted side: orders from 2025 (status ≠ cancelled) that carry at least one of the four named promotions in order_discounts (matched either directly via discount_id or through a coupon's discount_id), bucketed by distinct order_items row count (1/2/3/4/5+), with subtotal converted to EUR via same-day exchange_rates. - Non-discounted side: other 2025 orders (status ≠ cancelled) from the same tenants that ran any of those four promotions, but with zero rows in order_discounts at all, bucketed the same way. - Reports order counts, average EUR subtotal for each side, and the absolute/percentage lift, one row per bucket, all 5 buckets always shown. One judgment call flagged in the submit note: I treated "no row in order_discounts" literally (any discount excludes an order from the comparison group, not just the four named ones), and matched promotion names through both the direct and coupon-linked discount paths to be safe.”

The report comes back overnight

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

item_count_bucket | discounted_order_count | avg_discounted_subtotal_eur_pre_discount | nondiscounted_order_count | avg_nondiscounted_subtotal_eur | lift_eur | lift_pct
------------------+------------------------+------------------------------------------+---------------------------+--------------------------------+----------+---------
1 item            |                    820 |                                   219.14 |                      6079 |                         100.93 |   118.21 |   117.11
2 items           |                    683 |                                   214.72 |                      3571 |                         154.43 |     60.3 |    39.05
3 items           |                    436 |                                   267.51 |                      2091 |                         237.53 |    29.98 |    12.62
4 items           |                    258 |                                   357.63 |                       989 |                         287.21 |    70.42 |    24.52
5+ items          |                    161 |                                   719.98 |                       595 |                         429.37 |   290.61 |    67.68
(5 rows)

What came of it
The memo and the queries that followed; links found by title, so inferred
Oliver Grant's memo to Marco Bianchi at the end of the week:
- 1. Incrementality holds up under a harder test. Controlling for basket size (number of distinct items, not price), discounted orders still show higher value than non-discounted orders of the same item count — lift ranges from +13% (3-item orders) to +117% (1-item orders), positive in all 5 size buckets ("Incrementality re-cut: order value matched by basket size"). This survives the strongest control we've applied. Confidence: moderate-high — still correlational (heavier spenders may simply be more likely to use codes), but no longer explained away by "coupons just land on bigger carts."
It builds on:
- q158Aggregate within-customer basket-size lift by promotion family, 2025

It becomes benchmark query q363

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

numericsdocs/NUMERICS.md: rounding removed; results keep full precision
numericsdocs/NUMERICS.md: rounding removed; results keep full precision
made explicita selected t.col gets AS col, its standard name made explicit (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

The benchmark text, against the submitted SQL

SELECT  CASE    WHEN buckets.bucket_num = 1 THEN '1 item'    WHEN buckets.bucket_num = 2 THEN '2 items'    WHEN buckets.bucket_num = 3 THEN '3 items'    WHEN buckets.bucket_num = 4 THEN '4 items'    ELSE '5+ items'  END AS item_count_bucket,  COALESCE(disc.discounted_order_count, 0) AS discounted_order_count,  ROUND(disc.avg_subtotal_eur, 2) AS avg_discounted_subtotal_eur_pre_discount,  disc.avg_subtotal_eur AS avg_discounted_subtotal_eur_pre_discount,  COALESCE(nondisc.nondiscounted_order_count, 0) AS nondiscounted_order_count,  ROUND(nondisc.avg_subtotal_eur, 2) AS avg_nondiscounted_subtotal_eur,  ROUND(disc.avg_subtotal_eur - nondisc.avg_subtotal_eur, 2) AS lift_eur,  ROUND(100.0 * (disc.avg_subtotal_eur - nondisc.avg_subtotal_eur) / NULLIF(nondisc.avg_subtotal_eur, 0), 2) AS lift_pct  nondisc.avg_subtotal_eur AS avg_nondiscounted_subtotal_eur,  disc.avg_subtotal_eur - nondisc.avg_subtotal_eur AS lift_eur,  100.0 * (disc.avg_subtotal_eur - nondisc.avg_subtotal_eur) / NULLIF(nondisc.avg_subtotal_eur, 0) AS lift_pctFROM (SELECT 1 AS bucket_num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) bucketsLEFT JOIN (  SELECT    CASE WHEN item_count >= 5 THEN 5 ELSE item_count END AS bucket_num,    COUNT(*) AS discounted_order_count,    AVG(eur_subtotal) AS avg_subtotal_eur  FROM (    SELECT      o.id,      o.id AS id,      (SELECT COUNT(*) FROM order_items oi WHERE oi.order_id = o.id) AS item_count,      CASE WHEN o.currency_code = 'EUR' THEN o.subtotal           ELSE o.subtotal / (SELECT er.rate FROM exchange_rates er WHERE er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS DATE))      END AS eur_subtotal    FROM orders o    WHERE o.status <> 'cancelled'      AND o.placed_at >= DATE '2025-01-01'      AND o.placed_at < DATE '2026-01-01'      AND EXISTS (        SELECT 1        FROM order_discounts od        LEFT JOIN discounts d1 ON od.discount_id = d1.id        LEFT JOIN coupons cp ON od.coupon_id = cp.id        LEFT JOIN discounts d2 ON cp.discount_id = d2.id        WHERE od.order_id = o.id          AND COALESCE(d1.name, d2.name) IN ('Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', 'Summer Sale 2025')      )  ) disc_orders  GROUP BY CASE WHEN item_count >= 5 THEN 5 ELSE item_count END) disc ON disc.bucket_num = buckets.bucket_numLEFT JOIN (  SELECT    CASE WHEN item_count >= 5 THEN 5 ELSE item_count END AS bucket_num,    COUNT(*) AS nondiscounted_order_count,    AVG(eur_subtotal) AS avg_subtotal_eur  FROM (    SELECT      o.id,      o.id AS id,      (SELECT COUNT(*) FROM order_items oi WHERE oi.order_id = o.id) AS item_count,      CASE WHEN o.currency_code = 'EUR' THEN o.subtotal           ELSE o.subtotal / (SELECT er.rate FROM exchange_rates er WHERE er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS DATE))      END AS eur_subtotal    FROM orders o    WHERE o.status <> 'cancelled'      AND o.placed_at >= DATE '2025-01-01'      AND o.placed_at < DATE '2026-01-01'      AND o.tenant_id IN (        SELECT DISTINCT o2.tenant_id        SELECT DISTINCT o2.tenant_id AS tenant_id        FROM orders o2        WHERE o2.status <> 'cancelled'          AND o2.placed_at >= DATE '2025-01-01'          AND o2.placed_at < DATE '2026-01-01'          AND EXISTS (            SELECT 1            FROM order_discounts od2            LEFT JOIN discounts d1b ON od2.discount_id = d1b.id            LEFT JOIN coupons cp2 ON od2.coupon_id = cp2.id            LEFT JOIN discounts d2b ON cp2.discount_id = d2b.id            WHERE od2.order_id = o2.id              AND COALESCE(d1b.name, d2b.name) IN ('Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', 'Summer Sale 2025')          )      )      AND NOT EXISTS (        SELECT 1 FROM order_discounts od3 WHERE od3.order_id = o.id      )  ) nondisc_orders  GROUP BY CASE WHEN item_count >= 5 THEN 5 ELSE item_count END) nondisc ON nondisc.bucket_num = buckets.bucket_numORDER BY buckets.bucket_numORDER BY buckets.bucket_num NULLS LAST

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

SELECT
  CASE
    WHEN buckets.bucket_num = 1 THEN '1 item'
    WHEN buckets.bucket_num = 2 THEN '2 items'
    WHEN buckets.bucket_num = 3 THEN '3 items'
    WHEN buckets.bucket_num = 4 THEN '4 items'
    ELSE '5+ items'
  END AS item_count_bucket,
  COALESCE(disc.discounted_order_count, 0) AS discounted_order_count,
  disc.avg_subtotal_eur AS avg_discounted_subtotal_eur_pre_discount,
  COALESCE(nondisc.nondiscounted_order_count, 0) AS nondiscounted_order_count,
  nondisc.avg_subtotal_eur AS avg_nondiscounted_subtotal_eur,
  disc.avg_subtotal_eur - nondisc.avg_subtotal_eur AS lift_eur,
  100.0 * (disc.avg_subtotal_eur - nondisc.avg_subtotal_eur) / NULLIF(nondisc.avg_subtotal_eur, 0) AS lift_pct
FROM (SELECT 1 AS bucket_num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) buckets
LEFT JOIN (
  SELECT
    CASE WHEN item_count >= 5 THEN 5 ELSE item_count END AS bucket_num,
    COUNT(*) AS discounted_order_count,
    AVG(eur_subtotal) AS avg_subtotal_eur
  FROM (
    SELECT
      o.id AS id,
      (SELECT COUNT(*) FROM order_items oi WHERE oi.order_id = o.id) AS item_count,
      CASE WHEN o.currency_code = 'EUR' THEN o.subtotal
           ELSE o.subtotal / (SELECT er.rate FROM exchange_rates er WHERE er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS DATE))
      END AS eur_subtotal
    FROM orders o
    WHERE o.status <> 'cancelled'
      AND o.placed_at >= DATE '2025-01-01'
      AND o.placed_at < DATE '2026-01-01'
      AND EXISTS (
        SELECT 1
        FROM order_discounts od
        LEFT JOIN discounts d1 ON od.discount_id = d1.id
        LEFT JOIN coupons cp ON od.coupon_id = cp.id
        LEFT JOIN discounts d2 ON cp.discount_id = d2.id
        WHERE od.order_id = o.id
          AND COALESCE(d1.name, d2.name) IN ('Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', 'Summer Sale 2025')
      )
  ) disc_orders
  GROUP BY CASE WHEN item_count >= 5 THEN 5 ELSE item_count END
) disc ON disc.bucket_num = buckets.bucket_num
LEFT JOIN (
  SELECT
    CASE WHEN item_count >= 5 THEN 5 ELSE item_count END AS bucket_num,
    COUNT(*) AS nondiscounted_order_count,
    AVG(eur_subtotal) AS avg_subtotal_eur
  FROM (
    SELECT
      o.id AS id,
      (SELECT COUNT(*) FROM order_items oi WHERE oi.order_id = o.id) AS item_count,
      CASE WHEN o.currency_code = 'EUR' THEN o.subtotal
           ELSE o.subtotal / (SELECT er.rate FROM exchange_rates er WHERE er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS DATE))
      END AS eur_subtotal
    FROM orders o
    WHERE o.status <> 'cancelled'
      AND o.placed_at >= DATE '2025-01-01'
      AND o.placed_at < DATE '2026-01-01'
      AND o.tenant_id IN (
        SELECT DISTINCT o2.tenant_id AS tenant_id
        FROM orders o2
        WHERE o2.status <> 'cancelled'
          AND o2.placed_at >= DATE '2025-01-01'
          AND o2.placed_at < DATE '2026-01-01'
          AND EXISTS (
            SELECT 1
            FROM order_discounts od2
            LEFT JOIN discounts d1b ON od2.discount_id = d1b.id
            LEFT JOIN coupons cp2 ON od2.coupon_id = cp2.id
            LEFT JOIN discounts d2b ON cp2.discount_id = d2b.id
            WHERE od2.order_id = o2.id
              AND COALESCE(d1b.name, d2b.name) IN ('Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', 'Summer Sale 2025')
          )
      )
      AND NOT EXISTS (
        SELECT 1 FROM order_discounts od3 WHERE od3.order_id = o.id
      )
  ) nondisc_orders
  GROUP BY CASE WHEN item_count >= 5 THEN 5 ELSE item_count END
) nondisc ON nondisc.bucket_num = buckets.bucket_num
ORDER BY buckets.bucket_num NULLS LAST

Firebolt{ "errors": [ { "description": "Line 26, Column 28: 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": "ab4a8cf0-cd72-4ff7-a3d3-6f1fd4290b08", "query_lab
DataFusionError during planning: Correlated scalar subquery must be aggregated to return at most one row
ClickHouseCode: 48. DB::Exception: Current query is not supported yet, because can't find correlated column '__table18.id' in current header: __table18.currency_code, __table18.subtotal. (NOT_IMPLEMENTED)

Scale factor 1000, c8a.24xlarge, after the last load. q363 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: Incrementality re-cut: order value matched by basket size (item count), 2025
Question: Controlling for how many distinct items are in the order, do discounted orders (our four main marketing promotions) still show a higher average order value than non-discounted orders, or does the earlier lift mostly reflect that discounts land on already-bigger baskets?
Grain: one row per item-count bucket (1 item, 2 items, 3 items, 4 items, 5+ items)
Columns:
- item count bucket
- discounted order count (orders in 2025 carrying 'Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', or 'Summer Sale 2025')
- avg discounted order subtotal, pre-discount (EUR)
- non-discounted order count (same tenants' other 2025 orders with no row in order_discounts)
- avg non-discounted order subtotal (EUR)
- lift (EUR)
- lift (%)
Details: Source: order_discounts + discounts (name in the four promotion names) + orders (status != 'cancelled', placed_at in 2025, currency_code, tenant_id, user_id) for the discounted side; item count = count of distinct order_items rows per order (not quantity sum). For the comparison side, restrict to the same set of tenants that ran at least one of these four discounts in 2025, and use their other 2025 orders with no row in order_discounts, bucketed the same way by order_items count. Convert subtotal to EUR via exchange_rates (base_code='EUR', quote_code=currency_code, as_of=placed_at). Group orders with 5 or more items into a single '5+' bucket. Order by item count bucket ascending. Print all 5 buckets.

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

Felix calls dry_run

SELECT
  CASE
    WHEN buckets.bucket_num = 1 THEN '1 item'
    WHEN buckets.bucket_num = 2 THEN '2 items'
    WHEN buckets.bucket_num = 3 THEN '3 items'
    WHEN buckets.bucket_num = 4 THEN '4 items'
    ELSE '5+ items'
  END AS item_count_bucket,
  COALESCE(disc.discounted_order_count, 0) AS discounted_order_count,
  ROUND(disc.avg_subtotal_eur, 2) AS avg_discounted_subtotal_eur_pre_discount,
  COALESCE(nondisc.nondiscounted_order_count, 0) AS nondiscounted_order_count,
  ROUND(nondisc.avg_subtotal_eur, 2) AS avg_nondiscounted_subtotal_eur,
  ROUND(disc.avg_subtotal_eur - nondisc.avg_subtotal_eur, 2) AS lift_eur,
  ROUND(100.0 * (disc.avg_subtotal_eur - nondisc.avg_subtotal_eur) / NULLIF(nondisc.avg_subtotal_eur, 0), 2) AS lift_pct
FROM (SELECT 1 AS bucket_num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) buckets
LEFT JOIN (
  SELECT
    CASE WHEN item_count >= 5 THEN 5 ELSE item_count END AS bucket_num,
    COUNT(*) AS discounted_order_count,
    AVG(eur_subtotal) AS avg_subtotal_eur
  FROM (
    SELECT
      o.id,
      (SELECT COUNT(*) FROM order_items oi WHERE oi.order_id = o.id) AS item_count,
      CASE WHEN o.currency_code = 'EUR' THEN o.subtotal
           ELSE o.subtotal / (SELECT er.rate FROM exchange_rates er WHERE er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS DATE))
      END AS eur_subtotal
    FROM orders o
    WHERE o.status <> 'cancelled'
      AND o.placed_at >= DATE '2025-01-01'
      AND o.placed_at < DATE '2026-01-01'
      AND EXISTS (
        SELECT 1
        FROM order_discounts od
        LEFT JOIN discounts d1 ON od.discount_id = d1.id
        LEFT JOIN coupons cp ON od.coupon_id = cp.id
        LEFT JOIN discounts d2 ON cp.discount_id = d2.id
        WHERE od.order_id = o.id
          AND COALESCE(d1.name, d2.name) IN ('Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', 'Summer Sale 2025')
      )
  ) disc_orders
  GROUP BY CASE WHEN item_count >= 5 THEN 5 ELSE item_count END
) disc ON disc.bucket_num = buckets.bucket_num
LEFT JOIN (
  SELECT
    CASE WHEN item_count >= 5 THEN 5 ELSE item_count END AS bucket_num,
    COUNT(*) AS nondiscounted_order_count,
    AVG(eur_subtotal) AS avg_subtotal_eur
  FROM (
    SELECT
      o.id,
      (SELECT COUNT(*) FROM order_items oi WHERE oi.order_id = o.id) AS item_count,
      CASE WHEN o.currency_code = 'EUR' THEN o.subtotal
           ELSE o.subtotal / (SELECT er.rate FROM exchange_rates er WHERE er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS DATE))
      END AS eur_subtotal
    FROM orders o
    WHERE o.status <> 'cancelled'
      AND o.placed_at >= DATE '2025-01-01'
      AND o.placed_at < DATE '2026-01-01'
      AND o.tenant_id IN (
        SELECT DISTINCT o2.tenant_id
        FROM orders o2
        WHERE o2.status <> 'cancelled'
          AND o2.placed_at >= DATE '2025-01-01'
          AND o2.placed_at < DATE '2026-01-01'
          AND EXISTS (
            SELECT 1
            FROM order_discounts od2
            LEFT JOIN discounts d1b ON od2.discount_id = d1b.id
            LEFT JOIN coupons cp2 ON od2.coupon_id = cp2.id
            LEFT JOIN discounts d2b ON cp2.discount_id = d2b.id
            WHERE od2.order_id = o2.id
              AND COALESCE(d1b.name, d2b.name) IN ('Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', 'Summer Sale 2025')
          )
      )
      AND NOT EXISTS (
        SELECT 1 FROM order_discounts od3 WHERE od3.order_id = o.id
      )
  ) nondisc_orders
  GROUP BY CASE WHEN item_count >= 5 THEN 5 ELSE item_count END
) nondisc ON nondisc.bucket_num = buckets.bucket_num
ORDER BY buckets.bucket_num

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: item_count_bucket, discounted_order_count, avg_discounted_subtotal_eur_pre_discount, nondiscounted_order_count, avg_nondiscounted_subtotal_eur, lift_eur, lift_pct

FelixQuery compiles with the expected columns. Submitting.

Felix calls submit

SELECT
  CASE
    WHEN buckets.bucket_num = 1 THEN '1 item'
    WHEN buckets.bucket_num = 2 THEN '2 items'
    WHEN buckets.bucket_num = 3 THEN '3 items'
    WHEN buckets.bucket_num = 4 THEN '4 items'
    ELSE '5+ items'
  END AS item_count_bucket,
  COALESCE(disc.discounted_order_count, 0) AS discounted_order_count,
  ROUND(disc.avg_subtotal_eur, 2) AS avg_discounted_subtotal_eur_pre_discount,
  COALESCE(nondisc.nondiscounted_order_count, 0) AS nondiscounted_order_count,
  ROUND(nondisc.avg_subtotal_eur, 2) AS avg_nondiscounted_subtotal_eur,
  ROUND(disc.avg_subtotal_eur - nondisc.avg_subtotal_eur, 2) AS lift_eur,
  ROUND(100.0 * (disc.avg_subtotal_eur - nondisc.avg_subtotal_eur) / NULLIF(nondisc.avg_subtotal_eur, 0), 2) AS lift_pct
FROM (SELECT 1 AS bucket_num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) buckets
LEFT JOIN (
  SELECT
    CASE WHEN item_count >= 5 THEN 5 ELSE item_count END AS bucket_num,
    COUNT(*) AS discounted_order_count,
    AVG(eur_subtotal) AS avg_subtotal_eur
  FROM (
    SELECT
      o.id,
      (SELECT COUNT(*) FROM order_items oi WHERE oi.order_id = o.id) AS item_count,
      CASE WHEN o.currency_code = 'EUR' THEN o.subtotal
           ELSE o.subtotal / (SELECT er.rate FROM exchange_rates er WHERE er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS DATE))
      END AS eur_subtotal
    FROM orders o
    WHERE o.status <> 'cancelled'
      AND o.placed_at >= DATE '2025-01-01'
      AND o.placed_at < DATE '2026-01-01'
      AND EXISTS (
        SELECT 1
        FROM order_discounts od
        LEFT JOIN discounts d1 ON od.discount_id = d1.id
        LEFT JOIN coupons cp ON od.coupon_id = cp.id
        LEFT JOIN discounts d2 ON cp.discount_id = d2.id
        WHERE od.order_id = o.id
          AND COALESCE(d1.name, d2.name) IN ('Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', 'Summer Sale 2025')
      )
  ) disc_orders
  GROUP BY CASE WHEN item_count >= 5 THEN 5 ELSE item_count END
) disc ON disc.bucket_num = buckets.bucket_num
LEFT JOIN (
  SELECT
    CASE WHEN item_count >= 5 THEN 5 ELSE item_count END AS bucket_num,
    COUNT(*) AS nondiscounted_order_count,
    AVG(eur_subtotal) AS avg_subtotal_eur
  FROM (
    SELECT
      o.id,
      (SELECT COUNT(*) FROM order_items oi WHERE oi.order_id = o.id) AS item_count,
      CASE WHEN o.currency_code = 'EUR' THEN o.subtotal
           ELSE o.subtotal / (SELECT er.rate FROM exchange_rates er WHERE er.base_code = 'EUR' AND er.quote_code = o.currency_code AND er.as_of = CAST(o.placed_at AS DATE))
      END AS eur_subtotal
    FROM orders o
    WHERE o.status <> 'cancelled'
      AND o.placed_at >= DATE '2025-01-01'
      AND o.placed_at < DATE '2026-01-01'
      AND o.tenant_id IN (
        SELECT DISTINCT o2.tenant_id
        FROM orders o2
        WHERE o2.status <> 'cancelled'
          AND o2.placed_at >= DATE '2025-01-01'
          AND o2.placed_at < DATE '2026-01-01'
          AND EXISTS (
            SELECT 1
            FROM order_discounts od2
            LEFT JOIN discounts d1b ON od2.discount_id = d1b.id
            LEFT JOIN coupons cp2 ON od2.coupon_id = cp2.id
            LEFT JOIN discounts d2b ON cp2.discount_id = d2b.id
            WHERE od2.order_id = o2.id
              AND COALESCE(d1b.name, d2b.name) IN ('Welcome 10% off', 'Black Friday 2025', 'January Sale 2025', 'Summer Sale 2025')
          )
      )
      AND NOT EXISTS (
        SELECT 1 FROM order_discounts od3 WHERE od3.order_id = o.id
      )
  ) nondisc_orders
  GROUP BY CASE WHEN item_count >= 5 THEN 5 ELSE item_count END
) nondisc ON nondisc.bucket_num = buckets.bucket_num
ORDER BY buckets.bucket_num

The warehouse answers

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

FelixSubmitted. Summary of what the query does: - **Discounted side:** orders from 2025 (status ≠ cancelled) that carry at least one of the four named promotions in `order_discounts` (matched either directly via `discount_id` or through a coupon's `discount_id`), bucketed by distinct `order_items` row count (1/2/3/4/5+), with subtotal converted to EUR via same-day `exchange_rates`. - **Non-discounted side:** other 2025 orders (status ≠ cancelled) from the same tenants that ran any of those four promotions, but with zero rows in `order_discounts` at all, bucketed the same way. - Reports order counts, average EUR subtotal for each side, and the absolute/percentage lift, one row per bucket, all 5 buckets always shown. One judgment call flagged in the submit note: I treated "no row in order_discounts" literally (any discount excludes an order from the comparison group, not just the four named ones), and matched promotion names through both the direct and coupon-linked discount paths to be safe.

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 (290 KB) · Oliver Grant's transcript (972 KB) · Marco Bianchi's transcript (259 KB) · the night's plan (7 KB) · the night's reports (7 KB) · the week's memo (3 KB)

Incrementality re-cut: order value matched by basket size (item count), 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 q363

Everything, as recorded