DB Olympics

Queries · q163 · Head of Lifecycle Marketing · week 1, night 2

Loyalty member vs non-member behavior by account tenure

“Does the loyalty-member gap in orders, revenue, AOV and refunds from last night's report persist once we control for how long the customer has had an account, or is it just that members and non-members differ in account age?”

Manager · asked the questionAisha RahmanHead of Lifecycle Marketing. Ran customer messaging at a subscription box company.
Analyst · planned the reportIngrid SolbergRisk-focused: looks for the tail, the outliers and the worst cases.
Engineer · wrote the SQLBeaWrites SQL the way the company's BI tool generates it: verbose fully qualified aliases (t0, t1, ...), a subquery per measure joined back on the grain keys, COALESCE everywhere.

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

    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 2, request 1 of 3

    Report 1 shows points liability growing almost monotonically (~491k EUR outstanding by Dec-2025) with redemption running only ~20-30% of issuance most months - a real, uncapped cost worth flagging, but referral payouts are tiny (~20-60 EUR/mo, only ~60-70 redemptions in 3 years total) - the referral program barely moves money. Report 3's referred-customer sample (n=60) is too small to trust on its own but is itself a finding: referral uptake is negligible. The big surprise is Report 2: loyalty members look WORSE than non-members on every volume/spend metric (fewer orders, roughly 45% less lifetime revenue, lower AOV, shorter tenure) and have more than DOUBLE the refund rate (32.4% vs 15.5%). That directly contradicts the 'our best customers join' hypothesis and raises a real red flag on program economics and on refund-rate skew (Tobias's #2). Before telling Aisha/Tobias 'membership is bad for us,' I need to rule out confounds: (a) members might simply be newer/shorter-tenure accounts overall skewing the averages, and (b) the refund gap might be a timing artifact (e.g., people enroll right after a return, or refund behavior is front-loaded before they ever really engage post-enrollment) rather than something enrollment causes. I'm also chasing Aisha's #5 red-flag ask on the referral side given how thin participation is - checking whether what little referral activity exists is concentrated among a few accounts (possible gaming) rather than broad-based. Deferring participation-rate-over-time (#4) and the B2B payment-terms question (Tobias #3) to night 3 once the core behavior story is clearer.

    Loyalty member vs non-member behavior by account tenure
    Question
    Does the loyalty-member gap in orders, revenue, AOV and refunds from last night's report persist once we control for how long the customer has had an account, or is it just that members and non-members differ in account age?
    One row per
    combination of account-tenure band and loyalty segment (member vs non-member)
    Columns
    • tenure_band
    • segment
    • customer_count
    • avg_lifetime_orders
    • avg_lifetime_revenue_eur
    • avg_order_value_eur
    • refund_rate_pct

  4. Bea 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 u.id AS user_id,
         CASE
           WHEN (DATE '2025-12-31' - CAST(u.created_at AS DATE)) < 182 THEN 'under 6 months'
           WHEN (DATE '2025-12-31' - CAST(u.created_at AS DATE)) < 365 THEN '6 to 12 months'
           WHEN (DATE '2025-12-31' - CAST(u.created_at AS DATE)) < 730 THEN '12 to 24 months'
           ELSE 'over 24 months'
         END AS tenure_band,
         CASE WHEN EXISTS (
                SELECT 1 FROM loyalty_accounts la WHERE la.user_id = u.id
              ) THEN 'loyalty member' ELSE 'non-member' END AS segment
  FROM users u
  WHERE u.is_staff = false
    AND EXISTS (
      SELECT 1 FROM orders o
      WHERE o.user_id = u.id
        AND COALESCE(o.placed_at, o.created_at) >= TIMESTAMP '2023-01-01'
        AND COALESCE(o.placed_at, o.created_at) < TIMESTAMP '2026-01-01'
    )
),
grain_keys AS (
  SELECT DISTINCT tenure_band, segment FROM population
),
t_customer_count AS (
  SELECT tenure_band, segment, COUNT(*) AS customer_count
  FROM population
  GROUP BY tenure_band, segment
),
t_lifetime_orders AS (
  SELECT p.tenure_band, p.segment, COUNT(o.id) AS total_orders
  FROM population p
  JOIN orders o ON o.user_id = p.user_id
  GROUP BY p.tenure_band, p.segment
),
t_refunded_orders AS (
  SELECT p.tenure_band, p.segment, COUNT(o.id) AS refunded_orders
  FROM population p
  JOIN orders o ON o.user_id = p.user_id
  WHERE EXISTS (SELECT 1 FROM payments pay WHERE pay.order_id = o.id AND pay.amount_refunded > 0)
     OR EXISTS (SELECT 1 FROM order_returns r WHERE r.order_id = o.id)
  GROUP BY p.tenure_band, p.segment
),
t_revenue AS (
  SELECT p.tenure_band, p.segment,
         SUM(CASE WHEN o.currency_code = 'EUR' THEN o.grand_total
                  ELSE o.grand_total / NULLIF(er.rate, 0) END) AS total_revenue_eur,
         COUNT(o.id) AS non_cancelled_orders
  FROM population p
  JOIN orders o ON o.user_id = p.user_id
  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)
  WHERE o.status <> 'cancelled'
  GROUP BY p.tenure_band, p.segment
)
SELECT
  gk.tenure_band,
  gk.segment,
  COALESCE(cc.customer_count, 0) AS customer_count,
  ROUND(COALESCE(lo.total_orders, 0)::numeric / NULLIF(cc.customer_count, 0), 2) AS avg_lifetime_orders,
  ROUND(COALESCE(rev.total_revenue_eur, 0) / NULLIF(cc.customer_count, 0), 2) AS avg_lifetime_revenue_eur,
  ROUND(COALESCE(rev.total_revenue_eur, 0) / NULLIF(rev.non_cancelled_orders, 0), 2) AS avg_order_value_eur,
  ROUND(100.0 * COALESCE(ro.refunded_orders, 0) / NULLIF(lo.total_orders, 0), 2) AS refund_rate_pct
FROM grain_keys gk
LEFT JOIN t_customer_count cc ON cc.tenure_band = gk.tenure_band AND cc.segment = gk.segment
LEFT JOIN t_lifetime_orders lo ON lo.tenure_band = gk.tenure_band AND lo.segment = gk.segment
LEFT JOIN t_refunded_orders ro ON ro.tenure_band = gk.tenure_band AND ro.segment = gk.segment
LEFT JOIN t_revenue rev ON rev.tenure_band = gk.tenure_band AND rev.segment = gk.segment
ORDER BY
  CASE gk.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 gk.segment WHEN 'loyalty member' THEN 1 ELSE 2 END

    “Query compiles and returns the expected columns. Submitting.”

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

    “Submitted. The report is grained on (tenure_band × segment) with 8 rows, 7 columns, ordered youngest-to-oldest tenure then loyalty member before non-member — matching the request and staying well within the width/length limits.”

  5. The report comes back overnight

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

    tenure_band     | segment        | customer_count | avg_lifetime_orders | avg_lifetime_revenue_eur | avg_order_value_eur | refund_rate_pct
----------------+----------------+----------------+---------------------+--------------------------+---------------------+----------------
under 6 months  | loyalty member |            264 |                1.79 |                   358.92 |              211.98 |           27.27
under 6 months  | non-member     |           1466 |                1.91 |                   491.69 |              268.06 |           20.31
6 to 12 months  | loyalty member |            214 |                2.38 |                   480.71 |              212.98 |           31.96
6 to 12 months  | non-member     |           1113 |                3.52 |                   883.43 |              259.64 |           16.99
12 to 24 months | loyalty member |            376 |                4.17 |                   748.19 |              189.57 |           31.63
12 to 24 months | non-member     |           2477 |                5.56 |                  1435.39 |              267.27 |           16.36
over 24 months  | loyalty member |            476 |                5.02 |                   916.14 |              190.18 |           33.93
over 24 months  | non-member     |           3254 |                7.87 |                  2244.59 |              294.03 |           14.37
(8 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:

    It builds on:

  7. It becomes benchmark query q163

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

    • spellingcast
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision; bare NUMERIC cast that only served the rounding removed; intended fraction: integer numerator cast to NUMERIC(18,6) before the division
    • numericsdocs/NUMERICS.md: x / exchange rate cast to NUMERIC(18,6)
    • 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 u.id AS user_id,         CASE           WHEN (DATE '2025-12-31' - CAST(u.created_at AS DATE)) < 182 THEN 'under 6 months'           WHEN (DATE '2025-12-31' - CAST(u.created_at AS DATE)) < 365 THEN '6 to 12 months'           WHEN (DATE '2025-12-31' - CAST(u.created_at AS DATE)) < 730 THEN '12 to 24 months'           ELSE 'over 24 months'         END AS tenure_band,         CASE WHEN EXISTS (                SELECT 1 FROM loyalty_accounts la WHERE la.user_id = u.id              ) THEN 'loyalty member' ELSE 'non-member' END AS segment  FROM users u  WHERE u.is_staff = false    AND EXISTS (      SELECT 1 FROM orders o      WHERE o.user_id = u.id        AND COALESCE(o.placed_at, o.created_at) >= TIMESTAMP '2023-01-01'        AND COALESCE(o.placed_at, o.created_at) < TIMESTAMP '2026-01-01'    )),grain_keys AS (  SELECT DISTINCT tenure_band, segment FROM population),t_customer_count AS (  SELECT tenure_band, segment, COUNT(*) AS customer_count  FROM population  GROUP BY tenure_band, segment),t_lifetime_orders AS (  SELECT p.tenure_band, p.segment, COUNT(o.id) AS total_orders  SELECT p.tenure_band AS tenure_band, p.segment AS segment, COUNT(o.id) AS total_orders  FROM population p  JOIN orders o ON o.user_id = p.user_id  GROUP BY p.tenure_band, p.segment),t_refunded_orders AS (  SELECT p.tenure_band, p.segment, COUNT(o.id) AS refunded_orders  SELECT p.tenure_band AS tenure_band, p.segment AS segment, COUNT(o.id) AS refunded_orders  FROM population p  JOIN orders o ON o.user_id = p.user_id  WHERE EXISTS (SELECT 1 FROM payments pay WHERE pay.order_id = o.id AND pay.amount_refunded > 0)     OR EXISTS (SELECT 1 FROM order_returns r WHERE r.order_id = o.id)  GROUP BY p.tenure_band, p.segment),t_revenue AS (  SELECT p.tenure_band, p.segment,  SELECT p.tenure_band AS tenure_band, p.segment AS segment,         SUM(CASE WHEN o.currency_code = 'EUR' THEN o.grand_total                  ELSE o.grand_total / NULLIF(er.rate, 0) END) AS total_revenue_eur,                  ELSE CAST(o.grand_total / NULLIF(er.rate, 0) AS NUMERIC(18,6)) END) AS total_revenue_eur,         COUNT(o.id) AS non_cancelled_orders  FROM population p  JOIN orders o ON o.user_id = p.user_id  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)  WHERE o.status <> 'cancelled'  GROUP BY p.tenure_band, p.segment)SELECT  gk.tenure_band,  gk.segment,  gk.tenure_band AS tenure_band,  gk.segment AS segment,  COALESCE(cc.customer_count, 0) AS customer_count,  ROUND(COALESCE(lo.total_orders, 0)::numeric / NULLIF(cc.customer_count, 0), 2) AS avg_lifetime_orders,  ROUND(COALESCE(rev.total_revenue_eur, 0) / NULLIF(cc.customer_count, 0), 2) AS avg_lifetime_revenue_eur,  ROUND(COALESCE(rev.total_revenue_eur, 0) / NULLIF(rev.non_cancelled_orders, 0), 2) AS avg_order_value_eur,  ROUND(100.0 * COALESCE(ro.refunded_orders, 0) / NULLIF(lo.total_orders, 0), 2) AS refund_rate_pct  CAST(COALESCE(lo.total_orders, 0) AS numeric(18,6)) / NULLIF(cc.customer_count, 0) AS avg_lifetime_orders,  COALESCE(rev.total_revenue_eur, 0) / NULLIF(cc.customer_count, 0) AS avg_lifetime_revenue_eur,  COALESCE(rev.total_revenue_eur, 0) / NULLIF(rev.non_cancelled_orders, 0) AS avg_order_value_eur,  100.0 * COALESCE(ro.refunded_orders, 0) / NULLIF(lo.total_orders, 0) AS refund_rate_pctFROM grain_keys gkLEFT JOIN t_customer_count cc ON cc.tenure_band = gk.tenure_band AND cc.segment = gk.segmentLEFT JOIN t_lifetime_orders lo ON lo.tenure_band = gk.tenure_band AND lo.segment = gk.segmentLEFT JOIN t_refunded_orders ro ON ro.tenure_band = gk.tenure_band AND ro.segment = gk.segmentLEFT JOIN t_revenue rev ON rev.tenure_band = gk.tenure_band AND rev.segment = gk.segmentORDER BY  CASE gk.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 gk.segment WHEN 'loyalty member' THEN 1 ELSE 2 END  END NULLS LAST,  CASE gk.segment WHEN 'loyalty member' THEN 1 ELSE 2 END NULLS LAST

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

    WITH population AS (
  SELECT u.id AS user_id,
         CASE
           WHEN (DATE '2025-12-31' - CAST(u.created_at AS DATE)) < 182 THEN 'under 6 months'
           WHEN (DATE '2025-12-31' - CAST(u.created_at AS DATE)) < 365 THEN '6 to 12 months'
           WHEN (DATE '2025-12-31' - CAST(u.created_at AS DATE)) < 730 THEN '12 to 24 months'
           ELSE 'over 24 months'
         END AS tenure_band,
         CASE WHEN EXISTS (
                SELECT 1 FROM loyalty_accounts la WHERE la.user_id = u.id
              ) THEN 'loyalty member' ELSE 'non-member' END AS segment
  FROM users u
  WHERE u.is_staff = false
    AND EXISTS (
      SELECT 1 FROM orders o
      WHERE o.user_id = u.id
        AND COALESCE(o.placed_at, o.created_at) >= TIMESTAMP '2023-01-01'
        AND COALESCE(o.placed_at, o.created_at) < TIMESTAMP '2026-01-01'
    )
),
grain_keys AS (
  SELECT DISTINCT tenure_band, segment FROM population
),
t_customer_count AS (
  SELECT tenure_band, segment, COUNT(*) AS customer_count
  FROM population
  GROUP BY tenure_band, segment
),
t_lifetime_orders AS (
  SELECT p.tenure_band AS tenure_band, p.segment AS segment, COUNT(o.id) AS total_orders
  FROM population p
  JOIN orders o ON o.user_id = p.user_id
  GROUP BY p.tenure_band, p.segment
),
t_refunded_orders AS (
  SELECT p.tenure_band AS tenure_band, p.segment AS segment, COUNT(o.id) AS refunded_orders
  FROM population p
  JOIN orders o ON o.user_id = p.user_id
  WHERE EXISTS (SELECT 1 FROM payments pay WHERE pay.order_id = o.id AND pay.amount_refunded > 0)
     OR EXISTS (SELECT 1 FROM order_returns r WHERE r.order_id = o.id)
  GROUP BY p.tenure_band, p.segment
),
t_revenue AS (
  SELECT p.tenure_band AS tenure_band, p.segment AS segment,
         SUM(CASE WHEN o.currency_code = 'EUR' THEN o.grand_total
                  ELSE CAST(o.grand_total / NULLIF(er.rate, 0) AS NUMERIC(18,6)) END) AS total_revenue_eur,
         COUNT(o.id) AS non_cancelled_orders
  FROM population p
  JOIN orders o ON o.user_id = p.user_id
  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)
  WHERE o.status <> 'cancelled'
  GROUP BY p.tenure_band, p.segment
)
SELECT
  gk.tenure_band AS tenure_band,
  gk.segment AS segment,
  COALESCE(cc.customer_count, 0) AS customer_count,
  CAST(COALESCE(lo.total_orders, 0) AS numeric(18,6)) / NULLIF(cc.customer_count, 0) AS avg_lifetime_orders,
  COALESCE(rev.total_revenue_eur, 0) / NULLIF(cc.customer_count, 0) AS avg_lifetime_revenue_eur,
  COALESCE(rev.total_revenue_eur, 0) / NULLIF(rev.non_cancelled_orders, 0) AS avg_order_value_eur,
  100.0 * COALESCE(ro.refunded_orders, 0) / NULLIF(lo.total_orders, 0) AS refund_rate_pct
FROM grain_keys gk
LEFT JOIN t_customer_count cc ON cc.tenure_band = gk.tenure_band AND cc.segment = gk.segment
LEFT JOIN t_lifetime_orders lo ON lo.tenure_band = gk.tenure_band AND lo.segment = gk.segment
LEFT JOIN t_refunded_orders ro ON ro.tenure_band = gk.tenure_band AND ro.segment = gk.segment
LEFT JOIN t_revenue rev ON rev.tenure_band = gk.tenure_band AND rev.segment = gk.segment
ORDER BY
  CASE gk.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 gk.segment WHEN 'loyalty member' THEN 1 ELSE 2 END NULLS LAST
    100 ms1 s10 sHyper821 msDuckDB4.25 sGizmoSQL3.60 sclickhouse_brew2.59 sFirebolterrorDataFusionerrorClickHouseerror
    • Firebolt{ "errors": [ { "description": "Line 46, Column 43: 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": "4ae35ae6-d680-4eaa-9f7e-675687dab304", "query_lab
    • DataFusionThis feature is not implemented: Physical plan does not support logical expression Exists(Exists { subquery: <subquery>, negated: false })
    • ClickHouseCode: 48. DB::Exception: Current query is not supported yet, because can't find correlated column '__table6.id' in current header: __table6.created_at. (NOT_IMPLEMENTED)

    Scale factor 1000, c8a.24xlarge, after the last load. q163 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

    Bea's whole session, 7 turns
    • The request, as Bea received it
      Report request from Ingrid Solberg (analyst for the Head of Lifecycle Marketing):

Title: Loyalty member vs non-member behavior by account tenure
Question: Does the loyalty-member gap in orders, revenue, AOV and refunds from last night's report persist once we control for how long the customer has had an account, or is it just that members and non-members differ in account age?
Grain: one row per combination of account-tenure band and loyalty segment (member vs non-member)
Columns:
- tenure_band
- segment
- customer_count
- avg_lifetime_orders
- avg_lifetime_revenue_eur
- avg_order_value_eur
- refund_rate_pct
Details: Same population and definitions as last night's 'Loyalty member vs non-member customer behavior' report: all users (is_staff = false) across all tenants with at least one order between 2023-01-01 and 2025-12-31; segment = 'loyalty member' if present in loyalty_accounts (any tenant/program), else 'non-member'. Tenure band = time from users.created_at to 2025-12-31, bucketed as: 'under 6 months', '6 to 12 months', '12 to 24 months', 'over 24 months'. Revenue/AOV use non-cancelled orders converted to EUR via exchange_rates (order currency_code to EUR, rate as_of COALESCE(placed_at, created_at)). avg_lifetime_orders and refund_rate_pct use all orders (any status), same as before (refund = payments.amount_refunded greater than 0 OR an order_returns row). Order rows by tenure_band (youngest to oldest), then segment ('loyalty member' before 'non-member').

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Bea calls dry_run
      WITH population AS (
  SELECT u.id AS user_id,
         CASE
           WHEN (DATE '2025-12-31' - CAST(u.created_at AS DATE)) < 182 THEN 'under 6 months'
           WHEN (DATE '2025-12-31' - CAST(u.created_at AS DATE)) < 365 THEN '6 to 12 months'
           WHEN (DATE '2025-12-31' - CAST(u.created_at AS DATE)) < 730 THEN '12 to 24 months'
           ELSE 'over 24 months'
         END AS tenure_band,
         CASE WHEN EXISTS (
                SELECT 1 FROM loyalty_accounts la WHERE la.user_id = u.id
              ) THEN 'loyalty member' ELSE 'non-member' END AS segment
  FROM users u
  WHERE u.is_staff = false
    AND EXISTS (
      SELECT 1 FROM orders o
      WHERE o.user_id = u.id
        AND COALESCE(o.placed_at, o.created_at) >= TIMESTAMP '2023-01-01'
        AND COALESCE(o.placed_at, o.created_at) < TIMESTAMP '2026-01-01'
    )
),
grain_keys AS (
  SELECT DISTINCT tenure_band, segment FROM population
),
t_customer_count AS (
  SELECT tenure_band, segment, COUNT(*) AS customer_count
  FROM population
  GROUP BY tenure_band, segment
),
t_lifetime_orders AS (
  SELECT p.tenure_band, p.segment, COUNT(o.id) AS total_orders
  FROM population p
  JOIN orders o ON o.user_id = p.user_id
  GROUP BY p.tenure_band, p.segment
),
t_refunded_orders AS (
  SELECT p.tenure_band, p.segment, COUNT(o.id) AS refunded_orders
  FROM population p
  JOIN orders o ON o.user_id = p.user_id
  WHERE EXISTS (SELECT 1 FROM payments pay WHERE pay.order_id = o.id AND pay.amount_refunded > 0)
     OR EXISTS (SELECT 1 FROM order_returns r WHERE r.order_id = o.id)
  GROUP BY p.tenure_band, p.segment
),
t_revenue AS (
  SELECT p.tenure_band, p.segment,
         SUM(CASE WHEN o.currency_code = 'EUR' THEN o.grand_total
                  ELSE o.grand_total / NULLIF(er.rate, 0) END) AS total_revenue_eur,
         COUNT(o.id) AS non_cancelled_orders
  FROM population p
  JOIN orders o ON o.user_id = p.user_id
  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)
  WHERE o.status <> 'cancelled'
  GROUP BY p.tenure_band, p.segment
)
SELECT
  gk.tenure_band,
  gk.segment,
  COALESCE(cc.customer_count, 0) AS customer_count,
  ROUND(COALESCE(lo.total_orders, 0)::numeric / NULLIF(cc.customer_count, 0), 2) AS avg_lifetime_orders,
  ROUND(COALESCE(rev.total_revenue_eur, 0) / NULLIF(cc.customer_count, 0), 2) AS avg_lifetime_revenue_eur,
  ROUND(COALESCE(rev.total_revenue_eur, 0) / NULLIF(rev.non_cancelled_orders, 0), 2) AS avg_order_value_eur,
  ROUND(100.0 * COALESCE(ro.refunded_orders, 0) / NULLIF(lo.total_orders, 0), 2) AS refund_rate_pct
FROM grain_keys gk
LEFT JOIN t_customer_count cc ON cc.tenure_band = gk.tenure_band AND cc.segment = gk.segment
LEFT JOIN t_lifetime_orders lo ON lo.tenure_band = gk.tenure_band AND lo.segment = gk.segment
LEFT JOIN t_refunded_orders ro ON ro.tenure_band = gk.tenure_band AND ro.segment = gk.segment
LEFT JOIN t_revenue rev ON rev.tenure_band = gk.tenure_band AND rev.segment = gk.segment
ORDER BY
  CASE gk.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 gk.segment WHEN 'loyalty member' THEN 1 ELSE 2 END
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: tenure_band, segment, customer_count, avg_lifetime_orders, avg_lifetime_revenue_eur, avg_order_value_eur, refund_rate_pct
    • BeaQuery compiles and returns the expected columns. Submitting.
    • Bea calls submit
      WITH population AS (
  SELECT u.id AS user_id,
         CASE
           WHEN (DATE '2025-12-31' - CAST(u.created_at AS DATE)) < 182 THEN 'under 6 months'
           WHEN (DATE '2025-12-31' - CAST(u.created_at AS DATE)) < 365 THEN '6 to 12 months'
           WHEN (DATE '2025-12-31' - CAST(u.created_at AS DATE)) < 730 THEN '12 to 24 months'
           ELSE 'over 24 months'
         END AS tenure_band,
         CASE WHEN EXISTS (
                SELECT 1 FROM loyalty_accounts la WHERE la.user_id = u.id
              ) THEN 'loyalty member' ELSE 'non-member' END AS segment
  FROM users u
  WHERE u.is_staff = false
    AND EXISTS (
      SELECT 1 FROM orders o
      WHERE o.user_id = u.id
        AND COALESCE(o.placed_at, o.created_at) >= TIMESTAMP '2023-01-01'
        AND COALESCE(o.placed_at, o.created_at) < TIMESTAMP '2026-01-01'
    )
),
grain_keys AS (
  SELECT DISTINCT tenure_band, segment FROM population
),
t_customer_count AS (
  SELECT tenure_band, segment, COUNT(*) AS customer_count
  FROM population
  GROUP BY tenure_band, segment
),
t_lifetime_orders AS (
  SELECT p.tenure_band, p.segment, COUNT(o.id) AS total_orders
  FROM population p
  JOIN orders o ON o.user_id = p.user_id
  GROUP BY p.tenure_band, p.segment
),
t_refunded_orders AS (
  SELECT p.tenure_band, p.segment, COUNT(o.id) AS refunded_orders
  FROM population p
  JOIN orders o ON o.user_id = p.user_id
  WHERE EXISTS (SELECT 1 FROM payments pay WHERE pay.order_id = o.id AND pay.amount_refunded > 0)
     OR EXISTS (SELECT 1 FROM order_returns r WHERE r.order_id = o.id)
  GROUP BY p.tenure_band, p.segment
),
t_revenue AS (
  SELECT p.tenure_band, p.segment,
         SUM(CASE WHEN o.currency_code = 'EUR' THEN o.grand_total
                  ELSE o.grand_total / NULLIF(er.rate, 0) END) AS total_revenue_eur,
         COUNT(o.id) AS non_cancelled_orders
  FROM population p
  JOIN orders o ON o.user_id = p.user_id
  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)
  WHERE o.status <> 'cancelled'
  GROUP BY p.tenure_band, p.segment
)
SELECT
  gk.tenure_band,
  gk.segment,
  COALESCE(cc.customer_count, 0) AS customer_count,
  ROUND(COALESCE(lo.total_orders, 0)::numeric / NULLIF(cc.customer_count, 0), 2) AS avg_lifetime_orders,
  ROUND(COALESCE(rev.total_revenue_eur, 0) / NULLIF(cc.customer_count, 0), 2) AS avg_lifetime_revenue_eur,
  ROUND(COALESCE(rev.total_revenue_eur, 0) / NULLIF(rev.non_cancelled_orders, 0), 2) AS avg_order_value_eur,
  ROUND(100.0 * COALESCE(ro.refunded_orders, 0) / NULLIF(lo.total_orders, 0), 2) AS refund_rate_pct
FROM grain_keys gk
LEFT JOIN t_customer_count cc ON cc.tenure_band = gk.tenure_band AND cc.segment = gk.segment
LEFT JOIN t_lifetime_orders lo ON lo.tenure_band = gk.tenure_band AND lo.segment = gk.segment
LEFT JOIN t_refunded_orders ro ON ro.tenure_band = gk.tenure_band AND ro.segment = gk.segment
LEFT JOIN t_revenue rev ON rev.tenure_band = gk.tenure_band AND rev.segment = gk.segment
ORDER BY
  CASE gk.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 gk.segment WHEN 'loyalty member' THEN 1 ELSE 2 END
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • BeaSubmitted. The report is grained on (tenure_band × segment) with 8 rows, 7 columns, ordered youngest-to-oldest tenure then loyalty member before non-member — matching the request and staying well within the width/length limits.

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

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