Queries · q160 · Head of Lifecycle Marketing · week 1, night 1
“How much is the loyalty/referral programme costing the business each month - points value issued and redeemed, outstanding points liability, and referral rewards paid - and is that cost trending up or down?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
Week 1; shared with Tobias Lindqvist, Chief Financial Officer
Is the loyalty and referral programme worth it? Do members and referred customers behave differently from others, and what does the programme cost?
The manager, at the start of week 1
Subject: Loyalty & referral programme — what I need for Friday's memo
Hi Ingrid,
Welcome aboard — thank you for jumping on this in week one. Tobias and I need to give the leadership team a joint, evidence-based view on whether the loyalty and referral programme is paying for itself. The deliverable is your Friday memo, which Tobias and I will use to decide whether we expand the programme, leave it as is, or rework it. Given the slot budget, let's prioritize breadth of the right questions over depth on any one — we can always follow up next week.
Here's what I'm trying to answer, in my own words:
Given we only have a few report slots a night, I'd rank these in order 3, 1, 2 as the must-haves for Friday; 4 and 5 are nice-to-haves if slots allow. Please loop in whatever Tobias's analyst is already pulling on the finance side so we're not duplicating report requests.
Thanks, Ingrid — happy to talk through any of this before you start drafting requests.
Best, Aisha
Subject: Finance angle on the loyalty/referral review
Hi Ingrid,
A few things I'd like us to cover as you scope this:
Happy to look at a first cut together before it goes wider.
Tobias
The analyst · week 1, night 1, request 1 of 3
Prioritizing Aisha's must-haves (3,1,2) which also cover most of Tobias's asks (net cost, refund profile). Night 1 establishes three independent baselines: (a) program cost/liability trend, (b) member vs non-member behavior, (c) referred vs non-referred early behavior. I'm folding Tobias's refund-rate question into (b) and (c) as extra columns rather than separate reports, since it's a natural measure at the same grain. Deliberately NOT yet controlling for tenure/cohort matching in (b) - that's the obvious follow-up once I see whether raw numbers show a gap (my risk-focused read: if members already look no better on raw numbers, that itself is a red flag worth flagging Friday). Saving participation-rate trend (#4) and gaming/concentration red flags (#5) for night 2 once I've seen these results and know where to dig. Will flag to Aisha/Tobias to confirm their analyst isn't pulling the same cost report before night 2.
The engineer · 1 dry run on the empty schema, then the submission
WITH months AS ( SELECT DISTINCT month_label, month_start FROM calendar_days WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31' ), loyalty_tx AS ( SELECT lt.id, lt.direction, lt.occurred_at, lp.currency_code, (CAST(lt.points AS numeric) / lp.points_per_currency) AS amount_in_currency FROM loyalty_transactions lt JOIN loyalty_accounts la ON la.id = lt.loyalty_account_id JOIN loyalty_programs lp ON lp.id = la.loyalty_program_id WHERE lt.occurred_at >= TIMESTAMP '2023-01-01' AND lt.occurred_at < TIMESTAMP '2026-01-01' ), loyalty_tx_eur AS ( SELECT lt.direction, lt.amount_in_currency / COALESCE(er.rate, CASE WHEN lt.currency_code = 'EUR' THEN 1 END) AS amount_eur, date_trunc('month', lt.occurred_at)::date AS month_start FROM loyalty_tx lt LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = lt.currency_code AND er.as_of = CAST(lt.occurred_at AS date) ), loyalty_monthly AS ( SELECT month_start, SUM(CASE WHEN direction = 'earn' THEN ABS(amount_eur) ELSE 0 END) AS earned_value_eur, SUM(CASE WHEN direction <> 'earn' THEN ABS(amount_eur) ELSE 0 END) AS redeemed_value_eur FROM loyalty_tx_eur GROUP BY month_start ), referral_tx AS ( SELECT rr.redeemed_at, rr.reward_amount, rr.currency_code FROM referral_redemptions rr WHERE rr.redeemed_at >= TIMESTAMP '2023-01-01' AND rr.redeemed_at < TIMESTAMP '2026-01-01' AND rr.reward_amount IS NOT NULL ), referral_tx_eur AS ( SELECT rt.reward_amount / COALESCE(er.rate, CASE WHEN rt.currency_code = 'EUR' THEN 1 END) AS amount_eur, date_trunc('month', rt.redeemed_at)::date AS month_start FROM referral_tx rt LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = rt.currency_code AND er.as_of = CAST(rt.redeemed_at AS date) ), referral_monthly AS ( SELECT month_start, SUM(amount_eur) AS referral_rewards_paid_eur, COUNT(*) AS referral_redemptions_count FROM referral_tx_eur GROUP BY month_start ), enrollments_monthly AS ( SELECT date_trunc('month', enrolled_at)::date AS month_start, COUNT(*) AS new_enrollments FROM loyalty_accounts WHERE enrolled_at >= TIMESTAMP '2023-01-01' AND enrolled_at < TIMESTAMP '2026-01-01' GROUP BY date_trunc('month', enrolled_at)::date ), combined AS ( SELECT m.month_label, m.month_start, COALESCE(lm.earned_value_eur, 0) AS points_earned_value_eur, COALESCE(lm.redeemed_value_eur, 0) AS points_redeemed_value_eur, COALESCE(lm.earned_value_eur, 0) - COALESCE(lm.redeemed_value_eur, 0) AS net_points_liability_change_eur, COALESCE(rm.referral_rewards_paid_eur, 0) AS referral_rewards_paid_eur, COALESCE(rm.referral_redemptions_count, 0) AS referral_redemptions_count, COALESCE(em.new_enrollments, 0) AS new_loyalty_enrollments FROM months m LEFT JOIN loyalty_monthly lm ON lm.month_start = m.month_start LEFT JOIN referral_monthly rm ON rm.month_start = m.month_start LEFT JOIN enrollments_monthly em ON em.month_start = m.month_start ) SELECT month_label AS month, ROUND(points_earned_value_eur, 2) AS points_earned_value_eur, ROUND(points_redeemed_value_eur, 2) AS points_redeemed_value_eur, ROUND(net_points_liability_change_eur, 2) AS net_points_liability_change_eur, ROUND(SUM(net_points_liability_change_eur) OVER (ORDER BY month_start ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) AS cumulative_points_liability_eur, ROUND(referral_rewards_paid_eur, 2) AS referral_rewards_paid_eur, referral_redemptions_count, new_loyalty_enrollments FROM combined ORDER BY month_start
“Query compiles and produces exactly the 8 requested columns at monthly grain. Submitting.”
“Submitted. Summary of what the query does:
- Grain: one row per calendar month, Jan 2023 – Dec 2025 (36 rows), via calendar_days.
- Points value: loyalty_transactions joined through loyalty_accounts → loyalty_programs to get points_per_currency and program currency; points / points_per_currency gives program-currency value, converted to EUR via exchange_rates (base EUR, quote = program currency, as of the transaction date). direction = 'earn' is issuance, anything else is redemption (absolute value used).
- Net/cumulative liability: net = earned − redeemed per month; cumulative is a running SUM(...) OVER (ORDER BY month).
- Referral rewards: referral_redemptions.reward_amount, converted to EUR the same way by redeemed_at date, summed and counted per month.
- Enrollments: count of loyalty_accounts.enrolled_at per month.
Note passed to the analyst: the direction/redemption-currency conventions assumed, and that EUR-denominated rows fall back to rate=1 only if no explicit EUR/EUR exchange-rate row exists.”
As Ingrid Solberg saw it, from the small dataset the agents worked on
month | points_earned_value_eur | points_redeemed_value_eur | net_points_liability_change_eur | cumulative_points_liability_eur | referral_rewards_paid_eur | referral_redemptions_count | new_loyalty_enrollments --------+-------------------------+---------------------------+---------------------------------+---------------------------------+---------------------------+----------------------------+------------------------ 2023-01 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0 | 26 2023-02 | 6795.86 | 0.0 | 6795.86 | 6795.86 | 0.0 | 0 | 26 2023-03 | 12269.48 | 0.0 | 12269.48 | 19065.34 | 0.0 | 0 | 19 2023-04 | 7731.07 | 458.87 | 7272.2 | 26337.54 | 0.0 | 0 | 28 2023-05 | 9182.52 | 558.57 | 8623.95 | 34961.49 | 9.72 | 1 | 27 2023-06 | 14861.39 | 2016.52 | 12844.87 | 47806.36 | 9.93 | 1 | 24 2023-07 | 10904.88 | 1391.71 | 9513.17 | 57319.54 | 0.0 | 0 | 30 2023-08 | 10592.47 | 2571.75 | 8020.72 | 65340.26 | 20.11 | 2 | 23 2023-09 | 9777.02 | 1727.34 | 8049.68 | 73389.93 | 29.93 | 3 | 31 2023-10 | 8161.54 | 1978.64 | 6182.9 | 79572.83 | 0.0 | 0 | 28 2023-11 | 12610.08 | 3112.38 | 9497.7 | 89070.53 | 20.01 | 2 | 46 2023-12 | 25959.23 | 7487.64 | 18471.59 | 107542.12 | 10.0 | 1 | 43 2024-01 | 36333.27 | 12003.69 | 24329.58 | 131871.7 | 10.09 | 1 | 21 2024-02 | 18973.71 | 3612.65 | 15361.06 | 147232.76 | 39.95 | 4 | 21 2024-03 | 13914.19 | 2514.62 | 11399.57 | 158632.33 | 9.94 | 1 | 35 2024-04 | 14554.7 | 3945.76 | 10608.94 | 169241.27 | 10.02 | 1 | 24 2024-05 | 12483.15 | 2568.98 | 9914.17 | 179155.44 | 19.54 | 2 | 36 2024-06 | 18731.61 | 6646.59 | 12085.02 | 191240.46 | 10.0 | 1 | 31 2024-07 | 25923.37 | 4972.11 | 20951.27 | 212191.72 | 9.55 | 1 | 22 2024-08 | 15720.94 | 5803.3 | 9917.64 | 222109.37 | 29.46 | 3 | 30 2024-09 | 11399.38 | 4548.98 | 6850.4 | 228959.77 | 10.0 | 1 | 34 2024-10 | 13686.25 | 4764.38 | 8921.87 | 237881.64 | 39.87 | 4 | 35 2024-11 | 17138.65 | 5816.06 | 11322.59 | 249204.23 | 29.64 | 3 | 56 2024-12 | 26937.99 | 9161.06 | 17776.93 | 266981.16 | 59.93 | 6 | 71 2025-01 | 45225.29 | 11588.55 | 33636.73 | 300617.89 | 9.7 | 1 | 47 2025-02 | 27586.88 | 6466.53 | 21120.34 | 321738.23 | 29.83 | 3 | 26 2025-03 | 26353.26 | 7491.37 | 18861.89 | 340600.12 | 10.0 | 1 | 44 2025-04 | 23342.5 | 8413.71 | 14928.78 | 355528.91 | 20.03 | 2 | 27 2025-05 | 19817.63 | 7308.03 | 12509.59 | 368038.5 | 19.72 | 2 | 62 2025-06 | 19227.78 | 6181.43 | 13046.34 | 381084.84 | 29.31 | 3 | 36 2025-07 | 28562.11 | 6813.17 | 21748.94 | 402833.78 | 19.93 | 2 | 45 2025-08 | 24608.79 | 4983.28 | 19625.51 | 422459.29 | 20.21 | 2 | 35 2025-09 | 16433.11 | 7573.74 | 8859.38 | 431318.67 | 29.09 | 3 | 47 2025-10 | 17719.35 | 6618.06 | 11101.29 | 442419.96 | 39.53 | 4 | 58 2025-11 | 25165.22 | 7469.68 | 17695.53 | 460115.49 | 29.33 | 3 | 58 2025-12 | 40316.76 | 9213.83 | 31102.93 | 491218.42 | 28.67 | 3 | 86 (36 rows)
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
Changed only where the benchmark needs the same result on every system
WITH months AS ( SELECT DISTINCT month_label, month_start FROM calendar_days WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31'),loyalty_tx AS ( SELECT lt.id, lt.direction, lt.occurred_at, lp.currency_code, (CAST(lt.points AS numeric) / lp.points_per_currency) AS amount_in_currency lt.id AS id, lt.direction AS direction, lt.occurred_at AS occurred_at, lp.currency_code AS currency_code, (CAST(lt.points AS numeric(18,6)) / lp.points_per_currency) AS amount_in_currency FROM loyalty_transactions lt JOIN loyalty_accounts la ON la.id = lt.loyalty_account_id JOIN loyalty_programs lp ON lp.id = la.loyalty_program_id WHERE lt.occurred_at >= TIMESTAMP '2023-01-01' AND lt.occurred_at < TIMESTAMP '2026-01-01'),loyalty_tx_eur AS ( SELECT lt.direction, lt.amount_in_currency / COALESCE(er.rate, CASE WHEN lt.currency_code = 'EUR' THEN 1 END) AS amount_eur, date_trunc('month', lt.occurred_at)::date AS month_start lt.direction AS direction, CAST(lt.amount_in_currency / COALESCE(er.rate, CASE WHEN lt.currency_code = 'EUR' THEN 1 END) AS NUMERIC(18,6)) AS amount_eur, CAST(date_trunc('month', lt.occurred_at) AS date) AS month_start FROM loyalty_tx lt LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = lt.currency_code AND er.as_of = CAST(lt.occurred_at AS date)),loyalty_monthly AS ( SELECT month_start, SUM(CASE WHEN direction = 'earn' THEN ABS(amount_eur) ELSE 0 END) AS earned_value_eur, SUM(CASE WHEN direction <> 'earn' THEN ABS(amount_eur) ELSE 0 END) AS redeemed_value_eur FROM loyalty_tx_eur GROUP BY month_start),referral_tx AS ( SELECT rr.redeemed_at, rr.reward_amount, rr.currency_code rr.redeemed_at AS redeemed_at, rr.reward_amount AS reward_amount, rr.currency_code AS currency_code FROM referral_redemptions rr WHERE rr.redeemed_at >= TIMESTAMP '2023-01-01' AND rr.redeemed_at < TIMESTAMP '2026-01-01' AND rr.reward_amount IS NOT NULL),referral_tx_eur AS ( SELECT rt.reward_amount / COALESCE(er.rate, CASE WHEN rt.currency_code = 'EUR' THEN 1 END) AS amount_eur, date_trunc('month', rt.redeemed_at)::date AS month_start CAST(rt.reward_amount / COALESCE(er.rate, CASE WHEN rt.currency_code = 'EUR' THEN 1 END) AS NUMERIC(18,6)) AS amount_eur, CAST(date_trunc('month', rt.redeemed_at) AS date) AS month_start FROM referral_tx rt LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = rt.currency_code AND er.as_of = CAST(rt.redeemed_at AS date)),referral_monthly AS ( SELECT month_start, SUM(amount_eur) AS referral_rewards_paid_eur, COUNT(*) AS referral_redemptions_count FROM referral_tx_eur GROUP BY month_start),enrollments_monthly AS ( SELECT date_trunc('month', enrolled_at)::date AS month_start, CAST(date_trunc('month', enrolled_at) AS date) AS month_start, COUNT(*) AS new_enrollments FROM loyalty_accounts WHERE enrolled_at >= TIMESTAMP '2023-01-01' AND enrolled_at < TIMESTAMP '2026-01-01' GROUP BY date_trunc('month', enrolled_at)::date GROUP BY CAST(date_trunc('month', enrolled_at) AS date)),combined AS ( SELECT m.month_label, m.month_start, m.month_label AS month_label, m.month_start AS month_start, COALESCE(lm.earned_value_eur, 0) AS points_earned_value_eur, COALESCE(lm.redeemed_value_eur, 0) AS points_redeemed_value_eur, COALESCE(lm.earned_value_eur, 0) - COALESCE(lm.redeemed_value_eur, 0) AS net_points_liability_change_eur, COALESCE(rm.referral_rewards_paid_eur, 0) AS referral_rewards_paid_eur, COALESCE(rm.referral_redemptions_count, 0) AS referral_redemptions_count, COALESCE(em.new_enrollments, 0) AS new_loyalty_enrollments FROM months m LEFT JOIN loyalty_monthly lm ON lm.month_start = m.month_start LEFT JOIN referral_monthly rm ON rm.month_start = m.month_start LEFT JOIN enrollments_monthly em ON em.month_start = m.month_start)SELECT month_label AS month, ROUND(points_earned_value_eur, 2) AS points_earned_value_eur, ROUND(points_redeemed_value_eur, 2) AS points_redeemed_value_eur, ROUND(net_points_liability_change_eur, 2) AS net_points_liability_change_eur, ROUND(SUM(net_points_liability_change_eur) OVER (ORDER BY month_start ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) AS cumulative_points_liability_eur, ROUND(referral_rewards_paid_eur, 2) AS referral_rewards_paid_eur, points_earned_value_eur AS points_earned_value_eur, points_redeemed_value_eur AS points_redeemed_value_eur, net_points_liability_change_eur AS net_points_liability_change_eur, SUM(net_points_liability_change_eur) OVER (ORDER BY month_start NULLS LAST, month_label NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_points_liability_eur, referral_rewards_paid_eur AS referral_rewards_paid_eur, referral_redemptions_count, new_loyalty_enrollmentsFROM combinedORDER BY month_startORDER BY month_start NULLS LAST, month_label NULLS LAST
WITH months AS ( SELECT DISTINCT month_label, month_start FROM calendar_days WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31' ), loyalty_tx AS ( SELECT lt.id AS id, lt.direction AS direction, lt.occurred_at AS occurred_at, lp.currency_code AS currency_code, (CAST(lt.points AS numeric(18,6)) / lp.points_per_currency) AS amount_in_currency FROM loyalty_transactions lt JOIN loyalty_accounts la ON la.id = lt.loyalty_account_id JOIN loyalty_programs lp ON lp.id = la.loyalty_program_id WHERE lt.occurred_at >= TIMESTAMP '2023-01-01' AND lt.occurred_at < TIMESTAMP '2026-01-01' ), loyalty_tx_eur AS ( SELECT lt.direction AS direction, CAST(lt.amount_in_currency / COALESCE(er.rate, CASE WHEN lt.currency_code = 'EUR' THEN 1 END) AS NUMERIC(18,6)) AS amount_eur, CAST(date_trunc('month', lt.occurred_at) AS date) AS month_start FROM loyalty_tx lt LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = lt.currency_code AND er.as_of = CAST(lt.occurred_at AS date) ), loyalty_monthly AS ( SELECT month_start, SUM(CASE WHEN direction = 'earn' THEN ABS(amount_eur) ELSE 0 END) AS earned_value_eur, SUM(CASE WHEN direction <> 'earn' THEN ABS(amount_eur) ELSE 0 END) AS redeemed_value_eur FROM loyalty_tx_eur GROUP BY month_start ), referral_tx AS ( SELECT rr.redeemed_at AS redeemed_at, rr.reward_amount AS reward_amount, rr.currency_code AS currency_code FROM referral_redemptions rr WHERE rr.redeemed_at >= TIMESTAMP '2023-01-01' AND rr.redeemed_at < TIMESTAMP '2026-01-01' AND rr.reward_amount IS NOT NULL ), referral_tx_eur AS ( SELECT CAST(rt.reward_amount / COALESCE(er.rate, CASE WHEN rt.currency_code = 'EUR' THEN 1 END) AS NUMERIC(18,6)) AS amount_eur, CAST(date_trunc('month', rt.redeemed_at) AS date) AS month_start FROM referral_tx rt LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = rt.currency_code AND er.as_of = CAST(rt.redeemed_at AS date) ), referral_monthly AS ( SELECT month_start, SUM(amount_eur) AS referral_rewards_paid_eur, COUNT(*) AS referral_redemptions_count FROM referral_tx_eur GROUP BY month_start ), enrollments_monthly AS ( SELECT CAST(date_trunc('month', enrolled_at) AS date) AS month_start, COUNT(*) AS new_enrollments FROM loyalty_accounts WHERE enrolled_at >= TIMESTAMP '2023-01-01' AND enrolled_at < TIMESTAMP '2026-01-01' GROUP BY CAST(date_trunc('month', enrolled_at) AS date) ), combined AS ( SELECT m.month_label AS month_label, m.month_start AS month_start, COALESCE(lm.earned_value_eur, 0) AS points_earned_value_eur, COALESCE(lm.redeemed_value_eur, 0) AS points_redeemed_value_eur, COALESCE(lm.earned_value_eur, 0) - COALESCE(lm.redeemed_value_eur, 0) AS net_points_liability_change_eur, COALESCE(rm.referral_rewards_paid_eur, 0) AS referral_rewards_paid_eur, COALESCE(rm.referral_redemptions_count, 0) AS referral_redemptions_count, COALESCE(em.new_enrollments, 0) AS new_loyalty_enrollments FROM months m LEFT JOIN loyalty_monthly lm ON lm.month_start = m.month_start LEFT JOIN referral_monthly rm ON rm.month_start = m.month_start LEFT JOIN enrollments_monthly em ON em.month_start = m.month_start ) SELECT month_label AS month, points_earned_value_eur AS points_earned_value_eur, points_redeemed_value_eur AS points_redeemed_value_eur, net_points_liability_change_eur AS net_points_liability_change_eur, SUM(net_points_liability_change_eur) OVER (ORDER BY month_start NULLS LAST, month_label NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_points_liability_eur, referral_rewards_paid_eur AS referral_rewards_paid_eur, referral_redemptions_count, new_loyalty_enrollments FROM combined ORDER BY month_start NULLS LAST, month_label NULLS LAST
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 Ingrid Solberg (analyst for the Head of Lifecycle Marketing): Title: Loyalty points and referral reward cost by month Question: How much is the loyalty/referral programme costing the business each month - points value issued and redeemed, outstanding points liability, and referral rewards paid - and is that cost trending up or down? Grain: one calendar month (platform-wide, all tenants combined) Columns: - month (YYYY-MM) - points earned value (EUR) - points redeemed value (EUR) - net points liability change (EUR) - cumulative points liability outstanding (EUR, running total since 2023-01-01) - referral rewards paid (EUR) - referral redemptions (count) - new loyalty enrollments (count) Details: Time window: 2023-01-01 through 2025-12-31, grouped by calendar month (use calendar_days.month_label or date_trunc on occurred_at). Points value: from loyalty_transactions, convert points to currency using the points' loyalty_programs.points_per_currency and loyalty_programs.currency_code (amount_in_program_currency = points / points_per_currency), split by direction (treat the 'earn' direction as positive issuance and the other direction as redemption). Then convert that program-currency amount to EUR using exchange_rates (base_code='EUR', quote_code=program currency, as_of = the transaction date; EUR_amount = amount_in_currency / rate). Net points liability change = earned value - redeemed value for the month; cumulative liability outstanding = running sum of net change from the start of history through the end of that month. Referral rewards paid: sum of referral_redemptions.reward_amount by redeemed_at month, converted from referral_redemptions.currency_code to EUR the same way. Referral redemptions count: count of referral_redemptions rows per month. New loyalty enrollments: count of loyalty_accounts.enrolled_at per month. Order by month ascending. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runWITH months AS ( SELECT DISTINCT month_label, month_start FROM calendar_days WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31' ), loyalty_tx AS ( SELECT lt.id, lt.direction, lt.occurred_at, lp.currency_code, (CAST(lt.points AS numeric) / lp.points_per_currency) AS amount_in_currency FROM loyalty_transactions lt JOIN loyalty_accounts la ON la.id = lt.loyalty_account_id JOIN loyalty_programs lp ON lp.id = la.loyalty_program_id WHERE lt.occurred_at >= TIMESTAMP '2023-01-01' AND lt.occurred_at < TIMESTAMP '2026-01-01' ), loyalty_tx_eur AS ( SELECT lt.direction, lt.amount_in_currency / COALESCE(er.rate, CASE WHEN lt.currency_code = 'EUR' THEN 1 END) AS amount_eur, date_trunc('month', lt.occurred_at)::date AS month_start FROM loyalty_tx lt LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = lt.currency_code AND er.as_of = CAST(lt.occurred_at AS date) ), loyalty_monthly AS ( SELECT month_start, SUM(CASE WHEN direction = 'earn' THEN ABS(amount_eur) ELSE 0 END) AS earned_value_eur, SUM(CASE WHEN direction <> 'earn' THEN ABS(amount_eur) ELSE 0 END) AS redeemed_value_eur FROM loyalty_tx_eur GROUP BY month_start ), referral_tx AS ( SELECT rr.redeemed_at, rr.reward_amount, rr.currency_code FROM referral_redemptions rr WHERE rr.redeemed_at >= TIMESTAMP '2023-01-01' AND rr.redeemed_at < TIMESTAMP '2026-01-01' AND rr.reward_amount IS NOT NULL ), referral_tx_eur AS ( SELECT rt.reward_amount / COALESCE(er.rate, CASE WHEN rt.currency_code = 'EUR' THEN 1 END) AS amount_eur, date_trunc('month', rt.redeemed_at)::date AS month_start FROM referral_tx rt LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = rt.currency_code AND er.as_of = CAST(rt.redeemed_at AS date) ), referral_monthly AS ( SELECT month_start, SUM(amount_eur) AS referral_rewards_paid_eur, COUNT(*) AS referral_redemptions_count FROM referral_tx_eur GROUP BY month_start ), enrollments_monthly AS ( SELECT date_trunc('month', enrolled_at)::date AS month_start, COUNT(*) AS new_enrollments FROM loyalty_accounts WHERE enrolled_at >= TIMESTAMP '2023-01-01' AND enrolled_at < TIMESTAMP '2026-01-01' GROUP BY date_trunc('month', enrolled_at)::date ), combined AS ( SELECT m.month_label, m.month_start, COALESCE(lm.earned_value_eur, 0) AS points_earned_value_eur, COALESCE(lm.redeemed_value_eur, 0) AS points_redeemed_value_eur, COALESCE(lm.earned_value_eur, 0) - COALESCE(lm.redeemed_value_eur, 0) AS net_points_liability_change_eur, COALESCE(rm.referral_rewards_paid_eur, 0) AS referral_rewards_paid_eur, COALESCE(rm.referral_redemptions_count, 0) AS referral_redemptions_count, COALESCE(em.new_enrollments, 0) AS new_loyalty_enrollments FROM months m LEFT JOIN loyalty_monthly lm ON lm.month_start = m.month_start LEFT JOIN referral_monthly rm ON rm.month_start = m.month_start LEFT JOIN enrollments_monthly em ON em.month_start = m.month_start ) SELECT month_label AS month, ROUND(points_earned_value_eur, 2) AS points_earned_value_eur, ROUND(points_redeemed_value_eur, 2) AS points_redeemed_value_eur, ROUND(net_points_liability_change_eur, 2) AS net_points_liability_change_eur, ROUND(SUM(net_points_liability_change_eur) OVER (ORDER BY month_start ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) AS cumulative_points_liability_eur, ROUND(referral_rewards_paid_eur, 2) AS referral_rewards_paid_eur, referral_redemptions_count, new_loyalty_enrollments FROM combined ORDER BY month_start
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: month, points_earned_value_eur, points_redeemed_value_eur, net_points_liability_change_eur, cumulative_points_liability_eur, referral_rewards_paid_eur, referral_redemptions_count, new_loyalty_enrollments
submitWITH months AS ( SELECT DISTINCT month_label, month_start FROM calendar_days WHERE calendar_date BETWEEN DATE '2023-01-01' AND DATE '2025-12-31' ), loyalty_tx AS ( SELECT lt.id, lt.direction, lt.occurred_at, lp.currency_code, (CAST(lt.points AS numeric) / lp.points_per_currency) AS amount_in_currency FROM loyalty_transactions lt JOIN loyalty_accounts la ON la.id = lt.loyalty_account_id JOIN loyalty_programs lp ON lp.id = la.loyalty_program_id WHERE lt.occurred_at >= TIMESTAMP '2023-01-01' AND lt.occurred_at < TIMESTAMP '2026-01-01' ), loyalty_tx_eur AS ( SELECT lt.direction, lt.amount_in_currency / COALESCE(er.rate, CASE WHEN lt.currency_code = 'EUR' THEN 1 END) AS amount_eur, date_trunc('month', lt.occurred_at)::date AS month_start FROM loyalty_tx lt LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = lt.currency_code AND er.as_of = CAST(lt.occurred_at AS date) ), loyalty_monthly AS ( SELECT month_start, SUM(CASE WHEN direction = 'earn' THEN ABS(amount_eur) ELSE 0 END) AS earned_value_eur, SUM(CASE WHEN direction <> 'earn' THEN ABS(amount_eur) ELSE 0 END) AS redeemed_value_eur FROM loyalty_tx_eur GROUP BY month_start ), referral_tx AS ( SELECT rr.redeemed_at, rr.reward_amount, rr.currency_code FROM referral_redemptions rr WHERE rr.redeemed_at >= TIMESTAMP '2023-01-01' AND rr.redeemed_at < TIMESTAMP '2026-01-01' AND rr.reward_amount IS NOT NULL ), referral_tx_eur AS ( SELECT rt.reward_amount / COALESCE(er.rate, CASE WHEN rt.currency_code = 'EUR' THEN 1 END) AS amount_eur, date_trunc('month', rt.redeemed_at)::date AS month_start FROM referral_tx rt LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = rt.currency_code AND er.as_of = CAST(rt.redeemed_at AS date) ), referral_monthly AS ( SELECT month_start, SUM(amount_eur) AS referral_rewards_paid_eur, COUNT(*) AS referral_redemptions_count FROM referral_tx_eur GROUP BY month_start ), enrollments_monthly AS ( SELECT date_trunc('month', enrolled_at)::date AS month_start, COUNT(*) AS new_enrollments FROM loyalty_accounts WHERE enrolled_at >= TIMESTAMP '2023-01-01' AND enrolled_at < TIMESTAMP '2026-01-01' GROUP BY date_trunc('month', enrolled_at)::date ), combined AS ( SELECT m.month_label, m.month_start, COALESCE(lm.earned_value_eur, 0) AS points_earned_value_eur, COALESCE(lm.redeemed_value_eur, 0) AS points_redeemed_value_eur, COALESCE(lm.earned_value_eur, 0) - COALESCE(lm.redeemed_value_eur, 0) AS net_points_liability_change_eur, COALESCE(rm.referral_rewards_paid_eur, 0) AS referral_rewards_paid_eur, COALESCE(rm.referral_redemptions_count, 0) AS referral_redemptions_count, COALESCE(em.new_enrollments, 0) AS new_loyalty_enrollments FROM months m LEFT JOIN loyalty_monthly lm ON lm.month_start = m.month_start LEFT JOIN referral_monthly rm ON rm.month_start = m.month_start LEFT JOIN enrollments_monthly em ON em.month_start = m.month_start ) SELECT month_label AS month, ROUND(points_earned_value_eur, 2) AS points_earned_value_eur, ROUND(points_redeemed_value_eur, 2) AS points_redeemed_value_eur, ROUND(net_points_liability_change_eur, 2) AS net_points_liability_change_eur, ROUND(SUM(net_points_liability_change_eur) OVER (ORDER BY month_start ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) AS cumulative_points_liability_eur, ROUND(referral_rewards_paid_eur, 2) AS referral_rewards_paid_eur, referral_redemptions_count, new_loyalty_enrollments FROM combined ORDER BY month_start
Submitted to tonight's batch. Your work on this request is done.