Queries · q171 · Head of Lifecycle Marketing · week 1, night 4

Loyalty programme unit economics: points cost vs revenue per customer

“Putting cost and revenue side by side: how does the points value we issue and redeem per loyalty member compare to the revenue those members actually generate, versus non-members?”

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

1. A business event lands on the Head of Lifecycle Marketing's desk

   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?

   One of the 35 events we wrote for the role play; it shaped the manager's first note. How events arise · All the events

2. Aisha Rahman writes to Ingrid Solberg

   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:

   1. Do loyalty members actually buy differently? Compared with similar customers who aren't enrolled, do members order more often, spend more, or stick around longer? I want to know if membership correlates with genuinely better customer behavior, not just that our best customers happen to join.
   2. Do referred customers turn into good customers? When someone comes in through a referral, how do they behave in their first months compared to customers acquired other ways — do they buy, do they come back, do they stay?
   3. What is the programme actually costing us? I want a sense of the points/rewards and referral payouts we're giving out, ideally relative to the revenue those same members or referred customers generate, so we can see if the economics make sense.
   4. Is participation worth the overhead? How many of our customers are actually enrolled or have used a referral code, and is that share growing or flat? If take-up is small, the upside is capped no matter how good the behavior looks.
   5. Any red flags on how the programme is used? For example, signs that rewards are being redeemed without real repeat purchase behavior behind them, or that referral activity is concentrated in a way that looks more like gaming than genuine word-of-mouth.

   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

   Show the whole note
   A note from the Chief Financial Officer, Tobias Lindqvist

   Subject: Finance angle on the loyalty/referral review

   Hi Ingrid,

   A few things I'd like us to cover as you scope this:

   1. What is the programme actually costing us net — points and referral rewards issued versus redeemed/liability outstanding — and how does that compare to the margin uplift (if any) from members? I don't want to judge this on engagement alone.
   2. Do members and referred customers carry a different refund/return profile than regular customers? If the programme is quietly skewing our refund rate, that matters for how we read "incremental" revenue.
   3. For the B2B side especially, are enrolled accounts paying on better terms — faster settlement, fewer overdue balances — or is that unrelated to the programme?

   Happy to look at a first cut together before it goes wider.

   Tobias

3. Ingrid Solberg plans the night

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

   Two of last night's reports came back collapsed to a single row - which is itself informative, not just noise. Report 1 found zero member orders with a matched points-redemption transaction, so the 'reward-driven returns' hypothesis couldn't be tested via order_id linkage (either redemptions don't record order_id, or genuinely none exist in this join); I'm treating that specific mechanism as unconfirmed rather than ruled out, and won't push further on it given limited slots. Report 3 found literally zero loyalty-enrolled customers among B2B wholesaler tenants - before reporting 'the programme isn't offered/adopted in B2B' to Tobias as fact, I want to confirm it's a real pattern (the program simply isn't live there) rather than an artifact of the engineer's fuzzy industry-name matching. Report 2 confirms loyalty penetration plateaued at roughly 15 to 16 percent of customers for two years and referral penetration is still under 1 percent after three years - participation is small and flat, capping any upside regardless of behavior quality. For this final night I'm spending slots on: (1) a clean vertical-level view of where loyalty/referral even exist and are adopted - this resolves the B2B ambiguity and shows whether participation is concentrated in a few verticals; (2) checking whether the member refund-rate penalty is a generalizable pattern or an artifact of one or two verticals' return-prone mix, since that materially changes how we would advise fixing it; (3) a single consolidated unit-economics table (points cost versus revenue, per customer, member versus non-member) to give Tobias and Aisha the bottom-line ROI number the memo needs. This closes out the must-haves (1, 2, 3) with a robustness check and gives a clean answer on 3, 4 and 5 enough to write a defensible Friday memo.

   Read the whole plan
   Loyalty programme unit economics: points cost vs revenue per customer

   Question

   Putting cost and revenue side by side: how does the points value we issue and redeem per loyalty member compare to the revenue those members actually generate, versus non-members?

   One row per

   segment: 'loyalty member' vs 'non-member'

   Columns

   • segment
   • customer_count
   • total_revenue_eur
   • revenue_per_customer_eur
   • total_points_redeemed_cost_eur
   • redeemed_cost_per_customer_eur
   • total_points_earned_liability_eur
   • earned_liability_per_customer_eur
   • redeemed_cost_pct_of_revenue

4. Viktor writes the SQL

   The engineer · 1 dry run on the empty schema, then the submission

   Dry run 1 · STATUS: COMPILES (dry run on the empty schema, no data read)

   WITH population AS (
     SELECT DISTINCT u.id AS user_id, u.tenant_id
     FROM users u
     JOIN orders o
       ON o.tenant_id = u.tenant_id
      AND o.user_id = u.id
     WHERE u.is_staff = false
       AND o.placed_at >= DATE '2023-01-01'
       AND o.placed_at <  DATE '2026-01-01'
   ),
   segment_membership AS (
     SELECT
       p.user_id,
       p.tenant_id,
       CASE WHEN la.user_id IS NOT NULL THEN 'loyalty member' ELSE 'non-member' END AS segment
     FROM population p
     LEFT JOIN (SELECT DISTINCT tenant_id, user_id FROM loyalty_accounts) la
       ON la.tenant_id = p.tenant_id AND la.user_id = p.user_id
   ),
   revenue_detail AS (
     SELECT
       sm.segment,
       o.grand_total / COALESCE(er.rate, CASE WHEN o.currency_code = 'EUR' THEN 1 END) AS revenue_eur
     FROM segment_membership sm
     JOIN orders o
       ON o.tenant_id = sm.tenant_id AND o.user_id = sm.user_id
     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.status <> 'cancelled'
   ),
   loyalty_tx_detail AS (
     SELECT
       sm.segment,
       lt.direction,
       ABS(lt.points) / lp.points_per_currency
         / COALESCE(er.rate, CASE WHEN lp.currency_code = 'EUR' THEN 1 END) AS points_value_eur
     FROM segment_membership sm
     JOIN loyalty_accounts la
       ON la.tenant_id = sm.tenant_id AND la.user_id = sm.user_id
     JOIN loyalty_transactions lt
       ON lt.tenant_id = la.tenant_id AND lt.loyalty_account_id = la.id
     JOIN loyalty_programs lp
       ON lp.tenant_id = la.tenant_id AND lp.id = la.loyalty_program_id
     LEFT JOIN exchange_rates er
       ON er.base_code = 'EUR'
      AND er.quote_code = lp.currency_code
      AND er.as_of = CAST(lt.occurred_at AS date)
   ),
   customer_counts AS (
     SELECT segment, COUNT(*) AS customer_count
     FROM segment_membership
     GROUP BY segment
   ),
   revenue_totals AS (
     SELECT segment, SUM(revenue_eur) AS total_revenue_eur
     FROM revenue_detail
     GROUP BY segment
   ),
   redeemed_totals AS (
     SELECT segment, SUM(points_value_eur) AS total_redeemed_eur
     FROM loyalty_tx_detail
     WHERE direction <> 'earn'
     GROUP BY segment
   ),
   earned_totals AS (
     SELECT segment, SUM(points_value_eur) AS total_earned_eur
     FROM loyalty_tx_detail
     WHERE direction = 'earn'
     GROUP BY segment
   )
   SELECT
     cc.segment,
     cc.customer_count,
     ROUND(COALESCE(rev.total_revenue_eur, 0), 2) AS total_revenue_eur,
     ROUND(COALESCE(rev.total_revenue_eur, 0) / cc.customer_count, 2) AS revenue_per_customer_eur,
     ROUND(COALESCE(red.total_redeemed_eur, 0), 2) AS total_points_redeemed_cost_eur,
     ROUND(COALESCE(red.total_redeemed_eur, 0) / cc.customer_count, 2) AS redeemed_cost_per_customer_eur,
     ROUND(COALESCE(earn.total_earned_eur, 0), 2) AS total_points_earned_liability_eur,
     ROUND(COALESCE(earn.total_earned_eur, 0) / cc.customer_count, 2) AS earned_liability_per_customer_eur,
     ROUND(100.0 * COALESCE(red.total_redeemed_eur, 0) / NULLIF(COALESCE(rev.total_revenue_eur, 0), 0), 2) AS redeemed_cost_pct_of_revenue
   FROM customer_counts cc
   LEFT JOIN revenue_totals rev ON rev.segment = cc.segment
   LEFT JOIN redeemed_totals red ON red.segment = cc.segment
   LEFT JOIN earned_totals earn ON earn.segment = cc.segment
   ORDER BY CASE WHEN cc.segment = 'loyalty member' THEN 0 ELSE 1 END, cc.segment

   “The query compiles and produces exactly the 9 requested columns at the segment grain.”

   Submitted · the SQL of the last dry run, unchanged · Submitted to tonight's batch. Your work on this request is done.

   “Submitted. The report gives one row each for "loyalty member" and "non-member" with customer counts, EUR-normalized revenue, redeemed points cost, earned points liability, and the per-customer and percentage-of-revenue breakdowns as specified.”

5. The report comes back overnight

   As Ingrid Solberg saw it, from the small dataset the agents worked on

   segment        | customer_count | total_revenue_eur | revenue_per_customer_eur | total_points_redeemed_cost_eur | redeemed_cost_per_customer_eur | total_points_earned_liability_eur | earned_liability_per_customer_eur | redeemed_cost_pct_of_revenue
   ---------------+----------------+-------------------+--------------------------+--------------------------------+--------------------------------+-----------------------------------+-----------------------------------+-----------------------------
   loyalty member |           1330 |         915030.99 |                   687.99 |                      177782.98 |                         133.67 |                          669001.4 |                            503.01 |                        19.43
   non-member     |           8310 |        12563425.3 |                  1511.84 |                            0.0 |                            0.0 |                               0.0 |                               0.0 |                          0.0
   (2 rows)

6. What came of it

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

   Later queries that build on this report:

   • q371Loyalty points redemption rate by enrollment cohort age
   • q375What distinguishes higher-revenue loyalty members from the rest
   • q377Loyalty unit economics: ever-redeemed vs never-redeemed members

   It builds on:

   • q160Loyalty points and referral reward cost by month
   • q161Loyalty member vs non-member customer behavior

7. It becomes benchmark query q171

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

   • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
   • numericsdocs/NUMERICS.md: x / exchange rate cast to NUMERIC(18,6) (2×)
   • made explicita selected t.col gets AS col, its standard name made explicit (8×)
   • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (2×)
   The benchmark text, against the submitted SQL

   WITH population AS (  SELECT DISTINCT u.id AS user_id, u.tenant_id  SELECT DISTINCT u.id AS user_id, u.tenant_id AS tenant_id  FROM users u  JOIN orders o    ON o.tenant_id = u.tenant_id   AND o.user_id = u.id  WHERE u.is_staff = false    AND o.placed_at >= DATE '2023-01-01'    AND o.placed_at <  DATE '2026-01-01'),segment_membership AS (  SELECT    p.user_id,    p.tenant_id,    p.user_id AS user_id,    p.tenant_id AS tenant_id,    CASE WHEN la.user_id IS NOT NULL THEN 'loyalty member' ELSE 'non-member' END AS segment  FROM population p  LEFT JOIN (SELECT DISTINCT tenant_id, user_id FROM loyalty_accounts) la    ON la.tenant_id = p.tenant_id AND la.user_id = p.user_id),revenue_detail AS (  SELECT    sm.segment,    o.grand_total / COALESCE(er.rate, CASE WHEN o.currency_code = 'EUR' THEN 1 END) AS revenue_eur    sm.segment AS segment,    CAST(o.grand_total / COALESCE(er.rate, CASE WHEN o.currency_code = 'EUR' THEN 1 END) AS NUMERIC(18,6)) AS revenue_eur  FROM segment_membership sm  JOIN orders o    ON o.tenant_id = sm.tenant_id AND o.user_id = sm.user_id  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.status <> 'cancelled'),loyalty_tx_detail AS (  SELECT    sm.segment,    lt.direction,    ABS(lt.points) / lp.points_per_currency      / COALESCE(er.rate, CASE WHEN lp.currency_code = 'EUR' THEN 1 END) AS points_value_eur    sm.segment AS segment,    lt.direction AS direction,    CAST(ABS(lt.points) / lp.points_per_currency      / COALESCE(er.rate, CASE WHEN lp.currency_code = 'EUR' THEN 1 END) AS NUMERIC(18,6)) AS points_value_eur  FROM segment_membership sm  JOIN loyalty_accounts la    ON la.tenant_id = sm.tenant_id AND la.user_id = sm.user_id  JOIN loyalty_transactions lt    ON lt.tenant_id = la.tenant_id AND lt.loyalty_account_id = la.id  JOIN loyalty_programs lp    ON lp.tenant_id = la.tenant_id AND lp.id = la.loyalty_program_id  LEFT JOIN exchange_rates er    ON er.base_code = 'EUR'   AND er.quote_code = lp.currency_code   AND er.as_of = CAST(lt.occurred_at AS date)),customer_counts AS (  SELECT segment, COUNT(*) AS customer_count  FROM segment_membership  GROUP BY segment),revenue_totals AS (  SELECT segment, SUM(revenue_eur) AS total_revenue_eur  FROM revenue_detail  GROUP BY segment),redeemed_totals AS (  SELECT segment, SUM(points_value_eur) AS total_redeemed_eur  FROM loyalty_tx_detail  WHERE direction <> 'earn'  GROUP BY segment),earned_totals AS (  SELECT segment, SUM(points_value_eur) AS total_earned_eur  FROM loyalty_tx_detail  WHERE direction = 'earn'  GROUP BY segment)SELECT  cc.segment,  cc.customer_count,  ROUND(COALESCE(rev.total_revenue_eur, 0), 2) AS total_revenue_eur,  ROUND(COALESCE(rev.total_revenue_eur, 0) / cc.customer_count, 2) AS revenue_per_customer_eur,  ROUND(COALESCE(red.total_redeemed_eur, 0), 2) AS total_points_redeemed_cost_eur,  ROUND(COALESCE(red.total_redeemed_eur, 0) / cc.customer_count, 2) AS redeemed_cost_per_customer_eur,  ROUND(COALESCE(earn.total_earned_eur, 0), 2) AS total_points_earned_liability_eur,  ROUND(COALESCE(earn.total_earned_eur, 0) / cc.customer_count, 2) AS earned_liability_per_customer_eur,  ROUND(100.0 * COALESCE(red.total_redeemed_eur, 0) / NULLIF(COALESCE(rev.total_revenue_eur, 0), 0), 2) AS redeemed_cost_pct_of_revenue  cc.segment AS segment,  cc.customer_count AS customer_count,  COALESCE(rev.total_revenue_eur, 0) AS total_revenue_eur,  COALESCE(rev.total_revenue_eur, 0) / cc.customer_count AS revenue_per_customer_eur,  COALESCE(red.total_redeemed_eur, 0) AS total_points_redeemed_cost_eur,  COALESCE(red.total_redeemed_eur, 0) / cc.customer_count AS redeemed_cost_per_customer_eur,  COALESCE(earn.total_earned_eur, 0) AS total_points_earned_liability_eur,  COALESCE(earn.total_earned_eur, 0) / cc.customer_count AS earned_liability_per_customer_eur,  100.0 * COALESCE(red.total_redeemed_eur, 0) / NULLIF(COALESCE(rev.total_revenue_eur, 0), 0) AS redeemed_cost_pct_of_revenueFROM customer_counts ccLEFT JOIN revenue_totals rev ON rev.segment = cc.segmentLEFT JOIN redeemed_totals red ON red.segment = cc.segmentLEFT JOIN earned_totals earn ON earn.segment = cc.segmentORDER BY CASE WHEN cc.segment = 'loyalty member' THEN 0 ELSE 1 END, cc.segmentORDER BY CASE WHEN cc.segment = 'loyalty member' THEN 0 ELSE 1 END NULLS LAST, cc.segment NULLS LAST

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

   DuckDB · Hyper · GizmoSQL · CedarDB · pg_duckdb · PostgreSQL · DataFusion · Firebolt · ClickHouse · chDB · clickhouse_brew

   WITH population AS (
     SELECT DISTINCT u.id AS user_id, u.tenant_id AS tenant_id
     FROM users u
     JOIN orders o
       ON o.tenant_id = u.tenant_id
      AND o.user_id = u.id
     WHERE u.is_staff = false
       AND o.placed_at >= DATE '2023-01-01'
       AND o.placed_at <  DATE '2026-01-01'
   ),
   segment_membership AS (
     SELECT
       p.user_id AS user_id,
       p.tenant_id AS tenant_id,
       CASE WHEN la.user_id IS NOT NULL THEN 'loyalty member' ELSE 'non-member' END AS segment
     FROM population p
     LEFT JOIN (SELECT DISTINCT tenant_id, user_id FROM loyalty_accounts) la
       ON la.tenant_id = p.tenant_id AND la.user_id = p.user_id
   ),
   revenue_detail AS (
     SELECT
       sm.segment AS segment,
       CAST(o.grand_total / COALESCE(er.rate, CASE WHEN o.currency_code = 'EUR' THEN 1 END) AS NUMERIC(18,6)) AS revenue_eur
     FROM segment_membership sm
     JOIN orders o
       ON o.tenant_id = sm.tenant_id AND o.user_id = sm.user_id
     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.status <> 'cancelled'
   ),
   loyalty_tx_detail AS (
     SELECT
       sm.segment AS segment,
       lt.direction AS direction,
       CAST(ABS(lt.points) / lp.points_per_currency
         / COALESCE(er.rate, CASE WHEN lp.currency_code = 'EUR' THEN 1 END) AS NUMERIC(18,6)) AS points_value_eur
     FROM segment_membership sm
     JOIN loyalty_accounts la
       ON la.tenant_id = sm.tenant_id AND la.user_id = sm.user_id
     JOIN loyalty_transactions lt
       ON lt.tenant_id = la.tenant_id AND lt.loyalty_account_id = la.id
     JOIN loyalty_programs lp
       ON lp.tenant_id = la.tenant_id AND lp.id = la.loyalty_program_id
     LEFT JOIN exchange_rates er
       ON er.base_code = 'EUR'
      AND er.quote_code = lp.currency_code
      AND er.as_of = CAST(lt.occurred_at AS date)
   ),
   customer_counts AS (
     SELECT segment, COUNT(*) AS customer_count
     FROM segment_membership
     GROUP BY segment
   ),
   revenue_totals AS (
     SELECT segment, SUM(revenue_eur) AS total_revenue_eur
     FROM revenue_detail
     GROUP BY segment
   ),
   redeemed_totals AS (
     SELECT segment, SUM(points_value_eur) AS total_redeemed_eur
     FROM loyalty_tx_detail
     WHERE direction <> 'earn'
     GROUP BY segment
   ),
   earned_totals AS (
     SELECT segment, SUM(points_value_eur) AS total_earned_eur
     FROM loyalty_tx_detail
     WHERE direction = 'earn'
     GROUP BY segment
   )
   SELECT
     cc.segment AS segment,
     cc.customer_count AS customer_count,
     COALESCE(rev.total_revenue_eur, 0) AS total_revenue_eur,
     COALESCE(rev.total_revenue_eur, 0) / cc.customer_count AS revenue_per_customer_eur,
     COALESCE(red.total_redeemed_eur, 0) AS total_points_redeemed_cost_eur,
     COALESCE(red.total_redeemed_eur, 0) / cc.customer_count AS redeemed_cost_per_customer_eur,
     COALESCE(earn.total_earned_eur, 0) AS total_points_earned_liability_eur,
     COALESCE(earn.total_earned_eur, 0) / cc.customer_count AS earned_liability_per_customer_eur,
     100.0 * COALESCE(red.total_redeemed_eur, 0) / NULLIF(COALESCE(rev.total_revenue_eur, 0), 0) AS redeemed_cost_pct_of_revenue
   FROM customer_counts cc
   LEFT JOIN revenue_totals rev ON rev.segment = cc.segment
   LEFT JOIN redeemed_totals red ON red.segment = cc.segment
   LEFT JOIN earned_totals earn ON earn.segment = cc.segment
   ORDER BY CASE WHEN cc.segment = 'loyalty member' THEN 0 ELSE 1 END NULLS LAST, cc.segment NULLS LAST

   • Firebolt{ "errors": [ { "description": "Line 23, Column 24: Operations between decimals with different precision and scale is not supported at the moment. Explicitly cast one of the decimals to other decimal precision and scale." } ], "query": { "query_id": "03dc1a62-fb8c-404e-a5c3-3e6169ae821e", "query_lab
   • ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table16.grand_total Decimal(14, 4) Decimal64(size = 0), coalesce(__table17.rate, if(equals(__table16.currency_code, 'EUR'_String), 1_UInt8, _CAST(NULL_Nullable(Nothing), 'Nulla

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

8. Everything, as recorded

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

   Viktor's whole session, 7 turns

   • The request, as Viktor received it

     Report request from Ingrid Solberg (analyst for the Head of Lifecycle Marketing):
     
     Title: Loyalty programme unit economics: points cost vs revenue per customer
     Question: Putting cost and revenue side by side: how does the points value we issue and redeem per loyalty member compare to the revenue those members actually generate, versus non-members?
     Grain: one row per segment: 'loyalty member' vs 'non-member'
     Columns:
     - segment
     - customer_count
     - total_revenue_eur
     - revenue_per_customer_eur
     - total_points_redeemed_cost_eur
     - redeemed_cost_per_customer_eur
     - total_points_earned_liability_eur
     - earned_liability_per_customer_eur
     - redeemed_cost_pct_of_revenue
     Details: Population: same as the original 'Loyalty member vs non-member customer behavior' report (users, is_staff = false, at least one order 2023-01-01 to 2025-12-31, segment by presence in loyalty_accounts). total_revenue_eur = sum of grand_total for non-cancelled orders, converted to EUR via exchange_rates (order currency_code to EUR, rate as_of placed_at); revenue_per_customer_eur = total_revenue_eur divided by customer_count. total_points_redeemed_cost_eur = for loyalty members only, sum of loyalty_transactions with a non-'earn' direction, converting points to currency using that transaction's loyalty account's program points_per_currency and currency_code, then to EUR via exchange_rates as_of occurred_at (same method as the 'Loyalty points and referral reward cost by month' report); this is 0 for non-members. total_points_earned_liability_eur = same conversion but for 'earn'-direction loyalty_transactions (total value ever issued); 0 for non-members. redeemed_cost_per_customer_eur and earned_liability_per_customer_eur divide those totals by customer_count. redeemed_cost_pct_of_revenue = total_points_redeemed_cost_eur divided by total_revenue_eur, as a percentage (0 for non-members). Order rows: 'loyalty member' first, then 'non-member'.
     
     Check your query with the warehouse's dry_run tool, then hand it in with submit.

   • Viktor calls dry_run

     WITH population AS (
       SELECT DISTINCT u.id AS user_id, u.tenant_id
       FROM users u
       JOIN orders o
         ON o.tenant_id = u.tenant_id
        AND o.user_id = u.id
       WHERE u.is_staff = false
         AND o.placed_at >= DATE '2023-01-01'
         AND o.placed_at <  DATE '2026-01-01'
     ),
     segment_membership AS (
       SELECT
         p.user_id,
         p.tenant_id,
         CASE WHEN la.user_id IS NOT NULL THEN 'loyalty member' ELSE 'non-member' END AS segment
       FROM population p
       LEFT JOIN (SELECT DISTINCT tenant_id, user_id FROM loyalty_accounts) la
         ON la.tenant_id = p.tenant_id AND la.user_id = p.user_id
     ),
     revenue_detail AS (
       SELECT
         sm.segment,
         o.grand_total / COALESCE(er.rate, CASE WHEN o.currency_code = 'EUR' THEN 1 END) AS revenue_eur
       FROM segment_membership sm
       JOIN orders o
         ON o.tenant_id = sm.tenant_id AND o.user_id = sm.user_id
       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.status <> 'cancelled'
     ),
     loyalty_tx_detail AS (
       SELECT
         sm.segment,
         lt.direction,
         ABS(lt.points) / lp.points_per_currency
           / COALESCE(er.rate, CASE WHEN lp.currency_code = 'EUR' THEN 1 END) AS points_value_eur
       FROM segment_membership sm
       JOIN loyalty_accounts la
         ON la.tenant_id = sm.tenant_id AND la.user_id = sm.user_id
       JOIN loyalty_transactions lt
         ON lt.tenant_id = la.tenant_id AND lt.loyalty_account_id = la.id
       JOIN loyalty_programs lp
         ON lp.tenant_id = la.tenant_id AND lp.id = la.loyalty_program_id
       LEFT JOIN exchange_rates er
         ON er.base_code = 'EUR'
        AND er.quote_code = lp.currency_code
        AND er.as_of = CAST(lt.occurred_at AS date)
     ),
     customer_counts AS (
       SELECT segment, COUNT(*) AS customer_count
       FROM segment_membership
       GROUP BY segment
     ),
     revenue_totals AS (
       SELECT segment, SUM(revenue_eur) AS total_revenue_eur
       FROM revenue_detail
       GROUP BY segment
     ),
     redeemed_totals AS (
       SELECT segment, SUM(points_value_eur) AS total_redeemed_eur
       FROM loyalty_tx_detail
       WHERE direction <> 'earn'
       GROUP BY segment
     ),
     earned_totals AS (
       SELECT segment, SUM(points_value_eur) AS total_earned_eur
       FROM loyalty_tx_detail
       WHERE direction = 'earn'
       GROUP BY segment
     )
     SELECT
       cc.segment,
       cc.customer_count,
       ROUND(COALESCE(rev.total_revenue_eur, 0), 2) AS total_revenue_eur,
       ROUND(COALESCE(rev.total_revenue_eur, 0) / cc.customer_count, 2) AS revenue_per_customer_eur,
       ROUND(COALESCE(red.total_redeemed_eur, 0), 2) AS total_points_redeemed_cost_eur,
       ROUND(COALESCE(red.total_redeemed_eur, 0) / cc.customer_count, 2) AS redeemed_cost_per_customer_eur,
       ROUND(COALESCE(earn.total_earned_eur, 0), 2) AS total_points_earned_liability_eur,
       ROUND(COALESCE(earn.total_earned_eur, 0) / cc.customer_count, 2) AS earned_liability_per_customer_eur,
       ROUND(100.0 * COALESCE(red.total_redeemed_eur, 0) / NULLIF(COALESCE(rev.total_revenue_eur, 0), 0), 2) AS redeemed_cost_pct_of_revenue
     FROM customer_counts cc
     LEFT JOIN revenue_totals rev ON rev.segment = cc.segment
     LEFT JOIN redeemed_totals red ON red.segment = cc.segment
     LEFT JOIN earned_totals earn ON earn.segment = cc.segment
     ORDER BY CASE WHEN cc.segment = 'loyalty member' THEN 0 ELSE 1 END, cc.segment

   • The warehouse answers

     STATUS: COMPILES (dry run on the empty schema, no data read)
     COLUMNS: segment, customer_count, total_revenue_eur, revenue_per_customer_eur, total_points_redeemed_cost_eur, redeemed_cost_per_customer_eur, total_points_earned_liability_eur, earned_liability_per_customer_eur, redeemed_cost_pct_of_revenue

   • ViktorThe query compiles and produces exactly the 9 requested columns at the segment grain.
   • Viktor calls submit

     WITH population AS (
       SELECT DISTINCT u.id AS user_id, u.tenant_id
       FROM users u
       JOIN orders o
         ON o.tenant_id = u.tenant_id
        AND o.user_id = u.id
       WHERE u.is_staff = false
         AND o.placed_at >= DATE '2023-01-01'
         AND o.placed_at <  DATE '2026-01-01'
     ),
     segment_membership AS (
       SELECT
         p.user_id,
         p.tenant_id,
         CASE WHEN la.user_id IS NOT NULL THEN 'loyalty member' ELSE 'non-member' END AS segment
       FROM population p
       LEFT JOIN (SELECT DISTINCT tenant_id, user_id FROM loyalty_accounts) la
         ON la.tenant_id = p.tenant_id AND la.user_id = p.user_id
     ),
     revenue_detail AS (
       SELECT
         sm.segment,
         o.grand_total / COALESCE(er.rate, CASE WHEN o.currency_code = 'EUR' THEN 1 END) AS revenue_eur
       FROM segment_membership sm
       JOIN orders o
         ON o.tenant_id = sm.tenant_id AND o.user_id = sm.user_id
       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.status <> 'cancelled'
     ),
     loyalty_tx_detail AS (
       SELECT
         sm.segment,
         lt.direction,
         ABS(lt.points) / lp.points_per_currency
           / COALESCE(er.rate, CASE WHEN lp.currency_code = 'EUR' THEN 1 END) AS points_value_eur
       FROM segment_membership sm
       JOIN loyalty_accounts la
         ON la.tenant_id = sm.tenant_id AND la.user_id = sm.user_id
       JOIN loyalty_transactions lt
         ON lt.tenant_id = la.tenant_id AND lt.loyalty_account_id = la.id
       JOIN loyalty_programs lp
         ON lp.tenant_id = la.tenant_id AND lp.id = la.loyalty_program_id
       LEFT JOIN exchange_rates er
         ON er.base_code = 'EUR'
        AND er.quote_code = lp.currency_code
        AND er.as_of = CAST(lt.occurred_at AS date)
     ),
     customer_counts AS (
       SELECT segment, COUNT(*) AS customer_count
       FROM segment_membership
       GROUP BY segment
     ),
     revenue_totals AS (
       SELECT segment, SUM(revenue_eur) AS total_revenue_eur
       FROM revenue_detail
       GROUP BY segment
     ),
     redeemed_totals AS (
       SELECT segment, SUM(points_value_eur) AS total_redeemed_eur
       FROM loyalty_tx_detail
       WHERE direction <> 'earn'
       GROUP BY segment
     ),
     earned_totals AS (
       SELECT segment, SUM(points_value_eur) AS total_earned_eur
       FROM loyalty_tx_detail
       WHERE direction = 'earn'
       GROUP BY segment
     )
     SELECT
       cc.segment,
       cc.customer_count,
       ROUND(COALESCE(rev.total_revenue_eur, 0), 2) AS total_revenue_eur,
       ROUND(COALESCE(rev.total_revenue_eur, 0) / cc.customer_count, 2) AS revenue_per_customer_eur,
       ROUND(COALESCE(red.total_redeemed_eur, 0), 2) AS total_points_redeemed_cost_eur,
       ROUND(COALESCE(red.total_redeemed_eur, 0) / cc.customer_count, 2) AS redeemed_cost_per_customer_eur,
       ROUND(COALESCE(earn.total_earned_eur, 0), 2) AS total_points_earned_liability_eur,
       ROUND(COALESCE(earn.total_earned_eur, 0) / cc.customer_count, 2) AS earned_liability_per_customer_eur,
       ROUND(100.0 * COALESCE(red.total_redeemed_eur, 0) / NULLIF(COALESCE(rev.total_revenue_eur, 0), 0), 2) AS redeemed_cost_pct_of_revenue
     FROM customer_counts cc
     LEFT JOIN revenue_totals rev ON rev.segment = cc.segment
     LEFT JOIN redeemed_totals red ON red.segment = cc.segment
     LEFT JOIN earned_totals earn ON earn.segment = cc.segment
     ORDER BY CASE WHEN cc.segment = 'loyalty member' THEN 0 ELSE 1 END, cc.segment

   • The warehouse answers

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

   • ViktorSubmitted. The report gives one row each for "loyalty member" and "non-member" with customer counts, EUR-normalized revenue, redeemed points cost, earned points liability, and the per-customer and percentage-of-revenue breakdowns as specified.

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

   Viktor's transcript (286 KB) · Ingrid Solberg's transcript (906 KB) · Aisha Rahman's transcript (261 KB) · the night's plan (6 KB) · the night's reports (6 KB) · the week's memo (4 KB)