DB Olympics

Queries · q051 · Head of Growth Marketing · week 1, night 1

Discounting baseline: monthly coupon usage and discount rate

“How much discounting is happening platform-wide each month, and how often are coupon codes actually used?”

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

  1. Chloé Marchetti writes to Sofie Nilsen

    The manager, at the start of week 1

    Subject: What I need to get oriented this week

    Hi Sofie,

    Thrilled to be here — here's where my head's at. I know you've only got a handful of report slots a night, so I've ordered these by what I need most. Please push back if any of these are too broad or should split into more than one report.

    1. How are merchants actually acquiring shoppers, and through which channels? I want to understand where traffic and new customers are coming from across our merchant base — which channels show up most, and whether that's shifted recently. This is really just orientation for me: I don't yet know what "normal" looks like here.
    2. Are the coupons and promotions merchants run actually paying off? I'd like a sense of how much discounting is happening, how often codes get used, and whether promoted orders look healthy (not just cheaper). I'm trying to figure out if we should be telling merchants to promote more carefully — this is relevant for your product-pitch angle too.
    3. What does our email/campaign activity look like, and is anyone opening or clicking? I want a baseline read on send volume and engagement so I know whether campaigns are a strength or a weak spot for merchants on the platform.
    4. Is anyone actually using loyalty and referrals, and does it seem to matter? I keep hearing these are "nice to have" features. I want to know whether merchants who use them have any different shopper behavior, so I know whether to invest in pushing adoption.
    5. (If there's room this week) Do we run experiments/A-B tests at all, and does anyone act on the results? Lower priority — just curious whether this is a muscle we have or one I need to build from scratch.

    What I need this for: I'm meeting the leadership team Friday and want a grounded, numbers-based view of growth marketing's current state before I propose anything. No need for polish — I just want to know what's really happening so I don't propose things that are already working, or miss things that are quietly broken.

    Thanks for taking this on — let me know if you want me to cut this list down.

    Chloé

  2. Sofie Nilsen plans the night

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

    Chloé's #1 ask (channels) is pure orientation, so I'm spending two of tonight's three slots there to get both a traffic-side view (where sessions come from) and a conversion-side view (which channels actually get credit for orders/revenue) — these are different grains/sources (web_sessions vs attributions+campaigns) so they need separate reports. I collapsed each to a single 'channel' grain with a built-in YoY or share comparison instead of a month-by-channel matrix, to stay under the 50-row cap and still show 'what's normal' plus 'has it shifted'. Third slot starts Q2 (discounting) since it's next in priority and a simple monthly baseline will tell me quickly whether discounting is a big or small share of revenue before I dig into promo-order health specifics on a later night.

    Discounting baseline: monthly coupon usage and discount rate
    Question
    How much discounting is happening platform-wide each month, and how often are coupon codes actually used?
    One row per
    calendar month (2025)
    Columns
    • month - YYYY-MM label for the order's placed_at month
    • orders_count - count of orders with placed_at in that month and status not in ('cancelled')
    • orders_with_discount_count - count of those orders where discount_total > 0
    • discount_rate_pct - orders_with_discount_count / orders_count * 100, rounded to 1 decimal
    • total_order_revenue_eur - sum of orders.grand_total (net of discount) converted to EUR for that month's orders, rounded to 0 decimals
    • total_discount_amount_eur - sum of orders.discount_total converted to EUR for that month's orders, rounded to 0 decimals
    • discount_share_of_gmv_pct - total_discount_amount_eur / (total_order_revenue_eur + total_discount_amount_eur) * 100, rounded to 1 decimal (discount as a share of pre-discount gross merchandise value)
    • distinct_coupons_used - count of distinct coupon_id in coupon_redemptions where redeemed_at falls in that month
    • avg_discount_per_discounted_order_eur - total_discount_amount_eur / nullif(orders_with_discount_count,0), rounded to 0 decimals

  3. Ines writes the SQL

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

    Dry run 1 · STATUS: REJECTED
    WITH months AS (
  SELECT DISTINCT month_label AS month
  FROM calendar_days
  WHERE year_num = 2025
),
order_calc AS (
  SELECT
    to_char(o.placed_at, 'YYYY-MM') AS month,
    o.grand_total / CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE er.rate END AS revenue_eur,
    o.discount_total / CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE er.rate END AS discount_eur,
    CASE WHEN o.discount_total > 0 THEN 1 ELSE 0 END AS has_discount
  FROM orders o
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
    AND er.quote_code = o.currency_code
    AND er.as_of = date(o.placed_at)
  WHERE o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
    AND o.status <> 'cancelled'
),
order_agg AS (
  SELECT
    month,
    COUNT(*) AS orders_count,
    SUM(has_discount) AS orders_with_discount_count,
    SUM(revenue_eur) AS total_order_revenue_eur,
    SUM(discount_eur) AS total_discount_amount_eur
  FROM order_calc
  GROUP BY month
),
coupon_agg AS (
  SELECT
    to_char(cr.redeemed_at, 'YYYY-MM') AS month,
    COUNT(DISTINCT cr.coupon_id) AS distinct_coupons_used
  FROM coupon_redemptions cr
  WHERE cr.redeemed_at >= DATE '2025-01-01'
    AND cr.redeemed_at < DATE '2026-01-01'
  GROUP BY month
)
SELECT
  m.month,
  COALESCE(oa.orders_count, 0) AS orders_count,
  COALESCE(oa.orders_with_discount_count, 0) AS orders_with_discount_count,
  ROUND(COALESCE(oa.orders_with_discount_count, 0)::numeric * 100.0 / NULLIF(oa.orders_count,0), 1) AS discount_rate_pct,
  ROUND(COALESCE(oa.total_order_revenue_eur,0)::numeric, 0) AS total_order_revenue_eur,
  ROUND(COALESCE(oa.total_discount_amount_eur,0)::numeric, 0) AS total_discount_amount_eur,
  ROUND(COALESCE(oa.total_discount_amount_eur,0)::numeric * 100.0 / NULLIF(oa.total_order_revenue_eur + oa.total_discount_amount_eur, 0), 1) AS discount_share_of_gmv_pct,
  COALESCE(ca.distinct_coupons_used, 0) AS distinct_coupons_used,
  ROUND(COALESCE(oa.total_discount_amount_eur,0)::numeric / NULLIF(oa.orders_with_discount_count,0), 0) AS avg_discount_per_discounted_order_eur
FROM months m
LEFT JOIN order_agg oa ON oa.month = m.month
LEFT JOIN coupon_agg ca ON ca.month = m.month
ORDER BY m.month
    Dry run 2 · 2 changes from the dry run before · STATUS: COMPILES (dry run on the empty schema, no data read)
    WITH months AS (  SELECT DISTINCT month_label AS month  FROM calendar_days  WHERE year_num = 2025),order_calc AS (  SELECT    to_char(o.placed_at, 'YYYY-MM') AS month,    strftime(o.placed_at, '%Y-%m') AS month,    o.grand_total / CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE er.rate END AS revenue_eur,    o.discount_total / CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE er.rate END AS discount_eur,    CASE WHEN o.discount_total > 0 THEN 1 ELSE 0 END AS has_discount  FROM orders o  LEFT JOIN exchange_rates er    ON er.base_code = 'EUR'    AND er.quote_code = o.currency_code    AND er.as_of = date(o.placed_at)  WHERE o.placed_at >= DATE '2025-01-01'    AND o.placed_at < DATE '2026-01-01'    AND o.status <> 'cancelled'),order_agg AS (  SELECT    month,    COUNT(*) AS orders_count,    SUM(has_discount) AS orders_with_discount_count,    SUM(revenue_eur) AS total_order_revenue_eur,    SUM(discount_eur) AS total_discount_amount_eur  FROM order_calc  GROUP BY month),coupon_agg AS (  SELECT    to_char(cr.redeemed_at, 'YYYY-MM') AS month,    strftime(cr.redeemed_at, '%Y-%m') AS month,    COUNT(DISTINCT cr.coupon_id) AS distinct_coupons_used  FROM coupon_redemptions cr  WHERE cr.redeemed_at >= DATE '2025-01-01'    AND cr.redeemed_at < DATE '2026-01-01'  GROUP BY month)SELECT  m.month,  COALESCE(oa.orders_count, 0) AS orders_count,  COALESCE(oa.orders_with_discount_count, 0) AS orders_with_discount_count,  ROUND(COALESCE(oa.orders_with_discount_count, 0)::numeric * 100.0 / NULLIF(oa.orders_count,0), 1) AS discount_rate_pct,  ROUND(COALESCE(oa.total_order_revenue_eur,0)::numeric, 0) AS total_order_revenue_eur,  ROUND(COALESCE(oa.total_discount_amount_eur,0)::numeric, 0) AS total_discount_amount_eur,  ROUND(COALESCE(oa.total_discount_amount_eur,0)::numeric * 100.0 / NULLIF(oa.total_order_revenue_eur + oa.total_discount_amount_eur, 0), 1) AS discount_share_of_gmv_pct,  COALESCE(ca.distinct_coupons_used, 0) AS distinct_coupons_used,  ROUND(COALESCE(oa.total_discount_amount_eur,0)::numeric / NULLIF(oa.orders_with_discount_count,0), 0) AS avg_discount_per_discounted_order_eurFROM months mLEFT JOIN order_agg oa ON oa.month = m.monthLEFT JOIN coupon_agg ca ON ca.month = m.monthORDER BY m.month

    “Query compiles with the expected 9 columns at the requested month grain. 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 delivers one row per 2025 month with order counts, discount incidence, EUR-normalized revenue and discount totals, discount share of GMV, distinct coupons used, and average discount per discounted order — 9 columns, ordered by month ascending.”

  4. The report comes back overnight

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

    month   | orders_count | orders_with_discount_count | discount_rate_pct | total_order_revenue_eur | total_discount_amount_eur | discount_share_of_gmv_pct | distinct_coupons_used | avg_discount_per_discounted_order_eur
--------+--------------+----------------------------+-------------------+-------------------------+---------------------------+---------------------------+-----------------------+--------------------------------------
2025-01 |         1768 |                        940 |              53.2 |                  578716 |                     48507 |                       7.7 |                    28 |                                  52.0
2025-02 |         1370 |                        497 |              36.3 |                  430561 |                     25731 |                       5.6 |                    30 |                                  52.0
2025-03 |         1586 |                        559 |              35.2 |                  403598 |                     28344 |                       6.6 |                    27 |                                  51.0
2025-04 |         1520 |                        528 |              34.7 |                  471989 |                     32179 |                       6.4 |                    28 |                                  61.0
2025-05 |         1633 |                        595 |              36.4 |                  463804 |                     32917 |                       6.6 |                    35 |                                  55.0
2025-06 |         1461 |                        495 |              33.9 |                  346256 |                     24022 |                       6.5 |                    26 |                                  49.0
2025-07 |         1596 |                        940 |              58.9 |                  365113 |                     49877 |                      12.0 |                    22 |                                  53.0
2025-08 |         1502 |                        549 |              36.6 |                  331782 |                     24889 |                       7.0 |                    35 |                                  45.0
2025-09 |         1659 |                        601 |              36.2 |                  465898 |                     36138 |                       7.2 |                    35 |                                  60.0
2025-10 |         1828 |                        610 |              33.4 |                  489962 |                     33807 |                       6.5 |                    40 |                                  55.0
2025-11 |         2550 |                        949 |              37.2 |                  817689 |                     82706 |                       9.2 |                    96 |                                  87.0
2025-12 |         2994 |                        818 |              27.3 |                  671070 |                     48827 |                       6.8 |                    89 |                                  60.0
(12 rows)

  5. What came of it

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

    Later queries that build on this report:

  6. It becomes benchmark query q051

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

    • spellingcast (5×)
    • spellingdate cast
    • placeholderyear month (2×)
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision; bare NUMERIC cast that only served the rounding removed
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision; bare NUMERIC cast that only served the rounding removed
    • made explicita selected t.col gets AS col, its standard name made explicit
    • 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 months AS (  SELECT DISTINCT month_label AS month  FROM calendar_days  WHERE year_num = 2025),order_calc AS (  SELECT    strftime(o.placed_at, '%Y-%m') AS month,    {{year_month(o.placed_at)}} AS month,    o.grand_total / CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE er.rate END AS revenue_eur,    o.discount_total / CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE er.rate END AS discount_eur,    CASE WHEN o.discount_total > 0 THEN 1 ELSE 0 END AS has_discount  FROM orders o  LEFT JOIN exchange_rates er    ON er.base_code = 'EUR'    AND er.quote_code = o.currency_code    AND er.as_of = date(o.placed_at)    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'    AND o.status <> 'cancelled'),order_agg AS (  SELECT    month,    COUNT(*) AS orders_count,    SUM(has_discount) AS orders_with_discount_count,    SUM(revenue_eur) AS total_order_revenue_eur,    SUM(discount_eur) AS total_discount_amount_eur  FROM order_calc  GROUP BY month),coupon_agg AS (  SELECT    strftime(cr.redeemed_at, '%Y-%m') AS month,    {{year_month(cr.redeemed_at)}} AS month,    COUNT(DISTINCT cr.coupon_id) AS distinct_coupons_used  FROM coupon_redemptions cr  WHERE cr.redeemed_at >= DATE '2025-01-01'    AND cr.redeemed_at < DATE '2026-01-01'  GROUP BY month)SELECT  m.month,  m.month AS month,  COALESCE(oa.orders_count, 0) AS orders_count,  COALESCE(oa.orders_with_discount_count, 0) AS orders_with_discount_count,  ROUND(COALESCE(oa.orders_with_discount_count, 0)::numeric * 100.0 / NULLIF(oa.orders_count,0), 1) AS discount_rate_pct,  ROUND(COALESCE(oa.total_order_revenue_eur,0)::numeric, 0) AS total_order_revenue_eur,  ROUND(COALESCE(oa.total_discount_amount_eur,0)::numeric, 0) AS total_discount_amount_eur,  ROUND(COALESCE(oa.total_discount_amount_eur,0)::numeric * 100.0 / NULLIF(oa.total_order_revenue_eur + oa.total_discount_amount_eur, 0), 1) AS discount_share_of_gmv_pct,  CAST(COALESCE(oa.orders_with_discount_count, 0) AS numeric(18,6)) * 100.0 / NULLIF(oa.orders_count,0) AS discount_rate_pct,  COALESCE(oa.total_order_revenue_eur,0) AS total_order_revenue_eur,  COALESCE(oa.total_discount_amount_eur,0) AS total_discount_amount_eur,  COALESCE(oa.total_discount_amount_eur,0) * 100.0 / NULLIF(oa.total_order_revenue_eur + oa.total_discount_amount_eur, 0) AS discount_share_of_gmv_pct,  COALESCE(ca.distinct_coupons_used, 0) AS distinct_coupons_used,  ROUND(COALESCE(oa.total_discount_amount_eur,0)::numeric / NULLIF(oa.orders_with_discount_count,0), 0) AS avg_discount_per_discounted_order_eur  COALESCE(oa.total_discount_amount_eur,0) / NULLIF(oa.orders_with_discount_count,0) AS avg_discount_per_discounted_order_eurFROM months mLEFT JOIN order_agg oa ON oa.month = m.monthLEFT JOIN coupon_agg ca ON ca.month = m.monthORDER BY m.monthORDER BY m.month NULLS LAST

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

    WITH months AS (
  SELECT DISTINCT month_label AS month
  FROM calendar_days
  WHERE year_num = 2025
),
order_calc AS (
  SELECT
    strftime(o.placed_at, '%Y-%m') AS month,
    o.grand_total / CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE er.rate END AS revenue_eur,
    o.discount_total / CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE er.rate END AS discount_eur,
    CASE WHEN o.discount_total > 0 THEN 1 ELSE 0 END AS has_discount
  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'
    AND o.status <> 'cancelled'
),
order_agg AS (
  SELECT
    month,
    COUNT(*) AS orders_count,
    SUM(has_discount) AS orders_with_discount_count,
    SUM(revenue_eur) AS total_order_revenue_eur,
    SUM(discount_eur) AS total_discount_amount_eur
  FROM order_calc
  GROUP BY month
),
coupon_agg AS (
  SELECT
    strftime(cr.redeemed_at, '%Y-%m') AS month,
    COUNT(DISTINCT cr.coupon_id) AS distinct_coupons_used
  FROM coupon_redemptions cr
  WHERE cr.redeemed_at >= DATE '2025-01-01'
    AND cr.redeemed_at < DATE '2026-01-01'
  GROUP BY month
)
SELECT
  m.month AS month,
  COALESCE(oa.orders_count, 0) AS orders_count,
  COALESCE(oa.orders_with_discount_count, 0) AS orders_with_discount_count,
  CAST(COALESCE(oa.orders_with_discount_count, 0) AS numeric(18,6)) * 100.0 / NULLIF(oa.orders_count,0) AS discount_rate_pct,
  COALESCE(oa.total_order_revenue_eur,0) AS total_order_revenue_eur,
  COALESCE(oa.total_discount_amount_eur,0) AS total_discount_amount_eur,
  COALESCE(oa.total_discount_amount_eur,0) * 100.0 / NULLIF(oa.total_order_revenue_eur + oa.total_discount_amount_eur, 0) AS discount_share_of_gmv_pct,
  COALESCE(ca.distinct_coupons_used, 0) AS distinct_coupons_used,
  COALESCE(oa.total_discount_amount_eur,0) / NULLIF(oa.orders_with_discount_count,0) AS avg_discount_per_discounted_order_eur
FROM months m
LEFT JOIN order_agg oa ON oa.month = m.month
LEFT JOIN coupon_agg ca ON ca.month = m.month
ORDER BY m.month NULLS LAST
    10 ms100 ms1 sHyper138 msDuckDB53.1 msGizmoSQL56.4 msclickhouse_brew172 msFirebolterrorDataFusionwrong resultClickHouseerror
    • Firebolt{ "errors": [ { "description": "Line 9, Column 19: 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": "6beb4600-1a27-40c9-ba93-c4fff60a1dbb", "query_labe
    • DataFusionrow 1: col 2: "1964592" vs "0"; col 3: "1044497" vs "0"; col 4: "53.1661026818800035834412437798789774161760" vs ""
    • ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table5.grand_total Decimal(14, 4) Decimal64(size = 0), if(equals(__table5.currency_code, 'EUR'_String), 1_UInt8, __table6.rate) Nullable(Decimal(18, 6)) Nullable(size = 0, Deci

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

  7. Everything, as recorded

    The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted

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

Title: Discounting baseline: monthly coupon usage and discount rate
Question: How much discounting is happening platform-wide each month, and how often are coupon codes actually used?
Grain: one row per calendar month (2025)
Columns:
- month - YYYY-MM label for the order's placed_at month
- orders_count - count of orders with placed_at in that month and status not in ('cancelled')
- orders_with_discount_count - count of those orders where discount_total > 0
- discount_rate_pct - orders_with_discount_count / orders_count * 100, rounded to 1 decimal
- total_order_revenue_eur - sum of orders.grand_total (net of discount) converted to EUR for that month's orders, rounded to 0 decimals
- total_discount_amount_eur - sum of orders.discount_total converted to EUR for that month's orders, rounded to 0 decimals
- discount_share_of_gmv_pct - total_discount_amount_eur / (total_order_revenue_eur + total_discount_amount_eur) * 100, rounded to 1 decimal (discount as a share of pre-discount gross merchandise value)
- distinct_coupons_used - count of distinct coupon_id in coupon_redemptions where redeemed_at falls in that month
- avg_discount_per_discounted_order_eur - total_discount_amount_eur / nullif(orders_with_discount_count,0), rounded to 0 decimals
Details: Primary source: orders (placed_at for month bucketing, status != 'cancelled', discount_total/grand_total for discount figures). Convert each order's currency_code amounts to EUR using exchange_rates (base_code='EUR', quote_code=orders.currency_code, as_of = date of placed_at; EUR itself = rate 1). Secondary source: coupon_redemptions.redeemed_at for distinct_coupons_used, same month bucketing, no currency conversion needed for that count. No tenant filter — platform-wide. Cover months 2025-01 through 2025-12 only (use calendar_days or date_trunc on placed_at). Order by month ascending.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Ines calls dry_run
      WITH months AS (
  SELECT DISTINCT month_label AS month
  FROM calendar_days
  WHERE year_num = 2025
),
order_calc AS (
  SELECT
    to_char(o.placed_at, 'YYYY-MM') AS month,
    o.grand_total / CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE er.rate END AS revenue_eur,
    o.discount_total / CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE er.rate END AS discount_eur,
    CASE WHEN o.discount_total > 0 THEN 1 ELSE 0 END AS has_discount
  FROM orders o
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
    AND er.quote_code = o.currency_code
    AND er.as_of = date(o.placed_at)
  WHERE o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
    AND o.status <> 'cancelled'
),
order_agg AS (
  SELECT
    month,
    COUNT(*) AS orders_count,
    SUM(has_discount) AS orders_with_discount_count,
    SUM(revenue_eur) AS total_order_revenue_eur,
    SUM(discount_eur) AS total_discount_amount_eur
  FROM order_calc
  GROUP BY month
),
coupon_agg AS (
  SELECT
    to_char(cr.redeemed_at, 'YYYY-MM') AS month,
    COUNT(DISTINCT cr.coupon_id) AS distinct_coupons_used
  FROM coupon_redemptions cr
  WHERE cr.redeemed_at >= DATE '2025-01-01'
    AND cr.redeemed_at < DATE '2026-01-01'
  GROUP BY month
)
SELECT
  m.month,
  COALESCE(oa.orders_count, 0) AS orders_count,
  COALESCE(oa.orders_with_discount_count, 0) AS orders_with_discount_count,
  ROUND(COALESCE(oa.orders_with_discount_count, 0)::numeric * 100.0 / NULLIF(oa.orders_count,0), 1) AS discount_rate_pct,
  ROUND(COALESCE(oa.total_order_revenue_eur,0)::numeric, 0) AS total_order_revenue_eur,
  ROUND(COALESCE(oa.total_discount_amount_eur,0)::numeric, 0) AS total_discount_amount_eur,
  ROUND(COALESCE(oa.total_discount_amount_eur,0)::numeric * 100.0 / NULLIF(oa.total_order_revenue_eur + oa.total_discount_amount_eur, 0), 1) AS discount_share_of_gmv_pct,
  COALESCE(ca.distinct_coupons_used, 0) AS distinct_coupons_used,
  ROUND(COALESCE(oa.total_discount_amount_eur,0)::numeric / NULLIF(oa.orders_with_discount_count,0), 0) AS avg_discount_per_discounted_order_eur
FROM months m
LEFT JOIN order_agg oa ON oa.month = m.month
LEFT JOIN coupon_agg ca ON ca.month = m.month
ORDER BY m.month
    • The warehouse answers
      STATUS: REJECTED
  - does not compile: Catalog Error: Scalar Function with name to_char does not exist!
    • Ines calls dry_run
      WITH months AS (
  SELECT DISTINCT month_label AS month
  FROM calendar_days
  WHERE year_num = 2025
),
order_calc AS (
  SELECT
    strftime(o.placed_at, '%Y-%m') AS month,
    o.grand_total / CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE er.rate END AS revenue_eur,
    o.discount_total / CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE er.rate END AS discount_eur,
    CASE WHEN o.discount_total > 0 THEN 1 ELSE 0 END AS has_discount
  FROM orders o
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
    AND er.quote_code = o.currency_code
    AND er.as_of = date(o.placed_at)
  WHERE o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
    AND o.status <> 'cancelled'
),
order_agg AS (
  SELECT
    month,
    COUNT(*) AS orders_count,
    SUM(has_discount) AS orders_with_discount_count,
    SUM(revenue_eur) AS total_order_revenue_eur,
    SUM(discount_eur) AS total_discount_amount_eur
  FROM order_calc
  GROUP BY month
),
coupon_agg AS (
  SELECT
    strftime(cr.redeemed_at, '%Y-%m') AS month,
    COUNT(DISTINCT cr.coupon_id) AS distinct_coupons_used
  FROM coupon_redemptions cr
  WHERE cr.redeemed_at >= DATE '2025-01-01'
    AND cr.redeemed_at < DATE '2026-01-01'
  GROUP BY month
)
SELECT
  m.month,
  COALESCE(oa.orders_count, 0) AS orders_count,
  COALESCE(oa.orders_with_discount_count, 0) AS orders_with_discount_count,
  ROUND(COALESCE(oa.orders_with_discount_count, 0)::numeric * 100.0 / NULLIF(oa.orders_count,0), 1) AS discount_rate_pct,
  ROUND(COALESCE(oa.total_order_revenue_eur,0)::numeric, 0) AS total_order_revenue_eur,
  ROUND(COALESCE(oa.total_discount_amount_eur,0)::numeric, 0) AS total_discount_amount_eur,
  ROUND(COALESCE(oa.total_discount_amount_eur,0)::numeric * 100.0 / NULLIF(oa.total_order_revenue_eur + oa.total_discount_amount_eur, 0), 1) AS discount_share_of_gmv_pct,
  COALESCE(ca.distinct_coupons_used, 0) AS distinct_coupons_used,
  ROUND(COALESCE(oa.total_discount_amount_eur,0)::numeric / NULLIF(oa.orders_with_discount_count,0), 0) AS avg_discount_per_discounted_order_eur
FROM months m
LEFT JOIN order_agg oa ON oa.month = m.month
LEFT JOIN coupon_agg ca ON ca.month = m.month
ORDER BY m.month
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: month, orders_count, orders_with_discount_count, discount_rate_pct, total_order_revenue_eur, total_discount_amount_eur, discount_share_of_gmv_pct, distinct_coupons_used, avg_discount_per_discounted_order_eur
    • InesQuery compiles with the expected 9 columns at the requested month grain. Submitting.
    • Ines calls submit
      WITH months AS (
  SELECT DISTINCT month_label AS month
  FROM calendar_days
  WHERE year_num = 2025
),
order_calc AS (
  SELECT
    strftime(o.placed_at, '%Y-%m') AS month,
    o.grand_total / CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE er.rate END AS revenue_eur,
    o.discount_total / CASE WHEN o.currency_code = 'EUR' THEN 1 ELSE er.rate END AS discount_eur,
    CASE WHEN o.discount_total > 0 THEN 1 ELSE 0 END AS has_discount
  FROM orders o
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
    AND er.quote_code = o.currency_code
    AND er.as_of = date(o.placed_at)
  WHERE o.placed_at >= DATE '2025-01-01'
    AND o.placed_at < DATE '2026-01-01'
    AND o.status <> 'cancelled'
),
order_agg AS (
  SELECT
    month,
    COUNT(*) AS orders_count,
    SUM(has_discount) AS orders_with_discount_count,
    SUM(revenue_eur) AS total_order_revenue_eur,
    SUM(discount_eur) AS total_discount_amount_eur
  FROM order_calc
  GROUP BY month
),
coupon_agg AS (
  SELECT
    strftime(cr.redeemed_at, '%Y-%m') AS month,
    COUNT(DISTINCT cr.coupon_id) AS distinct_coupons_used
  FROM coupon_redemptions cr
  WHERE cr.redeemed_at >= DATE '2025-01-01'
    AND cr.redeemed_at < DATE '2026-01-01'
  GROUP BY month
)
SELECT
  m.month,
  COALESCE(oa.orders_count, 0) AS orders_count,
  COALESCE(oa.orders_with_discount_count, 0) AS orders_with_discount_count,
  ROUND(COALESCE(oa.orders_with_discount_count, 0)::numeric * 100.0 / NULLIF(oa.orders_count,0), 1) AS discount_rate_pct,
  ROUND(COALESCE(oa.total_order_revenue_eur,0)::numeric, 0) AS total_order_revenue_eur,
  ROUND(COALESCE(oa.total_discount_amount_eur,0)::numeric, 0) AS total_discount_amount_eur,
  ROUND(COALESCE(oa.total_discount_amount_eur,0)::numeric * 100.0 / NULLIF(oa.total_order_revenue_eur + oa.total_discount_amount_eur, 0), 1) AS discount_share_of_gmv_pct,
  COALESCE(ca.distinct_coupons_used, 0) AS distinct_coupons_used,
  ROUND(COALESCE(oa.total_discount_amount_eur,0)::numeric / NULLIF(oa.orders_with_discount_count,0), 0) AS avg_discount_per_discounted_order_eur
FROM months m
LEFT JOIN order_agg oa ON oa.month = m.month
LEFT JOIN coupon_agg ca ON ca.month = m.month
ORDER BY m.month
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • InesSubmitted. The report delivers one row per 2025 month with order counts, discount incidence, EUR-normalized revenue and discount totals, discount share of GMV, distinct coupons used, and average discount per discounted order — 9 columns, ordered by month ascending.

    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 (6 KB) · the week's memo (3 KB)