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

Which coupon codes cost more than they're worth

“Ranking individual coupon codes by discount-plus-refund cost, which specific codes are the worst offenders (like Welcome10), and which are clean?”

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 SQLInesThinks in window functions: ranks, running totals, LAG/LEAD for comparisons, percent-of-total.

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

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é
Sofie Nilsen plans the night
The analyst · week 2, night 1, request 2 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.
Which coupon codes cost more than they're worth
Question
Ranking individual coupon codes by discount-plus-refund cost, which specific codes are the worst offenders (like Welcome10), and which are clean?
One row per
coupon code
Columns
- code - coupons.code
- times_redeemed - count of coupon_redemptions rows for that code with redeemed_at in 2025
- orders_count - count of distinct order_id among those redemptions
- discount_given_eur - sum of coupon_redemptions.discount_amount converted to EUR, rounded to 0 decimals
- refund_amount_eur - sum of order_returns.refund_total for those same orders, converted to EUR, rounded to 0 decimals
- net_cost_eur - discount_given_eur + refund_amount_eur, rounded to 0 decimals
- refund_rate_pct - share of those orders with at least one linked order_returns row, *100, rounded to 1 decimal
- avg_order_value_eur - average grand_total (EUR) of those orders, rounded to 0 decimals

Ines 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 redemptions AS (
  SELECT cr.id AS redemption_id, cr.order_id, cr.discount_amount, cr.currency_code, cr.redeemed_at, c.code
  FROM coupon_redemptions cr
  JOIN coupons c ON c.id = cr.coupon_id
  WHERE cr.redeemed_at >= DATE '2025-01-01' AND cr.redeemed_at < DATE '2026-01-01'
),
discount_eur AS (
  SELECT r.code, r.order_id, r.discount_amount / er.rate AS discount_amount_eur
  FROM redemptions r
  JOIN exchange_rates er
    ON er.base_code = 'EUR' AND er.quote_code = r.currency_code AND er.as_of = CAST(r.redeemed_at AS date)
),
code_orders AS (
  SELECT DISTINCT code, order_id FROM redemptions
),
orders_eur AS (
  SELECT co.code, co.order_id, o.grand_total / er.rate AS grand_total_eur
  FROM code_orders co
  JOIN orders o ON o.id = co.order_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)
),
returns_eur AS (
  SELECT co.code, co.order_id, SUM(orr.refund_total / er.rate) AS order_refund_eur
  FROM code_orders co
  JOIN order_returns orr ON orr.order_id = co.order_id
  JOIN exchange_rates er
    ON er.base_code = 'EUR' AND er.quote_code = orr.currency_code AND er.as_of = CAST(orr.refunded_at AS date)
  GROUP BY co.code, co.order_id
),
discount_agg AS (
  SELECT code, COUNT(*) AS times_redeemed, COUNT(DISTINCT order_id) AS orders_count, SUM(discount_amount_eur) AS discount_given_eur
  FROM discount_eur
  GROUP BY code
),
refund_agg AS (
  SELECT code, SUM(order_refund_eur) AS refund_amount_eur, COUNT(*) AS orders_with_return
  FROM returns_eur
  GROUP BY code
),
order_value_agg AS (
  SELECT code, AVG(grand_total_eur) AS avg_order_value_eur
  FROM orders_eur
  GROUP BY code
)
SELECT
  d.code,
  d.times_redeemed,
  d.orders_count,
  ROUND(d.discount_given_eur, 0) AS discount_given_eur,
  ROUND(COALESCE(r.refund_amount_eur, 0), 0) AS refund_amount_eur,
  ROUND(d.discount_given_eur + COALESCE(r.refund_amount_eur, 0), 0) AS net_cost_eur,
  ROUND(100.0 * COALESCE(r.orders_with_return, 0) / NULLIF(d.orders_count, 0), 1) AS refund_rate_pct,
  ROUND(ov.avg_order_value_eur, 0) AS avg_order_value_eur
FROM discount_agg d
LEFT JOIN refund_agg r ON r.code = d.code
LEFT JOIN order_value_agg ov ON ov.code = d.code
ORDER BY net_cost_eur DESC, d.code ASC
LIMIT 20

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

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

“Submitted. Summary of what the report does: - Grain: one row per coupon code (code text, aggregated across tenants that happen to share a code string — flagged in the note since the request's framing around recognizable codes like "Welcome10" implied a cross-merchant view rather than a per-tenant one). - Logic: pulls 2025 coupon_redemptions, converts each redemption's discount to EUR using that day's rate, finds the distinct orders touched, pulls their order_returns refunds (EUR as of refunded_at) and orders.grand_total (EUR as of placed_at), then rolls up to the 8 requested measures and ranks by net_cost_eur descending, top 20.”

The report comes back overnight

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

code      | times_redeemed | orders_count | discount_given_eur | refund_amount_eur | net_cost_eur | refund_rate_pct | avg_order_value_eur
----------+----------------+--------------+--------------------+-------------------+--------------+-----------------+--------------------
WELCOME10 |            724 |          724 |            25976.0 |           25340.0 |      51316.0 |            28.5 |               348.0
BF2530    |             89 |           89 |             5497.0 |            5106.0 |      10603.0 |            16.9 |               164.0
BF2525    |             58 |           58 |             2216.0 |             677.0 |       2893.0 |            15.5 |               140.0
BF2520    |             78 |           78 |             1202.0 |             322.0 |       1524.0 |             6.4 |                75.0
BF2515    |             30 |           30 |              735.0 |             175.0 |        910.0 |             6.7 |               162.0
(5 rows)

It becomes benchmark query q257

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

spellingfetch first
numericsdocs/NUMERICS.md: rounding removed; results keep full precision
numericsdocs/NUMERICS.md: x / exchange rate cast to NUMERIC(18,6) (3×)
made explicita selected t.col gets AS col, its standard name made explicit (14×)
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×)
placeholderfetch first

The benchmark text, against the submitted SQL

WITH redemptions AS (  SELECT cr.id AS redemption_id, cr.order_id, cr.discount_amount, cr.currency_code, cr.redeemed_at, c.code  SELECT cr.id AS redemption_id, cr.order_id AS order_id, cr.discount_amount AS discount_amount, cr.currency_code AS currency_code, cr.redeemed_at AS redeemed_at, c.code AS code  FROM coupon_redemptions cr  JOIN coupons c ON c.id = cr.coupon_id  WHERE cr.redeemed_at >= DATE '2025-01-01' AND cr.redeemed_at < DATE '2026-01-01'),discount_eur AS (  SELECT r.code, r.order_id, r.discount_amount / er.rate AS discount_amount_eur  SELECT r.code AS code, r.order_id AS order_id, CAST(r.discount_amount / er.rate AS NUMERIC(18,6)) AS discount_amount_eur  FROM redemptions r  JOIN exchange_rates er    ON er.base_code = 'EUR' AND er.quote_code = r.currency_code AND er.as_of = CAST(r.redeemed_at AS date)),code_orders AS (  SELECT DISTINCT code, order_id FROM redemptions),orders_eur AS (  SELECT co.code, co.order_id, o.grand_total / er.rate AS grand_total_eur  SELECT co.code AS code, co.order_id AS order_id, CAST(o.grand_total / er.rate AS NUMERIC(18,6)) AS grand_total_eur  FROM code_orders co  JOIN orders o ON o.id = co.order_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)),returns_eur AS (  SELECT co.code, co.order_id, SUM(orr.refund_total / er.rate) AS order_refund_eur  SELECT co.code AS code, co.order_id AS order_id, SUM(CAST(orr.refund_total / er.rate AS NUMERIC(18,6))) AS order_refund_eur  FROM code_orders co  JOIN order_returns orr ON orr.order_id = co.order_id  JOIN exchange_rates er    ON er.base_code = 'EUR' AND er.quote_code = orr.currency_code AND er.as_of = CAST(orr.refunded_at AS date)  GROUP BY co.code, co.order_id),discount_agg AS (  SELECT code, COUNT(*) AS times_redeemed, COUNT(DISTINCT order_id) AS orders_count, SUM(discount_amount_eur) AS discount_given_eur  FROM discount_eur  GROUP BY code),refund_agg AS (  SELECT code, SUM(order_refund_eur) AS refund_amount_eur, COUNT(*) AS orders_with_return  FROM returns_eur  GROUP BY code),order_value_agg AS (  SELECT code, AVG(grand_total_eur) AS avg_order_value_eur  FROM orders_eur  GROUP BY code)SELECT  d.code,  d.times_redeemed,  d.orders_count,  ROUND(d.discount_given_eur, 0) AS discount_given_eur,  ROUND(COALESCE(r.refund_amount_eur, 0), 0) AS refund_amount_eur,  ROUND(d.discount_given_eur + COALESCE(r.refund_amount_eur, 0), 0) AS net_cost_eur,  ROUND(100.0 * COALESCE(r.orders_with_return, 0) / NULLIF(d.orders_count, 0), 1) AS refund_rate_pct,  ROUND(ov.avg_order_value_eur, 0) AS avg_order_value_eur  d.code AS code,  d.times_redeemed AS times_redeemed,  d.orders_count AS orders_count,  d.discount_given_eur AS discount_given_eur,  COALESCE(r.refund_amount_eur, 0) AS refund_amount_eur,  d.discount_given_eur + COALESCE(r.refund_amount_eur, 0) AS net_cost_eur,  100.0 * COALESCE(r.orders_with_return, 0) / NULLIF(d.orders_count, 0) AS refund_rate_pct,  ov.avg_order_value_eur AS avg_order_value_eurFROM discount_agg dLEFT JOIN refund_agg r ON r.code = d.codeLEFT JOIN order_value_agg ov ON ov.code = d.codeORDER BY net_cost_eur DESC, d.code ASCLIMIT 20ORDER BY net_cost_eur DESC NULLS FIRST, d.code ASC NULLS LAST{{fetch_first(20)}}

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

WITH redemptions AS (
  SELECT cr.id AS redemption_id, cr.order_id AS order_id, cr.discount_amount AS discount_amount, cr.currency_code AS currency_code, cr.redeemed_at AS redeemed_at, c.code AS code
  FROM coupon_redemptions cr
  JOIN coupons c ON c.id = cr.coupon_id
  WHERE cr.redeemed_at >= DATE '2025-01-01' AND cr.redeemed_at < DATE '2026-01-01'
),
discount_eur AS (
  SELECT r.code AS code, r.order_id AS order_id, CAST(r.discount_amount / er.rate AS NUMERIC(18,6)) AS discount_amount_eur
  FROM redemptions r
  JOIN exchange_rates er
    ON er.base_code = 'EUR' AND er.quote_code = r.currency_code AND er.as_of = CAST(r.redeemed_at AS date)
),
code_orders AS (
  SELECT DISTINCT code, order_id FROM redemptions
),
orders_eur AS (
  SELECT co.code AS code, co.order_id AS order_id, CAST(o.grand_total / er.rate AS NUMERIC(18,6)) AS grand_total_eur
  FROM code_orders co
  JOIN orders o ON o.id = co.order_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)
),
returns_eur AS (
  SELECT co.code AS code, co.order_id AS order_id, SUM(CAST(orr.refund_total / er.rate AS NUMERIC(18,6))) AS order_refund_eur
  FROM code_orders co
  JOIN order_returns orr ON orr.order_id = co.order_id
  JOIN exchange_rates er
    ON er.base_code = 'EUR' AND er.quote_code = orr.currency_code AND er.as_of = CAST(orr.refunded_at AS date)
  GROUP BY co.code, co.order_id
),
discount_agg AS (
  SELECT code, COUNT(*) AS times_redeemed, COUNT(DISTINCT order_id) AS orders_count, SUM(discount_amount_eur) AS discount_given_eur
  FROM discount_eur
  GROUP BY code
),
refund_agg AS (
  SELECT code, SUM(order_refund_eur) AS refund_amount_eur, COUNT(*) AS orders_with_return
  FROM returns_eur
  GROUP BY code
),
order_value_agg AS (
  SELECT code, AVG(grand_total_eur) AS avg_order_value_eur
  FROM orders_eur
  GROUP BY code
)
SELECT
  d.code AS code,
  d.times_redeemed AS times_redeemed,
  d.orders_count AS orders_count,
  d.discount_given_eur AS discount_given_eur,
  COALESCE(r.refund_amount_eur, 0) AS refund_amount_eur,
  d.discount_given_eur + COALESCE(r.refund_amount_eur, 0) AS net_cost_eur,
  100.0 * COALESCE(r.orders_with_return, 0) / NULLIF(d.orders_count, 0) AS refund_rate_pct,
  ov.avg_order_value_eur AS avg_order_value_eur
FROM discount_agg d
LEFT JOIN refund_agg r ON r.code = d.code
LEFT JOIN order_value_agg ov ON ov.code = d.code
ORDER BY net_cost_eur DESC NULLS FIRST, d.code ASC NULLS LAST
FETCH FIRST 20 ROWS ONLY

WITH redemptions AS (
  SELECT cr.id AS redemption_id, cr.order_id AS order_id, cr.discount_amount AS discount_amount, cr.currency_code AS currency_code, cr.redeemed_at AS redeemed_at, c.code AS code
  FROM coupon_redemptions cr
  JOIN coupons c ON c.id = cr.coupon_id
  WHERE cr.redeemed_at >= DATE '2025-01-01' AND cr.redeemed_at < DATE '2026-01-01'
),
discount_eur AS (
  SELECT r.code AS code, r.order_id AS order_id, CAST(r.discount_amount / er.rate AS NUMERIC(18,6)) AS discount_amount_eur
  FROM redemptions r
  JOIN exchange_rates er
    ON er.base_code = 'EUR' AND er.quote_code = r.currency_code AND er.as_of = CAST(r.redeemed_at AS date)
),
code_orders AS (
  SELECT DISTINCT code, order_id FROM redemptions
),
orders_eur AS (
  SELECT co.code AS code, co.order_id AS order_id, CAST(o.grand_total / er.rate AS NUMERIC(18,6)) AS grand_total_eur
  FROM code_orders co
  JOIN orders o ON o.id = co.order_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)
),
returns_eur AS (
  SELECT co.code AS code, co.order_id AS order_id, SUM(CAST(orr.refund_total / er.rate AS NUMERIC(18,6))) AS order_refund_eur
  FROM code_orders co
  JOIN order_returns orr ON orr.order_id = co.order_id
  JOIN exchange_rates er
    ON er.base_code = 'EUR' AND er.quote_code = orr.currency_code AND er.as_of = CAST(orr.refunded_at AS date)
  GROUP BY co.code, co.order_id
),
discount_agg AS (
  SELECT code, COUNT(*) AS times_redeemed, COUNT(DISTINCT order_id) AS orders_count, SUM(discount_amount_eur) AS discount_given_eur
  FROM discount_eur
  GROUP BY code
),
refund_agg AS (
  SELECT code, SUM(order_refund_eur) AS refund_amount_eur, COUNT(*) AS orders_with_return
  FROM returns_eur
  GROUP BY code
),
order_value_agg AS (
  SELECT code, AVG(grand_total_eur) AS avg_order_value_eur
  FROM orders_eur
  GROUP BY code
)
SELECT
  d.code AS code,
  d.times_redeemed AS times_redeemed,
  d.orders_count AS orders_count,
  d.discount_given_eur AS discount_given_eur,
  COALESCE(r.refund_amount_eur, 0) AS refund_amount_eur,
  d.discount_given_eur + COALESCE(r.refund_amount_eur, 0) AS net_cost_eur,
  100.0 * COALESCE(r.orders_with_return, 0) / NULLIF(d.orders_count, 0) AS refund_rate_pct,
  ov.avg_order_value_eur AS avg_order_value_eur
FROM discount_agg d
LEFT JOIN refund_agg r ON r.code = d.code
LEFT JOIN order_value_agg ov ON ov.code = d.code
ORDER BY net_cost_eur DESC NULLS FIRST, d.code ASC NULLS LAST
LIMIT 20

Firebolt{ "errors": [ { "description": "Line 8, Column 73: 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": "7587c2e7-4eb0-430e-95d6-2044f3b88281", "query_labe
ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table3.discount_amount Decimal(14, 4) Decimal64(size = 0), __table6.rate Decimal(12, 6) Decimal64(size = 0). (ARGUMENT_OUT_OF_BOUND)

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

Ines's whole session, 7 turns

The request, as Ines received it

Report request from Sofie Nilsen (analyst for the Head of Growth Marketing):

Title: Which coupon codes cost more than they're worth
Question: Ranking individual coupon codes by discount-plus-refund cost, which specific codes are the worst offenders (like Welcome10), and which are clean?
Grain: one row per coupon code
Columns:
- code - coupons.code
- times_redeemed - count of coupon_redemptions rows for that code with redeemed_at in 2025
- orders_count - count of distinct order_id among those redemptions
- discount_given_eur - sum of coupon_redemptions.discount_amount converted to EUR, rounded to 0 decimals
- refund_amount_eur - sum of order_returns.refund_total for those same orders, converted to EUR, rounded to 0 decimals
- net_cost_eur - discount_given_eur + refund_amount_eur, rounded to 0 decimals
- refund_rate_pct - share of those orders with at least one linked order_returns row, *100, rounded to 1 decimal
- avg_order_value_eur - average grand_total (EUR) of those orders, rounded to 0 decimals
Details: Source: coupon_redemptions (redeemed_at in 2025, all tenants) joined to coupons for the code, and to order_returns on order_id for refund amounts. Convert coupon_redemptions.discount_amount to EUR using exchange_rates as of redeemed_at date; convert order_returns.refund_total to EUR using exchange_rates as of order_returns.refunded_at date; convert orders.grand_total to EUR as of orders.placed_at date (all base_code='EUR', EUR=rate 1). Order by net_cost_eur descending, tie-break code ascending, limit to the top 20 codes by net_cost_eur.

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

Ines calls dry_run

WITH redemptions AS (
  SELECT cr.id AS redemption_id, cr.order_id, cr.discount_amount, cr.currency_code, cr.redeemed_at, c.code
  FROM coupon_redemptions cr
  JOIN coupons c ON c.id = cr.coupon_id
  WHERE cr.redeemed_at >= DATE '2025-01-01' AND cr.redeemed_at < DATE '2026-01-01'
),
discount_eur AS (
  SELECT r.code, r.order_id, r.discount_amount / er.rate AS discount_amount_eur
  FROM redemptions r
  JOIN exchange_rates er
    ON er.base_code = 'EUR' AND er.quote_code = r.currency_code AND er.as_of = CAST(r.redeemed_at AS date)
),
code_orders AS (
  SELECT DISTINCT code, order_id FROM redemptions
),
orders_eur AS (
  SELECT co.code, co.order_id, o.grand_total / er.rate AS grand_total_eur
  FROM code_orders co
  JOIN orders o ON o.id = co.order_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)
),
returns_eur AS (
  SELECT co.code, co.order_id, SUM(orr.refund_total / er.rate) AS order_refund_eur
  FROM code_orders co
  JOIN order_returns orr ON orr.order_id = co.order_id
  JOIN exchange_rates er
    ON er.base_code = 'EUR' AND er.quote_code = orr.currency_code AND er.as_of = CAST(orr.refunded_at AS date)
  GROUP BY co.code, co.order_id
),
discount_agg AS (
  SELECT code, COUNT(*) AS times_redeemed, COUNT(DISTINCT order_id) AS orders_count, SUM(discount_amount_eur) AS discount_given_eur
  FROM discount_eur
  GROUP BY code
),
refund_agg AS (
  SELECT code, SUM(order_refund_eur) AS refund_amount_eur, COUNT(*) AS orders_with_return
  FROM returns_eur
  GROUP BY code
),
order_value_agg AS (
  SELECT code, AVG(grand_total_eur) AS avg_order_value_eur
  FROM orders_eur
  GROUP BY code
)
SELECT
  d.code,
  d.times_redeemed,
  d.orders_count,
  ROUND(d.discount_given_eur, 0) AS discount_given_eur,
  ROUND(COALESCE(r.refund_amount_eur, 0), 0) AS refund_amount_eur,
  ROUND(d.discount_given_eur + COALESCE(r.refund_amount_eur, 0), 0) AS net_cost_eur,
  ROUND(100.0 * COALESCE(r.orders_with_return, 0) / NULLIF(d.orders_count, 0), 1) AS refund_rate_pct,
  ROUND(ov.avg_order_value_eur, 0) AS avg_order_value_eur
FROM discount_agg d
LEFT JOIN refund_agg r ON r.code = d.code
LEFT JOIN order_value_agg ov ON ov.code = d.code
ORDER BY net_cost_eur DESC, d.code ASC
LIMIT 20

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: code, times_redeemed, orders_count, discount_given_eur, refund_amount_eur, net_cost_eur, refund_rate_pct, avg_order_value_eur

InesQuery compiles and returns the expected columns. Submitting.

Ines calls submit

WITH redemptions AS (
  SELECT cr.id AS redemption_id, cr.order_id, cr.discount_amount, cr.currency_code, cr.redeemed_at, c.code
  FROM coupon_redemptions cr
  JOIN coupons c ON c.id = cr.coupon_id
  WHERE cr.redeemed_at >= DATE '2025-01-01' AND cr.redeemed_at < DATE '2026-01-01'
),
discount_eur AS (
  SELECT r.code, r.order_id, r.discount_amount / er.rate AS discount_amount_eur
  FROM redemptions r
  JOIN exchange_rates er
    ON er.base_code = 'EUR' AND er.quote_code = r.currency_code AND er.as_of = CAST(r.redeemed_at AS date)
),
code_orders AS (
  SELECT DISTINCT code, order_id FROM redemptions
),
orders_eur AS (
  SELECT co.code, co.order_id, o.grand_total / er.rate AS grand_total_eur
  FROM code_orders co
  JOIN orders o ON o.id = co.order_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)
),
returns_eur AS (
  SELECT co.code, co.order_id, SUM(orr.refund_total / er.rate) AS order_refund_eur
  FROM code_orders co
  JOIN order_returns orr ON orr.order_id = co.order_id
  JOIN exchange_rates er
    ON er.base_code = 'EUR' AND er.quote_code = orr.currency_code AND er.as_of = CAST(orr.refunded_at AS date)
  GROUP BY co.code, co.order_id
),
discount_agg AS (
  SELECT code, COUNT(*) AS times_redeemed, COUNT(DISTINCT order_id) AS orders_count, SUM(discount_amount_eur) AS discount_given_eur
  FROM discount_eur
  GROUP BY code
),
refund_agg AS (
  SELECT code, SUM(order_refund_eur) AS refund_amount_eur, COUNT(*) AS orders_with_return
  FROM returns_eur
  GROUP BY code
),
order_value_agg AS (
  SELECT code, AVG(grand_total_eur) AS avg_order_value_eur
  FROM orders_eur
  GROUP BY code
)
SELECT
  d.code,
  d.times_redeemed,
  d.orders_count,
  ROUND(d.discount_given_eur, 0) AS discount_given_eur,
  ROUND(COALESCE(r.refund_amount_eur, 0), 0) AS refund_amount_eur,
  ROUND(d.discount_given_eur + COALESCE(r.refund_amount_eur, 0), 0) AS net_cost_eur,
  ROUND(100.0 * COALESCE(r.orders_with_return, 0) / NULLIF(d.orders_count, 0), 1) AS refund_rate_pct,
  ROUND(ov.avg_order_value_eur, 0) AS avg_order_value_eur
FROM discount_agg d
LEFT JOIN refund_agg r ON r.code = d.code
LEFT JOIN order_value_agg ov ON ov.code = d.code
ORDER BY net_cost_eur DESC, d.code ASC
LIMIT 20

The warehouse answers

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

InesSubmitted. Summary of what the report does: - **Grain:** one row per coupon code (code text, aggregated across tenants that happen to share a code string — flagged in the note since the request's framing around recognizable codes like "Welcome10" implied a cross-merchant view rather than a per-tenant one). - **Logic:** pulls 2025 `coupon_redemptions`, converts each redemption's discount to EUR using that day's rate, finds the distinct orders touched, pulls their `order_returns` refunds (EUR as of `refunded_at`) and `orders.grand_total` (EUR as of `placed_at`), then rolls up to the 8 requested measures and ranks by `net_cost_eur` descending, top 20.

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

Ines'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)

Which coupon codes cost more than they're worth

Chloé Marchetti writes to Sofie Nilsen

Sofie Nilsen plans the night

Ines writes the SQL

The report comes back overnight

It becomes benchmark query q257

Everything, as recorded