Queries · q380 · Head of Lifecycle Marketing · week 2, night 4
“Does the Apparel finding - that engaged (ever-redeemed) members beat tenure-matched non-members while never-redeemed members lag behind - hold up across the whole platform, or was it specific to Apparel?”
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 4, request 2 of 3
Night 3 was the turning point. Within Apparel, tenure-controlled, genuinely engaged members (ever redeemed a point) clearly beat non-members of the same tenure - roughly double the orders and 85-91% more revenue at 12-24 and 24-plus month tenure, with refund rates statistically indistinguishable from non-members. The 'member penalty' seen all along turns out to live entirely in the 83% of members who enrolled but have never redeemed a single point - they underperform both the engaged members and non-members on every metric. Unit economics confirm the realized cost is 100% concentrated in that engaged 17% (225 of 1330 members), and for them it's a rich 40.95% of their own revenue - a much higher 'discount rate' than the blended 19.4% figure suggested, though against genuinely larger, more valuable customers. The new wrinkle: among engaged members, annualized order frequency drops sharply after their first redemption (6.64 to 2.79 orders/year) and 30% place no order at all afterward - before I call this a 'redemption kills engagement' story I need to rule out that it's just normal customer-lifecycle deceleration that would show up for anyone. For this final night: (1) test whether that frequency drop is specific to redeemers or a universal pattern, by comparing first-half vs second-half-of-tenure frequency across non-members, never-redeemed members and ever-redeemed members; (2) confirm the engagement-separates-good-from-bad-members finding holds platform-wide, not just in Apparel; (3) check whether members who redeem quickly after enrolling (likely signup-bonus hunters) are disproportionately the ones who never order again, which would be the clearest 'reward without real repeat purchase' red flag Aisha asked about.
The engineer · 1 dry run on the empty schema, then the submission
WITH eligible_users AS ( SELECT DISTINCT u.id AS user_id, u.tenant_id, u.created_at FROM users u JOIN orders o ON o.user_id = u.id AND o.tenant_id = u.tenant_id WHERE u.is_staff = false AND o.placed_at >= TIMESTAMP '2023-01-01 00:00:00' AND o.placed_at < TIMESTAMP '2026-01-01 00:00:00' ), tenure AS ( SELECT user_id, tenant_id, CASE WHEN created_at > TIMESTAMP '2026-01-01 00:00:00' - INTERVAL '6 months' THEN 'under 6 months' WHEN created_at > TIMESTAMP '2026-01-01 00:00:00' - INTERVAL '12 months' THEN '6 to 12 months' WHEN created_at > TIMESTAMP '2026-01-01 00:00:00' - INTERVAL '24 months' THEN '12 to 24 months' ELSE 'over 24 months' END AS tenure_band FROM eligible_users ), loyalty_member AS ( SELECT DISTINCT user_id, tenant_id FROM loyalty_accounts ), loyalty_redeemed AS ( SELECT DISTINCT user_id, tenant_id FROM loyalty_transactions WHERE direction = 'burn' ), membership AS ( SELECT eu.user_id, eu.tenant_id, CASE WHEN lm.user_id IS NULL THEN 'non-member' WHEN lr.user_id IS NOT NULL THEN 'ever-redeemed member' ELSE 'never-redeemed member' END AS customer_group FROM eligible_users eu LEFT JOIN loyalty_member lm ON lm.user_id = eu.user_id AND lm.tenant_id = eu.tenant_id LEFT JOIN loyalty_redeemed lr ON lr.user_id = eu.user_id AND lr.tenant_id = eu.tenant_id ), order_refund_flag AS ( SELECT o.id AS order_id, CASE WHEN EXISTS (SELECT 1 FROM payments p WHERE p.order_id = o.id AND p.amount_refunded > 0) OR EXISTS (SELECT 1 FROM order_returns r WHERE r.order_id = o.id) THEN 1 ELSE 0 END AS is_refunded FROM orders o ), order_eur AS ( SELECT o.id AS order_id, o.user_id, o.tenant_id, o.status, CASE WHEN o.status <> 'cancelled' THEN o.grand_total / er.rate ELSE NULL END AS revenue_eur 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(COALESCE(o.placed_at, o.created_at) AS date) ), customer_orders AS ( SELECT eu.user_id, eu.tenant_id, COUNT(o.id) AS lifetime_orders, SUM(CASE WHEN o.status <> 'cancelled' THEN oe.revenue_eur ELSE 0 END) AS lifetime_revenue_eur, SUM(CASE WHEN o.status <> 'cancelled' THEN 1 ELSE 0 END) AS non_cancelled_order_count, SUM(orf.is_refunded) AS refunded_order_count FROM eligible_users eu JOIN orders o ON o.user_id = eu.user_id AND o.tenant_id = eu.tenant_id JOIN order_eur oe ON oe.order_id = o.id JOIN order_refund_flag orf ON orf.order_id = o.id GROUP BY eu.user_id, eu.tenant_id ), final AS ( SELECT t.tenure_band, m.customer_group, COUNT(*) AS customer_count, AVG(co.lifetime_orders) AS avg_lifetime_orders, AVG(co.lifetime_revenue_eur) AS avg_lifetime_revenue_eur, SUM(co.lifetime_revenue_eur) / NULLIF(SUM(co.non_cancelled_order_count), 0) AS avg_order_value_eur, 100.0 * SUM(co.refunded_order_count) / NULLIF(SUM(co.lifetime_orders), 0) AS refund_rate_pct FROM tenure t JOIN membership m ON m.user_id = t.user_id AND m.tenant_id = t.tenant_id JOIN customer_orders co ON co.user_id = t.user_id AND co.tenant_id = t.tenant_id GROUP BY t.tenure_band, m.customer_group ) SELECT tenure_band, customer_group AS "group", customer_count, ROUND(avg_lifetime_orders, 2) AS avg_lifetime_orders, ROUND(avg_lifetime_revenue_eur, 2) AS avg_lifetime_revenue_eur, ROUND(avg_order_value_eur, 2) AS avg_order_value_eur, ROUND(refund_rate_pct, 2) AS refund_rate_pct FROM final ORDER BY CASE tenure_band WHEN 'under 6 months' THEN 1 WHEN '6 to 12 months' THEN 2 WHEN '12 to 24 months' THEN 3 WHEN 'over 24 months' THEN 4 END, CASE customer_group WHEN 'non-member' THEN 1 WHEN 'never-redeemed member' THEN 2 WHEN 'ever-redeemed member' THEN 3 END
“Query compiles and returns exactly the requested grain and columns. Submitting.”
“Submitted. The query replicates the Apparel report's exact logic (tenure bands from users.created_at, the three loyalty groups, EUR-converted revenue from non-cancelled orders, and the refund definition) but drops the vertical filter so it runs across all tenants, giving 12 rows (4 tenure bands × 3 groups).”
As Ingrid Solberg saw it, from the small dataset the agents worked on
tenure_band | group | customer_count | avg_lifetime_orders | avg_lifetime_revenue_eur | avg_order_value_eur | refund_rate_pct ----------------+-----------------------+----------------+---------------------+--------------------------+---------------------+---------------- under 6 months | non-member | 1480 | 1.91 | 364.74 | 135.45 | 20.52 under 6 months | never-redeemed member | 259 | 1.75 | 337.46 | 140.02 | 27.15 under 6 months | ever-redeemed member | 7 | 3.14 | 1585.53 | 554.94 | 27.27 6 to 12 months | non-member | 1099 | 3.54 | 729.83 | 140.18 | 16.81 6 to 12 months | never-redeemed member | 200 | 2.27 | 425.27 | 129.17 | 29.96 6 to 12 months | ever-redeemed member | 12 | 4.5 | 1924.62 | 290.51 | 50.0 12 to 24 months | non-member | 2482 | 5.55 | 856.12 | 111.81 | 16.38 12 to 24 months | never-redeemed member | 298 | 3.21 | 462.42 | 109.68 | 33.3 12 to 24 months | ever-redeemed member | 78 | 7.82 | 1825.16 | 186.88 | 29.02 over 24 months | non-member | 3249 | 7.88 | 1543.8 | 136.67 | 14.36 over 24 months | never-redeemed member | 348 | 3.28 | 510.63 | 115.35 | 33.25 over 24 months | ever-redeemed member | 128 | 9.74 | 1843.77 | 143.95 | 34.56 (12 rows)
The memo and the queries that followed; links found by title, so inferred
It builds on:
Changed only where the benchmark needs the same result on every system
WITH eligible_users AS ( SELECT DISTINCT u.id AS user_id, u.tenant_id, u.created_at SELECT DISTINCT u.id AS user_id, u.tenant_id AS tenant_id, u.created_at AS created_at FROM users u JOIN orders o ON o.user_id = u.id AND o.tenant_id = u.tenant_id WHERE u.is_staff = false AND o.placed_at >= TIMESTAMP '2023-01-01 00:00:00' AND o.placed_at < TIMESTAMP '2026-01-01 00:00:00'),tenure AS ( SELECT user_id, tenant_id, CASE WHEN created_at > TIMESTAMP '2026-01-01 00:00:00' - INTERVAL '6 months' THEN 'under 6 months' WHEN created_at > TIMESTAMP '2026-01-01 00:00:00' - INTERVAL '12 months' THEN '6 to 12 months' WHEN created_at > TIMESTAMP '2026-01-01 00:00:00' - INTERVAL '24 months' THEN '12 to 24 months' ELSE 'over 24 months' END AS tenure_band FROM eligible_users),loyalty_member AS ( SELECT DISTINCT user_id, tenant_id FROM loyalty_accounts),loyalty_redeemed AS ( SELECT DISTINCT user_id, tenant_id FROM loyalty_transactions WHERE direction = 'burn'),membership AS ( SELECT eu.user_id, eu.tenant_id, eu.user_id AS user_id, eu.tenant_id AS tenant_id, CASE WHEN lm.user_id IS NULL THEN 'non-member' WHEN lr.user_id IS NOT NULL THEN 'ever-redeemed member' ELSE 'never-redeemed member' END AS customer_group FROM eligible_users eu LEFT JOIN loyalty_member lm ON lm.user_id = eu.user_id AND lm.tenant_id = eu.tenant_id LEFT JOIN loyalty_redeemed lr ON lr.user_id = eu.user_id AND lr.tenant_id = eu.tenant_id),order_refund_flag AS ( SELECT o.id AS order_id, CASE WHEN EXISTS (SELECT 1 FROM payments p WHERE p.order_id = o.id AND p.amount_refunded > 0) OR EXISTS (SELECT 1 FROM order_returns r WHERE r.order_id = o.id) THEN 1 ELSE 0 END AS is_refunded FROM orders o),order_eur AS ( SELECT o.id AS order_id, o.user_id, o.tenant_id, o.status, CASE WHEN o.status <> 'cancelled' THEN o.grand_total / er.rate ELSE NULL END AS revenue_eur o.user_id AS user_id, o.tenant_id AS tenant_id, o.status AS status, CASE WHEN o.status <> 'cancelled' THEN CAST(o.grand_total / er.rate AS NUMERIC(18,6)) ELSE NULL END AS revenue_eur 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(COALESCE(o.placed_at, o.created_at) AS date)),customer_orders AS ( SELECT eu.user_id, eu.tenant_id, eu.user_id AS user_id, eu.tenant_id AS tenant_id, COUNT(o.id) AS lifetime_orders, SUM(CASE WHEN o.status <> 'cancelled' THEN oe.revenue_eur ELSE 0 END) AS lifetime_revenue_eur, SUM(CASE WHEN o.status <> 'cancelled' THEN 1 ELSE 0 END) AS non_cancelled_order_count, SUM(orf.is_refunded) AS refunded_order_count FROM eligible_users eu JOIN orders o ON o.user_id = eu.user_id AND o.tenant_id = eu.tenant_id JOIN order_eur oe ON oe.order_id = o.id JOIN order_refund_flag orf ON orf.order_id = o.id GROUP BY eu.user_id, eu.tenant_id),final AS ( SELECT t.tenure_band, m.customer_group, t.tenure_band AS tenure_band, m.customer_group AS customer_group, COUNT(*) AS customer_count, AVG(co.lifetime_orders) AS avg_lifetime_orders, AVG(CAST(co.lifetime_orders AS NUMERIC(18,6))) AS avg_lifetime_orders, AVG(co.lifetime_revenue_eur) AS avg_lifetime_revenue_eur, SUM(co.lifetime_revenue_eur) / NULLIF(SUM(co.non_cancelled_order_count), 0) AS avg_order_value_eur, 100.0 * SUM(co.refunded_order_count) / NULLIF(SUM(co.lifetime_orders), 0) AS refund_rate_pct FROM tenure t JOIN membership m ON m.user_id = t.user_id AND m.tenant_id = t.tenant_id JOIN customer_orders co ON co.user_id = t.user_id AND co.tenant_id = t.tenant_id GROUP BY t.tenure_band, m.customer_group)SELECT tenure_band, customer_group AS "group", customer_count, ROUND(avg_lifetime_orders, 2) AS avg_lifetime_orders, ROUND(avg_lifetime_revenue_eur, 2) AS avg_lifetime_revenue_eur, ROUND(avg_order_value_eur, 2) AS avg_order_value_eur, ROUND(refund_rate_pct, 2) AS refund_rate_pct avg_lifetime_orders AS avg_lifetime_orders, avg_lifetime_revenue_eur AS avg_lifetime_revenue_eur, avg_order_value_eur AS avg_order_value_eur, refund_rate_pct AS refund_rate_pctFROM finalORDER BY CASE tenure_band WHEN 'under 6 months' THEN 1 WHEN '6 to 12 months' THEN 2 WHEN '12 to 24 months' THEN 3 WHEN 'over 24 months' THEN 4 END, END NULLS LAST, CASE customer_group WHEN 'non-member' THEN 1 WHEN 'never-redeemed member' THEN 2 WHEN 'ever-redeemed member' THEN 3 END END NULLS LAST
WITH eligible_users AS ( SELECT DISTINCT u.id AS user_id, u.tenant_id AS tenant_id, u.created_at AS created_at FROM users u JOIN orders o ON o.user_id = u.id AND o.tenant_id = u.tenant_id WHERE u.is_staff = false AND o.placed_at >= TIMESTAMP '2023-01-01 00:00:00' AND o.placed_at < TIMESTAMP '2026-01-01 00:00:00' ), tenure AS ( SELECT user_id, tenant_id, CASE WHEN created_at > TIMESTAMP '2026-01-01 00:00:00' - INTERVAL '6 months' THEN 'under 6 months' WHEN created_at > TIMESTAMP '2026-01-01 00:00:00' - INTERVAL '12 months' THEN '6 to 12 months' WHEN created_at > TIMESTAMP '2026-01-01 00:00:00' - INTERVAL '24 months' THEN '12 to 24 months' ELSE 'over 24 months' END AS tenure_band FROM eligible_users ), loyalty_member AS ( SELECT DISTINCT user_id, tenant_id FROM loyalty_accounts ), loyalty_redeemed AS ( SELECT DISTINCT user_id, tenant_id FROM loyalty_transactions WHERE direction = 'burn' ), membership AS ( SELECT eu.user_id AS user_id, eu.tenant_id AS tenant_id, CASE WHEN lm.user_id IS NULL THEN 'non-member' WHEN lr.user_id IS NOT NULL THEN 'ever-redeemed member' ELSE 'never-redeemed member' END AS customer_group FROM eligible_users eu LEFT JOIN loyalty_member lm ON lm.user_id = eu.user_id AND lm.tenant_id = eu.tenant_id LEFT JOIN loyalty_redeemed lr ON lr.user_id = eu.user_id AND lr.tenant_id = eu.tenant_id ), order_refund_flag AS ( SELECT o.id AS order_id, CASE WHEN EXISTS (SELECT 1 FROM payments p WHERE p.order_id = o.id AND p.amount_refunded > 0) OR EXISTS (SELECT 1 FROM order_returns r WHERE r.order_id = o.id) THEN 1 ELSE 0 END AS is_refunded FROM orders o ), order_eur AS ( SELECT o.id AS order_id, o.user_id AS user_id, o.tenant_id AS tenant_id, o.status AS status, CASE WHEN o.status <> 'cancelled' THEN CAST(o.grand_total / er.rate AS NUMERIC(18,6)) ELSE NULL END AS revenue_eur 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(COALESCE(o.placed_at, o.created_at) AS date) ), customer_orders AS ( SELECT eu.user_id AS user_id, eu.tenant_id AS tenant_id, COUNT(o.id) AS lifetime_orders, SUM(CASE WHEN o.status <> 'cancelled' THEN oe.revenue_eur ELSE 0 END) AS lifetime_revenue_eur, SUM(CASE WHEN o.status <> 'cancelled' THEN 1 ELSE 0 END) AS non_cancelled_order_count, SUM(orf.is_refunded) AS refunded_order_count FROM eligible_users eu JOIN orders o ON o.user_id = eu.user_id AND o.tenant_id = eu.tenant_id JOIN order_eur oe ON oe.order_id = o.id JOIN order_refund_flag orf ON orf.order_id = o.id GROUP BY eu.user_id, eu.tenant_id ), final AS ( SELECT t.tenure_band AS tenure_band, m.customer_group AS customer_group, COUNT(*) AS customer_count, AVG(CAST(co.lifetime_orders AS NUMERIC(18,6))) AS avg_lifetime_orders, AVG(co.lifetime_revenue_eur) AS avg_lifetime_revenue_eur, SUM(co.lifetime_revenue_eur) / NULLIF(SUM(co.non_cancelled_order_count), 0) AS avg_order_value_eur, 100.0 * SUM(co.refunded_order_count) / NULLIF(SUM(co.lifetime_orders), 0) AS refund_rate_pct FROM tenure t JOIN membership m ON m.user_id = t.user_id AND m.tenant_id = t.tenant_id JOIN customer_orders co ON co.user_id = t.user_id AND co.tenant_id = t.tenant_id GROUP BY t.tenure_band, m.customer_group ) SELECT tenure_band, customer_group AS "group", customer_count, avg_lifetime_orders AS avg_lifetime_orders, avg_lifetime_revenue_eur AS avg_lifetime_revenue_eur, avg_order_value_eur AS avg_order_value_eur, refund_rate_pct AS refund_rate_pct FROM final ORDER BY CASE tenure_band WHEN 'under 6 months' THEN 1 WHEN '6 to 12 months' THEN 2 WHEN '12 to 24 months' THEN 3 WHEN 'over 24 months' THEN 4 END NULLS LAST, CASE customer_group WHEN 'non-member' THEN 1 WHEN 'never-redeemed member' THEN 2 WHEN 'ever-redeemed member' THEN 3 END 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: Platform-wide: never-redeemed vs ever-redeemed members vs non-members, by tenure
Question: Does the Apparel finding - that engaged (ever-redeemed) members beat tenure-matched non-members while never-redeemed members lag behind - hold up across the whole platform, or was it specific to Apparel?
Grain: one row per combination of account-tenure band and customer group: 'non-member', 'never-redeemed member', 'ever-redeemed member', across all tenants and verticals
Columns:
- tenure_band
- group
- customer_count
- avg_lifetime_orders
- avg_lifetime_revenue_eur
- avg_order_value_eur
- refund_rate_pct
Details: Identical definitions to the 'Apparel: never-redeemed vs ever-redeemed members vs non-members, by tenure' report, but with no industry filter - include users across all tenants and verticals. Population: users (is_staff = false) with at least one order 2023-01-01 to 2025-12-31. Tenure band = time from users.created_at to 2025-12-31, bucketed 'under 6 months', '6 to 12 months', '12 to 24 months', 'over 24 months'. Group: 'non-member' if no loyalty_accounts row; 'ever-redeemed member' if at least one 'burn'-direction loyalty_transactions row; else 'never-redeemed member'. Revenue/AOV use non-cancelled orders converted to EUR via exchange_rates (as_of placed_at); avg_lifetime_orders and refund_rate_pct use all orders (any status); refund = payments.amount_refunded greater than 0 OR an order_returns row. Order rows by tenure_band (youngest to oldest), then group ('non-member', 'never-redeemed member', 'ever-redeemed member').
Check your query with the warehouse's dry_run tool, then hand it in with submit.dry_runWITH eligible_users AS ( SELECT DISTINCT u.id AS user_id, u.tenant_id, u.created_at FROM users u JOIN orders o ON o.user_id = u.id AND o.tenant_id = u.tenant_id WHERE u.is_staff = false AND o.placed_at >= TIMESTAMP '2023-01-01 00:00:00' AND o.placed_at < TIMESTAMP '2026-01-01 00:00:00' ), tenure AS ( SELECT user_id, tenant_id, CASE WHEN created_at > TIMESTAMP '2026-01-01 00:00:00' - INTERVAL '6 months' THEN 'under 6 months' WHEN created_at > TIMESTAMP '2026-01-01 00:00:00' - INTERVAL '12 months' THEN '6 to 12 months' WHEN created_at > TIMESTAMP '2026-01-01 00:00:00' - INTERVAL '24 months' THEN '12 to 24 months' ELSE 'over 24 months' END AS tenure_band FROM eligible_users ), loyalty_member AS ( SELECT DISTINCT user_id, tenant_id FROM loyalty_accounts ), loyalty_redeemed AS ( SELECT DISTINCT user_id, tenant_id FROM loyalty_transactions WHERE direction = 'burn' ), membership AS ( SELECT eu.user_id, eu.tenant_id, CASE WHEN lm.user_id IS NULL THEN 'non-member' WHEN lr.user_id IS NOT NULL THEN 'ever-redeemed member' ELSE 'never-redeemed member' END AS customer_group FROM eligible_users eu LEFT JOIN loyalty_member lm ON lm.user_id = eu.user_id AND lm.tenant_id = eu.tenant_id LEFT JOIN loyalty_redeemed lr ON lr.user_id = eu.user_id AND lr.tenant_id = eu.tenant_id ), order_refund_flag AS ( SELECT o.id AS order_id, CASE WHEN EXISTS (SELECT 1 FROM payments p WHERE p.order_id = o.id AND p.amount_refunded > 0) OR EXISTS (SELECT 1 FROM order_returns r WHERE r.order_id = o.id) THEN 1 ELSE 0 END AS is_refunded FROM orders o ), order_eur AS ( SELECT o.id AS order_id, o.user_id, o.tenant_id, o.status, CASE WHEN o.status <> 'cancelled' THEN o.grand_total / er.rate ELSE NULL END AS revenue_eur 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(COALESCE(o.placed_at, o.created_at) AS date) ), customer_orders AS ( SELECT eu.user_id, eu.tenant_id, COUNT(o.id) AS lifetime_orders, SUM(CASE WHEN o.status <> 'cancelled' THEN oe.revenue_eur ELSE 0 END) AS lifetime_revenue_eur, SUM(CASE WHEN o.status <> 'cancelled' THEN 1 ELSE 0 END) AS non_cancelled_order_count, SUM(orf.is_refunded) AS refunded_order_count FROM eligible_users eu JOIN orders o ON o.user_id = eu.user_id AND o.tenant_id = eu.tenant_id JOIN order_eur oe ON oe.order_id = o.id JOIN order_refund_flag orf ON orf.order_id = o.id GROUP BY eu.user_id, eu.tenant_id ), final AS ( SELECT t.tenure_band, m.customer_group, COUNT(*) AS customer_count, AVG(co.lifetime_orders) AS avg_lifetime_orders, AVG(co.lifetime_revenue_eur) AS avg_lifetime_revenue_eur, SUM(co.lifetime_revenue_eur) / NULLIF(SUM(co.non_cancelled_order_count), 0) AS avg_order_value_eur, 100.0 * SUM(co.refunded_order_count) / NULLIF(SUM(co.lifetime_orders), 0) AS refund_rate_pct FROM tenure t JOIN membership m ON m.user_id = t.user_id AND m.tenant_id = t.tenant_id JOIN customer_orders co ON co.user_id = t.user_id AND co.tenant_id = t.tenant_id GROUP BY t.tenure_band, m.customer_group ) SELECT tenure_band, customer_group AS "group", customer_count, ROUND(avg_lifetime_orders, 2) AS avg_lifetime_orders, ROUND(avg_lifetime_revenue_eur, 2) AS avg_lifetime_revenue_eur, ROUND(avg_order_value_eur, 2) AS avg_order_value_eur, ROUND(refund_rate_pct, 2) AS refund_rate_pct FROM final ORDER BY CASE tenure_band WHEN 'under 6 months' THEN 1 WHEN '6 to 12 months' THEN 2 WHEN '12 to 24 months' THEN 3 WHEN 'over 24 months' THEN 4 END, CASE customer_group WHEN 'non-member' THEN 1 WHEN 'never-redeemed member' THEN 2 WHEN 'ever-redeemed member' THEN 3 END
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: tenure_band, group, customer_count, avg_lifetime_orders, avg_lifetime_revenue_eur, avg_order_value_eur, refund_rate_pct
submitWITH eligible_users AS ( SELECT DISTINCT u.id AS user_id, u.tenant_id, u.created_at FROM users u JOIN orders o ON o.user_id = u.id AND o.tenant_id = u.tenant_id WHERE u.is_staff = false AND o.placed_at >= TIMESTAMP '2023-01-01 00:00:00' AND o.placed_at < TIMESTAMP '2026-01-01 00:00:00' ), tenure AS ( SELECT user_id, tenant_id, CASE WHEN created_at > TIMESTAMP '2026-01-01 00:00:00' - INTERVAL '6 months' THEN 'under 6 months' WHEN created_at > TIMESTAMP '2026-01-01 00:00:00' - INTERVAL '12 months' THEN '6 to 12 months' WHEN created_at > TIMESTAMP '2026-01-01 00:00:00' - INTERVAL '24 months' THEN '12 to 24 months' ELSE 'over 24 months' END AS tenure_band FROM eligible_users ), loyalty_member AS ( SELECT DISTINCT user_id, tenant_id FROM loyalty_accounts ), loyalty_redeemed AS ( SELECT DISTINCT user_id, tenant_id FROM loyalty_transactions WHERE direction = 'burn' ), membership AS ( SELECT eu.user_id, eu.tenant_id, CASE WHEN lm.user_id IS NULL THEN 'non-member' WHEN lr.user_id IS NOT NULL THEN 'ever-redeemed member' ELSE 'never-redeemed member' END AS customer_group FROM eligible_users eu LEFT JOIN loyalty_member lm ON lm.user_id = eu.user_id AND lm.tenant_id = eu.tenant_id LEFT JOIN loyalty_redeemed lr ON lr.user_id = eu.user_id AND lr.tenant_id = eu.tenant_id ), order_refund_flag AS ( SELECT o.id AS order_id, CASE WHEN EXISTS (SELECT 1 FROM payments p WHERE p.order_id = o.id AND p.amount_refunded > 0) OR EXISTS (SELECT 1 FROM order_returns r WHERE r.order_id = o.id) THEN 1 ELSE 0 END AS is_refunded FROM orders o ), order_eur AS ( SELECT o.id AS order_id, o.user_id, o.tenant_id, o.status, CASE WHEN o.status <> 'cancelled' THEN o.grand_total / er.rate ELSE NULL END AS revenue_eur 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(COALESCE(o.placed_at, o.created_at) AS date) ), customer_orders AS ( SELECT eu.user_id, eu.tenant_id, COUNT(o.id) AS lifetime_orders, SUM(CASE WHEN o.status <> 'cancelled' THEN oe.revenue_eur ELSE 0 END) AS lifetime_revenue_eur, SUM(CASE WHEN o.status <> 'cancelled' THEN 1 ELSE 0 END) AS non_cancelled_order_count, SUM(orf.is_refunded) AS refunded_order_count FROM eligible_users eu JOIN orders o ON o.user_id = eu.user_id AND o.tenant_id = eu.tenant_id JOIN order_eur oe ON oe.order_id = o.id JOIN order_refund_flag orf ON orf.order_id = o.id GROUP BY eu.user_id, eu.tenant_id ), final AS ( SELECT t.tenure_band, m.customer_group, COUNT(*) AS customer_count, AVG(co.lifetime_orders) AS avg_lifetime_orders, AVG(co.lifetime_revenue_eur) AS avg_lifetime_revenue_eur, SUM(co.lifetime_revenue_eur) / NULLIF(SUM(co.non_cancelled_order_count), 0) AS avg_order_value_eur, 100.0 * SUM(co.refunded_order_count) / NULLIF(SUM(co.lifetime_orders), 0) AS refund_rate_pct FROM tenure t JOIN membership m ON m.user_id = t.user_id AND m.tenant_id = t.tenant_id JOIN customer_orders co ON co.user_id = t.user_id AND co.tenant_id = t.tenant_id GROUP BY t.tenure_band, m.customer_group ) SELECT tenure_band, customer_group AS "group", customer_count, ROUND(avg_lifetime_orders, 2) AS avg_lifetime_orders, ROUND(avg_lifetime_revenue_eur, 2) AS avg_lifetime_revenue_eur, ROUND(avg_order_value_eur, 2) AS avg_order_value_eur, ROUND(refund_rate_pct, 2) AS refund_rate_pct FROM final ORDER BY CASE tenure_band WHEN 'under 6 months' THEN 1 WHEN '6 to 12 months' THEN 2 WHEN '12 to 24 months' THEN 3 WHEN 'over 24 months' THEN 4 END, CASE customer_group WHEN 'non-member' THEN 1 WHEN 'never-redeemed member' THEN 2 WHEN 'ever-redeemed member' THEN 3 END
Submitted to tonight's batch. Your work on this request is done.