Queries · q377 · Head of Lifecycle Marketing · week 2, night 3
“Is the programme's realized cost concentrated entirely in the minority of members who actually redeem, while the disengaged majority sits as pure unclaimed liability rather than real cost?”
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 2
Subject: Re: Loyalty & referral — thank you, and where to dig next
Ingrid, this is excellent — especially catching that the refund-rate gap was a vertical mix artifact. Good instinct to go back and break that out rather than let the scary headline stand.
What surprised me most, and what I want explained before we take this to Tobias as a joint recommendation: a 19.4% cost-to-member-revenue ratio is strikingly high, and members showing lower revenue and order counts than non-members is the opposite of what a loyalty programme is supposed to produce. I don't want to present "the programme loses money" to leadership until I understand whether that's a true causal effect or another mix artifact like the refund rate turned out to be — since 72% of members sit in one vertical, I'm wary of a second apples-to-oranges comparison hiding in there.
Questions for this week, in my own words:
The B2B extension question can wait — I agree we shouldn't open that door until we trust the member economics number. Let's keep that out of this week's slots unless there's room left over.
Thanks again, Ingrid — this is exactly the kind of rigor I want from this team.
Best, Aisha
The analyst · week 2, night 3, request 2 of 3
Night 2 closed out Q3 cleanly: only 5 of 4,936 member orders fall on the same calendar day as any points redemption, and that handful is too thin to read anything into (and still not a true causal link) - combined with burn transactions never carrying an order_id, this tells me redemption is simply decoupled from checkout timing in how the system records it, not that gaming is happening. I'm treating that hypothesis as addressed: no usable signal, data model doesn't support closer scrutiny. Report 2 ruled out discounting as the cause of the Apparel AOV gap - discount rates are small and similar for both segments (4.6% vs 3.6%); the gap is almost entirely in the pre-discount subtotal, so members are just buying cheaper baskets, not getting bigger discounts. The big thread to pull tonight is Report 3: it turns out loyalty members are not one population - only 3.9% of the bottom 75% (by revenue) have EVER redeemed a point, versus 55.9% of the top 25%, and the top 25% look like dramatically better customers (6x the revenue, 3x the orders, 2.5x the tenure). That could mean engagement drives value, or it could just mean long-tenured big spenders naturally rack up and redeem more points - I want to pull that apart before telling Aisha 'engagement is the fix.' Tonight: (1) re-cut the Apparel tenure comparison into three groups - never-redeemed members, ever-redeemed members, non-members - to see if engaged members actually match or beat non-members once tenure is held constant, while disengaged members lag; (2) see whether the programme's real redeemed cost is concentrated entirely in the engaged minority (meaning the disengaged majority is pure low-stakes liability, not realized cost); (3) a before/after-first-redemption test on engaged members only (this one should actually have pre-period data, unlike the failed before/after-enrollment test) to get at causality - does ordering pick up after someone's first redemption, or were they already heavy buyers beforehand?
The engineer · 1 dry run on the empty schema, then the submission
SELECT CASE WHEN m.is_ever_redeemed THEN 'ever-redeemed member' ELSE 'never-redeemed member' END AS "group", COUNT(*) AS member_count, SUM(m.revenue_eur) AS total_revenue_eur, SUM(m.revenue_eur) / COUNT(*) AS revenue_per_customer_eur, SUM(m.redeemed_cost_eur) AS total_points_redeemed_cost_eur, SUM(m.earned_liability_eur) AS total_points_earned_liability_eur, CASE WHEN SUM(m.revenue_eur) = 0 THEN NULL ELSE SUM(m.redeemed_cost_eur) / SUM(m.revenue_eur) * 100 END AS redeemed_cost_pct_of_revenue FROM ( SELECT u.id AS user_id, EXISTS (SELECT 1 FROM loyalty_transactions lt WHERE lt.user_id = u.id AND lt.direction = 'burn') AS is_ever_redeemed, (SELECT COALESCE(SUM( CASE WHEN o.currency_code = 'EUR' THEN o.grand_total ELSE o.grand_total / er.rate END ), 0) 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.user_id = u.id AND o.status <> 'cancelled' AND o.placed_at >= DATE '2023-01-01' AND o.placed_at < DATE '2026-01-01' ) AS revenue_eur, (SELECT COALESCE(SUM( CASE WHEN lp.currency_code = 'EUR' THEN ABS(lt2.points) / lp.points_per_currency ELSE (ABS(lt2.points) / lp.points_per_currency) / er2.rate END ), 0) FROM loyalty_transactions lt2 JOIN loyalty_accounts la2 ON la2.id = lt2.loyalty_account_id JOIN loyalty_programs lp ON lp.id = la2.loyalty_program_id LEFT JOIN exchange_rates er2 ON er2.base_code = 'EUR' AND er2.quote_code = lp.currency_code AND er2.as_of = CAST(lt2.occurred_at AS DATE) WHERE lt2.user_id = u.id AND lt2.direction = 'burn' ) AS redeemed_cost_eur, (SELECT COALESCE(SUM( CASE WHEN lp.currency_code = 'EUR' THEN ABS(lt3.points) / lp.points_per_currency ELSE (ABS(lt3.points) / lp.points_per_currency) / er3.rate END ), 0) FROM loyalty_transactions lt3 JOIN loyalty_accounts la3 ON la3.id = lt3.loyalty_account_id JOIN loyalty_programs lp ON lp.id = la3.loyalty_program_id LEFT JOIN exchange_rates er3 ON er3.base_code = 'EUR' AND er3.quote_code = lp.currency_code AND er3.as_of = CAST(lt3.occurred_at AS DATE) WHERE lt3.user_id = u.id AND lt3.direction = 'earn' ) AS earned_liability_eur FROM users u WHERE EXISTS (SELECT 1 FROM loyalty_accounts la WHERE la.user_id = u.id) AND EXISTS (SELECT 1 FROM orders o2 WHERE o2.user_id = u.id AND o2.placed_at >= DATE '2023-01-01' AND o2.placed_at < DATE '2026-01-01') ) m GROUP BY m.is_ever_redeemed ORDER BY m.is_ever_redeemed DESC
“Query compiles and returns the expected columns. Submitting.”
SELECT CASE WHEN m.is_ever_redeemed THEN 'ever-redeemed member' ELSE 'never-redeemed member' END AS "group", COUNT(*) AS member_count, SUM(m.revenue_eur) AS total_revenue_eur, SUM(m.revenue_eur) / COUNT(*) AS revenue_per_customer_eur, SUM(m.redeemed_cost_eur) AS total_points_redeemed_cost_eur, SUM(m.earned_liability_eur) AS total_points_earned_liability_eur, CASE WHEN SUM(m.revenue_eur) = 0 THEN NULL ELSE SUM(m.redeemed_cost_eur) / SUM(m.revenue_eur) * 100 END AS redeemed_cost_pct_of_revenue ROUND(SUM(m.revenue_eur), 2) AS total_revenue_eur, ROUND(SUM(m.revenue_eur) / COUNT(*), 2) AS revenue_per_customer_eur, ROUND(SUM(m.redeemed_cost_eur), 2) AS total_points_redeemed_cost_eur, ROUND(SUM(m.earned_liability_eur), 2) AS total_points_earned_liability_eur, CASE WHEN SUM(m.revenue_eur) = 0 THEN NULL ELSE ROUND(SUM(m.redeemed_cost_eur) / SUM(m.revenue_eur) * 100, 2) END AS redeemed_cost_pct_of_revenueFROM ( SELECT u.id AS user_id, EXISTS (SELECT 1 FROM loyalty_transactions lt WHERE lt.user_id = u.id AND lt.direction = 'burn') AS is_ever_redeemed, (SELECT COALESCE(SUM( CASE WHEN o.currency_code = 'EUR' THEN o.grand_total ELSE o.grand_total / er.rate END ), 0) 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.user_id = u.id AND o.status <> 'cancelled' AND o.placed_at >= DATE '2023-01-01' AND o.placed_at < DATE '2026-01-01' ) AS revenue_eur, (SELECT COALESCE(SUM( CASE WHEN lp.currency_code = 'EUR' THEN ABS(lt2.points) / lp.points_per_currency ELSE (ABS(lt2.points) / lp.points_per_currency) / er2.rate END ), 0) FROM loyalty_transactions lt2 JOIN loyalty_accounts la2 ON la2.id = lt2.loyalty_account_id JOIN loyalty_programs lp ON lp.id = la2.loyalty_program_id LEFT JOIN exchange_rates er2 ON er2.base_code = 'EUR' AND er2.quote_code = lp.currency_code AND er2.as_of = CAST(lt2.occurred_at AS DATE) WHERE lt2.user_id = u.id AND lt2.direction = 'burn' ) AS redeemed_cost_eur, (SELECT COALESCE(SUM( CASE WHEN lp.currency_code = 'EUR' THEN ABS(lt3.points) / lp.points_per_currency ELSE (ABS(lt3.points) / lp.points_per_currency) / er3.rate END ), 0) FROM loyalty_transactions lt3 JOIN loyalty_accounts la3 ON la3.id = lt3.loyalty_account_id JOIN loyalty_programs lp ON lp.id = la3.loyalty_program_id LEFT JOIN exchange_rates er3 ON er3.base_code = 'EUR' AND er3.quote_code = lp.currency_code AND er3.as_of = CAST(lt3.occurred_at AS DATE) WHERE lt3.user_id = u.id AND lt3.direction = 'earn' ) AS earned_liability_eur FROM users u WHERE EXISTS (SELECT 1 FROM loyalty_accounts la WHERE la.user_id = u.id) AND EXISTS (SELECT 1 FROM orders o2 WHERE o2.user_id = u.id AND o2.placed_at >= DATE '2023-01-01' AND o2.placed_at < DATE '2026-01-01')) mGROUP BY m.is_ever_redeemedORDER BY m.is_ever_redeemed DESC
“Submitted. The report groups loyalty members (who placed at least one order in 2023–2025) into ever-redeemed vs never-redeemed, comparing their EUR-converted revenue against the realized points-redemption cost and the earned-points liability, with the redemption cost expressed as a percentage of revenue for each group.”
As Ingrid Solberg saw it, from the small dataset the agents worked on
group | member_count | total_revenue_eur | revenue_per_customer_eur | total_points_redeemed_cost_eur | total_points_earned_liability_eur | redeemed_cost_pct_of_revenue ----------------------+--------------+-------------------+--------------------------+--------------------------------+-----------------------------------+----------------------------- ever-redeemed member | 225 | 434119.65 | 1929.42 | 177782.98 | 345431.82 | 40.95 never-redeemed member | 1105 | 480911.34 | 435.21 | 0.0 | 323569.59 | 0.0 (2 rows)
The memo and the queries that followed; links found by title, so inferred
Ingrid Solberg's memo to Aisha Rahman at the end of the week:
3. The programme's real cost is concentrated, and richer than I reported last week. The engaged 17% (225 members) have redeemed €177.8k against €434k of their own revenue — a 40.95% effective discount rate, well above the 19.4% blended figure. The other 83% cost nothing in cash but carry €323.6k in paper liability that, per cohort-aging data, mostly never gets claimed (redemption rates plateau at 32–37% even for 3-year-old cohorts). (Reports: "Loyalty unit economics: ever-redeemed vs never-redeemed"; "Loyalty points redemption rate by enrollment cohort age.")
It builds on:
Changed only where the benchmark needs the same result on every system
SELECT CASE WHEN m.is_ever_redeemed THEN 'ever-redeemed member' ELSE 'never-redeemed member' END AS "group", COUNT(*) AS member_count, ROUND(SUM(m.revenue_eur), 2) AS total_revenue_eur, ROUND(SUM(m.revenue_eur) / COUNT(*), 2) AS revenue_per_customer_eur, ROUND(SUM(m.redeemed_cost_eur), 2) AS total_points_redeemed_cost_eur, ROUND(SUM(m.earned_liability_eur), 2) AS total_points_earned_liability_eur, CASE WHEN SUM(m.revenue_eur) = 0 THEN NULL ELSE ROUND(SUM(m.redeemed_cost_eur) / SUM(m.revenue_eur) * 100, 2) END AS redeemed_cost_pct_of_revenue SUM(m.revenue_eur) AS total_revenue_eur, SUM(m.revenue_eur) / COUNT(*) AS revenue_per_customer_eur, SUM(m.redeemed_cost_eur) AS total_points_redeemed_cost_eur, SUM(m.earned_liability_eur) AS total_points_earned_liability_eur, CASE WHEN SUM(m.revenue_eur) = 0 THEN NULL ELSE SUM(m.redeemed_cost_eur) / SUM(m.revenue_eur) * 100 END AS redeemed_cost_pct_of_revenueFROM ( SELECT u.id AS user_id, EXISTS (SELECT 1 FROM loyalty_transactions lt WHERE lt.user_id = u.id AND lt.direction = 'burn') AS is_ever_redeemed, (SELECT COALESCE(SUM( CASE WHEN o.currency_code = 'EUR' THEN o.grand_total ELSE o.grand_total / er.rate END ELSE CAST(o.grand_total / er.rate AS NUMERIC(18,6)) END ), 0) 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.user_id = u.id AND o.status <> 'cancelled' AND o.placed_at >= DATE '2023-01-01' AND o.placed_at < DATE '2026-01-01' ) AS revenue_eur, (SELECT COALESCE(SUM( CASE WHEN lp.currency_code = 'EUR' THEN ABS(lt2.points) / lp.points_per_currency ELSE (ABS(lt2.points) / lp.points_per_currency) / er2.rate END ELSE CAST((ABS(lt2.points) / lp.points_per_currency) / er2.rate AS NUMERIC(18,6)) END ), 0) FROM loyalty_transactions lt2 JOIN loyalty_accounts la2 ON la2.id = lt2.loyalty_account_id JOIN loyalty_programs lp ON lp.id = la2.loyalty_program_id LEFT JOIN exchange_rates er2 ON er2.base_code = 'EUR' AND er2.quote_code = lp.currency_code AND er2.as_of = CAST(lt2.occurred_at AS DATE) WHERE lt2.user_id = u.id AND lt2.direction = 'burn' ) AS redeemed_cost_eur, (SELECT COALESCE(SUM( CASE WHEN lp.currency_code = 'EUR' THEN ABS(lt3.points) / lp.points_per_currency ELSE (ABS(lt3.points) / lp.points_per_currency) / er3.rate END ELSE CAST((ABS(lt3.points) / lp.points_per_currency) / er3.rate AS NUMERIC(18,6)) END ), 0) FROM loyalty_transactions lt3 JOIN loyalty_accounts la3 ON la3.id = lt3.loyalty_account_id JOIN loyalty_programs lp ON lp.id = la3.loyalty_program_id LEFT JOIN exchange_rates er3 ON er3.base_code = 'EUR' AND er3.quote_code = lp.currency_code AND er3.as_of = CAST(lt3.occurred_at AS DATE) WHERE lt3.user_id = u.id AND lt3.direction = 'earn' ) AS earned_liability_eur FROM users u WHERE EXISTS (SELECT 1 FROM loyalty_accounts la WHERE la.user_id = u.id) AND EXISTS (SELECT 1 FROM orders o2 WHERE o2.user_id = u.id AND o2.placed_at >= DATE '2023-01-01' AND o2.placed_at < DATE '2026-01-01')) mGROUP BY m.is_ever_redeemedORDER BY m.is_ever_redeemed DESCORDER BY m.is_ever_redeemed DESC NULLS FIRST
SELECT CASE WHEN m.is_ever_redeemed THEN 'ever-redeemed member' ELSE 'never-redeemed member' END AS "group", COUNT(*) AS member_count, SUM(m.revenue_eur) AS total_revenue_eur, SUM(m.revenue_eur) / COUNT(*) AS revenue_per_customer_eur, SUM(m.redeemed_cost_eur) AS total_points_redeemed_cost_eur, SUM(m.earned_liability_eur) AS total_points_earned_liability_eur, CASE WHEN SUM(m.revenue_eur) = 0 THEN NULL ELSE SUM(m.redeemed_cost_eur) / SUM(m.revenue_eur) * 100 END AS redeemed_cost_pct_of_revenue FROM ( SELECT u.id AS user_id, EXISTS (SELECT 1 FROM loyalty_transactions lt WHERE lt.user_id = u.id AND lt.direction = 'burn') AS is_ever_redeemed, (SELECT COALESCE(SUM( CASE WHEN o.currency_code = 'EUR' THEN o.grand_total ELSE CAST(o.grand_total / er.rate AS NUMERIC(18,6)) END ), 0) 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.user_id = u.id AND o.status <> 'cancelled' AND o.placed_at >= DATE '2023-01-01' AND o.placed_at < DATE '2026-01-01' ) AS revenue_eur, (SELECT COALESCE(SUM( CASE WHEN lp.currency_code = 'EUR' THEN ABS(lt2.points) / lp.points_per_currency ELSE CAST((ABS(lt2.points) / lp.points_per_currency) / er2.rate AS NUMERIC(18,6)) END ), 0) FROM loyalty_transactions lt2 JOIN loyalty_accounts la2 ON la2.id = lt2.loyalty_account_id JOIN loyalty_programs lp ON lp.id = la2.loyalty_program_id LEFT JOIN exchange_rates er2 ON er2.base_code = 'EUR' AND er2.quote_code = lp.currency_code AND er2.as_of = CAST(lt2.occurred_at AS DATE) WHERE lt2.user_id = u.id AND lt2.direction = 'burn' ) AS redeemed_cost_eur, (SELECT COALESCE(SUM( CASE WHEN lp.currency_code = 'EUR' THEN ABS(lt3.points) / lp.points_per_currency ELSE CAST((ABS(lt3.points) / lp.points_per_currency) / er3.rate AS NUMERIC(18,6)) END ), 0) FROM loyalty_transactions lt3 JOIN loyalty_accounts la3 ON la3.id = lt3.loyalty_account_id JOIN loyalty_programs lp ON lp.id = la3.loyalty_program_id LEFT JOIN exchange_rates er3 ON er3.base_code = 'EUR' AND er3.quote_code = lp.currency_code AND er3.as_of = CAST(lt3.occurred_at AS DATE) WHERE lt3.user_id = u.id AND lt3.direction = 'earn' ) AS earned_liability_eur FROM users u WHERE EXISTS (SELECT 1 FROM loyalty_accounts la WHERE la.user_id = u.id) AND EXISTS (SELECT 1 FROM orders o2 WHERE o2.user_id = u.id AND o2.placed_at >= DATE '2023-01-01' AND o2.placed_at < DATE '2026-01-01') ) m GROUP BY m.is_ever_redeemed ORDER BY m.is_ever_redeemed DESC NULLS FIRST
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 unit economics: ever-redeemed vs never-redeemed members Question: Is the programme's realized cost concentrated entirely in the minority of members who actually redeem, while the disengaged majority sits as pure unclaimed liability rather than real cost? Grain: one row per member engagement group: 'ever-redeemed member' vs 'never-redeemed member' Columns: - group - member_count - total_revenue_eur - revenue_per_customer_eur - total_points_redeemed_cost_eur - total_points_earned_liability_eur - redeemed_cost_pct_of_revenue Details: Population: all users with a loyalty_accounts row who placed at least one order 2023-01-01 to 2025-12-31. Group: 'ever-redeemed member' if the user has at least one 'burn'-direction loyalty_transactions row, else 'never-redeemed member'. total_revenue_eur = sum of grand_total on non-cancelled orders, converted to EUR via exchange_rates (as_of placed_at); revenue_per_customer_eur = total_revenue_eur divided by member_count. total_points_redeemed_cost_eur = sum of 'burn'-direction loyalty_transactions converted to currency via the program's points_per_currency and currency_code, then to EUR via exchange_rates as_of occurred_at (same method as prior unit-economics reports); this will be 0 for never-redeemed members by construction. total_points_earned_liability_eur = same conversion for 'earn'-direction transactions. redeemed_cost_pct_of_revenue = total_points_redeemed_cost_eur divided by total_revenue_eur, as a percentage. Order rows: 'ever-redeemed member' first. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runSELECT CASE WHEN m.is_ever_redeemed THEN 'ever-redeemed member' ELSE 'never-redeemed member' END AS "group", COUNT(*) AS member_count, SUM(m.revenue_eur) AS total_revenue_eur, SUM(m.revenue_eur) / COUNT(*) AS revenue_per_customer_eur, SUM(m.redeemed_cost_eur) AS total_points_redeemed_cost_eur, SUM(m.earned_liability_eur) AS total_points_earned_liability_eur, CASE WHEN SUM(m.revenue_eur) = 0 THEN NULL ELSE SUM(m.redeemed_cost_eur) / SUM(m.revenue_eur) * 100 END AS redeemed_cost_pct_of_revenue FROM ( SELECT u.id AS user_id, EXISTS (SELECT 1 FROM loyalty_transactions lt WHERE lt.user_id = u.id AND lt.direction = 'burn') AS is_ever_redeemed, (SELECT COALESCE(SUM( CASE WHEN o.currency_code = 'EUR' THEN o.grand_total ELSE o.grand_total / er.rate END ), 0) 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.user_id = u.id AND o.status <> 'cancelled' AND o.placed_at >= DATE '2023-01-01' AND o.placed_at < DATE '2026-01-01' ) AS revenue_eur, (SELECT COALESCE(SUM( CASE WHEN lp.currency_code = 'EUR' THEN ABS(lt2.points) / lp.points_per_currency ELSE (ABS(lt2.points) / lp.points_per_currency) / er2.rate END ), 0) FROM loyalty_transactions lt2 JOIN loyalty_accounts la2 ON la2.id = lt2.loyalty_account_id JOIN loyalty_programs lp ON lp.id = la2.loyalty_program_id LEFT JOIN exchange_rates er2 ON er2.base_code = 'EUR' AND er2.quote_code = lp.currency_code AND er2.as_of = CAST(lt2.occurred_at AS DATE) WHERE lt2.user_id = u.id AND lt2.direction = 'burn' ) AS redeemed_cost_eur, (SELECT COALESCE(SUM( CASE WHEN lp.currency_code = 'EUR' THEN ABS(lt3.points) / lp.points_per_currency ELSE (ABS(lt3.points) / lp.points_per_currency) / er3.rate END ), 0) FROM loyalty_transactions lt3 JOIN loyalty_accounts la3 ON la3.id = lt3.loyalty_account_id JOIN loyalty_programs lp ON lp.id = la3.loyalty_program_id LEFT JOIN exchange_rates er3 ON er3.base_code = 'EUR' AND er3.quote_code = lp.currency_code AND er3.as_of = CAST(lt3.occurred_at AS DATE) WHERE lt3.user_id = u.id AND lt3.direction = 'earn' ) AS earned_liability_eur FROM users u WHERE EXISTS (SELECT 1 FROM loyalty_accounts la WHERE la.user_id = u.id) AND EXISTS (SELECT 1 FROM orders o2 WHERE o2.user_id = u.id AND o2.placed_at >= DATE '2023-01-01' AND o2.placed_at < DATE '2026-01-01') ) m GROUP BY m.is_ever_redeemed ORDER BY m.is_ever_redeemed DESC
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: group, member_count, total_revenue_eur, revenue_per_customer_eur, total_points_redeemed_cost_eur, total_points_earned_liability_eur, redeemed_cost_pct_of_revenue
submitSELECT CASE WHEN m.is_ever_redeemed THEN 'ever-redeemed member' ELSE 'never-redeemed member' END AS "group", COUNT(*) AS member_count, ROUND(SUM(m.revenue_eur), 2) AS total_revenue_eur, ROUND(SUM(m.revenue_eur) / COUNT(*), 2) AS revenue_per_customer_eur, ROUND(SUM(m.redeemed_cost_eur), 2) AS total_points_redeemed_cost_eur, ROUND(SUM(m.earned_liability_eur), 2) AS total_points_earned_liability_eur, CASE WHEN SUM(m.revenue_eur) = 0 THEN NULL ELSE ROUND(SUM(m.redeemed_cost_eur) / SUM(m.revenue_eur) * 100, 2) END AS redeemed_cost_pct_of_revenue FROM ( SELECT u.id AS user_id, EXISTS (SELECT 1 FROM loyalty_transactions lt WHERE lt.user_id = u.id AND lt.direction = 'burn') AS is_ever_redeemed, (SELECT COALESCE(SUM( CASE WHEN o.currency_code = 'EUR' THEN o.grand_total ELSE o.grand_total / er.rate END ), 0) 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.user_id = u.id AND o.status <> 'cancelled' AND o.placed_at >= DATE '2023-01-01' AND o.placed_at < DATE '2026-01-01' ) AS revenue_eur, (SELECT COALESCE(SUM( CASE WHEN lp.currency_code = 'EUR' THEN ABS(lt2.points) / lp.points_per_currency ELSE (ABS(lt2.points) / lp.points_per_currency) / er2.rate END ), 0) FROM loyalty_transactions lt2 JOIN loyalty_accounts la2 ON la2.id = lt2.loyalty_account_id JOIN loyalty_programs lp ON lp.id = la2.loyalty_program_id LEFT JOIN exchange_rates er2 ON er2.base_code = 'EUR' AND er2.quote_code = lp.currency_code AND er2.as_of = CAST(lt2.occurred_at AS DATE) WHERE lt2.user_id = u.id AND lt2.direction = 'burn' ) AS redeemed_cost_eur, (SELECT COALESCE(SUM( CASE WHEN lp.currency_code = 'EUR' THEN ABS(lt3.points) / lp.points_per_currency ELSE (ABS(lt3.points) / lp.points_per_currency) / er3.rate END ), 0) FROM loyalty_transactions lt3 JOIN loyalty_accounts la3 ON la3.id = lt3.loyalty_account_id JOIN loyalty_programs lp ON lp.id = la3.loyalty_program_id LEFT JOIN exchange_rates er3 ON er3.base_code = 'EUR' AND er3.quote_code = lp.currency_code AND er3.as_of = CAST(lt3.occurred_at AS DATE) WHERE lt3.user_id = u.id AND lt3.direction = 'earn' ) AS earned_liability_eur FROM users u WHERE EXISTS (SELECT 1 FROM loyalty_accounts la WHERE la.user_id = u.id) AND EXISTS (SELECT 1 FROM orders o2 WHERE o2.user_id = u.id AND o2.placed_at >= DATE '2023-01-01' AND o2.placed_at < DATE '2026-01-01') ) m GROUP BY m.is_ever_redeemed ORDER BY m.is_ever_redeemed DESC
Submitted to tonight's batch. Your work on this request is done.