DB Olympics

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

Is discount-related revenue leakage getting worse over 2025?

“Is the euro cost of discounting (discounts given plus refunds on those orders) trending up or down over the course of 2025, in step with the refund-rate creep other teams have flagged?”

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 SQLOmarPragmatic: the shortest correct query, aggregates first and joins dimensions afterwards, FILTER clauses for conditional counts.

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

    Is discount-related revenue leakage getting worse over 2025?
    Question
    Is the euro cost of discounting (discounts given plus refunds on those orders) trending up or down over the course of 2025, in step with the refund-rate creep other teams have flagged?
    One row per
    calendar month (2025)
    Columns
    • month - YYYY-MM label from orders.placed_at
    • orders_count - count of orders placed_at in that month, all tenants
    • discount_given_eur - sum of orders.discount_total converted to EUR for that month's orders, rounded to 0 decimals
    • refund_amount_eur - sum of order_returns.refund_total converted to EUR for returns tied to that month's orders, rounded to 0 decimals
    • gmv_eur - sum of orders.subtotal converted to EUR for that month's orders, rounded to 0 decimals
    • net_leakage_pct - (discount_given_eur + refund_amount_eur) / gmv_eur * 100, rounded to 1 decimal

  3. Omar writes the SQL

    The engineer · 2 dry runs on the empty schema, then the submission

    Dry run 1 · STATUS: REJECTED
    WITH order_agg AS (
  SELECT
    TO_CHAR(o.placed_at, 'YYYY-MM') AS month,
    COUNT(*) AS orders_count,
    SUM(o.discount_total * CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE 1.0 / er.rate END) AS discount_eur,
    SUM(o.subtotal * CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE 1.0 / er.rate END) AS gmv_eur
  FROM orders o
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
    AND er.quote_code = o.currency_code
    AND er.as_of = CAST(o.placed_at AS date)
  WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'
  GROUP BY 1
),
refund_agg AS (
  SELECT
    TO_CHAR(o.placed_at, 'YYYY-MM') AS month,
    SUM(ret.refund_total * CASE WHEN ret.currency_code = 'EUR' THEN 1 ELSE 1.0 / er.rate END) AS refund_eur
  FROM order_returns ret
  JOIN orders o ON o.id = ret.order_id
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
    AND er.quote_code = ret.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'
  GROUP BY 1
),
months AS (
  SELECT DISTINCT month_label FROM calendar_days WHERE year_num = 2025
)
SELECT
  m.month_label AS month,
  COALESCE(oa.orders_count, 0) AS orders_count,
  ROUND(COALESCE(oa.discount_eur, 0), 0) AS discount_given_eur,
  ROUND(COALESCE(ra.refund_eur, 0), 0) AS refund_amount_eur,
  ROUND(COALESCE(oa.gmv_eur, 0), 0) AS gmv_eur,
  ROUND(
    CASE WHEN COALESCE(oa.gmv_eur, 0) = 0 THEN NULL
    ELSE (COALESCE(oa.discount_eur, 0) + COALESCE(ra.refund_eur, 0)) / oa.gmv_eur * 100
    END, 1) AS net_leakage_pct
FROM months m
LEFT JOIN order_agg oa ON oa.month = m.month_label
LEFT JOIN refund_agg ra ON ra.month = m.month_label
ORDER BY m.month_label
    Dry run 2 · 2 changes from the dry run before · STATUS: COMPILES (dry run on the empty schema, no data read)
    WITH order_agg AS (  SELECT    TO_CHAR(o.placed_at, 'YYYY-MM') AS month,    strftime(o.placed_at, '%Y-%m') AS month,    COUNT(*) AS orders_count,    SUM(o.discount_total * CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE 1.0 / er.rate END) AS discount_eur,    SUM(o.subtotal * CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE 1.0 / er.rate END) AS gmv_eur  FROM orders o  LEFT JOIN exchange_rates er    ON er.base_code = 'EUR'    AND er.quote_code = o.currency_code    AND er.as_of = CAST(o.placed_at AS date)  WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'  GROUP BY 1),refund_agg AS (  SELECT    TO_CHAR(o.placed_at, 'YYYY-MM') AS month,    strftime(o.placed_at, '%Y-%m') AS month,    SUM(ret.refund_total * CASE WHEN ret.currency_code = 'EUR' THEN 1 ELSE 1.0 / er.rate END) AS refund_eur  FROM order_returns ret  JOIN orders o ON o.id = ret.order_id  LEFT JOIN exchange_rates er    ON er.base_code = 'EUR'    AND er.quote_code = ret.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'  GROUP BY 1),months AS (  SELECT DISTINCT month_label FROM calendar_days WHERE year_num = 2025)SELECT  m.month_label AS month,  COALESCE(oa.orders_count, 0) AS orders_count,  ROUND(COALESCE(oa.discount_eur, 0), 0) AS discount_given_eur,  ROUND(COALESCE(ra.refund_eur, 0), 0) AS refund_amount_eur,  ROUND(COALESCE(oa.gmv_eur, 0), 0) AS gmv_eur,  ROUND(    CASE WHEN COALESCE(oa.gmv_eur, 0) = 0 THEN NULL    ELSE (COALESCE(oa.discount_eur, 0) + COALESCE(ra.refund_eur, 0)) / oa.gmv_eur * 100    END, 1) AS net_leakage_pctFROM months mLEFT JOIN order_agg oa ON oa.month = m.month_labelLEFT JOIN refund_agg ra ON ra.month = m.month_labelORDER BY m.month_label

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

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

    “Submitted. The report computes, for each 2025 month: order count, EUR-converted discounts, EUR-converted refunds (attributed back to the original order's month via order_returns.order_id), GMV, and the net leakage percentage — with all 12 months guaranteed via the calendar_days dimension even if some have no qualifying orders/returns.”

  4. The report comes back overnight

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

    month   | orders_count | discount_given_eur | refund_amount_eur |  gmv_eur | net_leakage_pct
--------+--------------+--------------------+-------------------+----------+----------------
2025-01 |         1827 |            49786.0 |           30247.0 | 552211.0 |            14.5
2025-02 |         1400 |            25803.0 |           17427.0 | 397960.0 |            10.9
2025-03 |         1638 |            28890.0 |           22539.0 | 387778.0 |            13.3
2025-04 |         1568 |            32256.0 |           39840.0 | 446780.0 |            16.1
2025-05 |         1691 |            34930.0 |           49731.0 | 450644.0 |            18.8
2025-06 |         1509 |            24037.0 |           22741.0 | 325394.0 |            14.4
2025-07 |         1648 |            50733.0 |           21049.0 | 370784.0 |            19.4
2025-08 |         1564 |            25942.0 |           21713.0 | 334705.0 |            14.2
2025-09 |         1712 |            36184.0 |           33424.0 | 437884.0 |            15.9
2025-10 |         1879 |            34276.0 |           52837.0 | 483921.0 |            18.0
2025-11 |         2645 |            83340.0 |           78744.0 | 798013.0 |            20.3
2025-12 |         3112 |            50390.0 |           54949.0 | 658182.0 |            16.0
(12 rows)

  5. It becomes benchmark query q258

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

    • placeholderyear month (2×)
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
    • numericsdocs/NUMERICS.md: conversion written as x / rate instead of x * (1.0 / rate), the same value; the conversion cast then applies to the amount, not to the inverse rate
    • numericsdocs/NUMERICS.md: conversion written as x / rate instead of x * (1.0 / rate), the same value; the conversion cast then applies to the amount, not to the inverse rate
    • numericsdocs/NUMERICS.md: conversion written as x / rate instead of x * (1.0 / rate), the same value; the conversion cast then applies to the amount, not to the inverse rate
    • 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
    The benchmark text, against the submitted SQL
    WITH order_agg AS (  SELECT    strftime(o.placed_at, '%Y-%m') AS month,    {{year_month(o.placed_at)}} AS month,    COUNT(*) AS orders_count,    SUM(o.discount_total * CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE 1.0 / er.rate END) AS discount_eur,    SUM(o.subtotal * CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE 1.0 / er.rate END) AS gmv_eur    SUM(CASE WHEN o.currency_code = 'EUR' THEN o.discount_total ELSE CAST(o.discount_total / er.rate AS NUMERIC(18,6)) END) AS discount_eur,    SUM(CASE WHEN o.currency_code = 'EUR' THEN o.subtotal ELSE CAST(o.subtotal / er.rate AS NUMERIC(18,6)) END) AS gmv_eur  FROM orders o  LEFT JOIN exchange_rates er    ON er.base_code = 'EUR'    AND er.quote_code = o.currency_code    AND er.as_of = CAST(o.placed_at AS date)  WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'  GROUP BY 1),refund_agg AS (  SELECT    strftime(o.placed_at, '%Y-%m') AS month,    SUM(ret.refund_total * CASE WHEN ret.currency_code = 'EUR' THEN 1 ELSE 1.0 / er.rate END) AS refund_eur    {{year_month(o.placed_at)}} AS month,    SUM(CASE WHEN ret.currency_code = 'EUR' THEN ret.refund_total ELSE CAST(ret.refund_total / er.rate AS NUMERIC(18,6)) END) AS refund_eur  FROM order_returns ret  JOIN orders o ON o.id = ret.order_id  LEFT JOIN exchange_rates er    ON er.base_code = 'EUR'    AND er.quote_code = ret.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'  GROUP BY 1),months AS (  SELECT DISTINCT month_label FROM calendar_days WHERE year_num = 2025)SELECT  m.month_label AS month,  COALESCE(oa.orders_count, 0) AS orders_count,  ROUND(COALESCE(oa.discount_eur, 0), 0) AS discount_given_eur,  ROUND(COALESCE(ra.refund_eur, 0), 0) AS refund_amount_eur,  ROUND(COALESCE(oa.gmv_eur, 0), 0) AS gmv_eur,  ROUND(    CASE WHEN COALESCE(oa.gmv_eur, 0) = 0 THEN NULL    ELSE (COALESCE(oa.discount_eur, 0) + COALESCE(ra.refund_eur, 0)) / oa.gmv_eur * 100    END, 1) AS net_leakage_pct  COALESCE(oa.discount_eur, 0) AS discount_given_eur,  COALESCE(ra.refund_eur, 0) AS refund_amount_eur,  COALESCE(oa.gmv_eur, 0) AS gmv_eur,  CASE WHEN COALESCE(oa.gmv_eur, 0) = 0 THEN NULL  ELSE (COALESCE(oa.discount_eur, 0) + COALESCE(ra.refund_eur, 0)) / oa.gmv_eur * 100  END AS net_leakage_pctFROM months mLEFT JOIN order_agg oa ON oa.month = m.month_labelLEFT JOIN refund_agg ra ON ra.month = m.month_labelORDER BY m.month_labelORDER BY m.month_label NULLS LAST

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

    WITH order_agg AS (
  SELECT
    strftime(o.placed_at, '%Y-%m') AS month,
    COUNT(*) AS orders_count,
    SUM(CASE WHEN o.currency_code = 'EUR' THEN o.discount_total ELSE CAST(o.discount_total / er.rate AS NUMERIC(18,6)) END) AS discount_eur,
    SUM(CASE WHEN o.currency_code = 'EUR' THEN o.subtotal ELSE CAST(o.subtotal / er.rate AS NUMERIC(18,6)) END) AS gmv_eur
  FROM orders o
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
    AND er.quote_code = o.currency_code
    AND er.as_of = CAST(o.placed_at AS date)
  WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'
  GROUP BY 1
),
refund_agg AS (
  SELECT
    strftime(o.placed_at, '%Y-%m') AS month,
    SUM(CASE WHEN ret.currency_code = 'EUR' THEN ret.refund_total ELSE CAST(ret.refund_total / er.rate AS NUMERIC(18,6)) END) AS refund_eur
  FROM order_returns ret
  JOIN orders o ON o.id = ret.order_id
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
    AND er.quote_code = ret.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'
  GROUP BY 1
),
months AS (
  SELECT DISTINCT month_label FROM calendar_days WHERE year_num = 2025
)
SELECT
  m.month_label AS month,
  COALESCE(oa.orders_count, 0) AS orders_count,
  COALESCE(oa.discount_eur, 0) AS discount_given_eur,
  COALESCE(ra.refund_eur, 0) AS refund_amount_eur,
  COALESCE(oa.gmv_eur, 0) AS gmv_eur,
  CASE WHEN COALESCE(oa.gmv_eur, 0) = 0 THEN NULL
  ELSE (COALESCE(oa.discount_eur, 0) + COALESCE(ra.refund_eur, 0)) / oa.gmv_eur * 100
  END AS net_leakage_pct
FROM months m
LEFT JOIN order_agg oa ON oa.month = m.month_label
LEFT JOIN refund_agg ra ON ra.month = m.month_label
ORDER BY m.month_label NULLS LAST
    100 ms1 sHyper179 msDuckDB170 msGizmoSQL115 msclickhouse_brew322 msFirebolterrorDataFusionwrong resultClickHouseerror
    • Firebolt{ "errors": [ { "description": "Line 5, Column 92: 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": "f45df081-ba18-4443-a424-c6a2b362e706", "query_labe
    • DataFusionrow 1: col 2: "2029524" vs "0"; col 3: "51413184.919163" vs "0.000000"; col 4: "41538519.960626" vs "0.000000"
    • ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table4.discount_total Decimal(14, 4) Decimal64(size = 0), __table5.rate Nullable(Decimal(12, 6)) Nullable(size = 0, Decimal64(size = 0), UInt8(size = 0)). (ARGUMENT_OUT_OF_BOUN

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

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

    Omar's whole session, 9 turns
    • The request, as Omar received it
      Report request from Sofie Nilsen (analyst for the Head of Growth Marketing):

Title: Is discount-related revenue leakage getting worse over 2025?
Question: Is the euro cost of discounting (discounts given plus refunds on those orders) trending up or down over the course of 2025, in step with the refund-rate creep other teams have flagged?
Grain: one row per calendar month (2025)
Columns:
- month - YYYY-MM label from orders.placed_at
- orders_count - count of orders placed_at in that month, all tenants
- discount_given_eur - sum of orders.discount_total converted to EUR for that month's orders, rounded to 0 decimals
- refund_amount_eur - sum of order_returns.refund_total converted to EUR for returns tied to that month's orders, rounded to 0 decimals
- gmv_eur - sum of orders.subtotal converted to EUR for that month's orders, rounded to 0 decimals
- net_leakage_pct - (discount_given_eur + refund_amount_eur) / gmv_eur * 100, rounded to 1 decimal
Details: Source: orders (placed_at in 2025, all tenants) left joined to order_returns on order_id for refund_total (attributed to the month of the original order, not the return date, so leakage is tied to the order cohort). Same EUR conversion approach as the other two reports tonight (exchange_rates, as_of the relevant date, EUR=rate 1). Cover all 12 months of 2025 even if a month has no qualifying rows. Order by month ascending.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Omar calls dry_run
      WITH order_agg AS (
  SELECT
    TO_CHAR(o.placed_at, 'YYYY-MM') AS month,
    COUNT(*) AS orders_count,
    SUM(o.discount_total * CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE 1.0 / er.rate END) AS discount_eur,
    SUM(o.subtotal * CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE 1.0 / er.rate END) AS gmv_eur
  FROM orders o
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
    AND er.quote_code = o.currency_code
    AND er.as_of = CAST(o.placed_at AS date)
  WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'
  GROUP BY 1
),
refund_agg AS (
  SELECT
    TO_CHAR(o.placed_at, 'YYYY-MM') AS month,
    SUM(ret.refund_total * CASE WHEN ret.currency_code = 'EUR' THEN 1 ELSE 1.0 / er.rate END) AS refund_eur
  FROM order_returns ret
  JOIN orders o ON o.id = ret.order_id
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
    AND er.quote_code = ret.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'
  GROUP BY 1
),
months AS (
  SELECT DISTINCT month_label FROM calendar_days WHERE year_num = 2025
)
SELECT
  m.month_label AS month,
  COALESCE(oa.orders_count, 0) AS orders_count,
  ROUND(COALESCE(oa.discount_eur, 0), 0) AS discount_given_eur,
  ROUND(COALESCE(ra.refund_eur, 0), 0) AS refund_amount_eur,
  ROUND(COALESCE(oa.gmv_eur, 0), 0) AS gmv_eur,
  ROUND(
    CASE WHEN COALESCE(oa.gmv_eur, 0) = 0 THEN NULL
    ELSE (COALESCE(oa.discount_eur, 0) + COALESCE(ra.refund_eur, 0)) / oa.gmv_eur * 100
    END, 1) AS net_leakage_pct
FROM months m
LEFT JOIN order_agg oa ON oa.month = m.month_label
LEFT JOIN refund_agg ra ON ra.month = m.month_label
ORDER BY m.month_label
    • The warehouse answers
      STATUS: REJECTED
  - does not compile: Catalog Error: Scalar Function with name to_char does not exist!
    • Omar calls dry_run
      WITH order_agg AS (
  SELECT
    strftime(o.placed_at, '%Y-%m') AS month,
    COUNT(*) AS orders_count,
    SUM(o.discount_total * CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE 1.0 / er.rate END) AS discount_eur,
    SUM(o.subtotal * CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE 1.0 / er.rate END) AS gmv_eur
  FROM orders o
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
    AND er.quote_code = o.currency_code
    AND er.as_of = CAST(o.placed_at AS date)
  WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'
  GROUP BY 1
),
refund_agg AS (
  SELECT
    strftime(o.placed_at, '%Y-%m') AS month,
    SUM(ret.refund_total * CASE WHEN ret.currency_code = 'EUR' THEN 1 ELSE 1.0 / er.rate END) AS refund_eur
  FROM order_returns ret
  JOIN orders o ON o.id = ret.order_id
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
    AND er.quote_code = ret.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'
  GROUP BY 1
),
months AS (
  SELECT DISTINCT month_label FROM calendar_days WHERE year_num = 2025
)
SELECT
  m.month_label AS month,
  COALESCE(oa.orders_count, 0) AS orders_count,
  ROUND(COALESCE(oa.discount_eur, 0), 0) AS discount_given_eur,
  ROUND(COALESCE(ra.refund_eur, 0), 0) AS refund_amount_eur,
  ROUND(COALESCE(oa.gmv_eur, 0), 0) AS gmv_eur,
  ROUND(
    CASE WHEN COALESCE(oa.gmv_eur, 0) = 0 THEN NULL
    ELSE (COALESCE(oa.discount_eur, 0) + COALESCE(ra.refund_eur, 0)) / oa.gmv_eur * 100
    END, 1) AS net_leakage_pct
FROM months m
LEFT JOIN order_agg oa ON oa.month = m.month_label
LEFT JOIN refund_agg ra ON ra.month = m.month_label
ORDER BY m.month_label
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: month, orders_count, discount_given_eur, refund_amount_eur, gmv_eur, net_leakage_pct
    • OmarQuery compiles and returns the expected columns. Submitting.
    • Omar calls submit
      WITH order_agg AS (
  SELECT
    strftime(o.placed_at, '%Y-%m') AS month,
    COUNT(*) AS orders_count,
    SUM(o.discount_total * CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE 1.0 / er.rate END) AS discount_eur,
    SUM(o.subtotal * CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE 1.0 / er.rate END) AS gmv_eur
  FROM orders o
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
    AND er.quote_code = o.currency_code
    AND er.as_of = CAST(o.placed_at AS date)
  WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'
  GROUP BY 1
),
refund_agg AS (
  SELECT
    strftime(o.placed_at, '%Y-%m') AS month,
    SUM(ret.refund_total * CASE WHEN ret.currency_code = 'EUR' THEN 1 ELSE 1.0 / er.rate END) AS refund_eur
  FROM order_returns ret
  JOIN orders o ON o.id = ret.order_id
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
    AND er.quote_code = ret.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'
  GROUP BY 1
),
months AS (
  SELECT DISTINCT month_label FROM calendar_days WHERE year_num = 2025
)
SELECT
  m.month_label AS month,
  COALESCE(oa.orders_count, 0) AS orders_count,
  ROUND(COALESCE(oa.discount_eur, 0), 0) AS discount_given_eur,
  ROUND(COALESCE(ra.refund_eur, 0), 0) AS refund_amount_eur,
  ROUND(COALESCE(oa.gmv_eur, 0), 0) AS gmv_eur,
  ROUND(
    CASE WHEN COALESCE(oa.gmv_eur, 0) = 0 THEN NULL
    ELSE (COALESCE(oa.discount_eur, 0) + COALESCE(ra.refund_eur, 0)) / oa.gmv_eur * 100
    END, 1) AS net_leakage_pct
FROM months m
LEFT JOIN order_agg oa ON oa.month = m.month_label
LEFT JOIN refund_agg ra ON ra.month = m.month_label
ORDER BY m.month_label
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • OmarSubmitted. The report computes, for each 2025 month: order count, EUR-converted discounts, EUR-converted refunds (attributed back to the original order's month via `order_returns.order_id`), GMV, and the net leakage percentage — with all 12 months guaranteed via the `calendar_days` dimension even if some have no qualifying orders/returns.

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

    Omar's transcript (278 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)