Queries · q152 · Head of Pricing and Promotions · week 1, night 2

Coupons with a configured usage cap: actual breaches of usage_limit or per_customer_limit

“Among coupons that actually have a usage_limit or per_customer_limit configured, which ones have been redeemed beyond that stated cap?”

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

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

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

   Show the whole note
   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

3. Oliver Grant plans the night

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

   Two things from last night change tonight's plan:

   (1) The single biggest cost line in Report 1 — "Subscription delivery (prepaid)" — shows real discount spend (up to ~€24k on one tenant instance, and it recurs across ~6 rows/tenants) but €0.0 associated order revenue every single time, which also explains the absurd discount_pct_of_revenue values in Report 2 (Grind Collective 99.92%, Honeycomb Larder 96.93%, The Pencil Case 781.56%). Before I rank "biggest cost, no lift" discounts for Marco, I need to know whether this is a real zero-margin giveaway or a billing-model artifact where the actual revenue is collected via subscription invoices rather than the order itself. Getting this wrong would make me tell Marco to kill a discount that isn't actually losing money, or miss one that genuinely is.

   (2) Report 3's abuse framing was broken: usage_limit and per_customer_limit were NULL for every coupon shown, so "max redemptions by a customer > limit" could never actually fire — we only surfaced raw repeat-redemption counts, not real breaches. I want to redo this narrowly on coupons that actually have a configured cap, which is the only place "abuse" is well-defined.

   I'm also starting the hard incrementality question (Marco/Tobias Q2) now rather than waiting, using a within-customer before/after comparison (discounted order value vs. the same customers' other, non-discounted orders) as an honest, approximate read — this needs iteration so it's better to see a first pass early.

   All three tonight are independent and don't block each other.

   Read the whole plan
   Coupons with a configured usage cap: actual breaches of usage_limit or per_customer_limit

   Question

   Among coupons that actually have a usage_limit or per_customer_limit configured, which ones have been redeemed beyond that stated cap?

   One row per

   coupon (coupons.id)

   Columns

   • coupon code
   • tenant name
   • status
   • usage_limit
   • per_customer_limit
   • times_redeemed (actual count from coupon_redemptions)
   • redemptions over usage_limit (times_redeemed - usage_limit, floor 0)
   • max redemptions by a single customer
   • distinct customers whose redemptions exceed per_customer_limit
   • total discount redeemed (EUR)

4. Omar 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.coupon_id,
       cr.user_id,
       cr.discount_amount,
       cr.currency_code,
       cr.redeemed_at
     FROM coupon_redemptions cr
     WHERE cr.redeemed_at >= DATE '2023-01-01'
       AND cr.redeemed_at < DATE '2026-01-01'
   ),
   coupon_stats AS (
     SELECT
       r.coupon_id,
       COUNT(*) AS times_redeemed,
       COUNT(DISTINCT r.user_id) AS distinct_customers,
       SUM(r.discount_amount / COALESCE(er.rate, 1)) AS total_discount_eur
     FROM redemptions r
     LEFT 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)
     GROUP BY r.coupon_id
   ),
   per_customer AS (
     SELECT
       r.coupon_id,
       r.user_id,
       COUNT(*) AS redemption_count
     FROM redemptions r
     GROUP BY r.coupon_id, r.user_id
   ),
   customer_stats AS (
     SELECT
       pc.coupon_id,
       MAX(pc.redemption_count) AS max_redemptions_by_customer,
       COUNT(*) FILTER (WHERE c.per_customer_limit IS NOT NULL AND pc.redemption_count > c.per_customer_limit) AS customers_exceeding
     FROM per_customer pc
     JOIN coupons c ON c.id = pc.coupon_id
     GROUP BY pc.coupon_id
   )
   SELECT
     c.code AS coupon_code,
     t.name AS tenant_name,
     c.status,
     c.usage_limit,
     c.per_customer_limit,
     COALESCE(cs.times_redeemed, 0) AS times_redeemed,
     CASE WHEN c.usage_limit IS NOT NULL
          THEN GREATEST(COALESCE(cs.times_redeemed, 0) - c.usage_limit, 0)
          ELSE 0 END AS redemptions_over_usage_limit,
     COALESCE(custs.max_redemptions_by_customer, 0) AS max_redemptions_by_customer,
     COALESCE(custs.customers_exceeding, 0) AS customers_exceeding_per_customer_limit,
     ROUND(COALESCE(cs.total_discount_eur, 0), 2) AS total_discount_redeemed_eur,
     COUNT(*) OVER () AS total_matching_coupons
   FROM coupons c
   JOIN tenants t ON t.id = c.tenant_id
   LEFT JOIN coupon_stats cs ON cs.coupon_id = c.id
   LEFT JOIN customer_stats custs ON custs.coupon_id = c.id
   WHERE (c.usage_limit IS NOT NULL OR c.per_customer_limit IS NOT NULL)
     AND (
       (c.usage_limit IS NOT NULL AND COALESCE(cs.times_redeemed, 0) > c.usage_limit)
       OR COALESCE(custs.customers_exceeding, 0) > 0
     )
   ORDER BY redemptions_over_usage_limit DESC, customers_exceeding_per_customer_limit DESC, coupon_code ASC
   LIMIT 50

   “Query compiles cleanly. 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 the approach: - Grain: one row per coupon, restricted to coupons with a configured usage_limit and/or per_customer_limit, further filtered to only those actually breached (either total redemptions exceed usage_limit, or at least one customer exceeds per_customer_limit). - Conversion: discount amounts converted to EUR day-by-day using exchange_rates (EUR→currency rate, divided out), based on each redemption's own currency_code/redeemed_at — this made the orders join unnecessary since those fields are already on coupon_redemptions. - Per-customer logic: a subquery counts redemptions per (coupon, user), then rolls up into max redemptions by a single customer and the count of customers exceeding the per-customer cap. - Ordering/paging: sorted by redemptions-over-limit desc, then customers-exceeding desc, then coupon code; capped at 50 rows, with a total_matching_coupons column via a window function so the analyst can see if more rows exist beyond the printed page.”

5. The report comes back overnight

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

   coupon_code | tenant_name | status | usage_limit | per_customer_limit | times_redeemed | redemptions_over_usage_limit | max_redemptions_by_customer | customers_exceeding_per_customer_limit | total_discount_redeemed_eur | total_matching_coupons
   ------------+-------------+--------+-------------+--------------------+----------------+------------------------------+-----------------------------+----------------------------------------+-----------------------------+-----------------------
   (0 rows)

6. What came of it

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

   It builds on:

   • q150Coupon redemption patterns: repeat use, limit breaches and post-expiry usage, 2025

7. It becomes benchmark query q152

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

   • spellingfetch first
   • determinismtie-breaker: coupon codes are unique only per tenant
   • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
   • numericsdocs/NUMERICS.md: x / exchange rate cast to NUMERIC(18,6)
   • made explicita selected t.col gets AS col, its standard name made explicit (12×)
   • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (4×)
   • placeholderfetch first
   The benchmark text, against the submitted SQL

   WITH redemptions AS (  SELECT    cr.coupon_id,    cr.user_id,    cr.discount_amount,    cr.currency_code,    cr.redeemed_at    cr.coupon_id AS coupon_id,    cr.user_id AS user_id,    cr.discount_amount AS discount_amount,    cr.currency_code AS currency_code,    cr.redeemed_at AS redeemed_at  FROM coupon_redemptions cr  WHERE cr.redeemed_at >= DATE '2023-01-01'    AND cr.redeemed_at < DATE '2026-01-01'),coupon_stats AS (  SELECT    r.coupon_id,    r.coupon_id AS coupon_id,    COUNT(*) AS times_redeemed,    COUNT(DISTINCT r.user_id) AS distinct_customers,    SUM(r.discount_amount / COALESCE(er.rate, 1)) AS total_discount_eur    SUM(CAST(r.discount_amount / COALESCE(er.rate, 1) AS NUMERIC(18,6))) AS total_discount_eur  FROM redemptions r  LEFT 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)  GROUP BY r.coupon_id),per_customer AS (  SELECT    r.coupon_id,    r.user_id,    r.coupon_id AS coupon_id,    r.user_id AS user_id,    COUNT(*) AS redemption_count  FROM redemptions r  GROUP BY r.coupon_id, r.user_id),customer_stats AS (  SELECT    pc.coupon_id,    pc.coupon_id AS coupon_id,    MAX(pc.redemption_count) AS max_redemptions_by_customer,    COUNT(*) FILTER (WHERE c.per_customer_limit IS NOT NULL AND pc.redemption_count > c.per_customer_limit) AS customers_exceeding  FROM per_customer pc  JOIN coupons c ON c.id = pc.coupon_id  GROUP BY pc.coupon_id)SELECT  c.code AS coupon_code,  t.name AS tenant_name,  c.status,  c.usage_limit,  c.per_customer_limit,  c.status AS status,  c.usage_limit AS usage_limit,  c.per_customer_limit AS per_customer_limit,  COALESCE(cs.times_redeemed, 0) AS times_redeemed,  CASE WHEN c.usage_limit IS NOT NULL       THEN GREATEST(COALESCE(cs.times_redeemed, 0) - c.usage_limit, 0)       ELSE 0 END AS redemptions_over_usage_limit,  COALESCE(custs.max_redemptions_by_customer, 0) AS max_redemptions_by_customer,  COALESCE(custs.customers_exceeding, 0) AS customers_exceeding_per_customer_limit,  ROUND(COALESCE(cs.total_discount_eur, 0), 2) AS total_discount_redeemed_eur,  COALESCE(cs.total_discount_eur, 0) AS total_discount_redeemed_eur,  COUNT(*) OVER () AS total_matching_couponsFROM coupons cJOIN tenants t ON t.id = c.tenant_idLEFT JOIN coupon_stats cs ON cs.coupon_id = c.idLEFT JOIN customer_stats custs ON custs.coupon_id = c.idWHERE (c.usage_limit IS NOT NULL OR c.per_customer_limit IS NOT NULL)  AND (    (c.usage_limit IS NOT NULL AND COALESCE(cs.times_redeemed, 0) > c.usage_limit)    OR COALESCE(custs.customers_exceeding, 0) > 0  )ORDER BY redemptions_over_usage_limit DESC, customers_exceeding_per_customer_limit DESC, coupon_code ASCLIMIT 50ORDER BY redemptions_over_usage_limit DESC NULLS FIRST, customers_exceeding_per_customer_limit DESC NULLS FIRST, coupon_code ASC NULLS LAST, c.id ASC NULLS LAST{{fetch_first(50)}}

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

   DuckDB · Hyper · GizmoSQL · CedarDB · pg_duckdb · PostgreSQL · Firebolt · ClickHouse · chDB · clickhouse_brewDataFusion

   WITH redemptions AS (
     SELECT
       cr.coupon_id AS coupon_id,
       cr.user_id AS user_id,
       cr.discount_amount AS discount_amount,
       cr.currency_code AS currency_code,
       cr.redeemed_at AS redeemed_at
     FROM coupon_redemptions cr
     WHERE cr.redeemed_at >= DATE '2023-01-01'
       AND cr.redeemed_at < DATE '2026-01-01'
   ),
   coupon_stats AS (
     SELECT
       r.coupon_id AS coupon_id,
       COUNT(*) AS times_redeemed,
       COUNT(DISTINCT r.user_id) AS distinct_customers,
       SUM(CAST(r.discount_amount / COALESCE(er.rate, 1) AS NUMERIC(18,6))) AS total_discount_eur
     FROM redemptions r
     LEFT 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)
     GROUP BY r.coupon_id
   ),
   per_customer AS (
     SELECT
       r.coupon_id AS coupon_id,
       r.user_id AS user_id,
       COUNT(*) AS redemption_count
     FROM redemptions r
     GROUP BY r.coupon_id, r.user_id
   ),
   customer_stats AS (
     SELECT
       pc.coupon_id AS coupon_id,
       MAX(pc.redemption_count) AS max_redemptions_by_customer,
       COUNT(*) FILTER (WHERE c.per_customer_limit IS NOT NULL AND pc.redemption_count > c.per_customer_limit) AS customers_exceeding
     FROM per_customer pc
     JOIN coupons c ON c.id = pc.coupon_id
     GROUP BY pc.coupon_id
   )
   SELECT
     c.code AS coupon_code,
     t.name AS tenant_name,
     c.status AS status,
     c.usage_limit AS usage_limit,
     c.per_customer_limit AS per_customer_limit,
     COALESCE(cs.times_redeemed, 0) AS times_redeemed,
     CASE WHEN c.usage_limit IS NOT NULL
          THEN GREATEST(COALESCE(cs.times_redeemed, 0) - c.usage_limit, 0)
          ELSE 0 END AS redemptions_over_usage_limit,
     COALESCE(custs.max_redemptions_by_customer, 0) AS max_redemptions_by_customer,
     COALESCE(custs.customers_exceeding, 0) AS customers_exceeding_per_customer_limit,
     COALESCE(cs.total_discount_eur, 0) AS total_discount_redeemed_eur,
     COUNT(*) OVER () AS total_matching_coupons
   FROM coupons c
   JOIN tenants t ON t.id = c.tenant_id
   LEFT JOIN coupon_stats cs ON cs.coupon_id = c.id
   LEFT JOIN customer_stats custs ON custs.coupon_id = c.id
   WHERE (c.usage_limit IS NOT NULL OR c.per_customer_limit IS NOT NULL)
     AND (
       (c.usage_limit IS NOT NULL AND COALESCE(cs.times_redeemed, 0) > c.usage_limit)
       OR COALESCE(custs.customers_exceeding, 0) > 0
     )
   ORDER BY redemptions_over_usage_limit DESC NULLS FIRST, customers_exceeding_per_customer_limit DESC NULLS FIRST, coupon_code ASC NULLS LAST, c.id ASC NULLS LAST
   FETCH FIRST 50 ROWS ONLY

   • Firebolt{ "errors": [ { "description": "Line 17, Column 32: 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": "a8cce869-eaba-4399-9b4a-8d98c08653af", "query_lab
   • ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table4.discount_amount Decimal(14, 4) Decimal64(size = 0), coalesce(__table6.rate, 1_UInt8) Decimal(18, 6) Decimal64(size = 0). (ARGUMENT_OUT_OF_BOUND)

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

8. Everything, as recorded

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

   Omar's whole session, 7 turns

   • The request, as Omar received it

     Report request from Oliver Grant (analyst for the Head of Pricing and Promotions):
     
     Title: Coupons with a configured usage cap: actual breaches of usage_limit or per_customer_limit
     Question: Among coupons that actually have a usage_limit or per_customer_limit configured, which ones have been redeemed beyond that stated cap?
     Grain: one row per coupon (coupons.id)
     Columns:
     - coupon code
     - tenant name
     - status
     - usage_limit
     - per_customer_limit
     - times_redeemed (actual count from coupon_redemptions)
     - redemptions over usage_limit (times_redeemed - usage_limit, floor 0)
     - max redemptions by a single customer
     - distinct customers whose redemptions exceed per_customer_limit
     - total discount redeemed (EUR)
     Details: Source: coupons joined to tenants (name) and coupon_redemptions (for actual counts, distinct user_id, per-user redemption counts) and orders (currency_code, placed_at) for EUR conversion via exchange_rates (base_code='EUR', quote_code=currency_code, as_of=redeemed_at date). Filter to coupons where usage_limit IS NOT NULL OR per_customer_limit IS NOT NULL. Use full history (redeemed_at 2023-01-01 to 2025-12-31) since a cap can be breached across the coupon's whole life, not just 2025. Order by 'redemptions over usage_limit' descending, then 'distinct customers whose redemptions exceed per_customer_limit' descending, coupon code ascending as final tie-break. Note total row count; print top 50.
     
     Check your query with the warehouse's dry_run tool, then hand it in with submit.

   • Omar calls dry_run

     WITH redemptions AS (
       SELECT
         cr.coupon_id,
         cr.user_id,
         cr.discount_amount,
         cr.currency_code,
         cr.redeemed_at
       FROM coupon_redemptions cr
       WHERE cr.redeemed_at >= DATE '2023-01-01'
         AND cr.redeemed_at < DATE '2026-01-01'
     ),
     coupon_stats AS (
       SELECT
         r.coupon_id,
         COUNT(*) AS times_redeemed,
         COUNT(DISTINCT r.user_id) AS distinct_customers,
         SUM(r.discount_amount / COALESCE(er.rate, 1)) AS total_discount_eur
       FROM redemptions r
       LEFT 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)
       GROUP BY r.coupon_id
     ),
     per_customer AS (
       SELECT
         r.coupon_id,
         r.user_id,
         COUNT(*) AS redemption_count
       FROM redemptions r
       GROUP BY r.coupon_id, r.user_id
     ),
     customer_stats AS (
       SELECT
         pc.coupon_id,
         MAX(pc.redemption_count) AS max_redemptions_by_customer,
         COUNT(*) FILTER (WHERE c.per_customer_limit IS NOT NULL AND pc.redemption_count > c.per_customer_limit) AS customers_exceeding
       FROM per_customer pc
       JOIN coupons c ON c.id = pc.coupon_id
       GROUP BY pc.coupon_id
     )
     SELECT
       c.code AS coupon_code,
       t.name AS tenant_name,
       c.status,
       c.usage_limit,
       c.per_customer_limit,
       COALESCE(cs.times_redeemed, 0) AS times_redeemed,
       CASE WHEN c.usage_limit IS NOT NULL
            THEN GREATEST(COALESCE(cs.times_redeemed, 0) - c.usage_limit, 0)
            ELSE 0 END AS redemptions_over_usage_limit,
       COALESCE(custs.max_redemptions_by_customer, 0) AS max_redemptions_by_customer,
       COALESCE(custs.customers_exceeding, 0) AS customers_exceeding_per_customer_limit,
       ROUND(COALESCE(cs.total_discount_eur, 0), 2) AS total_discount_redeemed_eur,
       COUNT(*) OVER () AS total_matching_coupons
     FROM coupons c
     JOIN tenants t ON t.id = c.tenant_id
     LEFT JOIN coupon_stats cs ON cs.coupon_id = c.id
     LEFT JOIN customer_stats custs ON custs.coupon_id = c.id
     WHERE (c.usage_limit IS NOT NULL OR c.per_customer_limit IS NOT NULL)
       AND (
         (c.usage_limit IS NOT NULL AND COALESCE(cs.times_redeemed, 0) > c.usage_limit)
         OR COALESCE(custs.customers_exceeding, 0) > 0
       )
     ORDER BY redemptions_over_usage_limit DESC, customers_exceeding_per_customer_limit DESC, coupon_code ASC
     LIMIT 50

   • The warehouse answers

     STATUS: COMPILES (dry run on the empty schema, no data read)
     COLUMNS: coupon_code, tenant_name, status, usage_limit, per_customer_limit, times_redeemed, redemptions_over_usage_limit, max_redemptions_by_customer, customers_exceeding_per_customer_limit, total_discount_redeemed_eur, total_matching_coupons

   • OmarQuery compiles cleanly. Submitting.
   • Omar calls submit

     WITH redemptions AS (
       SELECT
         cr.coupon_id,
         cr.user_id,
         cr.discount_amount,
         cr.currency_code,
         cr.redeemed_at
       FROM coupon_redemptions cr
       WHERE cr.redeemed_at >= DATE '2023-01-01'
         AND cr.redeemed_at < DATE '2026-01-01'
     ),
     coupon_stats AS (
       SELECT
         r.coupon_id,
         COUNT(*) AS times_redeemed,
         COUNT(DISTINCT r.user_id) AS distinct_customers,
         SUM(r.discount_amount / COALESCE(er.rate, 1)) AS total_discount_eur
       FROM redemptions r
       LEFT 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)
       GROUP BY r.coupon_id
     ),
     per_customer AS (
       SELECT
         r.coupon_id,
         r.user_id,
         COUNT(*) AS redemption_count
       FROM redemptions r
       GROUP BY r.coupon_id, r.user_id
     ),
     customer_stats AS (
       SELECT
         pc.coupon_id,
         MAX(pc.redemption_count) AS max_redemptions_by_customer,
         COUNT(*) FILTER (WHERE c.per_customer_limit IS NOT NULL AND pc.redemption_count > c.per_customer_limit) AS customers_exceeding
       FROM per_customer pc
       JOIN coupons c ON c.id = pc.coupon_id
       GROUP BY pc.coupon_id
     )
     SELECT
       c.code AS coupon_code,
       t.name AS tenant_name,
       c.status,
       c.usage_limit,
       c.per_customer_limit,
       COALESCE(cs.times_redeemed, 0) AS times_redeemed,
       CASE WHEN c.usage_limit IS NOT NULL
            THEN GREATEST(COALESCE(cs.times_redeemed, 0) - c.usage_limit, 0)
            ELSE 0 END AS redemptions_over_usage_limit,
       COALESCE(custs.max_redemptions_by_customer, 0) AS max_redemptions_by_customer,
       COALESCE(custs.customers_exceeding, 0) AS customers_exceeding_per_customer_limit,
       ROUND(COALESCE(cs.total_discount_eur, 0), 2) AS total_discount_redeemed_eur,
       COUNT(*) OVER () AS total_matching_coupons
     FROM coupons c
     JOIN tenants t ON t.id = c.tenant_id
     LEFT JOIN coupon_stats cs ON cs.coupon_id = c.id
     LEFT JOIN customer_stats custs ON custs.coupon_id = c.id
     WHERE (c.usage_limit IS NOT NULL OR c.per_customer_limit IS NOT NULL)
       AND (
         (c.usage_limit IS NOT NULL AND COALESCE(cs.times_redeemed, 0) > c.usage_limit)
         OR COALESCE(custs.customers_exceeding, 0) > 0
       )
     ORDER BY redemptions_over_usage_limit DESC, customers_exceeding_per_customer_limit DESC, coupon_code ASC
     LIMIT 50

   • The warehouse answers

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

   • OmarSubmitted. Summary of the approach: - **Grain:** one row per coupon, restricted to coupons with a configured `usage_limit` and/or `per_customer_limit`, further filtered to only those actually breached (either total redemptions exceed `usage_limit`, or at least one customer exceeds `per_customer_limit`). - **Conversion:** discount amounts converted to EUR day-by-day using `exchange_rates` (EUR→currency rate, divided out), based on each redemption's own `currency_code`/`redeemed_at` — this made the `orders` join unnecessary since those fields are already on `coupon_redemptions`. - **Per-customer logic:** a subquery counts redemptions per (coupon, user), then rolls up into max redemptions by a single customer and the count of customers exceeding the per-customer cap. - **Ordering/paging:** sorted by redemptions-over-limit desc, then customers-exceeding desc, then coupon code; capped at 50 rows, with a `total_matching_coupons` column via a window function so the analyst can see if more rows exist beyond the printed page.

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

   Omar's transcript (274 KB) · Oliver Grant's transcript (972 KB) · Marco Bianchi's transcript (259 KB) · the night's plan (6 KB) · the night's reports (8 KB) · the week's memo (3 KB)