Queries · q148 · Head of Pricing and Promotions · week 1, night 1

Discount cost vs. order volume/revenue by discount, 2025

“Which discounts (promotions/coupons) cost us the most in 2025, and how much order volume and revenue do they sit on?”

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

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 1
Subject: Priorities for this week's discount cost-review with Tobias

Oliver,

Tobias and I need a credible answer to "are our promotions and coupons earning their keep," in time for Friday's memo. This feeds a joint decision on which promotions/coupons we keep, retire, or cap going into next quarter — so I'd rather have four solid, well-checked answers than a pile of rushed ones. Please pick the nights' reports accordingly and flag anything suspicious you find along the way rather than burying it in a footnote.

Here's what I actually want to know, in my own words:
1. Which promotions and coupons are costing us the most, and is that cost buying us anything? I want to see discount cost alongside the orders/revenue they sit on, so we can tell "big spend, big lift" apart from "big spend, no lift."
2. For our top discounts, do customers actually spend more because of them, or are we just discounting purchases that would have happened anyway? I know this is the hard one — I just want our best honest read on it, even if approximate.
3. Are any coupons being overused or abused — the same customers redeeming repeatedly, usage patterns that look like stacking or sharing of codes meant to be one-time or limited, or codes that are far outliving the campaign they were meant for?
4. Which discounts are quietly eating into margin on top of their price reduction — for example cases where returns or refunds cluster around discounted orders, so the real cost is higher than the discount line alone suggests.
5. (If there's a slot left) How does this differ by merchant or vertical — are one or two tenants responsible for most of the discount spend, which would change how we approach this.
Keep each report to one clear question — I'd rather get three clean answers this week than one bundled one that gets bounced. Let's sync briefly before you submit Thursday's batch in case the first results change what we ask.

Thanks, Marco
A note from the Chief Financial Officer, Tobias Lindqvist
Subject: Finance's angle on the promotions/coupons review

Oliver,

As you and Marco dig into discount performance this week, a few things I'd like the analysis to keep an eye on from the finance side:
1. Are discounted orders actually incremental, or just margin we're giving away on sales that would have happened anyway? I care less about redemption volume and more about whether promotions are growing net revenue after the discount, versus simply lowering what we collect on existing demand.
2. Is any of this exposed as cost we haven't fully absorbed yet? Where coupons stack with refunds or returns, I want to understand whether we're left carrying a loss after the fact, especially heading into year-end close.
3. Is there any pattern of abuse or concentration — the same accounts or a narrow set of merchants repeatedly running the heaviest discounts — that's quietly eating into margin rather than driving genuine growth?
Keep it tight; I mainly need the "so what" for the annual accounts and the 2026 budget assumptions on discount cost.

Thanks, Tobias
Oliver Grant plans the night
The analyst · week 1, night 1, request 1 of 3
Marco and Tobias both want: (1) cost vs. lift per discount, (2) incrementality read, (3) abuse/concentration signals, (4) margin erosion via returns/refunds, (5) merchant/vertical concentration. That's 5 questions but only 3 slots tonight, so I'm front-loading the foundational, non-overlapping pieces that everything else will build on, and saving the harder incrementality/refund-overlap asks for later nights once I've seen where the money actually sits:
1. A discount-level cost-vs-lift report (answers Marco Q1) — this is the spine of the whole review.
2. A tenant/vertical concentration report (answers Marco Q5 / Tobias Q3 concentration) — I want this early because it tells me whether the Q1 rankings are really "a few big promotions" or "a few big merchants," which changes how I read everything else.
3. A coupon-level abuse/misuse report (answers Marco Q3 / Tobias Q3 abuse) — distinct customers, repeat redemption, limit violations, post-expiry usage, since this is a pure redemption-pattern question independent of revenue lift.
I'm holding off on incrementality (Q2) and returns/refund leakage (Q4) until I see which discounts and tenants actually matter — no point building a careful before/after spend comparison for a promotion that turns out to be trivial. All three reports use 2025 full-year data (most relevant to the 2026 budget conversation) with amounts converted to EUR via exchange_rates so merchants in different currencies are comparable.
Discount cost vs. order volume/revenue by discount, 2025
Question
Which discounts (promotions/coupons) cost us the most in 2025, and how much order volume and revenue do they sit on?
One row per
discount (discounts.id, i.e. the underlying discount definition behind a coupon or automatic promotion)
Columns
- discount name
- discount kind (percentage/fixed_amount/free_shipping/bogo)
- linked coupon code(s) (comma-joined, or 'automatic' if none)
- total discount amount (EUR)
- order count
- total order revenue, grand_total (EUR)
- discount amount as % of order revenue
- distinct customers
- first order date
- last order date

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 fod AS (
  SELECT
    od.id AS od_id,
    od.tenant_id AS tenant_id,
    od.order_id AS order_id,
    od.discount_id AS discount_id,
    od.coupon_id AS coupon_id,
    od.amount AS discount_amount_local,
    od.currency_code AS discount_currency_code,
    o.grand_total AS order_grand_total_local,
    o.currency_code AS order_currency_code,
    o.user_id AS user_id,
    o.placed_at AS placed_at,
    CAST(o.placed_at AS date) AS placed_date
  FROM order_discounts od
  JOIN orders o
    ON o.id = od.order_id
   AND o.tenant_id = od.tenant_id
  WHERE od.discount_id IS NOT NULL
    AND o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
    AND o.status <> 'cancelled'
),
discount_base AS (
  SELECT DISTINCT discount_id FROM fod
),
coupon_codes AS (
  SELECT DISTINCT fod.discount_id AS discount_id, c.code AS code
  FROM fod
  JOIN coupons c
    ON c.id = fod.coupon_id
   AND c.tenant_id = fod.tenant_id
  WHERE fod.coupon_id IS NOT NULL
),
coupon_codes_agg AS (
  SELECT
    cc.discount_id AS discount_id,
    STRING_AGG(cc.code, ', ' ORDER BY cc.code) AS coupon_codes
  FROM coupon_codes cc
  GROUP BY cc.discount_id
),
m_discount_amount AS (
  SELECT
    fod.discount_id AS discount_id,
    SUM(fod.discount_amount_local / fxd.rate) AS discount_amount_eur
  FROM fod
  JOIN exchange_rates fxd
    ON fxd.base_code = 'EUR'
   AND fxd.quote_code = fod.discount_currency_code
   AND fxd.as_of = fod.placed_date
  GROUP BY fod.discount_id
),
m_orders AS (
  SELECT
    fod.discount_id AS discount_id,
    COUNT(DISTINCT fod.order_id) AS order_count,
    COUNT(DISTINCT fod.user_id) AS distinct_customers,
    MIN(fod.placed_at) AS first_order_date,
    MAX(fod.placed_at) AS last_order_date
  FROM fod
  GROUP BY fod.discount_id
),
distinct_orders AS (
  SELECT DISTINCT
    discount_id,
    order_id,
    order_grand_total_local,
    order_currency_code,
    placed_date
  FROM fod
),
m_revenue AS (
  SELECT
    do1.discount_id AS discount_id,
    SUM(do1.order_grand_total_local / fxo.rate) AS revenue_eur
  FROM distinct_orders do1
  JOIN exchange_rates fxo
    ON fxo.base_code = 'EUR'
   AND fxo.quote_code = do1.order_currency_code
   AND fxo.as_of = do1.placed_date
  GROUP BY do1.discount_id
)
SELECT
  d.name AS discount_name,
  d.kind AS discount_kind,
  COALESCE(cca.coupon_codes, 'automatic') AS coupon_codes,
  ROUND(COALESCE(mda.discount_amount_eur, 0), 2) AS discount_amount_eur,
  COALESCE(mo.order_count, 0) AS order_count,
  ROUND(COALESCE(mr.revenue_eur, 0), 2) AS order_revenue_eur,
  ROUND(
    CASE WHEN COALESCE(mr.revenue_eur, 0) = 0 THEN NULL
         ELSE 100.0 * COALESCE(mda.discount_amount_eur, 0) / mr.revenue_eur
    END, 2
  ) AS discount_pct_of_revenue,
  COALESCE(mo.distinct_customers, 0) AS distinct_customers,
  mo.first_order_date AS first_order_date,
  mo.last_order_date AS last_order_date
FROM discount_base db
JOIN discounts d ON d.id = db.discount_id
LEFT JOIN coupon_codes_agg cca ON cca.discount_id = db.discount_id
LEFT JOIN m_discount_amount mda ON mda.discount_id = db.discount_id
LEFT JOIN m_orders mo ON mo.discount_id = db.discount_id
LEFT JOIN m_revenue mr ON mr.discount_id = db.discount_id
ORDER BY discount_amount_eur DESC, d.name ASC

“Query compiles and returns the expected 10 columns at the requested grain. 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 discount definition (discounts.id), restricted to discounts that had at least one qualifying order in 2025. - Filters: orders with placed_at in 2025, excluding status = 'cancelled'. - Coupon codes: pulled from coupons via order_discounts.coupon_id actually used, deduplicated and comma-joined; shows 'automatic' when a discount was only ever applied without a coupon. - Currency conversion: each local amount (discount cost, and separately order grand_total) divided by that day's EUR→local exchange rate, so figures are genuinely in EUR. - Revenue/order-count/customers/dates are computed over distinct orders per discount to avoid double-counting if an order carries multiple discount line rows for the same discount. - Ordered by EUR discount cost descending, with discount name as a deterministic tie-break. Row-count/50-row printing is left to the report-print step as described in the handbook.”

The report comes back overnight

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

discount_name                   | discount_kind | coupon_codes | discount_amount_eur | order_count | order_revenue_eur | discount_pct_of_revenue | distinct_customers | first_order_date           | last_order_date           
--------------------------------+---------------+--------------+---------------------+-------------+-------------------+-------------------------+--------------------+----------------------------+---------------------------
Subscription delivery (prepaid) | percentage    | automatic    |             24250.3 |         650 |               0.0 |                    NULL |                 74 | 2025-01-02 11:58:59.390293 | 2025-12-31 19:28:30.272796
Subscription delivery (prepaid) | percentage    | automatic    |            16524.72 |         354 |               0.0 |                    NULL |                 51 | 2025-01-02 20:45:36.939141 | 2025-12-31 09:19:10.507421
Subscription delivery (prepaid) | percentage    | automatic    |            11802.62 |         326 |               0.0 |                    NULL |                 57 | 2025-01-03 11:00:13.120386 | 2025-12-30 09:55:25.746387
Volume tier 10+                 | percentage    | automatic    |            11162.46 |         104 |         306145.13 |                    3.65 |                 28 | 2025-01-06 07:44:00.592182 | 2025-12-16 07:53:08.388578
Volume tier 50+                 | percentage    | automatic    |            11121.06 |          15 |         100400.09 |                   11.08 |                 13 | 2025-01-30 22:50:53.502141 | 2025-11-19 04:31:29.294344
Volume tier 10+                 | percentage    | automatic    |             9644.04 |         102 |         197285.77 |                    4.89 |                 32 | 2025-01-05 21:33:30.767642 | 2025-12-19 22:33:49.26592 
January Sale 2025               | percentage    | automatic    |             8920.65 |         124 |          20453.43 |                   43.61 |                101 | 2025-01-02 06:32:48.191693 | 2025-01-29 22:10:25.756618
Summer Sale 2025                | percentage    | automatic    |             8259.52 |         146 |          23347.93 |                   35.38 |                134 | 2025-07-01 01:27:00.824622 | 2025-07-21 23:12:53.737039
Volume tier 10+                 | percentage    | automatic    |             7734.34 |          38 |         187684.36 |                    4.12 |                 12 | 2025-01-04 12:08:46.73159  | 2025-12-23 11:23:06.114114
Welcome 10% off                 | percentage    | WELCOME10    |             6484.53 |         260 |          54857.68 |                   11.82 |                260 | 2025-01-01 17:39:43.055779 | 2025-12-29 21:08:38.269165
Welcome 10% off                 | percentage    | WELCOME10    |             6066.57 |           7 |          59412.06 |                   10.21 |                  7 | 2025-04-07 21:02:46.432258 | 2025-12-04 14:45:36.974754
Subscription delivery (prepaid) | percentage    | automatic    |             5595.96 |         106 |               0.0 |                    NULL |                 21 | 2025-01-07 07:11:51.594656 | 2025-12-30 18:57:56.568504
Subscription delivery (prepaid) | percentage    | automatic    |             4541.17 |          43 |               0.0 |                    NULL |                  8 | 2025-01-06 16:11:40.547746 | 2025-12-18 14:15:24.27774 
Welcome 10% off                 | percentage    | WELCOME10    |             3797.23 |           4 |          41015.06 |                    9.26 |                  4 | 2025-01-17 12:42:52.303155 | 2025-08-14 18:59:01.310734
Summer Sale 2025                | percentage    | automatic    |             3569.73 |          24 |          13367.71 |                    26.7 |                 19 | 2025-07-02 21:26:49.466986 | 2025-07-21 18:35:43.003384
Black Friday 2025               | percentage    | BF2530       |             3314.68 |          23 |           8342.99 |                   39.73 |                 22 | 2025-11-25 22:10:52.913544 | 2025-12-02 03:51:42.182592
Welcome 10% off                 | percentage    | WELCOME10    |             2781.16 |         140 |          23682.68 |                   11.74 |                140 | 2025-01-04 10:38:43.022527 | 2025-12-30 16:05:29.83974 
Welcome 10% off                 | percentage    | WELCOME10    |             2101.89 |           7 |          23581.72 |                    8.91 |                  7 | 2025-02-06 13:01:30.695388 | 2025-12-03 17:07:46.299609
Volume tier 50+                 | percentage    | automatic    |             1856.19 |           5 |          30591.95 |                    6.07 |                  4 | 2025-01-24 13:35:44.378514 | 2025-12-17 21:09:25.270872
Summer Sale 2025                | percentage    | automatic    |             1731.49 |          67 |            8100.8 |                   21.37 |                 60 | 2025-07-01 08:47:43.431539 | 2025-07-21 23:06:37.27223 
Subscription delivery (prepaid) | percentage    | automatic    |             1554.16 |          16 |               0.0 |                    NULL |                  4 | 2025-01-12 18:26:50.795408 | 2025-12-19 13:56:01.167377
January Sale 2025               | percentage    | automatic    |             1154.67 |          18 |           4230.68 |                   27.29 |                 15 | 2025-01-05 00:29:42.450176 | 2025-01-29 03:23:13.123009
Volume tier 50+                 | percentage    | automatic    |             1142.61 |           3 |          13149.28 |                    8.69 |                  3 | 2025-09-30 08:54:00.982856 | 2025-11-24 08:10:47.215728
Black Friday 2025               | percentage    | BF2525       |             1135.09 |          31 |           4034.85 |                   28.13 |                 30 | 2025-11-25 12:40:12.006143 | 2025-12-02 19:22:08.813309
Welcome 10% off                 | percentage    | WELCOME10    |             1033.86 |          26 |           9705.69 |                   10.65 |                 26 | 2025-01-11 00:12:26.112514 | 2025-12-31 02:22:59.860031
Black Friday 2025               | percentage    | BF2525       |              842.05 |          21 |           3197.38 |                   26.34 |                 17 | 2025-11-26 09:04:49.813092 | 2025-12-02 15:00:53.733482
Black Friday 2025               | percentage    | BF2520       |              673.37 |          44 |           3340.47 |                   20.16 |                 40 | 2025-11-25 10:59:27.520109 | 2025-12-02 22:12:32.958563
Black Friday 2025               | percentage    | BF2530       |              615.75 |          15 |           1806.64 |                   34.08 |                 14 | 2025-11-25 05:58:39.892512 | 2025-12-02 18:26:03.91083 
Welcome 10% off                 | percentage    | WELCOME10    |              607.94 |          30 |           6457.38 |                    9.41 |                 30 | 2025-01-20 18:01:33.795192 | 2025-12-23 20:51:49.178185
Welcome 10% off                 | percentage    | WELCOME10    |              522.74 |          60 |            5589.0 |                    9.35 |                 60 | 2025-01-06 16:55:20.641933 | 2025-12-21 07:27:52.494286
Black Friday 2025               | percentage    | BF2530       |              421.65 |           6 |           1166.08 |                   36.16 |                  5 | 2025-11-27 06:45:29.871859 | 2025-12-02 18:08:38.125263
Black Friday 2025               | percentage    | BF2515       |              384.04 |           4 |           2416.85 |                   15.89 |                  2 | 2025-11-27 12:00:48.953451 | 2025-12-02 14:42:45.334948
Summer Sale 2025                | percentage    | automatic    |              373.58 |           8 |           1726.44 |                   21.64 |                  8 | 2025-07-02 10:05:42.363988 | 2025-07-18 10:23:58.328123
Black Friday 2025               | percentage    | BF2530       |              337.85 |          11 |           1014.14 |                   33.31 |                  9 | 2025-11-26 14:03:14.927289 | 2025-12-02 13:18:50.959247
Black Friday 2025               | percentage    | BF2530       |              320.17 |           6 |            850.91 |                   37.63 |                  6 | 2025-11-27 09:42:02.395608 | 2025-12-02 14:40:19.774473
Summer Sale 2025                | percentage    | automatic    |              278.76 |           5 |           1169.89 |                   23.83 |                  4 | 2025-07-04 17:37:28.155988 | 2025-07-17 15:32:54.519817
January Sale 2025               | percentage    | automatic    |              246.48 |          23 |           1814.05 |                   13.59 |                 22 | 2025-01-03 04:16:19.920428 | 2025-01-28 13:57:01.488625
Welcome 10% off                 | percentage    | WELCOME10    |              244.84 |           1 |           2399.68 |                    10.2 |                  1 | 2025-10-28 03:55:40.965121 | 2025-10-28 03:55:40.965121
Welcome 10% off                 | percentage    | WELCOME10    |              217.51 |           5 |           2348.82 |                    9.26 |                  5 | 2025-03-29 08:45:24.698481 | 2025-12-21 17:22:16.083714
Summer Sale 2025                | percentage    | automatic    |              215.31 |          10 |           1322.44 |                   16.28 |                  8 | 2025-07-04 05:55:56.27717  | 2025-07-20 08:06:15.138141
Subscription delivery (prepaid) | percentage    | automatic    |              202.82 |           3 |               0.0 |                    NULL |                  1 | 2025-10-21 21:21:21.797654 | 2025-12-21 20:49:21.797654
January Sale 2025               | percentage    | automatic    |              199.01 |          18 |           1486.75 |                   13.39 |                 17 | 2025-01-03 19:14:32.111174 | 2025-01-29 19:25:44.633766
Black Friday 2025               | percentage    | BF2520       |              194.85 |           5 |            846.86 |                   23.01 |                  5 | 2025-11-25 19:06:04.943086 | 2025-12-01 23:14:31.945049
Black Friday 2025               | percentage    | BF2515       |              189.91 |          13 |           1301.52 |                   14.59 |                 12 | 2025-11-25 13:09:53.511091 | 2025-12-01 20:54:08.280888
Welcome 10% off                 | percentage    | WELCOME10    |              176.98 |          13 |           1999.07 |                    8.85 |                 13 | 2025-01-10 22:39:39.664592 | 2025-12-23 19:00:36.611325
Welcome 10% off                 | percentage    | WELCOME10    |              169.21 |          14 |           1938.65 |                    8.73 |                 14 | 2025-03-11 14:45:41.886149 | 2025-12-31 14:58:15.783532
Summer Sale 2025                | percentage    | automatic    |              159.56 |           5 |             420.5 |                   37.94 |                  4 | 2025-07-02 20:07:32.006876 | 2025-07-18 18:53:11.062627
Black Friday 2025               | percentage    | BF2520       |              152.97 |          20 |            837.43 |                   18.27 |                 18 | 2025-11-25 14:48:38.344555 | 2025-12-02 18:17:46.337131
Black Friday 2025               | percentage    | BF2530       |              139.68 |           4 |            355.25 |                   39.32 |                  4 | 2025-11-28 19:43:10.683411 | 2025-12-01 21:19:01.472568
Welcome 10% off                 | percentage    | WELCOME10    |              137.89 |           1 |           1551.32 |                    8.89 |                  1 | 2025-04-14 13:53:09.65033  | 2025-04-14 13:53:09.65033 
(257 rows, first 50 printed)

What came of it
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:

It becomes benchmark query q148

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

determinismtie-breaker: discounts primary key
numericsdocs/NUMERICS.md: rounding removed; results keep full precision
numericsdocs/NUMERICS.md: rounding removed; results keep full precision
numericsdocs/NUMERICS.md: x / exchange rate cast to NUMERIC(18,6) (2×)
placeholderstring agg
determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (3×)

The benchmark text, against the submitted SQL

WITH fod AS (  SELECT    od.id AS od_id,    od.tenant_id AS tenant_id,    od.order_id AS order_id,    od.discount_id AS discount_id,    od.coupon_id AS coupon_id,    od.amount AS discount_amount_local,    od.currency_code AS discount_currency_code,    o.grand_total AS order_grand_total_local,    o.currency_code AS order_currency_code,    o.user_id AS user_id,    o.placed_at AS placed_at,    CAST(o.placed_at AS date) AS placed_date  FROM order_discounts od  JOIN orders o    ON o.id = od.order_id   AND o.tenant_id = od.tenant_id  WHERE od.discount_id IS NOT NULL    AND o.placed_at >= DATE '2025-01-01'    AND o.placed_at < DATE '2026-01-01'    AND o.status <> 'cancelled'),discount_base AS (  SELECT DISTINCT discount_id FROM fod),coupon_codes AS (  SELECT DISTINCT fod.discount_id AS discount_id, c.code AS code  FROM fod  JOIN coupons c    ON c.id = fod.coupon_id   AND c.tenant_id = fod.tenant_id  WHERE fod.coupon_id IS NOT NULL),coupon_codes_agg AS (  SELECT    cc.discount_id AS discount_id,    STRING_AGG(cc.code, ', ' ORDER BY cc.code) AS coupon_codes    {{string_agg_sorted(cc.code, ', ')}} AS coupon_codes  FROM coupon_codes cc  GROUP BY cc.discount_id),m_discount_amount AS (  SELECT    fod.discount_id AS discount_id,    SUM(fod.discount_amount_local / fxd.rate) AS discount_amount_eur    SUM(CAST(fod.discount_amount_local / fxd.rate AS NUMERIC(18,6))) AS discount_amount_eur  FROM fod  JOIN exchange_rates fxd    ON fxd.base_code = 'EUR'   AND fxd.quote_code = fod.discount_currency_code   AND fxd.as_of = fod.placed_date  GROUP BY fod.discount_id),m_orders AS (  SELECT    fod.discount_id AS discount_id,    COUNT(DISTINCT fod.order_id) AS order_count,    COUNT(DISTINCT fod.user_id) AS distinct_customers,    MIN(fod.placed_at) AS first_order_date,    MAX(fod.placed_at) AS last_order_date  FROM fod  GROUP BY fod.discount_id),distinct_orders AS (  SELECT DISTINCT    discount_id,    order_id,    order_grand_total_local,    order_currency_code,    placed_date  FROM fod),m_revenue AS (  SELECT    do1.discount_id AS discount_id,    SUM(do1.order_grand_total_local / fxo.rate) AS revenue_eur    SUM(CAST(do1.order_grand_total_local / fxo.rate AS NUMERIC(18,6))) AS revenue_eur  FROM distinct_orders do1  JOIN exchange_rates fxo    ON fxo.base_code = 'EUR'   AND fxo.quote_code = do1.order_currency_code   AND fxo.as_of = do1.placed_date  GROUP BY do1.discount_id)SELECT  d.name AS discount_name,  d.kind AS discount_kind,  COALESCE(cca.coupon_codes, 'automatic') AS coupon_codes,  ROUND(COALESCE(mda.discount_amount_eur, 0), 2) AS discount_amount_eur,  COALESCE(mda.discount_amount_eur, 0) AS discount_amount_eur,  COALESCE(mo.order_count, 0) AS order_count,  ROUND(COALESCE(mr.revenue_eur, 0), 2) AS order_revenue_eur,  ROUND(    CASE WHEN COALESCE(mr.revenue_eur, 0) = 0 THEN NULL         ELSE 100.0 * COALESCE(mda.discount_amount_eur, 0) / mr.revenue_eur    END, 2  ) AS discount_pct_of_revenue,  COALESCE(mr.revenue_eur, 0) AS order_revenue_eur,  CASE WHEN COALESCE(mr.revenue_eur, 0) = 0 THEN NULL       ELSE 100.0 * COALESCE(mda.discount_amount_eur, 0) / mr.revenue_eur  END AS discount_pct_of_revenue,  COALESCE(mo.distinct_customers, 0) AS distinct_customers,  mo.first_order_date AS first_order_date,  mo.last_order_date AS last_order_dateFROM discount_base dbJOIN discounts d ON d.id = db.discount_idLEFT JOIN coupon_codes_agg cca ON cca.discount_id = db.discount_idLEFT JOIN m_discount_amount mda ON mda.discount_id = db.discount_idLEFT JOIN m_orders mo ON mo.discount_id = db.discount_idLEFT JOIN m_revenue mr ON mr.discount_id = db.discount_idORDER BY discount_amount_eur DESC, d.name ASCORDER BY discount_amount_eur DESC NULLS FIRST, d.name ASC NULLS LAST, d.id ASC NULLS LAST

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

WITH fod AS (
  SELECT
    od.id AS od_id,
    od.tenant_id AS tenant_id,
    od.order_id AS order_id,
    od.discount_id AS discount_id,
    od.coupon_id AS coupon_id,
    od.amount AS discount_amount_local,
    od.currency_code AS discount_currency_code,
    o.grand_total AS order_grand_total_local,
    o.currency_code AS order_currency_code,
    o.user_id AS user_id,
    o.placed_at AS placed_at,
    CAST(o.placed_at AS date) AS placed_date
  FROM order_discounts od
  JOIN orders o
    ON o.id = od.order_id
   AND o.tenant_id = od.tenant_id
  WHERE od.discount_id IS NOT NULL
    AND o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
    AND o.status <> 'cancelled'
),
discount_base AS (
  SELECT DISTINCT discount_id FROM fod
),
coupon_codes AS (
  SELECT DISTINCT fod.discount_id AS discount_id, c.code AS code
  FROM fod
  JOIN coupons c
    ON c.id = fod.coupon_id
   AND c.tenant_id = fod.tenant_id
  WHERE fod.coupon_id IS NOT NULL
),
coupon_codes_agg AS (
  SELECT
    cc.discount_id AS discount_id,
    STRING_AGG(cc.code, ', ' ORDER BY cc.code) AS coupon_codes
  FROM coupon_codes cc
  GROUP BY cc.discount_id
),
m_discount_amount AS (
  SELECT
    fod.discount_id AS discount_id,
    SUM(CAST(fod.discount_amount_local / fxd.rate AS NUMERIC(18,6))) AS discount_amount_eur
  FROM fod
  JOIN exchange_rates fxd
    ON fxd.base_code = 'EUR'
   AND fxd.quote_code = fod.discount_currency_code
   AND fxd.as_of = fod.placed_date
  GROUP BY fod.discount_id
),
m_orders AS (
  SELECT
    fod.discount_id AS discount_id,
    COUNT(DISTINCT fod.order_id) AS order_count,
    COUNT(DISTINCT fod.user_id) AS distinct_customers,
    MIN(fod.placed_at) AS first_order_date,
    MAX(fod.placed_at) AS last_order_date
  FROM fod
  GROUP BY fod.discount_id
),
distinct_orders AS (
  SELECT DISTINCT
    discount_id,
    order_id,
    order_grand_total_local,
    order_currency_code,
    placed_date
  FROM fod
),
m_revenue AS (
  SELECT
    do1.discount_id AS discount_id,
    SUM(CAST(do1.order_grand_total_local / fxo.rate AS NUMERIC(18,6))) AS revenue_eur
  FROM distinct_orders do1
  JOIN exchange_rates fxo
    ON fxo.base_code = 'EUR'
   AND fxo.quote_code = do1.order_currency_code
   AND fxo.as_of = do1.placed_date
  GROUP BY do1.discount_id
)
SELECT
  d.name AS discount_name,
  d.kind AS discount_kind,
  COALESCE(cca.coupon_codes, 'automatic') AS coupon_codes,
  COALESCE(mda.discount_amount_eur, 0) AS discount_amount_eur,
  COALESCE(mo.order_count, 0) AS order_count,
  COALESCE(mr.revenue_eur, 0) AS order_revenue_eur,
  CASE WHEN COALESCE(mr.revenue_eur, 0) = 0 THEN NULL
       ELSE 100.0 * COALESCE(mda.discount_amount_eur, 0) / mr.revenue_eur
  END AS discount_pct_of_revenue,
  COALESCE(mo.distinct_customers, 0) AS distinct_customers,
  mo.first_order_date AS first_order_date,
  mo.last_order_date AS last_order_date
FROM discount_base db
JOIN discounts d ON d.id = db.discount_id
LEFT JOIN coupon_codes_agg cca ON cca.discount_id = db.discount_id
LEFT JOIN m_discount_amount mda ON mda.discount_id = db.discount_id
LEFT JOIN m_orders mo ON mo.discount_id = db.discount_id
LEFT JOIN m_revenue mr ON mr.discount_id = db.discount_id
ORDER BY discount_amount_eur DESC NULLS FIRST, d.name ASC NULLS LAST, d.id ASC NULLS LAST

WITH fod AS (
  SELECT
    od.id AS od_id,
    od.tenant_id AS tenant_id,
    od.order_id AS order_id,
    od.discount_id AS discount_id,
    od.coupon_id AS coupon_id,
    od.amount AS discount_amount_local,
    od.currency_code AS discount_currency_code,
    o.grand_total AS order_grand_total_local,
    o.currency_code AS order_currency_code,
    o.user_id AS user_id,
    o.placed_at AS placed_at,
    CAST(o.placed_at AS date) AS placed_date
  FROM order_discounts od
  JOIN orders o
    ON o.id = od.order_id
   AND o.tenant_id = od.tenant_id
  WHERE od.discount_id IS NOT NULL
    AND o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
    AND o.status <> 'cancelled'
),
discount_base AS (
  SELECT DISTINCT discount_id FROM fod
),
coupon_codes AS (
  SELECT DISTINCT fod.discount_id AS discount_id, c.code AS code
  FROM fod
  JOIN coupons c
    ON c.id = fod.coupon_id
   AND c.tenant_id = fod.tenant_id
  WHERE fod.coupon_id IS NOT NULL
),
coupon_codes_agg AS (
  SELECT
    cc.discount_id AS discount_id,
    CASE WHEN COUNT(cc.code) = 0 THEN NULL ELSE ARRAY_TO_STRING(ARRAY_SORT(ARRAY_AGG(cc.code)), ', ') END AS coupon_codes
  FROM coupon_codes cc
  GROUP BY cc.discount_id
),
m_discount_amount AS (
  SELECT
    fod.discount_id AS discount_id,
    SUM(CAST(fod.discount_amount_local / fxd.rate AS NUMERIC(18,6))) AS discount_amount_eur
  FROM fod
  JOIN exchange_rates fxd
    ON fxd.base_code = 'EUR'
   AND fxd.quote_code = fod.discount_currency_code
   AND fxd.as_of = fod.placed_date
  GROUP BY fod.discount_id
),
m_orders AS (
  SELECT
    fod.discount_id AS discount_id,
    COUNT(DISTINCT fod.order_id) AS order_count,
    COUNT(DISTINCT fod.user_id) AS distinct_customers,
    MIN(fod.placed_at) AS first_order_date,
    MAX(fod.placed_at) AS last_order_date
  FROM fod
  GROUP BY fod.discount_id
),
distinct_orders AS (
  SELECT DISTINCT
    discount_id,
    order_id,
    order_grand_total_local,
    order_currency_code,
    placed_date
  FROM fod
),
m_revenue AS (
  SELECT
    do1.discount_id AS discount_id,
    SUM(CAST(do1.order_grand_total_local / fxo.rate AS NUMERIC(18,6))) AS revenue_eur
  FROM distinct_orders do1
  JOIN exchange_rates fxo
    ON fxo.base_code = 'EUR'
   AND fxo.quote_code = do1.order_currency_code
   AND fxo.as_of = do1.placed_date
  GROUP BY do1.discount_id
)
SELECT
  d.name AS discount_name,
  d.kind AS discount_kind,
  COALESCE(cca.coupon_codes, 'automatic') AS coupon_codes,
  COALESCE(mda.discount_amount_eur, 0) AS discount_amount_eur,
  COALESCE(mo.order_count, 0) AS order_count,
  COALESCE(mr.revenue_eur, 0) AS order_revenue_eur,
  CASE WHEN COALESCE(mr.revenue_eur, 0) = 0 THEN NULL
       ELSE 100.0 * COALESCE(mda.discount_amount_eur, 0) / mr.revenue_eur
  END AS discount_pct_of_revenue,
  COALESCE(mo.distinct_customers, 0) AS distinct_customers,
  mo.first_order_date AS first_order_date,
  mo.last_order_date AS last_order_date
FROM discount_base db
JOIN discounts d ON d.id = db.discount_id
LEFT JOIN coupon_codes_agg cca ON cca.discount_id = db.discount_id
LEFT JOIN m_discount_amount mda ON mda.discount_id = db.discount_id
LEFT JOIN m_orders mo ON mo.discount_id = db.discount_id
LEFT JOIN m_revenue mr ON mr.discount_id = db.discount_id
ORDER BY discount_amount_eur DESC NULLS FIRST, d.name ASC NULLS LAST, d.id ASC NULLS LAST

WITH fod AS (
  SELECT
    od.id AS od_id,
    od.tenant_id AS tenant_id,
    od.order_id AS order_id,
    od.discount_id AS discount_id,
    od.coupon_id AS coupon_id,
    od.amount AS discount_amount_local,
    od.currency_code AS discount_currency_code,
    o.grand_total AS order_grand_total_local,
    o.currency_code AS order_currency_code,
    o.user_id AS user_id,
    o.placed_at AS placed_at,
    CAST(o.placed_at AS date) AS placed_date
  FROM order_discounts od
  JOIN orders o
    ON o.id = od.order_id
   AND o.tenant_id = od.tenant_id
  WHERE od.discount_id IS NOT NULL
    AND o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
    AND o.status <> 'cancelled'
),
discount_base AS (
  SELECT DISTINCT discount_id FROM fod
),
coupon_codes AS (
  SELECT DISTINCT fod.discount_id AS discount_id, c.code AS code
  FROM fod
  JOIN coupons c
    ON c.id = fod.coupon_id
   AND c.tenant_id = fod.tenant_id
  WHERE fod.coupon_id IS NOT NULL
),
coupon_codes_agg AS (
  SELECT
    cc.discount_id AS discount_id,
    arrayStringConcat(arraySort(groupArray(cc.code)), ', ') AS coupon_codes
  FROM coupon_codes cc
  GROUP BY cc.discount_id
),
m_discount_amount AS (
  SELECT
    fod.discount_id AS discount_id,
    SUM(CAST(fod.discount_amount_local / fxd.rate AS NUMERIC(18,6))) AS discount_amount_eur
  FROM fod
  JOIN exchange_rates fxd
    ON fxd.base_code = 'EUR'
   AND fxd.quote_code = fod.discount_currency_code
   AND fxd.as_of = fod.placed_date
  GROUP BY fod.discount_id
),
m_orders AS (
  SELECT
    fod.discount_id AS discount_id,
    COUNT(DISTINCT fod.order_id) AS order_count,
    COUNT(DISTINCT fod.user_id) AS distinct_customers,
    MIN(fod.placed_at) AS first_order_date,
    MAX(fod.placed_at) AS last_order_date
  FROM fod
  GROUP BY fod.discount_id
),
distinct_orders AS (
  SELECT DISTINCT
    discount_id,
    order_id,
    order_grand_total_local,
    order_currency_code,
    placed_date
  FROM fod
),
m_revenue AS (
  SELECT
    do1.discount_id AS discount_id,
    SUM(CAST(do1.order_grand_total_local / fxo.rate AS NUMERIC(18,6))) AS revenue_eur
  FROM distinct_orders do1
  JOIN exchange_rates fxo
    ON fxo.base_code = 'EUR'
   AND fxo.quote_code = do1.order_currency_code
   AND fxo.as_of = do1.placed_date
  GROUP BY do1.discount_id
)
SELECT
  d.name AS discount_name,
  d.kind AS discount_kind,
  COALESCE(cca.coupon_codes, 'automatic') AS coupon_codes,
  COALESCE(mda.discount_amount_eur, 0) AS discount_amount_eur,
  COALESCE(mo.order_count, 0) AS order_count,
  COALESCE(mr.revenue_eur, 0) AS order_revenue_eur,
  CASE WHEN COALESCE(mr.revenue_eur, 0) = 0 THEN NULL
       ELSE 100.0 * COALESCE(mda.discount_amount_eur, 0) / mr.revenue_eur
  END AS discount_pct_of_revenue,
  COALESCE(mo.distinct_customers, 0) AS distinct_customers,
  mo.first_order_date AS first_order_date,
  mo.last_order_date AS last_order_date
FROM discount_base db
JOIN discounts d ON d.id = db.discount_id
LEFT JOIN coupon_codes_agg cca ON cca.discount_id = db.discount_id
LEFT JOIN m_discount_amount mda ON mda.discount_id = db.discount_id
LEFT JOIN m_orders mo ON mo.discount_id = db.discount_id
LEFT JOIN m_revenue mr ON mr.discount_id = db.discount_id
ORDER BY discount_amount_eur DESC NULLS FIRST, d.name ASC NULLS LAST, d.id ASC NULLS LAST

Firebolt{ "errors": [ { "description": "Line 45, Column 40: 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": "cc5ba128-e22c-46cb-af0f-14aa9d2d278c", "query_lab
ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table13.discount_amount_local Decimal(14, 4) Decimal64(size = 0), __table16.rate Decimal(12, 6) Decimal64(size = 0). (ARGUMENT_OUT_OF_BOUND)

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

Bea's whole session, 7 turns

The request, as Bea received it

Report request from Oliver Grant (analyst for the Head of Pricing and Promotions):

Title: Discount cost vs. order volume/revenue by discount, 2025
Question: Which discounts (promotions/coupons) cost us the most in 2025, and how much order volume and revenue do they sit on?
Grain: one row per discount (discounts.id, i.e. the underlying discount definition behind a coupon or automatic promotion)
Columns:
- discount name
- discount kind (percentage/fixed_amount/free_shipping/bogo)
- linked coupon code(s) (comma-joined, or 'automatic' if none)
- total discount amount (EUR)
- order count
- total order revenue, grand_total (EUR)
- discount amount as % of order revenue
- distinct customers
- first order date
- last order date
Details: Source: order_discounts joined to orders (for grand_total, currency_code, placed_at, user_id) and discounts (for name, kind); coupons for code where order_discounts.coupon_id is set. Filter orders.placed_at in 2025-01-01 to 2025-12-31 inclusive, exclude cancelled orders (status != 'cancelled'). Convert amounts to EUR using exchange_rates (base_code='EUR', quote_code=order's currency_code, as_of=placed_at date; weekends carry prior business day rate per handbook) — divide local amount by rate to get EUR, or use the appropriate direction so figures are genuinely in EUR. Order by total discount amount (EUR) descending. Report total row count returned; print top 50.

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

Bea calls dry_run

WITH fod AS (
  SELECT
    od.id AS od_id,
    od.tenant_id AS tenant_id,
    od.order_id AS order_id,
    od.discount_id AS discount_id,
    od.coupon_id AS coupon_id,
    od.amount AS discount_amount_local,
    od.currency_code AS discount_currency_code,
    o.grand_total AS order_grand_total_local,
    o.currency_code AS order_currency_code,
    o.user_id AS user_id,
    o.placed_at AS placed_at,
    CAST(o.placed_at AS date) AS placed_date
  FROM order_discounts od
  JOIN orders o
    ON o.id = od.order_id
   AND o.tenant_id = od.tenant_id
  WHERE od.discount_id IS NOT NULL
    AND o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
    AND o.status <> 'cancelled'
),
discount_base AS (
  SELECT DISTINCT discount_id FROM fod
),
coupon_codes AS (
  SELECT DISTINCT fod.discount_id AS discount_id, c.code AS code
  FROM fod
  JOIN coupons c
    ON c.id = fod.coupon_id
   AND c.tenant_id = fod.tenant_id
  WHERE fod.coupon_id IS NOT NULL
),
coupon_codes_agg AS (
  SELECT
    cc.discount_id AS discount_id,
    STRING_AGG(cc.code, ', ' ORDER BY cc.code) AS coupon_codes
  FROM coupon_codes cc
  GROUP BY cc.discount_id
),
m_discount_amount AS (
  SELECT
    fod.discount_id AS discount_id,
    SUM(fod.discount_amount_local / fxd.rate) AS discount_amount_eur
  FROM fod
  JOIN exchange_rates fxd
    ON fxd.base_code = 'EUR'
   AND fxd.quote_code = fod.discount_currency_code
   AND fxd.as_of = fod.placed_date
  GROUP BY fod.discount_id
),
m_orders AS (
  SELECT
    fod.discount_id AS discount_id,
    COUNT(DISTINCT fod.order_id) AS order_count,
    COUNT(DISTINCT fod.user_id) AS distinct_customers,
    MIN(fod.placed_at) AS first_order_date,
    MAX(fod.placed_at) AS last_order_date
  FROM fod
  GROUP BY fod.discount_id
),
distinct_orders AS (
  SELECT DISTINCT
    discount_id,
    order_id,
    order_grand_total_local,
    order_currency_code,
    placed_date
  FROM fod
),
m_revenue AS (
  SELECT
    do1.discount_id AS discount_id,
    SUM(do1.order_grand_total_local / fxo.rate) AS revenue_eur
  FROM distinct_orders do1
  JOIN exchange_rates fxo
    ON fxo.base_code = 'EUR'
   AND fxo.quote_code = do1.order_currency_code
   AND fxo.as_of = do1.placed_date
  GROUP BY do1.discount_id
)
SELECT
  d.name AS discount_name,
  d.kind AS discount_kind,
  COALESCE(cca.coupon_codes, 'automatic') AS coupon_codes,
  ROUND(COALESCE(mda.discount_amount_eur, 0), 2) AS discount_amount_eur,
  COALESCE(mo.order_count, 0) AS order_count,
  ROUND(COALESCE(mr.revenue_eur, 0), 2) AS order_revenue_eur,
  ROUND(
    CASE WHEN COALESCE(mr.revenue_eur, 0) = 0 THEN NULL
         ELSE 100.0 * COALESCE(mda.discount_amount_eur, 0) / mr.revenue_eur
    END, 2
  ) AS discount_pct_of_revenue,
  COALESCE(mo.distinct_customers, 0) AS distinct_customers,
  mo.first_order_date AS first_order_date,
  mo.last_order_date AS last_order_date
FROM discount_base db
JOIN discounts d ON d.id = db.discount_id
LEFT JOIN coupon_codes_agg cca ON cca.discount_id = db.discount_id
LEFT JOIN m_discount_amount mda ON mda.discount_id = db.discount_id
LEFT JOIN m_orders mo ON mo.discount_id = db.discount_id
LEFT JOIN m_revenue mr ON mr.discount_id = db.discount_id
ORDER BY discount_amount_eur DESC, d.name ASC

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: discount_name, discount_kind, coupon_codes, discount_amount_eur, order_count, order_revenue_eur, discount_pct_of_revenue, distinct_customers, first_order_date, last_order_date

BeaQuery compiles and returns the expected 10 columns at the requested grain. Submitting.

Bea calls submit

WITH fod AS (
  SELECT
    od.id AS od_id,
    od.tenant_id AS tenant_id,
    od.order_id AS order_id,
    od.discount_id AS discount_id,
    od.coupon_id AS coupon_id,
    od.amount AS discount_amount_local,
    od.currency_code AS discount_currency_code,
    o.grand_total AS order_grand_total_local,
    o.currency_code AS order_currency_code,
    o.user_id AS user_id,
    o.placed_at AS placed_at,
    CAST(o.placed_at AS date) AS placed_date
  FROM order_discounts od
  JOIN orders o
    ON o.id = od.order_id
   AND o.tenant_id = od.tenant_id
  WHERE od.discount_id IS NOT NULL
    AND o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
    AND o.status <> 'cancelled'
),
discount_base AS (
  SELECT DISTINCT discount_id FROM fod
),
coupon_codes AS (
  SELECT DISTINCT fod.discount_id AS discount_id, c.code AS code
  FROM fod
  JOIN coupons c
    ON c.id = fod.coupon_id
   AND c.tenant_id = fod.tenant_id
  WHERE fod.coupon_id IS NOT NULL
),
coupon_codes_agg AS (
  SELECT
    cc.discount_id AS discount_id,
    STRING_AGG(cc.code, ', ' ORDER BY cc.code) AS coupon_codes
  FROM coupon_codes cc
  GROUP BY cc.discount_id
),
m_discount_amount AS (
  SELECT
    fod.discount_id AS discount_id,
    SUM(fod.discount_amount_local / fxd.rate) AS discount_amount_eur
  FROM fod
  JOIN exchange_rates fxd
    ON fxd.base_code = 'EUR'
   AND fxd.quote_code = fod.discount_currency_code
   AND fxd.as_of = fod.placed_date
  GROUP BY fod.discount_id
),
m_orders AS (
  SELECT
    fod.discount_id AS discount_id,
    COUNT(DISTINCT fod.order_id) AS order_count,
    COUNT(DISTINCT fod.user_id) AS distinct_customers,
    MIN(fod.placed_at) AS first_order_date,
    MAX(fod.placed_at) AS last_order_date
  FROM fod
  GROUP BY fod.discount_id
),
distinct_orders AS (
  SELECT DISTINCT
    discount_id,
    order_id,
    order_grand_total_local,
    order_currency_code,
    placed_date
  FROM fod
),
m_revenue AS (
  SELECT
    do1.discount_id AS discount_id,
    SUM(do1.order_grand_total_local / fxo.rate) AS revenue_eur
  FROM distinct_orders do1
  JOIN exchange_rates fxo
    ON fxo.base_code = 'EUR'
   AND fxo.quote_code = do1.order_currency_code
   AND fxo.as_of = do1.placed_date
  GROUP BY do1.discount_id
)
SELECT
  d.name AS discount_name,
  d.kind AS discount_kind,
  COALESCE(cca.coupon_codes, 'automatic') AS coupon_codes,
  ROUND(COALESCE(mda.discount_amount_eur, 0), 2) AS discount_amount_eur,
  COALESCE(mo.order_count, 0) AS order_count,
  ROUND(COALESCE(mr.revenue_eur, 0), 2) AS order_revenue_eur,
  ROUND(
    CASE WHEN COALESCE(mr.revenue_eur, 0) = 0 THEN NULL
         ELSE 100.0 * COALESCE(mda.discount_amount_eur, 0) / mr.revenue_eur
    END, 2
  ) AS discount_pct_of_revenue,
  COALESCE(mo.distinct_customers, 0) AS distinct_customers,
  mo.first_order_date AS first_order_date,
  mo.last_order_date AS last_order_date
FROM discount_base db
JOIN discounts d ON d.id = db.discount_id
LEFT JOIN coupon_codes_agg cca ON cca.discount_id = db.discount_id
LEFT JOIN m_discount_amount mda ON mda.discount_id = db.discount_id
LEFT JOIN m_orders mo ON mo.discount_id = db.discount_id
LEFT JOIN m_revenue mr ON mr.discount_id = db.discount_id
ORDER BY discount_amount_eur DESC, d.name ASC

The warehouse answers

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

BeaSubmitted. Summary of what the report does: - **Grain:** one row per discount definition (`discounts.id`), restricted to discounts that had at least one qualifying order in 2025. - **Filters:** orders with `placed_at` in 2025, excluding `status = 'cancelled'`. - **Coupon codes:** pulled from `coupons` via `order_discounts.coupon_id` actually used, deduplicated and comma-joined; shows `'automatic'` when a discount was only ever applied without a coupon. - **Currency conversion:** each local amount (discount cost, and separately order `grand_total`) divided by that day's EUR→local exchange rate, so figures are genuinely in EUR. - **Revenue/order-count/customers/dates** are computed over distinct orders per discount to avoid double-counting if an order carries multiple discount line rows for the same discount. - Ordered by EUR discount cost descending, with discount name as a deterministic tie-break. Row-count/50-row printing is left to the report-print step as described in the handbook.

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

Discount cost vs. order volume/revenue by discount, 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

Bea writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q148

Everything, as recorded