DB Olympics

Queries · q378 · Head of Lifecycle Marketing · week 2, night 3

Member order behavior before vs after their first points redemption

“Among members who have redeemed at least once, does their order frequency and spend actually pick up after that first redemption, or were they already heavy buyers beforehand - i.e. does redeeming look like a cause of engagement or just a marker of customers who were already engaged?”

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 2

    Subject: Re: Loyalty & referral — thank you, and where to dig next

    Ingrid, this is excellent — especially catching that the refund-rate gap was a vertical mix artifact. Good instinct to go back and break that out rather than let the scary headline stand.

    What surprised me most, and what I want explained before we take this to Tobias as a joint recommendation: a 19.4% cost-to-member-revenue ratio is strikingly high, and members showing lower revenue and order counts than non-members is the opposite of what a loyalty programme is supposed to produce. I don't want to present "the programme loses money" to leadership until I understand whether that's a true causal effect or another mix artifact like the refund rate turned out to be — since 72% of members sit in one vertical, I'm wary of a second apples-to-oranges comparison hiding in there.

    Questions for this week, in my own words:

    1. Within Apparel alone — since that's where almost all members live — do members still look worse off than similar non-member customers in that same vertical, once we also account for how long they've been customers? That's the comparison I actually trust as a verdict on the programme.
    2. Is the growing points liability a timing issue or a real design flaw? I want to understand whether we're simply in a "young programme, redemptions lag issuance" phase that will level out, or whether the redemption rate structurally understates true future cost — i.e., should I expect that €491k to keep climbing indefinitely at this pace.
    3. Can we fix the redemption-to-order linkage well enough to test the gaming hypothesis properly? That was left untested, not cleared, and it's exactly the kind of finding that would change my recommendation, so I'd like one more serious attempt before we call it inconclusive for good.
    4. If the member economics do hold up as genuinely negative, is there a design change that would plausibly fix it — rather than only "keep, cut, or extend to B2B" as the choice — are there any patterns in how the better-performing members (if any exist) use the programme differently from the rest? I don't need a redesign from you, just whether the data shows any sign of what a healthier version would look like.

    The B2B extension question can wait — I agree we shouldn't open that door until we trust the member economics number. Let's keep that out of this week's slots unless there's room left over.

    Thanks again, Ingrid — this is exactly the kind of rigor I want from this team.

    Best, Aisha

  3. Ingrid Solberg plans the night

    The analyst · week 2, night 3, request 3 of 3

    Night 2 closed out Q3 cleanly: only 5 of 4,936 member orders fall on the same calendar day as any points redemption, and that handful is too thin to read anything into (and still not a true causal link) - combined with burn transactions never carrying an order_id, this tells me redemption is simply decoupled from checkout timing in how the system records it, not that gaming is happening. I'm treating that hypothesis as addressed: no usable signal, data model doesn't support closer scrutiny. Report 2 ruled out discounting as the cause of the Apparel AOV gap - discount rates are small and similar for both segments (4.6% vs 3.6%); the gap is almost entirely in the pre-discount subtotal, so members are just buying cheaper baskets, not getting bigger discounts. The big thread to pull tonight is Report 3: it turns out loyalty members are not one population - only 3.9% of the bottom 75% (by revenue) have EVER redeemed a point, versus 55.9% of the top 25%, and the top 25% look like dramatically better customers (6x the revenue, 3x the orders, 2.5x the tenure). That could mean engagement drives value, or it could just mean long-tenured big spenders naturally rack up and redeem more points - I want to pull that apart before telling Aisha 'engagement is the fix.' Tonight: (1) re-cut the Apparel tenure comparison into three groups - never-redeemed members, ever-redeemed members, non-members - to see if engaged members actually match or beat non-members once tenure is held constant, while disengaged members lag; (2) see whether the programme's real redeemed cost is concentrated entirely in the engaged minority (meaning the disengaged majority is pure low-stakes liability, not realized cost); (3) a before/after-first-redemption test on engaged members only (this one should actually have pre-period data, unlike the failed before/after-enrollment test) to get at causality - does ordering pick up after someone's first redemption, or were they already heavy buyers beforehand?

    Member order behavior before vs after their first points redemption
    Question
    Among members who have redeemed at least once, does their order frequency and spend actually pick up after that first redemption, or were they already heavy buyers beforehand - i.e. does redeeming look like a cause of engagement or just a marker of customers who were already engaged?
    One row per
    relative period: 'before first redemption' vs 'after first redemption', aggregated across all such 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 population AS (
  SELECT 
    t0.id AS user_id,
    t0.tenant_id AS tenant_id,
    t0.created_at AS user_created_at,
    t1.status AS tenant_status,
    t2.first_redeem_at AS first_redeem_at
  FROM users t0
  JOIN tenants t1 ON t1.id = t0.tenant_id
  JOIN (
    SELECT t3.user_id AS user_id, t3.tenant_id AS tenant_id, MIN(t3.occurred_at) AS first_redeem_at
    FROM loyalty_transactions t3
    WHERE t3.direction = 'burn'
    GROUP BY t3.user_id, t3.tenant_id
  ) t2 ON t2.user_id = t0.id AND t2.tenant_id = t0.tenant_id
  WHERE EXISTS (
    SELECT 1 FROM loyalty_accounts t4
    WHERE t4.user_id = t0.id AND t4.tenant_id = t0.tenant_id
  )
),
base_orders AS (
  SELECT
    t5.id AS order_id,
    t5.tenant_id AS tenant_id,
    t5.user_id AS user_id,
    t5.placed_at AS placed_at,
    t5.status AS status,
    t5.currency_code AS currency_code,
    t5.grand_total AS grand_total,
    CASE WHEN t5.placed_at < t6.first_redeem_at THEN 'before first redemption' ELSE 'after first redemption' END AS period
  FROM orders t5
  JOIN population t6 ON t6.user_id = t5.user_id AND t6.tenant_id = t5.tenant_id
  WHERE t5.placed_at IS NOT NULL
    AND t5.placed_at >= TIMESTAMP '2023-01-01 00:00:00'
    AND t5.placed_at < TIMESTAMP '2026-01-01 00:00:00'
),
periods AS (
  SELECT 'before first redemption' AS period
  UNION ALL
  SELECT 'after first redemption' AS period
),
order_counts AS (
  SELECT
    t7.period AS period,
    COUNT(*) AS total_orders,
    COUNT(DISTINCT t7.user_id) AS distinct_customers
  FROM base_orders t7
  GROUP BY t7.period
),
aov AS (
  SELECT
    t8.period AS period,
    AVG(
      CASE WHEN t8.currency_code = 'EUR' THEN t8.grand_total
           ELSE t8.grand_total / t9.rate
      END
    ) AS avg_order_value_eur
  FROM base_orders t8
  LEFT JOIN exchange_rates t9
    ON t9.base_code = 'EUR'
   AND t9.quote_code = t8.currency_code
   AND t9.as_of = CAST(t8.placed_at AS DATE)
  WHERE t8.status <> 'cancelled'
  GROUP BY t8.period
),
order_refund_flags AS (
  SELECT
    t10.order_id AS order_id,
    t10.period AS period,
    CASE WHEN COALESCE(t11.has_refund, false) OR COALESCE(t12.has_return, false) THEN 1 ELSE 0 END AS is_refunded
  FROM base_orders t10
  LEFT JOIN (
    SELECT t13.order_id AS order_id, bool_or(t13.amount_refunded > 0) AS has_refund
    FROM payments t13
    WHERE t13.order_id IS NOT NULL
    GROUP BY t13.order_id
  ) t11 ON t11.order_id = t10.order_id
  LEFT JOIN (
    SELECT DISTINCT t14.order_id AS order_id, true AS has_return
    FROM order_returns t14
  ) t12 ON t12.order_id = t10.order_id
),
refund_rate AS (
  SELECT
    t15.period AS period,
    100.0 * SUM(t15.is_refunded) / COUNT(*) AS refund_rate_pct
  FROM order_refund_flags t15
  GROUP BY t15.period
),
exposure_before AS (
  SELECT
    'before first redemption' AS period,
    t16.user_id AS user_id,
    GREATEST(
      1,
      CAST(EXTRACT(EPOCH FROM (t16.first_redeem_at - COALESCE(t17.first_order_before, t16.user_created_at))) / 86400 AS BIGINT)
    ) AS exposed_days
  FROM population t16
  LEFT JOIN (
    SELECT t18.user_id AS user_id, MIN(t18.placed_at) AS first_order_before
    FROM base_orders t18
    WHERE t18.period = 'before first redemption'
    GROUP BY t18.user_id
  ) t17 ON t17.user_id = t16.user_id
),
exposure_after AS (
  SELECT
    'after first redemption' AS period,
    t19.user_id AS user_id,
    GREATEST(
      1,
      CAST(
        EXTRACT(
          EPOCH FROM (
            COALESCE(
              CASE WHEN t19.tenant_status = 'churned' THEN t20.last_order_after ELSE TIMESTAMP '2025-12-31 00:00:00' END,
              t19.first_redeem_at
            ) - t19.first_redeem_at
          )
        ) / 86400 AS BIGINT
      )
    ) AS exposed_days
  FROM population t19
  LEFT JOIN (
    SELECT t21.user_id AS user_id, MAX(t21.placed_at) AS last_order_after
    FROM base_orders t21
    WHERE t21.period = 'after first redemption'
    GROUP BY t21.user_id
  ) t20 ON t20.user_id = t19.user_id
),
exposure_union AS (
  SELECT * FROM exposure_before
  UNION ALL
  SELECT * FROM exposure_after
),
exposure_years AS (
  SELECT
    t22.period AS period,
    SUM(t22.exposed_days) / 365.0 AS exposed_customer_years
  FROM exposure_union t22
  GROUP BY t22.period
)
SELECT
  t23.period AS period,
  COALESCE(t24.distinct_customers, 0) AS distinct_customers,
  COALESCE(t24.total_orders, 0) AS total_orders,
  ROUND(COALESCE(t25.avg_order_value_eur, 0), 2) AS avg_order_value_eur,
  ROUND(COALESCE(t24.total_orders, 0) / NULLIF(t27.exposed_customer_years, 0), 2) AS orders_per_customer_per_year,
  ROUND(COALESCE(t26.refund_rate_pct, 0), 2) AS refund_rate_pct
FROM periods t23
LEFT JOIN order_counts t24 ON t24.period = t23.period
LEFT JOIN aov t25 ON t25.period = t23.period
LEFT JOIN refund_rate t26 ON t26.period = t23.period
LEFT JOIN exposure_years t27 ON t27.period = t23.period
ORDER BY CASE WHEN t23.period = 'before first redemption' THEN 0 ELSE 1 END, t23.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. Summary of what the report does: - Grain: one row per period — "before first redemption" vs "after first redemption" — for members (users with a loyalty account who have redeemed at least once). - Split point: each member's own first burn transaction timestamp; their orders (2023‑01‑01 to 2025‑12‑31, any status, placed_at not null) are bucketed before/after it. - Measures: distinct customers, total orders, AOV in EUR (non-cancelled orders, FX via exchange_rates), orders/customer/year (total orders ÷ summed exposed-years), and refund rate (refunded payment or any return record). - Note flagged to the analyst: the spec's "or 2025‑12‑31 if still active" for exposure-after had no direct customer-churn field, so I used tenant status as the proxy (churned tenant → cap at last order; active tenant → right-censor at 2025‑12‑31). Everything else matches the request as given.”

  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
------------------------+--------------------+--------------+---------------------+------------------------------+----------------
before first redemption |                225 |         1228 |              239.47 |                         6.64 |           32.33
after first redemption  |                158 |          705 |              221.73 |                         2.79 |           34.61
(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 q378

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

    • numericsdocs/NUMERICS.md: fraction to whole number with FLOOR (whole days elapsed) instead of a CAST, whose tie rounding differs between engines
    • numericsdocs/NUMERICS.md: fraction to whole number with FLOOR (whole days elapsed) instead of a CAST, whose tie rounding differs between engines
    • numericsdocs/NUMERICS.md: fraction to whole number with FLOOR (whole days elapsed) instead of a CAST, whose tie rounding differs between engines
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
    • numericsdocs/NUMERICS.md: EXTRACT(EPOCH FROM (b - a)) cast to NUMERIC(18,6) (2×)
    • numericsdocs/NUMERICS.md: x / exchange rate cast to NUMERIC(18,6)
    • placeholderbool or
    • placeholderseconds between (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 population AS (  SELECT     t0.id AS user_id,    t0.tenant_id AS tenant_id,    t0.created_at AS user_created_at,    t1.status AS tenant_status,    t2.first_redeem_at AS first_redeem_at  FROM users t0  JOIN tenants t1 ON t1.id = t0.tenant_id  JOIN (    SELECT t3.user_id AS user_id, t3.tenant_id AS tenant_id, MIN(t3.occurred_at) AS first_redeem_at    FROM loyalty_transactions t3    WHERE t3.direction = 'burn'    GROUP BY t3.user_id, t3.tenant_id  ) t2 ON t2.user_id = t0.id AND t2.tenant_id = t0.tenant_id  WHERE EXISTS (    SELECT 1 FROM loyalty_accounts t4    WHERE t4.user_id = t0.id AND t4.tenant_id = t0.tenant_id  )),base_orders AS (  SELECT    t5.id AS order_id,    t5.tenant_id AS tenant_id,    t5.user_id AS user_id,    t5.placed_at AS placed_at,    t5.status AS status,    t5.currency_code AS currency_code,    t5.grand_total AS grand_total,    CASE WHEN t5.placed_at < t6.first_redeem_at THEN 'before first redemption' ELSE 'after first redemption' END AS period  FROM orders t5  JOIN population t6 ON t6.user_id = t5.user_id AND t6.tenant_id = t5.tenant_id  WHERE t5.placed_at IS NOT NULL    AND t5.placed_at >= TIMESTAMP '2023-01-01 00:00:00'    AND t5.placed_at < TIMESTAMP '2026-01-01 00:00:00'),periods AS (  SELECT 'before first redemption' AS period  UNION ALL  SELECT 'after first redemption' AS period),order_counts AS (  SELECT    t7.period AS period,    COUNT(*) AS total_orders,    COUNT(DISTINCT t7.user_id) AS distinct_customers  FROM base_orders t7  GROUP BY t7.period),aov AS (  SELECT    t8.period AS period,    AVG(      CASE WHEN t8.currency_code = 'EUR' THEN t8.grand_total           ELSE t8.grand_total / t9.rate           ELSE CAST(t8.grand_total / t9.rate AS NUMERIC(18,6))      END    ) AS avg_order_value_eur  FROM base_orders t8  LEFT JOIN exchange_rates t9    ON t9.base_code = 'EUR'   AND t9.quote_code = t8.currency_code   AND t9.as_of = CAST(t8.placed_at AS DATE)  WHERE t8.status <> 'cancelled'  GROUP BY t8.period),order_refund_flags AS (  SELECT    t10.order_id AS order_id,    t10.period AS period,    CASE WHEN COALESCE(t11.has_refund, false) OR COALESCE(t12.has_return, false) THEN 1 ELSE 0 END AS is_refunded  FROM base_orders t10  LEFT JOIN (    SELECT t13.order_id AS order_id, bool_or(t13.amount_refunded > 0) AS has_refund    SELECT t13.order_id AS order_id, {{bool_or(t13.amount_refunded > 0)}} AS has_refund    FROM payments t13    WHERE t13.order_id IS NOT NULL    GROUP BY t13.order_id  ) t11 ON t11.order_id = t10.order_id  LEFT JOIN (    SELECT DISTINCT t14.order_id AS order_id, true AS has_return    FROM order_returns t14  ) t12 ON t12.order_id = t10.order_id),refund_rate AS (  SELECT    t15.period AS period,    100.0 * SUM(t15.is_refunded) / COUNT(*) AS refund_rate_pct  FROM order_refund_flags t15  GROUP BY t15.period),exposure_before AS (  SELECT    'before first redemption' AS period,    t16.user_id AS user_id,    GREATEST(      1,      CAST(EXTRACT(EPOCH FROM (t16.first_redeem_at - COALESCE(t17.first_order_before, t16.user_created_at))) / 86400 AS BIGINT)      FLOOR({{seconds_between(COALESCE(t17.first_order_before, t16.user_created_at), t16.first_redeem_at)}} / 86400)    ) AS exposed_days  FROM population t16  LEFT JOIN (    SELECT t18.user_id AS user_id, MIN(t18.placed_at) AS first_order_before    FROM base_orders t18    WHERE t18.period = 'before first redemption'    GROUP BY t18.user_id  ) t17 ON t17.user_id = t16.user_id),exposure_after AS (  SELECT    'after first redemption' AS period,    t19.user_id AS user_id,    GREATEST(      1,      CAST(        EXTRACT(          EPOCH FROM (            COALESCE(      FLOOR(        {{seconds_between(t19.first_redeem_at, COALESCE(              CASE WHEN t19.tenant_status = 'churned' THEN t20.last_order_after ELSE TIMESTAMP '2025-12-31 00:00:00' END,              t19.first_redeem_at            ) - t19.first_redeem_at          )        ) / 86400 AS BIGINT            ))}} / 86400      )    ) AS exposed_days  FROM population t19  LEFT JOIN (    SELECT t21.user_id AS user_id, MAX(t21.placed_at) AS last_order_after    FROM base_orders t21    WHERE t21.period = 'after first redemption'    GROUP BY t21.user_id  ) t20 ON t20.user_id = t19.user_id),exposure_union AS (  SELECT * FROM exposure_before  UNION ALL  SELECT * FROM exposure_after),exposure_years AS (  SELECT    t22.period AS period,    SUM(t22.exposed_days) / 365.0 AS exposed_customer_years  FROM exposure_union t22  GROUP BY t22.period)SELECT  t23.period AS period,  COALESCE(t24.distinct_customers, 0) AS distinct_customers,  COALESCE(t24.total_orders, 0) AS total_orders,  ROUND(COALESCE(t25.avg_order_value_eur, 0), 2) AS avg_order_value_eur,  ROUND(COALESCE(t24.total_orders, 0) / NULLIF(t27.exposed_customer_years, 0), 2) AS orders_per_customer_per_year,  ROUND(COALESCE(t26.refund_rate_pct, 0), 2) AS refund_rate_pct  COALESCE(t25.avg_order_value_eur, 0) AS avg_order_value_eur,  COALESCE(t24.total_orders, 0) / NULLIF(t27.exposed_customer_years, 0) AS orders_per_customer_per_year,  COALESCE(t26.refund_rate_pct, 0) AS refund_rate_pctFROM periods t23LEFT JOIN order_counts t24 ON t24.period = t23.periodLEFT JOIN aov t25 ON t25.period = t23.periodLEFT JOIN refund_rate t26 ON t26.period = t23.periodLEFT JOIN exposure_years t27 ON t27.period = t23.periodORDER BY CASE WHEN t23.period = 'before first redemption' THEN 0 ELSE 1 END, t23.periodORDER BY CASE WHEN t23.period = 'before first redemption' THEN 0 ELSE 1 END NULLS LAST, t23.period NULLS LAST

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

    WITH population AS (
  SELECT 
    t0.id AS user_id,
    t0.tenant_id AS tenant_id,
    t0.created_at AS user_created_at,
    t1.status AS tenant_status,
    t2.first_redeem_at AS first_redeem_at
  FROM users t0
  JOIN tenants t1 ON t1.id = t0.tenant_id
  JOIN (
    SELECT t3.user_id AS user_id, t3.tenant_id AS tenant_id, MIN(t3.occurred_at) AS first_redeem_at
    FROM loyalty_transactions t3
    WHERE t3.direction = 'burn'
    GROUP BY t3.user_id, t3.tenant_id
  ) t2 ON t2.user_id = t0.id AND t2.tenant_id = t0.tenant_id
  WHERE EXISTS (
    SELECT 1 FROM loyalty_accounts t4
    WHERE t4.user_id = t0.id AND t4.tenant_id = t0.tenant_id
  )
),
base_orders AS (
  SELECT
    t5.id AS order_id,
    t5.tenant_id AS tenant_id,
    t5.user_id AS user_id,
    t5.placed_at AS placed_at,
    t5.status AS status,
    t5.currency_code AS currency_code,
    t5.grand_total AS grand_total,
    CASE WHEN t5.placed_at < t6.first_redeem_at THEN 'before first redemption' ELSE 'after first redemption' END AS period
  FROM orders t5
  JOIN population t6 ON t6.user_id = t5.user_id AND t6.tenant_id = t5.tenant_id
  WHERE t5.placed_at IS NOT NULL
    AND t5.placed_at >= TIMESTAMP '2023-01-01 00:00:00'
    AND t5.placed_at < TIMESTAMP '2026-01-01 00:00:00'
),
periods AS (
  SELECT 'before first redemption' AS period
  UNION ALL
  SELECT 'after first redemption' AS period
),
order_counts AS (
  SELECT
    t7.period AS period,
    COUNT(*) AS total_orders,
    COUNT(DISTINCT t7.user_id) AS distinct_customers
  FROM base_orders t7
  GROUP BY t7.period
),
aov AS (
  SELECT
    t8.period AS period,
    AVG(
      CASE WHEN t8.currency_code = 'EUR' THEN t8.grand_total
           ELSE CAST(t8.grand_total / t9.rate AS NUMERIC(18,6))
      END
    ) AS avg_order_value_eur
  FROM base_orders t8
  LEFT JOIN exchange_rates t9
    ON t9.base_code = 'EUR'
   AND t9.quote_code = t8.currency_code
   AND t9.as_of = CAST(t8.placed_at AS DATE)
  WHERE t8.status <> 'cancelled'
  GROUP BY t8.period
),
order_refund_flags AS (
  SELECT
    t10.order_id AS order_id,
    t10.period AS period,
    CASE WHEN COALESCE(t11.has_refund, false) OR COALESCE(t12.has_return, false) THEN 1 ELSE 0 END AS is_refunded
  FROM base_orders t10
  LEFT JOIN (
    SELECT t13.order_id AS order_id, BOOL_OR(t13.amount_refunded > 0) AS has_refund
    FROM payments t13
    WHERE t13.order_id IS NOT NULL
    GROUP BY t13.order_id
  ) t11 ON t11.order_id = t10.order_id
  LEFT JOIN (
    SELECT DISTINCT t14.order_id AS order_id, true AS has_return
    FROM order_returns t14
  ) t12 ON t12.order_id = t10.order_id
),
refund_rate AS (
  SELECT
    t15.period AS period,
    100.0 * SUM(t15.is_refunded) / COUNT(*) AS refund_rate_pct
  FROM order_refund_flags t15
  GROUP BY t15.period
),
exposure_before AS (
  SELECT
    'before first redemption' AS period,
    t16.user_id AS user_id,
    GREATEST(
      1,
      FLOOR(CAST(EXTRACT(EPOCH FROM (t16.first_redeem_at - COALESCE(t17.first_order_before, t16.user_created_at))) AS NUMERIC(18,6)) / 86400)
    ) AS exposed_days
  FROM population t16
  LEFT JOIN (
    SELECT t18.user_id AS user_id, MIN(t18.placed_at) AS first_order_before
    FROM base_orders t18
    WHERE t18.period = 'before first redemption'
    GROUP BY t18.user_id
  ) t17 ON t17.user_id = t16.user_id
),
exposure_after AS (
  SELECT
    'after first redemption' AS period,
    t19.user_id AS user_id,
    GREATEST(
      1,
      FLOOR(
        CAST(EXTRACT(EPOCH FROM (COALESCE(
              CASE WHEN t19.tenant_status = 'churned' THEN t20.last_order_after ELSE TIMESTAMP '2025-12-31 00:00:00' END,
              t19.first_redeem_at
            ) - t19.first_redeem_at)) AS NUMERIC(18,6)) / 86400
      )
    ) AS exposed_days
  FROM population t19
  LEFT JOIN (
    SELECT t21.user_id AS user_id, MAX(t21.placed_at) AS last_order_after
    FROM base_orders t21
    WHERE t21.period = 'after first redemption'
    GROUP BY t21.user_id
  ) t20 ON t20.user_id = t19.user_id
),
exposure_union AS (
  SELECT * FROM exposure_before
  UNION ALL
  SELECT * FROM exposure_after
),
exposure_years AS (
  SELECT
    t22.period AS period,
    SUM(t22.exposed_days) / 365.0 AS exposed_customer_years
  FROM exposure_union t22
  GROUP BY t22.period
)
SELECT
  t23.period AS period,
  COALESCE(t24.distinct_customers, 0) AS distinct_customers,
  COALESCE(t24.total_orders, 0) AS total_orders,
  COALESCE(t25.avg_order_value_eur, 0) AS avg_order_value_eur,
  COALESCE(t24.total_orders, 0) / NULLIF(t27.exposed_customer_years, 0) AS orders_per_customer_per_year,
  COALESCE(t26.refund_rate_pct, 0) AS refund_rate_pct
FROM periods t23
LEFT JOIN order_counts t24 ON t24.period = t23.period
LEFT JOIN aov t25 ON t25.period = t23.period
LEFT JOIN refund_rate t26 ON t26.period = t23.period
LEFT JOIN exposure_years t27 ON t27.period = t23.period
ORDER BY CASE WHEN t23.period = 'before first redemption' THEN 0 ELSE 1 END NULLS LAST, t23.period NULLS LAST
    100 ms1 s10 sHyper498 msDuckDB495 msGizmoSQL496 msclickhouse_brew1.13 sFirebolterrorDataFusion2.02 sClickHouseerror
    • Firebolt{ "errors": [ { "description": "Line 55, Column 37: 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": "427be81c-2014-4e4e-958d-d62c0b9831ed", "query_lab
    • ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table18.grand_total Decimal(14, 4) Decimal64(size = 0), __table28.rate Nullable(Decimal(12, 6)) Nullable(size = 0, Decimal64(size = 0), UInt8(size = 0)). (ARGUMENT_OUT_OF_BOUND

    Scale factor 1000, c8a.24xlarge, after the last load. q378 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: Member order behavior before vs after their first points redemption
Question: Among members who have redeemed at least once, does their order frequency and spend actually pick up after that first redemption, or were they already heavy buyers beforehand - i.e. does redeeming look like a cause of engagement or just a marker of customers who were already engaged?
Grain: one row per relative period: 'before first redemption' vs 'after first redemption', aggregated across all such 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 loyalty_accounts row who have at least one 'burn'-direction loyalty_transactions row. For each such user, find their first redemption's occurred_at timestamp. Split their orders (any status, placed_at not null, 2023-01-01 to 2025-12-31) into 'before first redemption' (placed_at before that timestamp) and 'after first redemption' (placed_at on or after that timestamp). total_orders and distinct_customers are 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 the 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 exposed days in that period divided by 365); exposed days before = first-redemption timestamp minus the customer's first order placed_at (or users.created_at if no earlier order); exposed days after = (customer's last order placed_at, or 2025-12-31 if still active) minus the first-redemption timestamp; floor exposed days at 1. Order rows: 'before first redemption' first, then 'after first redemption'.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Bea calls dry_run
      WITH population AS (
  SELECT 
    t0.id AS user_id,
    t0.tenant_id AS tenant_id,
    t0.created_at AS user_created_at,
    t1.status AS tenant_status,
    t2.first_redeem_at AS first_redeem_at
  FROM users t0
  JOIN tenants t1 ON t1.id = t0.tenant_id
  JOIN (
    SELECT t3.user_id AS user_id, t3.tenant_id AS tenant_id, MIN(t3.occurred_at) AS first_redeem_at
    FROM loyalty_transactions t3
    WHERE t3.direction = 'burn'
    GROUP BY t3.user_id, t3.tenant_id
  ) t2 ON t2.user_id = t0.id AND t2.tenant_id = t0.tenant_id
  WHERE EXISTS (
    SELECT 1 FROM loyalty_accounts t4
    WHERE t4.user_id = t0.id AND t4.tenant_id = t0.tenant_id
  )
),
base_orders AS (
  SELECT
    t5.id AS order_id,
    t5.tenant_id AS tenant_id,
    t5.user_id AS user_id,
    t5.placed_at AS placed_at,
    t5.status AS status,
    t5.currency_code AS currency_code,
    t5.grand_total AS grand_total,
    CASE WHEN t5.placed_at < t6.first_redeem_at THEN 'before first redemption' ELSE 'after first redemption' END AS period
  FROM orders t5
  JOIN population t6 ON t6.user_id = t5.user_id AND t6.tenant_id = t5.tenant_id
  WHERE t5.placed_at IS NOT NULL
    AND t5.placed_at >= TIMESTAMP '2023-01-01 00:00:00'
    AND t5.placed_at < TIMESTAMP '2026-01-01 00:00:00'
),
periods AS (
  SELECT 'before first redemption' AS period
  UNION ALL
  SELECT 'after first redemption' AS period
),
order_counts AS (
  SELECT
    t7.period AS period,
    COUNT(*) AS total_orders,
    COUNT(DISTINCT t7.user_id) AS distinct_customers
  FROM base_orders t7
  GROUP BY t7.period
),
aov AS (
  SELECT
    t8.period AS period,
    AVG(
      CASE WHEN t8.currency_code = 'EUR' THEN t8.grand_total
           ELSE t8.grand_total / t9.rate
      END
    ) AS avg_order_value_eur
  FROM base_orders t8
  LEFT JOIN exchange_rates t9
    ON t9.base_code = 'EUR'
   AND t9.quote_code = t8.currency_code
   AND t9.as_of = CAST(t8.placed_at AS DATE)
  WHERE t8.status <> 'cancelled'
  GROUP BY t8.period
),
order_refund_flags AS (
  SELECT
    t10.order_id AS order_id,
    t10.period AS period,
    CASE WHEN COALESCE(t11.has_refund, false) OR COALESCE(t12.has_return, false) THEN 1 ELSE 0 END AS is_refunded
  FROM base_orders t10
  LEFT JOIN (
    SELECT t13.order_id AS order_id, bool_or(t13.amount_refunded > 0) AS has_refund
    FROM payments t13
    WHERE t13.order_id IS NOT NULL
    GROUP BY t13.order_id
  ) t11 ON t11.order_id = t10.order_id
  LEFT JOIN (
    SELECT DISTINCT t14.order_id AS order_id, true AS has_return
    FROM order_returns t14
  ) t12 ON t12.order_id = t10.order_id
),
refund_rate AS (
  SELECT
    t15.period AS period,
    100.0 * SUM(t15.is_refunded) / COUNT(*) AS refund_rate_pct
  FROM order_refund_flags t15
  GROUP BY t15.period
),
exposure_before AS (
  SELECT
    'before first redemption' AS period,
    t16.user_id AS user_id,
    GREATEST(
      1,
      CAST(EXTRACT(EPOCH FROM (t16.first_redeem_at - COALESCE(t17.first_order_before, t16.user_created_at))) / 86400 AS BIGINT)
    ) AS exposed_days
  FROM population t16
  LEFT JOIN (
    SELECT t18.user_id AS user_id, MIN(t18.placed_at) AS first_order_before
    FROM base_orders t18
    WHERE t18.period = 'before first redemption'
    GROUP BY t18.user_id
  ) t17 ON t17.user_id = t16.user_id
),
exposure_after AS (
  SELECT
    'after first redemption' AS period,
    t19.user_id AS user_id,
    GREATEST(
      1,
      CAST(
        EXTRACT(
          EPOCH FROM (
            COALESCE(
              CASE WHEN t19.tenant_status = 'churned' THEN t20.last_order_after ELSE TIMESTAMP '2025-12-31 00:00:00' END,
              t19.first_redeem_at
            ) - t19.first_redeem_at
          )
        ) / 86400 AS BIGINT
      )
    ) AS exposed_days
  FROM population t19
  LEFT JOIN (
    SELECT t21.user_id AS user_id, MAX(t21.placed_at) AS last_order_after
    FROM base_orders t21
    WHERE t21.period = 'after first redemption'
    GROUP BY t21.user_id
  ) t20 ON t20.user_id = t19.user_id
),
exposure_union AS (
  SELECT * FROM exposure_before
  UNION ALL
  SELECT * FROM exposure_after
),
exposure_years AS (
  SELECT
    t22.period AS period,
    SUM(t22.exposed_days) / 365.0 AS exposed_customer_years
  FROM exposure_union t22
  GROUP BY t22.period
)
SELECT
  t23.period AS period,
  COALESCE(t24.distinct_customers, 0) AS distinct_customers,
  COALESCE(t24.total_orders, 0) AS total_orders,
  ROUND(COALESCE(t25.avg_order_value_eur, 0), 2) AS avg_order_value_eur,
  ROUND(COALESCE(t24.total_orders, 0) / NULLIF(t27.exposed_customer_years, 0), 2) AS orders_per_customer_per_year,
  ROUND(COALESCE(t26.refund_rate_pct, 0), 2) AS refund_rate_pct
FROM periods t23
LEFT JOIN order_counts t24 ON t24.period = t23.period
LEFT JOIN aov t25 ON t25.period = t23.period
LEFT JOIN refund_rate t26 ON t26.period = t23.period
LEFT JOIN exposure_years t27 ON t27.period = t23.period
ORDER BY CASE WHEN t23.period = 'before first redemption' THEN 0 ELSE 1 END, t23.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 population AS (
  SELECT 
    t0.id AS user_id,
    t0.tenant_id AS tenant_id,
    t0.created_at AS user_created_at,
    t1.status AS tenant_status,
    t2.first_redeem_at AS first_redeem_at
  FROM users t0
  JOIN tenants t1 ON t1.id = t0.tenant_id
  JOIN (
    SELECT t3.user_id AS user_id, t3.tenant_id AS tenant_id, MIN(t3.occurred_at) AS first_redeem_at
    FROM loyalty_transactions t3
    WHERE t3.direction = 'burn'
    GROUP BY t3.user_id, t3.tenant_id
  ) t2 ON t2.user_id = t0.id AND t2.tenant_id = t0.tenant_id
  WHERE EXISTS (
    SELECT 1 FROM loyalty_accounts t4
    WHERE t4.user_id = t0.id AND t4.tenant_id = t0.tenant_id
  )
),
base_orders AS (
  SELECT
    t5.id AS order_id,
    t5.tenant_id AS tenant_id,
    t5.user_id AS user_id,
    t5.placed_at AS placed_at,
    t5.status AS status,
    t5.currency_code AS currency_code,
    t5.grand_total AS grand_total,
    CASE WHEN t5.placed_at < t6.first_redeem_at THEN 'before first redemption' ELSE 'after first redemption' END AS period
  FROM orders t5
  JOIN population t6 ON t6.user_id = t5.user_id AND t6.tenant_id = t5.tenant_id
  WHERE t5.placed_at IS NOT NULL
    AND t5.placed_at >= TIMESTAMP '2023-01-01 00:00:00'
    AND t5.placed_at < TIMESTAMP '2026-01-01 00:00:00'
),
periods AS (
  SELECT 'before first redemption' AS period
  UNION ALL
  SELECT 'after first redemption' AS period
),
order_counts AS (
  SELECT
    t7.period AS period,
    COUNT(*) AS total_orders,
    COUNT(DISTINCT t7.user_id) AS distinct_customers
  FROM base_orders t7
  GROUP BY t7.period
),
aov AS (
  SELECT
    t8.period AS period,
    AVG(
      CASE WHEN t8.currency_code = 'EUR' THEN t8.grand_total
           ELSE t8.grand_total / t9.rate
      END
    ) AS avg_order_value_eur
  FROM base_orders t8
  LEFT JOIN exchange_rates t9
    ON t9.base_code = 'EUR'
   AND t9.quote_code = t8.currency_code
   AND t9.as_of = CAST(t8.placed_at AS DATE)
  WHERE t8.status <> 'cancelled'
  GROUP BY t8.period
),
order_refund_flags AS (
  SELECT
    t10.order_id AS order_id,
    t10.period AS period,
    CASE WHEN COALESCE(t11.has_refund, false) OR COALESCE(t12.has_return, false) THEN 1 ELSE 0 END AS is_refunded
  FROM base_orders t10
  LEFT JOIN (
    SELECT t13.order_id AS order_id, bool_or(t13.amount_refunded > 0) AS has_refund
    FROM payments t13
    WHERE t13.order_id IS NOT NULL
    GROUP BY t13.order_id
  ) t11 ON t11.order_id = t10.order_id
  LEFT JOIN (
    SELECT DISTINCT t14.order_id AS order_id, true AS has_return
    FROM order_returns t14
  ) t12 ON t12.order_id = t10.order_id
),
refund_rate AS (
  SELECT
    t15.period AS period,
    100.0 * SUM(t15.is_refunded) / COUNT(*) AS refund_rate_pct
  FROM order_refund_flags t15
  GROUP BY t15.period
),
exposure_before AS (
  SELECT
    'before first redemption' AS period,
    t16.user_id AS user_id,
    GREATEST(
      1,
      CAST(EXTRACT(EPOCH FROM (t16.first_redeem_at - COALESCE(t17.first_order_before, t16.user_created_at))) / 86400 AS BIGINT)
    ) AS exposed_days
  FROM population t16
  LEFT JOIN (
    SELECT t18.user_id AS user_id, MIN(t18.placed_at) AS first_order_before
    FROM base_orders t18
    WHERE t18.period = 'before first redemption'
    GROUP BY t18.user_id
  ) t17 ON t17.user_id = t16.user_id
),
exposure_after AS (
  SELECT
    'after first redemption' AS period,
    t19.user_id AS user_id,
    GREATEST(
      1,
      CAST(
        EXTRACT(
          EPOCH FROM (
            COALESCE(
              CASE WHEN t19.tenant_status = 'churned' THEN t20.last_order_after ELSE TIMESTAMP '2025-12-31 00:00:00' END,
              t19.first_redeem_at
            ) - t19.first_redeem_at
          )
        ) / 86400 AS BIGINT
      )
    ) AS exposed_days
  FROM population t19
  LEFT JOIN (
    SELECT t21.user_id AS user_id, MAX(t21.placed_at) AS last_order_after
    FROM base_orders t21
    WHERE t21.period = 'after first redemption'
    GROUP BY t21.user_id
  ) t20 ON t20.user_id = t19.user_id
),
exposure_union AS (
  SELECT * FROM exposure_before
  UNION ALL
  SELECT * FROM exposure_after
),
exposure_years AS (
  SELECT
    t22.period AS period,
    SUM(t22.exposed_days) / 365.0 AS exposed_customer_years
  FROM exposure_union t22
  GROUP BY t22.period
)
SELECT
  t23.period AS period,
  COALESCE(t24.distinct_customers, 0) AS distinct_customers,
  COALESCE(t24.total_orders, 0) AS total_orders,
  ROUND(COALESCE(t25.avg_order_value_eur, 0), 2) AS avg_order_value_eur,
  ROUND(COALESCE(t24.total_orders, 0) / NULLIF(t27.exposed_customer_years, 0), 2) AS orders_per_customer_per_year,
  ROUND(COALESCE(t26.refund_rate_pct, 0), 2) AS refund_rate_pct
FROM periods t23
LEFT JOIN order_counts t24 ON t24.period = t23.period
LEFT JOIN aov t25 ON t25.period = t23.period
LEFT JOIN refund_rate t26 ON t26.period = t23.period
LEFT JOIN exposure_years t27 ON t27.period = t23.period
ORDER BY CASE WHEN t23.period = 'before first redemption' THEN 0 ELSE 1 END, t23.period
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • BeaSubmitted. Summary of what the report does: - **Grain:** one row per period — "before first redemption" vs "after first redemption" — for members (users with a loyalty account who have redeemed at least once). - **Split point:** each member's own first `burn` transaction timestamp; their orders (2023‑01‑01 to 2025‑12‑31, any status, `placed_at` not null) are bucketed before/after it. - **Measures:** distinct customers, total orders, AOV in EUR (non-cancelled orders, FX via `exchange_rates`), orders/customer/year (total orders ÷ summed exposed-years), and refund rate (refunded payment or any return record). - **Note flagged to the analyst:** the spec's "or 2025‑12‑31 if still active" for exposure-after had no direct customer-churn field, so I used tenant status as the proxy (churned tenant → cap at last order; active tenant → right-censor at 2025‑12‑31). Everything else matches the request as given.

    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 (316 KB) · Ingrid Solberg's transcript (906 KB) · Aisha Rahman's transcript (261 KB) · the night's plan (7 KB) · the night's reports (5 KB) · the week's memo (3 KB)