Queries · q222 · Chief Financial Officer · week 2, night 1
“Roughly how much discount euro value comes from the same customer reusing a coupon code multiple times, given no usage or per-customer limit is enforced?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
Week 2
The external auditors start the 2025 year-end audit in two weeks. They will want to see that the money adds up across the whole chain: what shoppers paid, what was refunded or credited, what merchants received in payouts, what Tidewick kept, and that invoices agree with all of it. They will also trace sample transactions end to end.
The manager, at the start of week 2
Subject: Good progress — but the audit clock is now the priority
Kofi,
Thanks — this is useful, and I appreciate you flagging the caveats rather than smoothing them over. A few reactions and then where I want to focus next week.
What stood out:
The big new driver: external audit starts in two weeks. They'll want to walk the full money trail — what a shopper paid, what was refunded or credited back, what the merchant was paid out, what we kept as our fee, and that it all agrees with the invoices and accounts — and they'll pick sample transactions and trace them start to finish. I'd rather we got ahead of that than react to it.
Questions for this week, in priority order:
Audit readiness (#1) is the one I most need — please make that the lead request even if it means FX and B2B receivables slip another week.
Thanks, Tobias
The analyst · week 2, night 1, request 3 of 3
Audit readiness is explicitly the lead ask, so I'm using one dense report to walk a sample of real transactions end-to-end — payment → refund → merchant payout (gross/fee/net) → ledger balance — in one place, since that's literally what the auditors will do when they pick sample transactions. I folded the payout settlement check and the ledger tie-out into a single payment-grain report (12 columns, at the comfortable limit) rather than two separate reports, to leave room tonight for the other two priorities.
For priority #2 (refund figure discrepancy), my working hypothesis going in is that this is a structural date-field mismatch rather than a data error: payments.amount_refunded gets bucketed by the original purchase's captured_at quarter, while order_returns.refund_total gets bucketed by refunded_at (when the refund actually happened) — and Yuki's finding that refunds typically land ~3 weeks after settlement supports refunds routinely crossing a quarter boundary. I'm testing that directly by measuring the lag between purchase quarter and refund quarter for 2025 returns, which should explain most of the 16-40% swings without needing a full reconciliation.
For #4 (room permitting), I'm sizing the uncapped-coupon-reuse gap Oliver flagged, since "no usage/per-customer limit exists" is only a control gap in euros if people are actually exploiting it — I want €-at-risk from repeat redemptions by the same customer, comparable in kind to the €57k free-delivery number, so Tobias can judge scale before deciding if it goes to the auditors.
#3 (plan-fee revenue) gets no slot tonight: I rechecked the schema and confirmed there is no table that could hold Tidewick's own platform-plan billing of merchants (plans/subscriptions/invoices here are each merchant's billing of their own customers) — this is a true data gap, not something a cleverer query can solve, so I'll report that status in prose and recommend sourcing it from the corporate billing/finance system instead.
The engineer · 1 dry run on the empty schema, then the submission
with redemptions as ( select cr.coupon_id, c.code as coupon_code, d.kind as discount_kind, cr.user_id, cr.redeemed_at, cr.discount_amount / er.rate as discount_amount_eur, row_number() over (partition by cr.coupon_id, cr.user_id order by cr.redeemed_at asc, cr.id asc) as rn from coupon_redemptions cr join coupons c on c.id = cr.coupon_id join discounts d on d.id = c.discount_id join exchange_rates er on er.base_code = 'EUR' and er.quote_code = cr.currency_code and er.as_of = cast(cr.redeemed_at as date) where cr.redeemed_at >= timestamp '2025-01-01' and cr.redeemed_at < timestamp '2026-01-01' ) select coupon_code, discount_kind, count(*) as total_redemptions_2025, count(distinct user_id) as distinct_customers_2025, count(*) - count(distinct user_id) as redemptions_beyond_first_use, round(sum(discount_amount_eur), 2) as total_discount_amount_eur, round(sum(discount_amount_eur) filter (where rn > 1), 2) as discount_amount_beyond_first_use_eur, round(100.0 * sum(discount_amount_eur) filter (where rn > 1) / nullif(sum(discount_amount_eur), 0), 2) as pct_of_discount_from_repeat_use from redemptions group by coupon_id, coupon_code, discount_kind order by discount_amount_beyond_first_use_eur desc nulls last, coupon_code asc limit 20
“Submitted. Summary of what the query does:
- Joins coupon_redemptions → coupons (for code) → discounts (for kind), restricted to redeemed_at in 2025.
- Converts each redemption's discount_amount to EUR via exchange_rates (EUR→currency rate on the redemption date), as specified.
- Uses ROW_NUMBER() per (coupon, user) ordered by redeemed_at to identify each customer's first vs. repeat redemption of a given coupon.
- Aggregates per coupon: total redemptions, distinct customers, the repeat-use gap, total EUR discount, and the EUR discount attributable only to 2nd+ uses per customer (via FILTER (WHERE rn > 1)), plus that as a percentage of the coupon's total discount.
- Orders by repeat-use discount exposure descending, capped at the top 20 rows as requested.”
As Kofi Mensah saw it, from the small dataset the agents worked on
coupon_code | discount_kind | total_redemptions_2025 | distinct_customers_2025 | redemptions_beyond_first_use | total_discount_amount_eur | discount_amount_beyond_first_use_eur | pct_of_discount_from_repeat_use ------------+---------------+------------------------+-------------------------+------------------------------+---------------------------+--------------------------------------+-------------------------------- BF2530 | percentage | 23 | 22 | 1 | 3314.68 | 224.96 | 6.79 BF2525 | percentage | 23 | 19 | 4 | 964.81 | 195.82 | 20.3 BF2515 | percentage | 4 | 2 | 2 | 384.04 | 133.82 | 34.85 BF2530 | percentage | 16 | 14 | 2 | 651.53 | 113.29 | 17.39 BF2525 | percentage | 31 | 30 | 1 | 1135.09 | 85.97 | 7.57 BF2530 | percentage | 11 | 9 | 2 | 337.85 | 75.31 | 22.29 BF2520 | percentage | 45 | 41 | 4 | 682.36 | 57.81 | 8.47 BF2525 | percentage | 2 | 1 | 1 | 77.15 | 56.89 | 73.74 BF2530 | percentage | 8 | 6 | 2 | 105.36 | 31.93 | 30.3 BF2530 | percentage | 6 | 5 | 1 | 421.65 | 15.96 | 3.79 BF2515 | percentage | 13 | 12 | 1 | 189.91 | 13.98 | 7.36 BF2520 | percentage | 20 | 18 | 2 | 152.97 | 5.06 | 3.31 BF2530 | percentage | 4 | 3 | 1 | 38.66 | 3.36 | 8.7 BF2515 | percentage | 2 | 2 | 0 | 6.49 | NULL | NULL BF2515 | percentage | 1 | 1 | 0 | 23.66 | NULL | NULL BF2515 | percentage | 8 | 8 | 0 | 93.94 | NULL | NULL BF2515 | percentage | 1 | 1 | 0 | 23.73 | NULL | NULL BF2515 | percentage | 1 | 1 | 0 | 13.61 | NULL | NULL BF2520 | percentage | 6 | 6 | 0 | 212.02 | NULL | NULL BF2520 | percentage | 1 | 1 | 0 | 52.82 | NULL | NULL (20 rows)
Changed only where the benchmark needs the same result on every system
with redemptions as ( select cr.coupon_id, cr.coupon_id AS coupon_id, c.code as coupon_code, d.kind as discount_kind, cr.user_id, cr.redeemed_at, cr.discount_amount / er.rate as discount_amount_eur, row_number() over (partition by cr.coupon_id, cr.user_id order by cr.redeemed_at asc, cr.id asc) as rn cr.user_id AS user_id, cr.redeemed_at AS redeemed_at, CAST(cr.discount_amount / er.rate AS NUMERIC(18,6)) as discount_amount_eur, row_number() over (partition by cr.coupon_id, cr.user_id order by cr.redeemed_at asc NULLS LAST, cr.id asc NULLS LAST) as rn from coupon_redemptions cr join coupons c on c.id = cr.coupon_id join discounts d on d.id = c.discount_id join exchange_rates er on er.base_code = 'EUR' and er.quote_code = cr.currency_code and er.as_of = cast(cr.redeemed_at as date) where cr.redeemed_at >= timestamp '2025-01-01' and cr.redeemed_at < timestamp '2026-01-01')select coupon_code, discount_kind, count(*) as total_redemptions_2025, count(distinct user_id) as distinct_customers_2025, count(*) - count(distinct user_id) as redemptions_beyond_first_use, round(sum(discount_amount_eur), 2) as total_discount_amount_eur, round(sum(discount_amount_eur) filter (where rn > 1), 2) as discount_amount_beyond_first_use_eur, round(100.0 * sum(discount_amount_eur) filter (where rn > 1) / nullif(sum(discount_amount_eur), 0), 2) as pct_of_discount_from_repeat_use sum(discount_amount_eur) as total_discount_amount_eur, sum(discount_amount_eur) filter (where rn > 1) as discount_amount_beyond_first_use_eur, 100.0 * sum(discount_amount_eur) filter (where rn > 1) / nullif(sum(discount_amount_eur), 0) as pct_of_discount_from_repeat_usefrom redemptionsgroup by coupon_id, coupon_code, discount_kindorder by discount_amount_beyond_first_use_eur desc nulls last, coupon_code asclimit 20order by discount_amount_beyond_first_use_eur desc nulls last, coupon_code asc NULLS LAST, coupon_id asc NULLS LAST{{fetch_first(20)}}
with redemptions as ( select cr.coupon_id AS coupon_id, c.code as coupon_code, d.kind as discount_kind, cr.user_id AS user_id, cr.redeemed_at AS redeemed_at, CAST(cr.discount_amount / er.rate AS NUMERIC(18,6)) as discount_amount_eur, row_number() over (partition by cr.coupon_id, cr.user_id order by cr.redeemed_at asc NULLS LAST, cr.id asc NULLS LAST) as rn from coupon_redemptions cr join coupons c on c.id = cr.coupon_id join discounts d on d.id = c.discount_id join exchange_rates er on er.base_code = 'EUR' and er.quote_code = cr.currency_code and er.as_of = cast(cr.redeemed_at as date) where cr.redeemed_at >= timestamp '2025-01-01' and cr.redeemed_at < timestamp '2026-01-01' ) select coupon_code, discount_kind, count(*) as total_redemptions_2025, count(distinct user_id) as distinct_customers_2025, count(*) - count(distinct user_id) as redemptions_beyond_first_use, sum(discount_amount_eur) as total_discount_amount_eur, sum(discount_amount_eur) filter (where rn > 1) as discount_amount_beyond_first_use_eur, 100.0 * sum(discount_amount_eur) filter (where rn > 1) / nullif(sum(discount_amount_eur), 0) as pct_of_discount_from_repeat_use from redemptions group by coupon_id, coupon_code, discount_kind order by discount_amount_beyond_first_use_eur desc nulls last, coupon_code asc NULLS LAST, coupon_id asc NULLS LAST FETCH FIRST 20 ROWS ONLY
with redemptions as ( select cr.coupon_id AS coupon_id, c.code as coupon_code, d.kind as discount_kind, cr.user_id AS user_id, cr.redeemed_at AS redeemed_at, CAST(cr.discount_amount / er.rate AS NUMERIC(18,6)) as discount_amount_eur, row_number() over (partition by cr.coupon_id, cr.user_id order by cr.redeemed_at asc NULLS LAST, cr.id asc NULLS LAST) as rn from coupon_redemptions cr join coupons c on c.id = cr.coupon_id join discounts d on d.id = c.discount_id join exchange_rates er on er.base_code = 'EUR' and er.quote_code = cr.currency_code and er.as_of = cast(cr.redeemed_at as date) where cr.redeemed_at >= timestamp '2025-01-01' and cr.redeemed_at < timestamp '2026-01-01' ) select coupon_code, discount_kind, count(*) as total_redemptions_2025, count(distinct user_id) as distinct_customers_2025, count(*) - count(distinct user_id) as redemptions_beyond_first_use, sum(discount_amount_eur) as total_discount_amount_eur, sum(discount_amount_eur) filter (where rn > 1) as discount_amount_beyond_first_use_eur, 100.0 * sum(discount_amount_eur) filter (where rn > 1) / nullif(sum(discount_amount_eur), 0) as pct_of_discount_from_repeat_use from redemptions group by coupon_id, coupon_code, discount_kind order by discount_amount_beyond_first_use_eur desc nulls last, coupon_code asc NULLS LAST, coupon_id asc NULLS LAST LIMIT 20
The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted
Report request from Kofi Mensah (analyst for the Chief Financial Officer): Title: Coupon Reuse Exposure: Redemptions Beyond First Use per Customer, 2025 Question: Roughly how much discount euro value comes from the same customer reusing a coupon code multiple times, given no usage or per-customer limit is enforced? Grain: one coupon code, for redemptions in 2025 Columns: - coupon_code - discount_kind - total_redemptions_2025 - distinct_customers_2025 - redemptions_beyond_first_use (total_redemptions - distinct_customers) - total_discount_amount_eur - discount_amount_beyond_first_use_eur (excess discount from a customer's 2nd+ redemption of the same code) - pct_of_discount_from_repeat_use Details: Source: coupon_redemptions joined to coupons (for code, and discount_id -> discounts for discount_kind), filtered to redeemed_at in calendar year 2025. Convert discount_amount to EUR via exchange_rates (base_code='EUR', quote_code=coupon_redemptions.currency_code, as_of=date(redeemed_at)). Group by coupon_id/code. distinct_customers_2025 counts distinct user_id redeeming that code in 2025. redemptions_beyond_first_use = total_redemptions_2025 - distinct_customers_2025 (i.e., repeat uses by the same customer beyond their first). discount_amount_beyond_first_use_eur: for each (coupon, user_id) pair, sum discount_amount_eur from all but the earliest redemption (by redeemed_at) for that user, then sum across users for the coupon. pct_of_discount_from_repeat_use = discount_amount_beyond_first_use_eur / total_discount_amount_eur * 100. Order by discount_amount_beyond_first_use_eur descending; print top 20 coupons and note total number of distinct coupons redeemed in 2025. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runwith redemptions as ( select cr.coupon_id, c.code as coupon_code, d.kind as discount_kind, cr.user_id, cr.redeemed_at, cr.discount_amount / er.rate as discount_amount_eur, row_number() over (partition by cr.coupon_id, cr.user_id order by cr.redeemed_at asc, cr.id asc) as rn from coupon_redemptions cr join coupons c on c.id = cr.coupon_id join discounts d on d.id = c.discount_id join exchange_rates er on er.base_code = 'EUR' and er.quote_code = cr.currency_code and er.as_of = cast(cr.redeemed_at as date) where cr.redeemed_at >= timestamp '2025-01-01' and cr.redeemed_at < timestamp '2026-01-01' ) select coupon_code, discount_kind, count(*) as total_redemptions_2025, count(distinct user_id) as distinct_customers_2025, count(*) - count(distinct user_id) as redemptions_beyond_first_use, round(sum(discount_amount_eur), 2) as total_discount_amount_eur, round(sum(discount_amount_eur) filter (where rn > 1), 2) as discount_amount_beyond_first_use_eur, round(100.0 * sum(discount_amount_eur) filter (where rn > 1) / nullif(sum(discount_amount_eur), 0), 2) as pct_of_discount_from_repeat_use from redemptions group by coupon_id, coupon_code, discount_kind order by discount_amount_beyond_first_use_eur desc nulls last, coupon_code asc limit 20
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: coupon_code, discount_kind, total_redemptions_2025, distinct_customers_2025, redemptions_beyond_first_use, total_discount_amount_eur, discount_amount_beyond_first_use_eur, pct_of_discount_from_repeat_use
submitwith redemptions as ( select cr.coupon_id, c.code as coupon_code, d.kind as discount_kind, cr.user_id, cr.redeemed_at, cr.discount_amount / er.rate as discount_amount_eur, row_number() over (partition by cr.coupon_id, cr.user_id order by cr.redeemed_at asc, cr.id asc) as rn from coupon_redemptions cr join coupons c on c.id = cr.coupon_id join discounts d on d.id = c.discount_id join exchange_rates er on er.base_code = 'EUR' and er.quote_code = cr.currency_code and er.as_of = cast(cr.redeemed_at as date) where cr.redeemed_at >= timestamp '2025-01-01' and cr.redeemed_at < timestamp '2026-01-01' ) select coupon_code, discount_kind, count(*) as total_redemptions_2025, count(distinct user_id) as distinct_customers_2025, count(*) - count(distinct user_id) as redemptions_beyond_first_use, round(sum(discount_amount_eur), 2) as total_discount_amount_eur, round(sum(discount_amount_eur) filter (where rn > 1), 2) as discount_amount_beyond_first_use_eur, round(100.0 * sum(discount_amount_eur) filter (where rn > 1) / nullif(sum(discount_amount_eur), 0), 2) as pct_of_discount_from_repeat_use from redemptions group by coupon_id, coupon_code, discount_kind order by discount_amount_beyond_first_use_eur desc nulls last, coupon_code asc limit 20
Submitted to tonight's batch. Your work on this request is done.