DB Olympics

Queries · q256 · Head of Growth Marketing · week 2, night 1

The real euro cost of discounting, by vertical

“Beyond return rates, how much revenue does discounting actually cost each vertical once you add up both the discounts given and the refunds paid out on those orders?”

Manager · asked the questionChloé MarchettiHead of Growth Marketing. Joined from a direct-to-consumer brand. Wants to sell merchants more of Tidewick's marketing tools.
Analyst · planned the reportSofie NilsenStoryteller: looks for the two or three findings that matter and digs until each is explained.
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. Chloé Marchetti writes to Sofie Nilsen

    The manager, at the start of week 2

    Subject: Good progress — four things I want nailed down this week

    Hi Sofie,

    Thanks for this — genuinely useful, and the vertical breakdown on discounting is exactly the kind of "don't trust the pooled number" catch I was worried about. A few things surprised me and I want to go deeper before I take anything to leadership.

    What surprised me / needs explaining:

    • Aisha's team flagged that loyalty members "cost" nearly a fifth of their own revenue in redeemed points, plus a large pile of points issued but not yet redeemed sitting as a liability. That's a very different lens from AOV — a program can look flat on AOV and still be expensive. I want these two views reconciled, not treated as separate facts.
    • Aisha also walked back the "loyalty members return more" claim once category mix was accounted for. That's a good catch, but it makes me nervous about the apparel-vs-electronics AOV comparison in your memo — is that difference also just a category mix artifact, or is it real?
    • Referrals look like our best-performing lever by far, yet reach is under 1% of customers. That gap between "strong where it exists" and "barely exists" feels like the single biggest opportunity I've seen all week.

    My questions for this week, roughly in priority order:

    1. Does discounting actually cost us margin, and in which verticals is it worst? This was already your next step — please keep it top of the list. I need a real cost picture (not just return rates) before I tell merchants to rein in sitewide sales.
    2. Is the loyalty programme actually worth running as-is? I want one clear picture that weighs what members cost us (redeemed and owed points) against what they're worth (spend, frequency, retention), controlling for the category mix issue Aisha found. I need to decide whether we fix it, shrink it, or let it be.
    3. What would it take to grow referrals, and who's a good referrer? Given how strong but tiny this program is, I want to understand what referring customers look like (so we can find more of them) and whether growth here looks more promising than fixing loyalty.
    4. Of the orders we can't currently link to a channel, is there any pattern at all (timing, merchant type, repeat vs new customer) that hints at what's driving them? I don't expect a clean answer — I just want to know if this blind spot is worth investing in closing, or if it's simply untrackable and I should stop asking.

    What I need this for: I'm trying to turn this into a short list of 2-3 concrete recommendations for merchants and for our own roadmap — where to tell them to pull back (discounting), where to double down (referrals), and where we might be quietly burning money (loyalty payouts). I'd rather have fewer, firmer answers than a wider, shakier set.

    Thanks again — this is coming together well.

    Chloé

  2. Sofie Nilsen plans the night

    The analyst · week 2, night 1, request 1 of 3

    Chloé's #1 ask is a real cost picture for discounting, not just return rates, and two other teams already shipped adjacent pieces I should build on rather than duplicate: Oliver Grant (Pricing) found Welcome10's refunds now exceed its own discount cost and that no coupon has usage limits; Ingrid Solberg (Lifecycle) already did the rigorous loyalty cost-vs-value reconciliation Chloé is asking me for in Q2, including the category-mix correction on the 'members return more' claim. So tonight I'm putting all three slots behind Q1 (discounting), turning last week's return-rate finding into an actual euro cost picture by vertical, a ranked list of which specific coupon codes are worst (to give Chloé something actionable and consistent with Oliver's Welcome10 catch), and a monthly trend to see if the leakage is worsening alongside the refund-rate creep the CEO and CFO teams flagged. I'll pick up loyalty reconciliation, referral growth, and the unlinked-orders question starting night 2, leaning on Ingrid's numbers rather than re-deriving them.

    The real euro cost of discounting, by vertical
    Question
    Beyond return rates, how much revenue does discounting actually cost each vertical once you add up both the discounts given and the refunds paid out on those orders?
    One row per
    industry and order group (discounted vs full price)
    Columns
    • industry - tenants.industry
    • order_group - 'discounted' (orders.discount_total > 0) or 'full price' (= 0)
    • orders_count - count of orders placed_at in 2025 in that industry/group
    • discount_given_eur - sum of orders.discount_total converted to EUR, rounded to 0 decimals
    • refund_amount_eur - sum of order_returns.refund_total (joined on order_id) for orders in that group, converted to EUR, rounded to 0 decimals
    • gmv_eur - sum of orders.subtotal (pre-discount product value) converted to EUR, rounded to 0 decimals
    • net_leakage_pct - (discount_given_eur + refund_amount_eur) / gmv_eur * 100, rounded to 1 decimal
    • refund_rate_pct - share of orders in that cell with at least one linked order_returns row, *100, rounded to 1 decimal

  3. 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 t0 AS (
  SELECT
    o.id AS order_id,
    t.industry AS industry,
    CASE WHEN o.discount_total > 0 THEN 'discounted' ELSE 'full price' END AS order_group,
    o.subtotal / er.rate AS subtotal_eur,
    o.discount_total / er.rate AS discount_eur
  FROM orders o
  JOIN tenants t ON t.id = o.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 o.placed_at >= TIMESTAMP '2025-01-01'
    AND o.placed_at < TIMESTAMP '2026-01-01'
),
t1 AS (
  SELECT
    orr.order_id AS order_id,
    SUM(orr.refund_total / er2.rate) AS refund_eur
  FROM order_returns orr
  JOIN exchange_rates er2
    ON er2.base_code = 'EUR'
   AND er2.quote_code = orr.currency_code
   AND er2.as_of = CAST(COALESCE(orr.refunded_at, orr.requested_at) AS date)
  GROUP BY orr.order_id
),
t2 AS (
  SELECT DISTINCT industry, order_group FROM t0
),
t3 AS (
  SELECT industry, order_group,
    COUNT(*) AS orders_count,
    SUM(discount_eur) AS discount_given_eur_raw,
    SUM(subtotal_eur) AS gmv_eur_raw
  FROM t0
  GROUP BY industry, order_group
),
t4 AS (
  SELECT t0.industry AS industry, t0.order_group AS order_group,
    SUM(COALESCE(t1.refund_eur,0)) AS refund_amount_eur_raw
  FROM t0
  LEFT JOIN t1 ON t1.order_id = t0.order_id
  GROUP BY t0.industry, t0.order_group
),
t5 AS (
  SELECT t0.industry AS industry, t0.order_group AS order_group,
    COUNT(DISTINCT CASE WHEN t1.order_id IS NOT NULL THEN t0.order_id END) AS orders_with_return
  FROM t0
  LEFT JOIN t1 ON t1.order_id = t0.order_id
  GROUP BY t0.industry, t0.order_group
)
SELECT
  t2.industry AS industry,
  t2.order_group AS order_group,
  COALESCE(t3.orders_count, 0) AS orders_count,
  ROUND(COALESCE(t3.discount_given_eur_raw,0), 0) AS discount_given_eur,
  ROUND(COALESCE(t4.refund_amount_eur_raw,0), 0) AS refund_amount_eur,
  ROUND(COALESCE(t3.gmv_eur_raw,0), 0) AS gmv_eur,
  ROUND(
    CASE WHEN COALESCE(t3.gmv_eur_raw,0) = 0 THEN NULL
    ELSE (COALESCE(t3.discount_given_eur_raw,0) + COALESCE(t4.refund_amount_eur_raw,0)) / t3.gmv_eur_raw * 100 END
  , 1) AS net_leakage_pct,
  ROUND(
    CASE WHEN COALESCE(t3.orders_count,0) = 0 THEN NULL
    ELSE COALESCE(t5.orders_with_return,0)::numeric / t3.orders_count * 100 END
  , 1) AS refund_rate_pct
FROM t2
LEFT JOIN t3 ON t3.industry = t2.industry AND t3.order_group = t2.order_group
LEFT JOIN t4 ON t4.industry = t2.industry AND t4.order_group = t2.order_group
LEFT JOIN t5 ON t5.industry = t2.industry AND t5.order_group = t2.order_group
ORDER BY t2.industry ASC, t2.order_group ASC

    “Query compiles and returns the expected columns. Submitting now.”

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

    “Submitted. Summary of the approach: - Grain: one row per (tenants.industry, order_group), where order_group is 'discounted' when orders.discount_total > 0 else 'full price'. - GMV & discounts: pulled from orders filtered to placed_at in 2025, converted to EUR via exchange_rates (base EUR → order's currency_code, rate as of placed_at date). - Refunds: summed from order_returns.refund_total joined on order_id, converted to EUR via the rate as of the return's date — I used COALESCE(refunded_at, requested_at) since some returns don't yet have a refunded_at, so their refund value isn't lost. - net_leakage_pct: (discounts + refunds) / gmv * 100. - refund_rate_pct: share of orders in the cell with at least one linked return row. Noted the one deviation (refunded_at fallback) in the submit note for the analyst.”

  4. The report comes back overnight

    As Sofie Nilsen saw it, from the small dataset the agents worked on

    industry                  | order_group | orders_count | discount_given_eur | refund_amount_eur |  gmv_eur | net_leakage_pct | refund_rate_pct
--------------------------+-------------+--------------+--------------------+-------------------+----------+-----------------+----------------
Apparel & Footwear        | discounted  |          870 |            32434.0 |           33115.0 | 164367.0 |            39.9 |            37.8
Apparel & Footwear        | full price  |         5079 |                0.0 |          188062.0 | 862386.0 |            21.8 |            34.4
Beauty                    | discounted  |          269 |             8756.0 |            1967.0 |  24258.0 |            44.2 |             8.6
Beauty                    | full price  |         1010 |                0.0 |            2266.0 |  87062.0 |             2.6 |             4.4
Books & Stationery        | discounted  |            8 |              227.0 |               0.0 |    440.0 |            51.5 |             0.0
Books & Stationery        | full price  |          111 |                0.0 |             128.0 |   4128.0 |             3.1 |             7.2
Consumer Electronics      | discounted  |           15 |              831.0 |             209.0 |   6986.0 |            14.9 |            13.3
Consumer Electronics      | full price  |           32 |                0.0 |            4400.0 |  25590.0 |            17.2 |             9.4
Food & Coffee             | discounted  |         1099 |            41500.0 |              14.0 |  46580.0 |            89.1 |             0.2
Food & Coffee             | full price  |          685 |                0.0 |             227.0 |  35641.0 |             0.6 |             1.6
Home & Living             | discounted  |          150 |             4002.0 |            1881.0 |  23032.0 |            25.5 |            18.0
Home & Living             | full price  |          693 |                0.0 |            7661.0 |  94049.0 |             8.1 |            12.8
Industrial Supplies (B2B) | discounted  |          127 |            28231.0 |            3534.0 | 368552.0 |             8.6 |             7.9
Industrial Supplies (B2B) | full price  |           32 |                0.0 |             530.0 |  17495.0 |             3.0 |             3.1
Jewelry & Accessories     | discounted  |            4 |              115.0 |               0.0 |    889.0 |            13.0 |             0.0
Jewelry & Accessories     | full price  |           10 |                0.0 |               0.0 |   2275.0 |             0.0 |            10.0
Office Supplies (B2B)     | discounted  |          163 |            29282.0 |           19352.0 | 556429.0 |             8.7 |             8.0
Office Supplies (B2B)     | full price  |           82 |                0.0 |            2754.0 |  68411.0 |             4.0 |             2.4
Pet Supplies              | discounted  |          504 |            18123.0 |               8.0 |  21780.0 |            83.2 |             0.2
Pet Supplies              | full price  |          617 |                0.0 |             594.0 |  32931.0 |             1.8 |             2.3
Sports & Outdoor          | discounted  |          119 |            10221.0 |            8890.0 |  51250.0 |            37.3 |            26.1
Sports & Outdoor          | full price  |          435 |                0.0 |           17341.0 | 149699.0 |            11.6 |            18.6
(22 rows)

  5. What came of it

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

    Sofie Nilsen's memo to Chloé Marchetti at the end of the week:

    • 1. Discounting: real cost is concentrated, and worsening. Pulling discount + refund euros together by vertical (Report: The real euro cost of discounting, by vertical), Apparel, Beauty, Home & Living, and Sports & Outdoor all show discounted orders costing 17–42 percentage points more of gross merchandise value than full-price orders in the same vertical (e.g. Apparel: 39.9% vs 21.8%; Beauty: 44.2% vs 2.6%). Food & Coffee and Pet Supplies show even bigger leakage (83–89%), but that's near-free subscription trial offers — a deliberate acquisition cost, not wasteful discounting; I'd treat it separately. At the code level, only 5 coupon templates drive nearly all 2025 redemptions platform-wide, and WELCOME10 alone cost €51,316 net (€25,976 discount + €25,340 refunds, 28.5% refund rate on 724 uses) — confirming and quantifying Oliver's catch. Monthly trend shows leakage climbing from 10.9% (Feb) to 20.3% (Nov), tracking the refund creep the CEO/CFO teams flagged. Confident. Recommend tightening WELCOME10 specifically and flagging the four consumer verticals above to merchants.

    It builds on:

  6. It becomes benchmark query q256

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

    • spellingcast
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision; bare NUMERIC cast that only served the rounding removed; intended fraction: integer numerator cast to NUMERIC(18,6) before the division
    • 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 (2×)
    The benchmark text, against the submitted SQL
    WITH t0 AS (  SELECT    o.id AS order_id,    t.industry AS industry,    CASE WHEN o.discount_total > 0 THEN 'discounted' ELSE 'full price' END AS order_group,    o.subtotal / er.rate AS subtotal_eur,    o.discount_total / er.rate AS discount_eur    CAST(o.subtotal / er.rate AS NUMERIC(18,6)) AS subtotal_eur,    CAST(o.discount_total / er.rate AS NUMERIC(18,6)) AS discount_eur  FROM orders o  JOIN tenants t ON t.id = o.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 o.placed_at >= TIMESTAMP '2025-01-01'    AND o.placed_at < TIMESTAMP '2026-01-01'),t1 AS (  SELECT    orr.order_id AS order_id,    SUM(orr.refund_total / er2.rate) AS refund_eur    SUM(CAST(orr.refund_total / er2.rate AS NUMERIC(18,6))) AS refund_eur  FROM order_returns orr  JOIN exchange_rates er2    ON er2.base_code = 'EUR'   AND er2.quote_code = orr.currency_code   AND er2.as_of = CAST(COALESCE(orr.refunded_at, orr.requested_at) AS date)  GROUP BY orr.order_id),t2 AS (  SELECT DISTINCT industry, order_group FROM t0),t3 AS (  SELECT industry, order_group,    COUNT(*) AS orders_count,    SUM(discount_eur) AS discount_given_eur_raw,    SUM(subtotal_eur) AS gmv_eur_raw  FROM t0  GROUP BY industry, order_group),t4 AS (  SELECT t0.industry AS industry, t0.order_group AS order_group,    SUM(COALESCE(t1.refund_eur,0)) AS refund_amount_eur_raw  FROM t0  LEFT JOIN t1 ON t1.order_id = t0.order_id  GROUP BY t0.industry, t0.order_group),t5 AS (  SELECT t0.industry AS industry, t0.order_group AS order_group,    COUNT(DISTINCT CASE WHEN t1.order_id IS NOT NULL THEN t0.order_id END) AS orders_with_return  FROM t0  LEFT JOIN t1 ON t1.order_id = t0.order_id  GROUP BY t0.industry, t0.order_group)SELECT  t2.industry AS industry,  t2.order_group AS order_group,  COALESCE(t3.orders_count, 0) AS orders_count,  ROUND(COALESCE(t3.discount_given_eur_raw,0), 0) AS discount_given_eur,  ROUND(COALESCE(t4.refund_amount_eur_raw,0), 0) AS refund_amount_eur,  ROUND(COALESCE(t3.gmv_eur_raw,0), 0) AS gmv_eur,  ROUND(    CASE WHEN COALESCE(t3.gmv_eur_raw,0) = 0 THEN NULL    ELSE (COALESCE(t3.discount_given_eur_raw,0) + COALESCE(t4.refund_amount_eur_raw,0)) / t3.gmv_eur_raw * 100 END  , 1) AS net_leakage_pct,  ROUND(    CASE WHEN COALESCE(t3.orders_count,0) = 0 THEN NULL    ELSE COALESCE(t5.orders_with_return,0)::numeric / t3.orders_count * 100 END  , 1) AS refund_rate_pct  COALESCE(t3.discount_given_eur_raw,0) AS discount_given_eur,  COALESCE(t4.refund_amount_eur_raw,0) AS refund_amount_eur,  COALESCE(t3.gmv_eur_raw,0) AS gmv_eur,  CASE WHEN COALESCE(t3.gmv_eur_raw,0) = 0 THEN NULL  ELSE (COALESCE(t3.discount_given_eur_raw,0) + COALESCE(t4.refund_amount_eur_raw,0)) / t3.gmv_eur_raw * 100 END AS net_leakage_pct,  CASE WHEN COALESCE(t3.orders_count,0) = 0 THEN NULL  ELSE CAST(COALESCE(t5.orders_with_return,0) AS numeric(18,6)) / t3.orders_count * 100 END AS refund_rate_pctFROM t2LEFT JOIN t3 ON t3.industry = t2.industry AND t3.order_group = t2.order_groupLEFT JOIN t4 ON t4.industry = t2.industry AND t4.order_group = t2.order_groupLEFT JOIN t5 ON t5.industry = t2.industry AND t5.order_group = t2.order_groupORDER BY t2.industry ASC, t2.order_group ASCORDER BY t2.industry ASC NULLS LAST, t2.order_group ASC NULLS LAST

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

    WITH t0 AS (
  SELECT
    o.id AS order_id,
    t.industry AS industry,
    CASE WHEN o.discount_total > 0 THEN 'discounted' ELSE 'full price' END AS order_group,
    CAST(o.subtotal / er.rate AS NUMERIC(18,6)) AS subtotal_eur,
    CAST(o.discount_total / er.rate AS NUMERIC(18,6)) AS discount_eur
  FROM orders o
  JOIN tenants t ON t.id = o.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 o.placed_at >= TIMESTAMP '2025-01-01'
    AND o.placed_at < TIMESTAMP '2026-01-01'
),
t1 AS (
  SELECT
    orr.order_id AS order_id,
    SUM(CAST(orr.refund_total / er2.rate AS NUMERIC(18,6))) AS refund_eur
  FROM order_returns orr
  JOIN exchange_rates er2
    ON er2.base_code = 'EUR'
   AND er2.quote_code = orr.currency_code
   AND er2.as_of = CAST(COALESCE(orr.refunded_at, orr.requested_at) AS date)
  GROUP BY orr.order_id
),
t2 AS (
  SELECT DISTINCT industry, order_group FROM t0
),
t3 AS (
  SELECT industry, order_group,
    COUNT(*) AS orders_count,
    SUM(discount_eur) AS discount_given_eur_raw,
    SUM(subtotal_eur) AS gmv_eur_raw
  FROM t0
  GROUP BY industry, order_group
),
t4 AS (
  SELECT t0.industry AS industry, t0.order_group AS order_group,
    SUM(COALESCE(t1.refund_eur,0)) AS refund_amount_eur_raw
  FROM t0
  LEFT JOIN t1 ON t1.order_id = t0.order_id
  GROUP BY t0.industry, t0.order_group
),
t5 AS (
  SELECT t0.industry AS industry, t0.order_group AS order_group,
    COUNT(DISTINCT CASE WHEN t1.order_id IS NOT NULL THEN t0.order_id END) AS orders_with_return
  FROM t0
  LEFT JOIN t1 ON t1.order_id = t0.order_id
  GROUP BY t0.industry, t0.order_group
)
SELECT
  t2.industry AS industry,
  t2.order_group AS order_group,
  COALESCE(t3.orders_count, 0) AS orders_count,
  COALESCE(t3.discount_given_eur_raw,0) AS discount_given_eur,
  COALESCE(t4.refund_amount_eur_raw,0) AS refund_amount_eur,
  COALESCE(t3.gmv_eur_raw,0) AS gmv_eur,
  CASE WHEN COALESCE(t3.gmv_eur_raw,0) = 0 THEN NULL
  ELSE (COALESCE(t3.discount_given_eur_raw,0) + COALESCE(t4.refund_amount_eur_raw,0)) / t3.gmv_eur_raw * 100 END AS net_leakage_pct,
  CASE WHEN COALESCE(t3.orders_count,0) = 0 THEN NULL
  ELSE CAST(COALESCE(t5.orders_with_return,0) AS numeric(18,6)) / t3.orders_count * 100 END AS refund_rate_pct
FROM t2
LEFT JOIN t3 ON t3.industry = t2.industry AND t3.order_group = t2.order_group
LEFT JOIN t4 ON t4.industry = t2.industry AND t4.order_group = t2.order_group
LEFT JOIN t5 ON t5.industry = t2.industry AND t5.order_group = t2.order_group
ORDER BY t2.industry ASC NULLS LAST, t2.order_group ASC NULLS LAST
    100 ms1 s10 sHyper242 msDuckDB257 msGizmoSQL220 msclickhouse_brew840 msFirebolterrorDataFusion2.19 sClickHouseerror
    • Firebolt{ "errors": [ { "description": "Line 6, Column 21: 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": "78baef51-5208-4dfa-8dd9-258b248e9c29", "query_labe
    • ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table8.subtotal Decimal(14, 4) Decimal64(size = 0), __table10.rate Decimal(12, 6) Decimal64(size = 0). (ARGUMENT_OUT_OF_BOUND)

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

  7. 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 Sofie Nilsen (analyst for the Head of Growth Marketing):

Title: The real euro cost of discounting, by vertical
Question: Beyond return rates, how much revenue does discounting actually cost each vertical once you add up both the discounts given and the refunds paid out on those orders?
Grain: one row per industry and order group (discounted vs full price)
Columns:
- industry - tenants.industry
- order_group - 'discounted' (orders.discount_total > 0) or 'full price' (= 0)
- orders_count - count of orders placed_at in 2025 in that industry/group
- discount_given_eur - sum of orders.discount_total converted to EUR, rounded to 0 decimals
- refund_amount_eur - sum of order_returns.refund_total (joined on order_id) for orders in that group, converted to EUR, rounded to 0 decimals
- gmv_eur - sum of orders.subtotal (pre-discount product value) converted to EUR, rounded to 0 decimals
- net_leakage_pct - (discount_given_eur + refund_amount_eur) / gmv_eur * 100, rounded to 1 decimal
- refund_rate_pct - share of orders in that cell with at least one linked order_returns row, *100, rounded to 1 decimal
Details: Source: orders joined to tenants (industry), order_returns (refund_total, matched on order_id). Filter placed_at in 2025-01-01 to 2026-01-01 exclusive, all tenants. Convert orders.discount_total and orders.subtotal to EUR using exchange_rates as of placed_at date (base_code='EUR', quote_code=order currency, EUR=rate 1); convert order_returns.refund_total to EUR using exchange_rates as of order_returns.refunded_at date (same method). Order by industry ascending, then order_group ascending ('discounted' before 'full price'). Expect ~18-22 rows (industries x 2).

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Bea calls dry_run
      WITH t0 AS (
  SELECT
    o.id AS order_id,
    t.industry AS industry,
    CASE WHEN o.discount_total > 0 THEN 'discounted' ELSE 'full price' END AS order_group,
    o.subtotal / er.rate AS subtotal_eur,
    o.discount_total / er.rate AS discount_eur
  FROM orders o
  JOIN tenants t ON t.id = o.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 o.placed_at >= TIMESTAMP '2025-01-01'
    AND o.placed_at < TIMESTAMP '2026-01-01'
),
t1 AS (
  SELECT
    orr.order_id AS order_id,
    SUM(orr.refund_total / er2.rate) AS refund_eur
  FROM order_returns orr
  JOIN exchange_rates er2
    ON er2.base_code = 'EUR'
   AND er2.quote_code = orr.currency_code
   AND er2.as_of = CAST(COALESCE(orr.refunded_at, orr.requested_at) AS date)
  GROUP BY orr.order_id
),
t2 AS (
  SELECT DISTINCT industry, order_group FROM t0
),
t3 AS (
  SELECT industry, order_group,
    COUNT(*) AS orders_count,
    SUM(discount_eur) AS discount_given_eur_raw,
    SUM(subtotal_eur) AS gmv_eur_raw
  FROM t0
  GROUP BY industry, order_group
),
t4 AS (
  SELECT t0.industry AS industry, t0.order_group AS order_group,
    SUM(COALESCE(t1.refund_eur,0)) AS refund_amount_eur_raw
  FROM t0
  LEFT JOIN t1 ON t1.order_id = t0.order_id
  GROUP BY t0.industry, t0.order_group
),
t5 AS (
  SELECT t0.industry AS industry, t0.order_group AS order_group,
    COUNT(DISTINCT CASE WHEN t1.order_id IS NOT NULL THEN t0.order_id END) AS orders_with_return
  FROM t0
  LEFT JOIN t1 ON t1.order_id = t0.order_id
  GROUP BY t0.industry, t0.order_group
)
SELECT
  t2.industry AS industry,
  t2.order_group AS order_group,
  COALESCE(t3.orders_count, 0) AS orders_count,
  ROUND(COALESCE(t3.discount_given_eur_raw,0), 0) AS discount_given_eur,
  ROUND(COALESCE(t4.refund_amount_eur_raw,0), 0) AS refund_amount_eur,
  ROUND(COALESCE(t3.gmv_eur_raw,0), 0) AS gmv_eur,
  ROUND(
    CASE WHEN COALESCE(t3.gmv_eur_raw,0) = 0 THEN NULL
    ELSE (COALESCE(t3.discount_given_eur_raw,0) + COALESCE(t4.refund_amount_eur_raw,0)) / t3.gmv_eur_raw * 100 END
  , 1) AS net_leakage_pct,
  ROUND(
    CASE WHEN COALESCE(t3.orders_count,0) = 0 THEN NULL
    ELSE COALESCE(t5.orders_with_return,0)::numeric / t3.orders_count * 100 END
  , 1) AS refund_rate_pct
FROM t2
LEFT JOIN t3 ON t3.industry = t2.industry AND t3.order_group = t2.order_group
LEFT JOIN t4 ON t4.industry = t2.industry AND t4.order_group = t2.order_group
LEFT JOIN t5 ON t5.industry = t2.industry AND t5.order_group = t2.order_group
ORDER BY t2.industry ASC, t2.order_group ASC
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: industry, order_group, orders_count, discount_given_eur, refund_amount_eur, gmv_eur, net_leakage_pct, refund_rate_pct
    • BeaQuery compiles and returns the expected columns. Submitting now.
    • Bea calls submit
      WITH t0 AS (
  SELECT
    o.id AS order_id,
    t.industry AS industry,
    CASE WHEN o.discount_total > 0 THEN 'discounted' ELSE 'full price' END AS order_group,
    o.subtotal / er.rate AS subtotal_eur,
    o.discount_total / er.rate AS discount_eur
  FROM orders o
  JOIN tenants t ON t.id = o.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 o.placed_at >= TIMESTAMP '2025-01-01'
    AND o.placed_at < TIMESTAMP '2026-01-01'
),
t1 AS (
  SELECT
    orr.order_id AS order_id,
    SUM(orr.refund_total / er2.rate) AS refund_eur
  FROM order_returns orr
  JOIN exchange_rates er2
    ON er2.base_code = 'EUR'
   AND er2.quote_code = orr.currency_code
   AND er2.as_of = CAST(COALESCE(orr.refunded_at, orr.requested_at) AS date)
  GROUP BY orr.order_id
),
t2 AS (
  SELECT DISTINCT industry, order_group FROM t0
),
t3 AS (
  SELECT industry, order_group,
    COUNT(*) AS orders_count,
    SUM(discount_eur) AS discount_given_eur_raw,
    SUM(subtotal_eur) AS gmv_eur_raw
  FROM t0
  GROUP BY industry, order_group
),
t4 AS (
  SELECT t0.industry AS industry, t0.order_group AS order_group,
    SUM(COALESCE(t1.refund_eur,0)) AS refund_amount_eur_raw
  FROM t0
  LEFT JOIN t1 ON t1.order_id = t0.order_id
  GROUP BY t0.industry, t0.order_group
),
t5 AS (
  SELECT t0.industry AS industry, t0.order_group AS order_group,
    COUNT(DISTINCT CASE WHEN t1.order_id IS NOT NULL THEN t0.order_id END) AS orders_with_return
  FROM t0
  LEFT JOIN t1 ON t1.order_id = t0.order_id
  GROUP BY t0.industry, t0.order_group
)
SELECT
  t2.industry AS industry,
  t2.order_group AS order_group,
  COALESCE(t3.orders_count, 0) AS orders_count,
  ROUND(COALESCE(t3.discount_given_eur_raw,0), 0) AS discount_given_eur,
  ROUND(COALESCE(t4.refund_amount_eur_raw,0), 0) AS refund_amount_eur,
  ROUND(COALESCE(t3.gmv_eur_raw,0), 0) AS gmv_eur,
  ROUND(
    CASE WHEN COALESCE(t3.gmv_eur_raw,0) = 0 THEN NULL
    ELSE (COALESCE(t3.discount_given_eur_raw,0) + COALESCE(t4.refund_amount_eur_raw,0)) / t3.gmv_eur_raw * 100 END
  , 1) AS net_leakage_pct,
  ROUND(
    CASE WHEN COALESCE(t3.orders_count,0) = 0 THEN NULL
    ELSE COALESCE(t5.orders_with_return,0)::numeric / t3.orders_count * 100 END
  , 1) AS refund_rate_pct
FROM t2
LEFT JOIN t3 ON t3.industry = t2.industry AND t3.order_group = t2.order_group
LEFT JOIN t4 ON t4.industry = t2.industry AND t4.order_group = t2.order_group
LEFT JOIN t5 ON t5.industry = t2.industry AND t5.order_group = t2.order_group
ORDER BY t2.industry ASC, t2.order_group ASC
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • BeaSubmitted. Summary of the approach: - **Grain**: one row per `(tenants.industry, order_group)`, where `order_group` is `'discounted'` when `orders.discount_total > 0` else `'full price'`. - **GMV & discounts**: pulled from `orders` filtered to `placed_at` in 2025, converted to EUR via `exchange_rates` (base `EUR` → order's `currency_code`, rate as of `placed_at` date). - **Refunds**: summed from `order_returns.refund_total` joined on `order_id`, converted to EUR via the rate as of the return's date — I used `COALESCE(refunded_at, requested_at)` since some returns don't yet have a `refunded_at`, so their refund value isn't lost. - **net_leakage_pct**: `(discounts + refunds) / gmv * 100`. - **refund_rate_pct**: share of orders in the cell with at least one linked return row. Noted the one deviation (refunded_at fallback) in the submit note for the analyst.

    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 (280 KB) · Sofie Nilsen's transcript (793 KB) · Chloé Marchetti's transcript (263 KB) · the night's plan (5 KB) · the night's reports (7 KB) · the week's memo (3 KB)