DB Olympics

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

Loyalty member order behavior before vs after enrollment

“For the same loyalty members, does their own order frequency, spend and refund rate actually change after they enroll, compared to their behavior before they joined - or is the elevated refund rate already present pre-enrollment?”

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 2 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 order behavior before vs after enrollment
    Question
    For the same loyalty members, does their own order frequency, spend and refund rate actually change after they enroll, compared to their behavior before they joined - or is the elevated refund rate already present pre-enrollment?
    One row per
    relative period: 'pre-enrollment' vs 'post-enrollment', aggregated across all loyalty members' own orders
    Columns
    • period
    • distinct_customers
    • total_orders
    • avg_order_value_eur
    • orders_per_customer_per_year
    • 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 lu AS (
  SELECT la.tenant_id AS tenant_id, la.user_id AS user_id, MIN(la.enrolled_at) AS enrolled_at
  FROM loyalty_accounts la
  GROUP BY la.tenant_id, la.user_id
),
lu2 AS (
  SELECT t0.tenant_id AS tenant_id, t0.user_id AS user_id, t0.enrolled_at AS enrolled_at, u0.created_at AS user_created_at
  FROM lu t0
  JOIN users u0 ON u0.tenant_id = t0.tenant_id AND u0.id = t0.user_id
),
ord AS (
  SELECT
    o0.tenant_id AS tenant_id,
    o0.user_id AS user_id,
    o0.id AS order_id,
    o0.placed_at AS placed_at,
    o0.status AS status,
    o0.currency_code AS currency_code,
    o0.grand_total AS grand_total,
    CASE WHEN o0.placed_at < lu2.enrolled_at THEN 'pre-enrollment' ELSE 'post-enrollment' END AS period
  FROM orders o0
  JOIN lu2 ON lu2.tenant_id = o0.tenant_id AND lu2.user_id = o0.user_id
  WHERE o0.placed_at IS NOT NULL
),
ord_refund AS (
  SELECT
    ord.tenant_id AS tenant_id,
    ord.order_id AS order_id,
    ord.period AS period,
    CASE WHEN EXISTS (
            SELECT 1 FROM payments p0
            WHERE p0.tenant_id = ord.tenant_id AND p0.order_id = ord.order_id AND p0.amount_refunded > 0
          )
          OR EXISTS (
            SELECT 1 FROM order_returns r0
            WHERE r0.tenant_id = ord.tenant_id AND r0.order_id = ord.order_id
          )
     THEN 1 ELSE 0 END AS is_refunded
  FROM ord
),
ord_eur AS (
  SELECT
    ord.tenant_id AS tenant_id,
    ord.order_id AS order_id,
    ord.period AS period,
    CASE WHEN ord.currency_code = 'EUR' THEN ord.grand_total
         ELSE ord.grand_total / NULLIF(er0.rate, 0)
    END AS eur_amount
  FROM ord
  LEFT JOIN exchange_rates er0
    ON er0.base_code = 'EUR'
   AND er0.quote_code = ord.currency_code
   AND er0.as_of = CAST(ord.placed_at AS DATE)
  WHERE ord.status <> 'cancelled'
),
m_orders AS (
  SELECT ord.period AS period, COUNT(*) AS total_orders, COUNT(DISTINCT ord.user_id) AS distinct_customers
  FROM ord
  GROUP BY ord.period
),
m_aov AS (
  SELECT ord_eur.period AS period, AVG(ord_eur.eur_amount) AS avg_order_value_eur
  FROM ord_eur
  GROUP BY ord_eur.period
),
m_refund AS (
  SELECT ord_refund.period AS period, 100.0 * SUM(ord_refund.is_refunded) / COUNT(*) AS refund_rate_pct
  FROM ord_refund
  GROUP BY ord_refund.period
),
cust_pre AS (
  SELECT ord.tenant_id AS tenant_id, ord.user_id AS user_id, MIN(ord.placed_at) AS first_pre_order_at
  FROM ord
  WHERE ord.period = 'pre-enrollment'
  GROUP BY ord.tenant_id, ord.user_id
),
cust_post AS (
  SELECT ord.tenant_id AS tenant_id, ord.user_id AS user_id, MAX(ord.placed_at) AS last_post_order_at
  FROM ord
  WHERE ord.period = 'post-enrollment'
  GROUP BY ord.tenant_id, ord.user_id
),
cust_exposure AS (
  SELECT
    lu2.tenant_id AS tenant_id,
    lu2.user_id AS user_id,
    GREATEST(1, CAST(lu2.enrolled_at AS DATE) - CAST(COALESCE(cp0.first_pre_order_at, lu2.user_created_at) AS DATE)) AS pre_exposed_days,
    GREATEST(1, CAST(COALESCE(cpo0.last_post_order_at, DATE '2025-12-31') AS DATE) - CAST(lu2.enrolled_at AS DATE)) AS post_exposed_days
  FROM lu2
  LEFT JOIN cust_pre cp0 ON cp0.tenant_id = lu2.tenant_id AND cp0.user_id = lu2.user_id
  LEFT JOIN cust_post cpo0 ON cpo0.tenant_id = lu2.tenant_id AND cpo0.user_id = lu2.user_id
),
exposure_by_period AS (
  SELECT 'pre-enrollment' AS period, SUM(ce.pre_exposed_days) AS total_exposed_days FROM cust_exposure ce
  UNION ALL
  SELECT 'post-enrollment' AS period, SUM(ce.post_exposed_days) AS total_exposed_days FROM cust_exposure ce
),
periods AS (
  SELECT 'pre-enrollment' AS period
  UNION ALL
  SELECT 'post-enrollment' AS period
)
SELECT
  p0.period AS period,
  COALESCE(mo0.distinct_customers, 0) AS distinct_customers,
  COALESCE(mo0.total_orders, 0) AS total_orders,
  ROUND(COALESCE(ma0.avg_order_value_eur, 0), 2) AS avg_order_value_eur,
  ROUND(
    COALESCE(mo0.total_orders, 0) / NULLIF(COALESCE(ep0.total_exposed_days, 0) / 365.0, 0)
  , 2) AS orders_per_customer_per_year,
  ROUND(COALESCE(mr0.refund_rate_pct, 0), 2) AS refund_rate_pct
FROM periods p0
LEFT JOIN m_orders mo0 ON mo0.period = p0.period
LEFT JOIN m_aov ma0 ON ma0.period = p0.period
LEFT JOIN m_refund mr0 ON mr0.period = p0.period
LEFT JOIN exposure_by_period ep0 ON ep0.period = p0.period
ORDER BY CASE WHEN p0.period = 'pre-enrollment' THEN 0 ELSE 1 END, p0.period

    “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 query builds the loyalty-enrollment population (earliest enrolled_at per user), splits each user's orders into pre/post periods relative to that date, and reports per-period distinct customers, order counts, EUR-converted average order value (excluding cancelled orders), refund rate (payment refunds or RMAs), and orders-per-customer-per-year normalized by each customer's exposed days in that period.”

  5. The report comes back overnight

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

    period          | distinct_customers | total_orders | avg_order_value_eur | orders_per_customer_per_year | refund_rate_pct
----------------+--------------------+--------------+---------------------+------------------------------+----------------
pre-enrollment  |                  0 |            0 |                 0.0 |                          0.0 |             0.0
post-enrollment |               1330 |         4941 |               194.4 |                         3.25 |           32.36
(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:

    It builds on:

  7. It becomes benchmark query q164

    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)
    • placeholderdate diff days (2×)
    • 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 lu AS (  SELECT la.tenant_id AS tenant_id, la.user_id AS user_id, MIN(la.enrolled_at) AS enrolled_at  FROM loyalty_accounts la  GROUP BY la.tenant_id, la.user_id),lu2 AS (  SELECT t0.tenant_id AS tenant_id, t0.user_id AS user_id, t0.enrolled_at AS enrolled_at, u0.created_at AS user_created_at  FROM lu t0  JOIN users u0 ON u0.tenant_id = t0.tenant_id AND u0.id = t0.user_id),ord AS (  SELECT    o0.tenant_id AS tenant_id,    o0.user_id AS user_id,    o0.id AS order_id,    o0.placed_at AS placed_at,    o0.status AS status,    o0.currency_code AS currency_code,    o0.grand_total AS grand_total,    CASE WHEN o0.placed_at < lu2.enrolled_at THEN 'pre-enrollment' ELSE 'post-enrollment' END AS period  FROM orders o0  JOIN lu2 ON lu2.tenant_id = o0.tenant_id AND lu2.user_id = o0.user_id  WHERE o0.placed_at IS NOT NULL),ord_refund AS (  SELECT    ord.tenant_id AS tenant_id,    ord.order_id AS order_id,    ord.period AS period,    CASE WHEN EXISTS (            SELECT 1 FROM payments p0            WHERE p0.tenant_id = ord.tenant_id AND p0.order_id = ord.order_id AND p0.amount_refunded > 0          )          OR EXISTS (            SELECT 1 FROM order_returns r0            WHERE r0.tenant_id = ord.tenant_id AND r0.order_id = ord.order_id          )     THEN 1 ELSE 0 END AS is_refunded  FROM ord),ord_eur AS (  SELECT    ord.tenant_id AS tenant_id,    ord.order_id AS order_id,    ord.period AS period,    CASE WHEN ord.currency_code = 'EUR' THEN ord.grand_total         ELSE ord.grand_total / NULLIF(er0.rate, 0)         ELSE CAST(ord.grand_total / NULLIF(er0.rate, 0) AS NUMERIC(18,6))    END AS eur_amount  FROM ord  LEFT JOIN exchange_rates er0    ON er0.base_code = 'EUR'   AND er0.quote_code = ord.currency_code   AND er0.as_of = CAST(ord.placed_at AS DATE)  WHERE ord.status <> 'cancelled'),m_orders AS (  SELECT ord.period AS period, COUNT(*) AS total_orders, COUNT(DISTINCT ord.user_id) AS distinct_customers  FROM ord  GROUP BY ord.period),m_aov AS (  SELECT ord_eur.period AS period, AVG(ord_eur.eur_amount) AS avg_order_value_eur  FROM ord_eur  GROUP BY ord_eur.period),m_refund AS (  SELECT ord_refund.period AS period, 100.0 * SUM(ord_refund.is_refunded) / COUNT(*) AS refund_rate_pct  FROM ord_refund  GROUP BY ord_refund.period),cust_pre AS (  SELECT ord.tenant_id AS tenant_id, ord.user_id AS user_id, MIN(ord.placed_at) AS first_pre_order_at  FROM ord  WHERE ord.period = 'pre-enrollment'  GROUP BY ord.tenant_id, ord.user_id),cust_post AS (  SELECT ord.tenant_id AS tenant_id, ord.user_id AS user_id, MAX(ord.placed_at) AS last_post_order_at  FROM ord  WHERE ord.period = 'post-enrollment'  GROUP BY ord.tenant_id, ord.user_id),cust_exposure AS (  SELECT    lu2.tenant_id AS tenant_id,    lu2.user_id AS user_id,    GREATEST(1, CAST(lu2.enrolled_at AS DATE) - CAST(COALESCE(cp0.first_pre_order_at, lu2.user_created_at) AS DATE)) AS pre_exposed_days,    GREATEST(1, CAST(COALESCE(cpo0.last_post_order_at, DATE '2025-12-31') AS DATE) - CAST(lu2.enrolled_at AS DATE)) AS post_exposed_days    GREATEST(1, {{date_diff_days(COALESCE(cp0.first_pre_order_at, lu2.user_created_at), lu2.enrolled_at)}}) AS pre_exposed_days,    GREATEST(1, {{date_diff_days(lu2.enrolled_at, COALESCE(cpo0.last_post_order_at, DATE '2025-12-31'))}}) AS post_exposed_days  FROM lu2  LEFT JOIN cust_pre cp0 ON cp0.tenant_id = lu2.tenant_id AND cp0.user_id = lu2.user_id  LEFT JOIN cust_post cpo0 ON cpo0.tenant_id = lu2.tenant_id AND cpo0.user_id = lu2.user_id),exposure_by_period AS (  SELECT 'pre-enrollment' AS period, SUM(ce.pre_exposed_days) AS total_exposed_days FROM cust_exposure ce  UNION ALL  SELECT 'post-enrollment' AS period, SUM(ce.post_exposed_days) AS total_exposed_days FROM cust_exposure ce),periods AS (  SELECT 'pre-enrollment' AS period  UNION ALL  SELECT 'post-enrollment' AS period)SELECT  p0.period AS period,  COALESCE(mo0.distinct_customers, 0) AS distinct_customers,  COALESCE(mo0.total_orders, 0) AS total_orders,  ROUND(COALESCE(ma0.avg_order_value_eur, 0), 2) AS avg_order_value_eur,  ROUND(    COALESCE(mo0.total_orders, 0) / NULLIF(COALESCE(ep0.total_exposed_days, 0) / 365.0, 0)  , 2) AS orders_per_customer_per_year,  ROUND(COALESCE(mr0.refund_rate_pct, 0), 2) AS refund_rate_pct  COALESCE(ma0.avg_order_value_eur, 0) AS avg_order_value_eur,  COALESCE(mo0.total_orders, 0) / NULLIF(COALESCE(ep0.total_exposed_days, 0) / 365.0, 0) AS orders_per_customer_per_year,  COALESCE(mr0.refund_rate_pct, 0) AS refund_rate_pctFROM periods p0LEFT JOIN m_orders mo0 ON mo0.period = p0.periodLEFT JOIN m_aov ma0 ON ma0.period = p0.periodLEFT JOIN m_refund mr0 ON mr0.period = p0.periodLEFT JOIN exposure_by_period ep0 ON ep0.period = p0.periodORDER BY CASE WHEN p0.period = 'pre-enrollment' THEN 0 ELSE 1 END, p0.periodORDER BY CASE WHEN p0.period = 'pre-enrollment' THEN 0 ELSE 1 END NULLS LAST, p0.period NULLS LAST

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

    WITH lu AS (
  SELECT la.tenant_id AS tenant_id, la.user_id AS user_id, MIN(la.enrolled_at) AS enrolled_at
  FROM loyalty_accounts la
  GROUP BY la.tenant_id, la.user_id
),
lu2 AS (
  SELECT t0.tenant_id AS tenant_id, t0.user_id AS user_id, t0.enrolled_at AS enrolled_at, u0.created_at AS user_created_at
  FROM lu t0
  JOIN users u0 ON u0.tenant_id = t0.tenant_id AND u0.id = t0.user_id
),
ord AS (
  SELECT
    o0.tenant_id AS tenant_id,
    o0.user_id AS user_id,
    o0.id AS order_id,
    o0.placed_at AS placed_at,
    o0.status AS status,
    o0.currency_code AS currency_code,
    o0.grand_total AS grand_total,
    CASE WHEN o0.placed_at < lu2.enrolled_at THEN 'pre-enrollment' ELSE 'post-enrollment' END AS period
  FROM orders o0
  JOIN lu2 ON lu2.tenant_id = o0.tenant_id AND lu2.user_id = o0.user_id
  WHERE o0.placed_at IS NOT NULL
),
ord_refund AS (
  SELECT
    ord.tenant_id AS tenant_id,
    ord.order_id AS order_id,
    ord.period AS period,
    CASE WHEN EXISTS (
            SELECT 1 FROM payments p0
            WHERE p0.tenant_id = ord.tenant_id AND p0.order_id = ord.order_id AND p0.amount_refunded > 0
          )
          OR EXISTS (
            SELECT 1 FROM order_returns r0
            WHERE r0.tenant_id = ord.tenant_id AND r0.order_id = ord.order_id
          )
     THEN 1 ELSE 0 END AS is_refunded
  FROM ord
),
ord_eur AS (
  SELECT
    ord.tenant_id AS tenant_id,
    ord.order_id AS order_id,
    ord.period AS period,
    CASE WHEN ord.currency_code = 'EUR' THEN ord.grand_total
         ELSE CAST(ord.grand_total / NULLIF(er0.rate, 0) AS NUMERIC(18,6))
    END AS eur_amount
  FROM ord
  LEFT JOIN exchange_rates er0
    ON er0.base_code = 'EUR'
   AND er0.quote_code = ord.currency_code
   AND er0.as_of = CAST(ord.placed_at AS DATE)
  WHERE ord.status <> 'cancelled'
),
m_orders AS (
  SELECT ord.period AS period, COUNT(*) AS total_orders, COUNT(DISTINCT ord.user_id) AS distinct_customers
  FROM ord
  GROUP BY ord.period
),
m_aov AS (
  SELECT ord_eur.period AS period, AVG(ord_eur.eur_amount) AS avg_order_value_eur
  FROM ord_eur
  GROUP BY ord_eur.period
),
m_refund AS (
  SELECT ord_refund.period AS period, 100.0 * SUM(ord_refund.is_refunded) / COUNT(*) AS refund_rate_pct
  FROM ord_refund
  GROUP BY ord_refund.period
),
cust_pre AS (
  SELECT ord.tenant_id AS tenant_id, ord.user_id AS user_id, MIN(ord.placed_at) AS first_pre_order_at
  FROM ord
  WHERE ord.period = 'pre-enrollment'
  GROUP BY ord.tenant_id, ord.user_id
),
cust_post AS (
  SELECT ord.tenant_id AS tenant_id, ord.user_id AS user_id, MAX(ord.placed_at) AS last_post_order_at
  FROM ord
  WHERE ord.period = 'post-enrollment'
  GROUP BY ord.tenant_id, ord.user_id
),
cust_exposure AS (
  SELECT
    lu2.tenant_id AS tenant_id,
    lu2.user_id AS user_id,
    GREATEST(1, DATE_DIFF('day', COALESCE(cp0.first_pre_order_at, lu2.user_created_at), lu2.enrolled_at)) AS pre_exposed_days,
    GREATEST(1, DATE_DIFF('day', lu2.enrolled_at, COALESCE(cpo0.last_post_order_at, DATE '2025-12-31'))) AS post_exposed_days
  FROM lu2
  LEFT JOIN cust_pre cp0 ON cp0.tenant_id = lu2.tenant_id AND cp0.user_id = lu2.user_id
  LEFT JOIN cust_post cpo0 ON cpo0.tenant_id = lu2.tenant_id AND cpo0.user_id = lu2.user_id
),
exposure_by_period AS (
  SELECT 'pre-enrollment' AS period, SUM(ce.pre_exposed_days) AS total_exposed_days FROM cust_exposure ce
  UNION ALL
  SELECT 'post-enrollment' AS period, SUM(ce.post_exposed_days) AS total_exposed_days FROM cust_exposure ce
),
periods AS (
  SELECT 'pre-enrollment' AS period
  UNION ALL
  SELECT 'post-enrollment' AS period
)
SELECT
  p0.period AS period,
  COALESCE(mo0.distinct_customers, 0) AS distinct_customers,
  COALESCE(mo0.total_orders, 0) AS total_orders,
  COALESCE(ma0.avg_order_value_eur, 0) AS avg_order_value_eur,
  COALESCE(mo0.total_orders, 0) / NULLIF(COALESCE(ep0.total_exposed_days, 0) / 365.0, 0) AS orders_per_customer_per_year,
  COALESCE(mr0.refund_rate_pct, 0) AS refund_rate_pct
FROM periods p0
LEFT JOIN m_orders mo0 ON mo0.period = p0.period
LEFT JOIN m_aov ma0 ON ma0.period = p0.period
LEFT JOIN m_refund mr0 ON mr0.period = p0.period
LEFT JOIN exposure_by_period ep0 ON ep0.period = p0.period
ORDER BY CASE WHEN p0.period = 'pre-enrollment' THEN 0 ELSE 1 END NULLS LAST, p0.period NULLS LAST
    100 ms1 s10 sHyper210 msDuckDB307 msGizmoSQL323 msclickhouse_brew1.85 sFirebolterrorDataFusionerrorClickHouseerror
    • Firebolt{ "errors": [ { "description": "Line 47, Column 36: 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": "2b4ebf96-e65c-481d-9cbd-48c9e0b0fc01", "query_lab
    • DataFusionThis feature is not implemented: Physical plan does not support logical expression Exists(Exists { subquery: <subquery>, negated: false })
    • ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table15.grand_total Decimal(14, 4) Decimal64(size = 0), nullIf(__table21.rate, 0_UInt8) Nullable(Decimal(12, 6)) Nullable(size = 0, Decimal64(size = 0), UInt8(size = 0)). (ARGU

    Scale factor 1000, c8a.24xlarge, after the last load. q164 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 order behavior before vs after enrollment
Question: For the same loyalty members, does their own order frequency, spend and refund rate actually change after they enroll, compared to their behavior before they joined - or is the elevated refund rate already present pre-enrollment?
Grain: one row per relative period: 'pre-enrollment' vs 'post-enrollment', aggregated across all loyalty members' own orders
Columns:
- period
- distinct_customers
- total_orders
- avg_order_value_eur
- orders_per_customer_per_year
- refund_rate_pct
Details: Population: users with a row in loyalty_accounts; use the earliest enrolled_at per user if they have multiple program enrollments. For each such user, split their orders (any status, placed_at not null) into 'pre-enrollment' (placed_at before enrolled_at) and 'post-enrollment' (placed_at on or after enrolled_at). total_orders and distinct_customers are simple counts within each period. avg_order_value_eur uses non-cancelled orders, grand_total converted to EUR via exchange_rates (as_of placed_at). refund_rate_pct = share of orders in that period with payments.amount_refunded greater than 0 OR an order_returns row. orders_per_customer_per_year = total_orders in the period divided by (sum across customers of their exposed days in that period divided by 365); exposed days pre-enrollment = enrolled_at minus the customer's first order placed_at (or users.created_at if no earlier order) for that customer; exposed days post-enrollment = (customer's last order placed_at, or 2025-12-31 if still active) minus enrolled_at; floor exposed days at 1 to avoid division by zero. Order rows: 'pre-enrollment' first, then 'post-enrollment'.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Bea calls dry_run
      WITH lu AS (
  SELECT la.tenant_id AS tenant_id, la.user_id AS user_id, MIN(la.enrolled_at) AS enrolled_at
  FROM loyalty_accounts la
  GROUP BY la.tenant_id, la.user_id
),
lu2 AS (
  SELECT t0.tenant_id AS tenant_id, t0.user_id AS user_id, t0.enrolled_at AS enrolled_at, u0.created_at AS user_created_at
  FROM lu t0
  JOIN users u0 ON u0.tenant_id = t0.tenant_id AND u0.id = t0.user_id
),
ord AS (
  SELECT
    o0.tenant_id AS tenant_id,
    o0.user_id AS user_id,
    o0.id AS order_id,
    o0.placed_at AS placed_at,
    o0.status AS status,
    o0.currency_code AS currency_code,
    o0.grand_total AS grand_total,
    CASE WHEN o0.placed_at < lu2.enrolled_at THEN 'pre-enrollment' ELSE 'post-enrollment' END AS period
  FROM orders o0
  JOIN lu2 ON lu2.tenant_id = o0.tenant_id AND lu2.user_id = o0.user_id
  WHERE o0.placed_at IS NOT NULL
),
ord_refund AS (
  SELECT
    ord.tenant_id AS tenant_id,
    ord.order_id AS order_id,
    ord.period AS period,
    CASE WHEN EXISTS (
            SELECT 1 FROM payments p0
            WHERE p0.tenant_id = ord.tenant_id AND p0.order_id = ord.order_id AND p0.amount_refunded > 0
          )
          OR EXISTS (
            SELECT 1 FROM order_returns r0
            WHERE r0.tenant_id = ord.tenant_id AND r0.order_id = ord.order_id
          )
     THEN 1 ELSE 0 END AS is_refunded
  FROM ord
),
ord_eur AS (
  SELECT
    ord.tenant_id AS tenant_id,
    ord.order_id AS order_id,
    ord.period AS period,
    CASE WHEN ord.currency_code = 'EUR' THEN ord.grand_total
         ELSE ord.grand_total / NULLIF(er0.rate, 0)
    END AS eur_amount
  FROM ord
  LEFT JOIN exchange_rates er0
    ON er0.base_code = 'EUR'
   AND er0.quote_code = ord.currency_code
   AND er0.as_of = CAST(ord.placed_at AS DATE)
  WHERE ord.status <> 'cancelled'
),
m_orders AS (
  SELECT ord.period AS period, COUNT(*) AS total_orders, COUNT(DISTINCT ord.user_id) AS distinct_customers
  FROM ord
  GROUP BY ord.period
),
m_aov AS (
  SELECT ord_eur.period AS period, AVG(ord_eur.eur_amount) AS avg_order_value_eur
  FROM ord_eur
  GROUP BY ord_eur.period
),
m_refund AS (
  SELECT ord_refund.period AS period, 100.0 * SUM(ord_refund.is_refunded) / COUNT(*) AS refund_rate_pct
  FROM ord_refund
  GROUP BY ord_refund.period
),
cust_pre AS (
  SELECT ord.tenant_id AS tenant_id, ord.user_id AS user_id, MIN(ord.placed_at) AS first_pre_order_at
  FROM ord
  WHERE ord.period = 'pre-enrollment'
  GROUP BY ord.tenant_id, ord.user_id
),
cust_post AS (
  SELECT ord.tenant_id AS tenant_id, ord.user_id AS user_id, MAX(ord.placed_at) AS last_post_order_at
  FROM ord
  WHERE ord.period = 'post-enrollment'
  GROUP BY ord.tenant_id, ord.user_id
),
cust_exposure AS (
  SELECT
    lu2.tenant_id AS tenant_id,
    lu2.user_id AS user_id,
    GREATEST(1, CAST(lu2.enrolled_at AS DATE) - CAST(COALESCE(cp0.first_pre_order_at, lu2.user_created_at) AS DATE)) AS pre_exposed_days,
    GREATEST(1, CAST(COALESCE(cpo0.last_post_order_at, DATE '2025-12-31') AS DATE) - CAST(lu2.enrolled_at AS DATE)) AS post_exposed_days
  FROM lu2
  LEFT JOIN cust_pre cp0 ON cp0.tenant_id = lu2.tenant_id AND cp0.user_id = lu2.user_id
  LEFT JOIN cust_post cpo0 ON cpo0.tenant_id = lu2.tenant_id AND cpo0.user_id = lu2.user_id
),
exposure_by_period AS (
  SELECT 'pre-enrollment' AS period, SUM(ce.pre_exposed_days) AS total_exposed_days FROM cust_exposure ce
  UNION ALL
  SELECT 'post-enrollment' AS period, SUM(ce.post_exposed_days) AS total_exposed_days FROM cust_exposure ce
),
periods AS (
  SELECT 'pre-enrollment' AS period
  UNION ALL
  SELECT 'post-enrollment' AS period
)
SELECT
  p0.period AS period,
  COALESCE(mo0.distinct_customers, 0) AS distinct_customers,
  COALESCE(mo0.total_orders, 0) AS total_orders,
  ROUND(COALESCE(ma0.avg_order_value_eur, 0), 2) AS avg_order_value_eur,
  ROUND(
    COALESCE(mo0.total_orders, 0) / NULLIF(COALESCE(ep0.total_exposed_days, 0) / 365.0, 0)
  , 2) AS orders_per_customer_per_year,
  ROUND(COALESCE(mr0.refund_rate_pct, 0), 2) AS refund_rate_pct
FROM periods p0
LEFT JOIN m_orders mo0 ON mo0.period = p0.period
LEFT JOIN m_aov ma0 ON ma0.period = p0.period
LEFT JOIN m_refund mr0 ON mr0.period = p0.period
LEFT JOIN exposure_by_period ep0 ON ep0.period = p0.period
ORDER BY CASE WHEN p0.period = 'pre-enrollment' THEN 0 ELSE 1 END, p0.period
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: period, distinct_customers, total_orders, avg_order_value_eur, orders_per_customer_per_year, refund_rate_pct
    • BeaQuery compiles and returns the expected columns. Submitting.
    • Bea calls submit
      WITH lu AS (
  SELECT la.tenant_id AS tenant_id, la.user_id AS user_id, MIN(la.enrolled_at) AS enrolled_at
  FROM loyalty_accounts la
  GROUP BY la.tenant_id, la.user_id
),
lu2 AS (
  SELECT t0.tenant_id AS tenant_id, t0.user_id AS user_id, t0.enrolled_at AS enrolled_at, u0.created_at AS user_created_at
  FROM lu t0
  JOIN users u0 ON u0.tenant_id = t0.tenant_id AND u0.id = t0.user_id
),
ord AS (
  SELECT
    o0.tenant_id AS tenant_id,
    o0.user_id AS user_id,
    o0.id AS order_id,
    o0.placed_at AS placed_at,
    o0.status AS status,
    o0.currency_code AS currency_code,
    o0.grand_total AS grand_total,
    CASE WHEN o0.placed_at < lu2.enrolled_at THEN 'pre-enrollment' ELSE 'post-enrollment' END AS period
  FROM orders o0
  JOIN lu2 ON lu2.tenant_id = o0.tenant_id AND lu2.user_id = o0.user_id
  WHERE o0.placed_at IS NOT NULL
),
ord_refund AS (
  SELECT
    ord.tenant_id AS tenant_id,
    ord.order_id AS order_id,
    ord.period AS period,
    CASE WHEN EXISTS (
            SELECT 1 FROM payments p0
            WHERE p0.tenant_id = ord.tenant_id AND p0.order_id = ord.order_id AND p0.amount_refunded > 0
          )
          OR EXISTS (
            SELECT 1 FROM order_returns r0
            WHERE r0.tenant_id = ord.tenant_id AND r0.order_id = ord.order_id
          )
     THEN 1 ELSE 0 END AS is_refunded
  FROM ord
),
ord_eur AS (
  SELECT
    ord.tenant_id AS tenant_id,
    ord.order_id AS order_id,
    ord.period AS period,
    CASE WHEN ord.currency_code = 'EUR' THEN ord.grand_total
         ELSE ord.grand_total / NULLIF(er0.rate, 0)
    END AS eur_amount
  FROM ord
  LEFT JOIN exchange_rates er0
    ON er0.base_code = 'EUR'
   AND er0.quote_code = ord.currency_code
   AND er0.as_of = CAST(ord.placed_at AS DATE)
  WHERE ord.status <> 'cancelled'
),
m_orders AS (
  SELECT ord.period AS period, COUNT(*) AS total_orders, COUNT(DISTINCT ord.user_id) AS distinct_customers
  FROM ord
  GROUP BY ord.period
),
m_aov AS (
  SELECT ord_eur.period AS period, AVG(ord_eur.eur_amount) AS avg_order_value_eur
  FROM ord_eur
  GROUP BY ord_eur.period
),
m_refund AS (
  SELECT ord_refund.period AS period, 100.0 * SUM(ord_refund.is_refunded) / COUNT(*) AS refund_rate_pct
  FROM ord_refund
  GROUP BY ord_refund.period
),
cust_pre AS (
  SELECT ord.tenant_id AS tenant_id, ord.user_id AS user_id, MIN(ord.placed_at) AS first_pre_order_at
  FROM ord
  WHERE ord.period = 'pre-enrollment'
  GROUP BY ord.tenant_id, ord.user_id
),
cust_post AS (
  SELECT ord.tenant_id AS tenant_id, ord.user_id AS user_id, MAX(ord.placed_at) AS last_post_order_at
  FROM ord
  WHERE ord.period = 'post-enrollment'
  GROUP BY ord.tenant_id, ord.user_id
),
cust_exposure AS (
  SELECT
    lu2.tenant_id AS tenant_id,
    lu2.user_id AS user_id,
    GREATEST(1, CAST(lu2.enrolled_at AS DATE) - CAST(COALESCE(cp0.first_pre_order_at, lu2.user_created_at) AS DATE)) AS pre_exposed_days,
    GREATEST(1, CAST(COALESCE(cpo0.last_post_order_at, DATE '2025-12-31') AS DATE) - CAST(lu2.enrolled_at AS DATE)) AS post_exposed_days
  FROM lu2
  LEFT JOIN cust_pre cp0 ON cp0.tenant_id = lu2.tenant_id AND cp0.user_id = lu2.user_id
  LEFT JOIN cust_post cpo0 ON cpo0.tenant_id = lu2.tenant_id AND cpo0.user_id = lu2.user_id
),
exposure_by_period AS (
  SELECT 'pre-enrollment' AS period, SUM(ce.pre_exposed_days) AS total_exposed_days FROM cust_exposure ce
  UNION ALL
  SELECT 'post-enrollment' AS period, SUM(ce.post_exposed_days) AS total_exposed_days FROM cust_exposure ce
),
periods AS (
  SELECT 'pre-enrollment' AS period
  UNION ALL
  SELECT 'post-enrollment' AS period
)
SELECT
  p0.period AS period,
  COALESCE(mo0.distinct_customers, 0) AS distinct_customers,
  COALESCE(mo0.total_orders, 0) AS total_orders,
  ROUND(COALESCE(ma0.avg_order_value_eur, 0), 2) AS avg_order_value_eur,
  ROUND(
    COALESCE(mo0.total_orders, 0) / NULLIF(COALESCE(ep0.total_exposed_days, 0) / 365.0, 0)
  , 2) AS orders_per_customer_per_year,
  ROUND(COALESCE(mr0.refund_rate_pct, 0), 2) AS refund_rate_pct
FROM periods p0
LEFT JOIN m_orders mo0 ON mo0.period = p0.period
LEFT JOIN m_aov ma0 ON ma0.period = p0.period
LEFT JOIN m_refund mr0 ON mr0.period = p0.period
LEFT JOIN exposure_by_period ep0 ON ep0.period = p0.period
ORDER BY CASE WHEN p0.period = 'pre-enrollment' THEN 0 ELSE 1 END, p0.period
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • BeaSubmitted. The query builds the loyalty-enrollment population (earliest `enrolled_at` per user), splits each user's orders into pre/post periods relative to that date, and reports per-period distinct customers, order counts, EUR-converted average order value (excluding cancelled orders), refund rate (payment refunds or RMAs), and orders-per-customer-per-year normalized by each customer's exposed days in that period.

    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 (297 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)