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

Apparel: never-redeemed vs ever-redeemed members vs non-members, by tenure

“Once we separate loyalty members into those who have actually redeemed a point at least once versus those who never have, do the engaged ones match or beat non-members of similar tenure, while the disengaged majority lags behind?”

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 SQLGraceWrites tidy, layered CTEs with descriptive names, one step per CTE, then a final select.

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

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
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
Ingrid Solberg plans the night
The analyst · week 2, night 3, request 1 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?
Apparel: never-redeemed vs ever-redeemed members vs non-members, by tenure
Question
Once we separate loyalty members into those who have actually redeemed a point at least once versus those who never have, do the engaged ones match or beat non-members of similar tenure, while the disengaged majority lags behind?
One row per
combination of account-tenure band and customer group: 'never-redeemed member', 'ever-redeemed member', 'non-member', restricted to the Apparel and Footwear vertical
Columns
- tenure_band
- group
- customer_count
- avg_lifetime_orders
- avg_lifetime_revenue_eur
- avg_order_value_eur
- refund_rate_pct

Grace 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 apparel_tenants AS (
  SELECT id AS tenant_id
  FROM tenants
  WHERE industry = 'Apparel & Footwear'
),
eligible_users AS (
  SELECT DISTINCT u.id AS user_id, u.tenant_id, u.created_at
  FROM users u
  JOIN apparel_tenants t ON t.tenant_id = u.tenant_id
  WHERE u.is_staff = false
    AND EXISTS (
      SELECT 1 FROM orders o
      WHERE o.user_id = u.id
        AND o.tenant_id = u.tenant_id
        AND o.placed_at >= TIMESTAMP '2023-01-01'
        AND o.placed_at < TIMESTAMP '2026-01-01'
    )
),
loyalty_group AS (
  SELECT
    eu.user_id,
    CASE
      WHEN la.user_id IS NULL THEN 'non-member'
      WHEN lb.user_id IS NOT NULL THEN 'ever-redeemed member'
      ELSE 'never-redeemed member'
    END AS customer_group
  FROM eligible_users eu
  LEFT JOIN (SELECT DISTINCT user_id FROM loyalty_accounts) la
    ON la.user_id = eu.user_id
  LEFT JOIN (SELECT DISTINCT user_id FROM loyalty_transactions WHERE direction = 'burn') lb
    ON lb.user_id = eu.user_id
),
tenure AS (
  SELECT
    eu.user_id,
    CASE
      WHEN (DATE '2025-12-31' - eu.created_at::date) < 182 THEN 'under 6 months'
      WHEN (DATE '2025-12-31' - eu.created_at::date) < 365 THEN '6 to 12 months'
      WHEN (DATE '2025-12-31' - eu.created_at::date) < 730 THEN '12 to 24 months'
      ELSE 'over 24 months'
    END AS tenure_band
  FROM eligible_users eu
),
orders_scoped AS (
  SELECT
    o.id AS order_id,
    o.user_id,
    o.status,
    CASE WHEN o.status <> 'cancelled' THEN
      CASE WHEN o.currency_code = 'EUR' THEN o.grand_total
           ELSE o.grand_total / er.rate END
    ELSE NULL END AS revenue_eur,
    CASE WHEN o.status <> 'cancelled' THEN 1 ELSE 0 END AS is_noncancelled,
    CASE WHEN EXISTS (SELECT 1 FROM payments p WHERE p.order_id = o.id AND p.amount_refunded > 0)
         OR EXISTS (SELECT 1 FROM order_returns r WHERE r.order_id = o.id)
    THEN 1 ELSE 0 END AS is_refunded
  FROM orders o
  JOIN eligible_users eu ON eu.user_id = o.user_id AND eu.tenant_id = o.tenant_id
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
    AND er.quote_code = o.currency_code
    AND er.as_of = o.placed_at::date
),
user_agg AS (
  SELECT
    user_id,
    COUNT(*) AS lifetime_orders,
    SUM(is_noncancelled) AS noncancelled_orders,
    SUM(COALESCE(revenue_eur, 0)) AS lifetime_revenue_eur,
    SUM(is_refunded) AS refunded_orders
  FROM orders_scoped
  GROUP BY user_id
),
user_level AS (
  SELECT
    t.tenure_band,
    lg.customer_group,
    ua.lifetime_orders,
    ua.noncancelled_orders,
    ua.lifetime_revenue_eur,
    ua.refunded_orders
  FROM eligible_users eu
  JOIN tenure t ON t.user_id = eu.user_id
  JOIN loyalty_group lg ON lg.user_id = eu.user_id
  JOIN user_agg ua ON ua.user_id = eu.user_id
)
SELECT
  tenure_band,
  customer_group AS "group",
  COUNT(*) AS customer_count,
  ROUND(AVG(lifetime_orders)::numeric, 2) AS avg_lifetime_orders,
  ROUND(AVG(lifetime_revenue_eur)::numeric, 2) AS avg_lifetime_revenue_eur,
  ROUND((SUM(lifetime_revenue_eur) / NULLIF(SUM(noncancelled_orders), 0))::numeric, 2) AS avg_order_value_eur,
  ROUND((100.0 * SUM(refunded_orders) / NULLIF(SUM(lifetime_orders), 0))::numeric, 2) AS refund_rate_pct
FROM user_level
GROUP BY tenure_band, customer_group
ORDER BY
  CASE tenure_band
    WHEN 'under 6 months' THEN 1
    WHEN '6 to 12 months' THEN 2
    WHEN '12 to 24 months' THEN 3
    WHEN 'over 24 months' THEN 4
  END,
  CASE customer_group
    WHEN 'non-member' THEN 1
    WHEN 'never-redeemed member' THEN 2
    WHEN 'ever-redeemed member' THEN 3
  END

“Query compiles with exactly the requested grain and 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 key choices made explicit in the note to Ingrid: - Tenure bands: computed from day-count differences (182/365/730 days) between users.created_at and 2025-12-31, as a standard proxy for 6/12/24-month boundaries. - Population: non-staff users in Apparel & Footwear tenants with at least one order (any status) placed between 2023-01-01 and 2025-12-31. - Grouping: non-member (no loyalty_accounts row) vs never-redeemed (has account, no burn transaction) vs ever-redeemed (has account, at least one burn transaction). - Revenue/AOV: non-cancelled orders only, converted to EUR via exchange_rates keyed on placed_at date; AOV is the group's total revenue divided by total non-cancelled order count (not an average of per-customer ratios). - Lifetime orders & refund rate: computed over all orders regardless of status; refund = any payments.amount_refunded > 0 or an order_returns row for that order.”

The report comes back overnight

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

tenure_band     | group                 | customer_count | avg_lifetime_orders | avg_lifetime_revenue_eur | avg_order_value_eur | refund_rate_pct
----------------+-----------------------+----------------+---------------------+--------------------------+---------------------+----------------
under 6 months  | non-member            |            634 |                1.80 |                   350.18 |              202.94 |           36.87
under 6 months  | never-redeemed member |            184 |                1.72 |                   296.74 |              184.46 |           33.86
6 to 12 months  | non-member            |            483 |                2.75 |                   495.71 |              188.08 |           37.33
6 to 12 months  | never-redeemed member |            148 |                2.27 |                   368.43 |              172.56 |           34.52
6 to 12 months  | ever-redeemed member  |              6 |                5.50 |                  1430.22 |              268.17 |           75.76
12 to 24 months | non-member            |           1019 |                4.05 |                   750.15 |              193.62 |           38.76
12 to 24 months | never-redeemed member |            216 |                3.26 |                   468.46 |              153.78 |           39.97
12 to 24 months | ever-redeemed member  |             46 |                8.35 |                  1434.80 |              180.33 |           38.02
over 24 months  | non-member            |           1275 |                5.33 |                  1012.97 |              199.31 |           39.50
over 24 months  | never-redeemed member |            261 |                3.43 |                   495.12 |              151.50 |           38.17
over 24 months  | ever-redeemed member  |             92 |               10.01 |                  1872.74 |              193.59 |           39.09
(11 rows)

What came of it
The memo and the queries that followed; links found by title, so inferred
Ingrid Solberg's memo to Aisha Rahman at the end of the week:
- 1. Engaged members (those who've ever redeemed a point) genuinely outperform similar non-members — this is the verdict you asked for. Within Apparel, tenure-controlled: at 24+ months, "ever-redeemed" members average 10.0 orders and €1,873 revenue vs. 5.3 orders and €1,013 for non-members — with refund rates essentially tied (39.1% vs 39.5%). The drag comes entirely from the 83% of members who've never redeemed a single point — they trail both engaged members and non-members at every tenure band (12–24mo: €468 revenue vs. €750 for non-members). (Reports: "Apparel: never-redeemed vs ever-redeemed vs non-members, by tenure"; confirmed platform-wide in the same-named report without the vertical filter — identical pattern, e.g. 24+mo: 9.7 orders/€1,844 engaged vs. 7.9/€1,544 non-member vs. 3.3/€511 disengaged.)
Later queries that build on this report:
- q380Platform-wide: never-redeemed vs ever-redeemed members vs non-members, by tenure
It builds on:
- q375What distinguishes higher-revenue loyalty members from the rest
- q370Apparel and Footwear: loyalty member vs non-member behavior by tenure

It becomes benchmark query q376

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

spellingcast (8×)
numericsdocs/NUMERICS.md: rounding removed; results keep full precision; bare NUMERIC cast that only served the rounding removed; AVG over an integer computed as a decimal: AVG(CAST(x AS NUMERIC(18,6)))
numericsdocs/NUMERICS.md: x / exchange rate cast to NUMERIC(18,6)
made explicita selected t.col gets AS col, its standard name made explicit (12×)
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 apparel_tenants AS (  SELECT id AS tenant_id  FROM tenants  WHERE industry = 'Apparel & Footwear'),eligible_users AS (  SELECT DISTINCT u.id AS user_id, u.tenant_id, u.created_at  SELECT DISTINCT u.id AS user_id, u.tenant_id AS tenant_id, u.created_at AS created_at  FROM users u  JOIN apparel_tenants t ON t.tenant_id = u.tenant_id  WHERE u.is_staff = false    AND EXISTS (      SELECT 1 FROM orders o      WHERE o.user_id = u.id        AND o.tenant_id = u.tenant_id        AND o.placed_at >= TIMESTAMP '2023-01-01'        AND o.placed_at < TIMESTAMP '2026-01-01'    )),loyalty_group AS (  SELECT    eu.user_id,    eu.user_id AS user_id,    CASE      WHEN la.user_id IS NULL THEN 'non-member'      WHEN lb.user_id IS NOT NULL THEN 'ever-redeemed member'      ELSE 'never-redeemed member'    END AS customer_group  FROM eligible_users eu  LEFT JOIN (SELECT DISTINCT user_id FROM loyalty_accounts) la    ON la.user_id = eu.user_id  LEFT JOIN (SELECT DISTINCT user_id FROM loyalty_transactions WHERE direction = 'burn') lb    ON lb.user_id = eu.user_id),tenure AS (  SELECT    eu.user_id,    eu.user_id AS user_id,    CASE      WHEN (DATE '2025-12-31' - eu.created_at::date) < 182 THEN 'under 6 months'      WHEN (DATE '2025-12-31' - eu.created_at::date) < 365 THEN '6 to 12 months'      WHEN (DATE '2025-12-31' - eu.created_at::date) < 730 THEN '12 to 24 months'      WHEN (DATE '2025-12-31' - CAST(eu.created_at AS date)) < 182 THEN 'under 6 months'      WHEN (DATE '2025-12-31' - CAST(eu.created_at AS date)) < 365 THEN '6 to 12 months'      WHEN (DATE '2025-12-31' - CAST(eu.created_at AS date)) < 730 THEN '12 to 24 months'      ELSE 'over 24 months'    END AS tenure_band  FROM eligible_users eu),orders_scoped AS (  SELECT    o.id AS order_id,    o.user_id,    o.status,    o.user_id AS user_id,    o.status AS status,    CASE WHEN o.status <> 'cancelled' THEN      CASE WHEN o.currency_code = 'EUR' THEN o.grand_total           ELSE o.grand_total / er.rate END           ELSE CAST(o.grand_total / er.rate AS NUMERIC(18,6)) END    ELSE NULL END AS revenue_eur,    CASE WHEN o.status <> 'cancelled' THEN 1 ELSE 0 END AS is_noncancelled,    CASE WHEN EXISTS (SELECT 1 FROM payments p WHERE p.order_id = o.id AND p.amount_refunded > 0)         OR EXISTS (SELECT 1 FROM order_returns r WHERE r.order_id = o.id)    THEN 1 ELSE 0 END AS is_refunded  FROM orders o  JOIN eligible_users eu ON eu.user_id = o.user_id AND eu.tenant_id = o.tenant_id  LEFT JOIN exchange_rates er    ON er.base_code = 'EUR'    AND er.quote_code = o.currency_code    AND er.as_of = o.placed_at::date    AND er.as_of = CAST(o.placed_at AS date)),user_agg AS (  SELECT    user_id,    COUNT(*) AS lifetime_orders,    SUM(is_noncancelled) AS noncancelled_orders,    SUM(COALESCE(revenue_eur, 0)) AS lifetime_revenue_eur,    SUM(is_refunded) AS refunded_orders  FROM orders_scoped  GROUP BY user_id),user_level AS (  SELECT    t.tenure_band,    lg.customer_group,    ua.lifetime_orders,    ua.noncancelled_orders,    ua.lifetime_revenue_eur,    ua.refunded_orders    t.tenure_band AS tenure_band,    lg.customer_group AS customer_group,    ua.lifetime_orders AS lifetime_orders,    ua.noncancelled_orders AS noncancelled_orders,    ua.lifetime_revenue_eur AS lifetime_revenue_eur,    ua.refunded_orders AS refunded_orders  FROM eligible_users eu  JOIN tenure t ON t.user_id = eu.user_id  JOIN loyalty_group lg ON lg.user_id = eu.user_id  JOIN user_agg ua ON ua.user_id = eu.user_id)SELECT  tenure_band,  customer_group AS "group",  COUNT(*) AS customer_count,  ROUND(AVG(lifetime_orders)::numeric, 2) AS avg_lifetime_orders,  ROUND(AVG(lifetime_revenue_eur)::numeric, 2) AS avg_lifetime_revenue_eur,  ROUND((SUM(lifetime_revenue_eur) / NULLIF(SUM(noncancelled_orders), 0))::numeric, 2) AS avg_order_value_eur,  ROUND((100.0 * SUM(refunded_orders) / NULLIF(SUM(lifetime_orders), 0))::numeric, 2) AS refund_rate_pct  AVG(CAST(lifetime_orders AS NUMERIC(18,6))) AS avg_lifetime_orders,  AVG(lifetime_revenue_eur) AS avg_lifetime_revenue_eur,  (SUM(lifetime_revenue_eur) / NULLIF(SUM(noncancelled_orders), 0)) AS avg_order_value_eur,  (100.0 * SUM(refunded_orders) / NULLIF(SUM(lifetime_orders), 0)) AS refund_rate_pctFROM user_levelGROUP BY tenure_band, customer_groupORDER BY  CASE tenure_band    WHEN 'under 6 months' THEN 1    WHEN '6 to 12 months' THEN 2    WHEN '12 to 24 months' THEN 3    WHEN 'over 24 months' THEN 4  END,  END NULLS LAST,  CASE customer_group    WHEN 'non-member' THEN 1    WHEN 'never-redeemed member' THEN 2    WHEN 'ever-redeemed member' THEN 3  END  END NULLS LAST

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

WITH apparel_tenants AS (
  SELECT id AS tenant_id
  FROM tenants
  WHERE industry = 'Apparel & Footwear'
),
eligible_users AS (
  SELECT DISTINCT u.id AS user_id, u.tenant_id AS tenant_id, u.created_at AS created_at
  FROM users u
  JOIN apparel_tenants t ON t.tenant_id = u.tenant_id
  WHERE u.is_staff = false
    AND EXISTS (
      SELECT 1 FROM orders o
      WHERE o.user_id = u.id
        AND o.tenant_id = u.tenant_id
        AND o.placed_at >= TIMESTAMP '2023-01-01'
        AND o.placed_at < TIMESTAMP '2026-01-01'
    )
),
loyalty_group AS (
  SELECT
    eu.user_id AS user_id,
    CASE
      WHEN la.user_id IS NULL THEN 'non-member'
      WHEN lb.user_id IS NOT NULL THEN 'ever-redeemed member'
      ELSE 'never-redeemed member'
    END AS customer_group
  FROM eligible_users eu
  LEFT JOIN (SELECT DISTINCT user_id FROM loyalty_accounts) la
    ON la.user_id = eu.user_id
  LEFT JOIN (SELECT DISTINCT user_id FROM loyalty_transactions WHERE direction = 'burn') lb
    ON lb.user_id = eu.user_id
),
tenure AS (
  SELECT
    eu.user_id AS user_id,
    CASE
      WHEN (DATE '2025-12-31' - CAST(eu.created_at AS date)) < 182 THEN 'under 6 months'
      WHEN (DATE '2025-12-31' - CAST(eu.created_at AS date)) < 365 THEN '6 to 12 months'
      WHEN (DATE '2025-12-31' - CAST(eu.created_at AS date)) < 730 THEN '12 to 24 months'
      ELSE 'over 24 months'
    END AS tenure_band
  FROM eligible_users eu
),
orders_scoped AS (
  SELECT
    o.id AS order_id,
    o.user_id AS user_id,
    o.status AS status,
    CASE WHEN o.status <> 'cancelled' THEN
      CASE WHEN o.currency_code = 'EUR' THEN o.grand_total
           ELSE CAST(o.grand_total / er.rate AS NUMERIC(18,6)) END
    ELSE NULL END AS revenue_eur,
    CASE WHEN o.status <> 'cancelled' THEN 1 ELSE 0 END AS is_noncancelled,
    CASE WHEN EXISTS (SELECT 1 FROM payments p WHERE p.order_id = o.id AND p.amount_refunded > 0)
         OR EXISTS (SELECT 1 FROM order_returns r WHERE r.order_id = o.id)
    THEN 1 ELSE 0 END AS is_refunded
  FROM orders o
  JOIN eligible_users eu ON eu.user_id = o.user_id AND eu.tenant_id = o.tenant_id
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
    AND er.quote_code = o.currency_code
    AND er.as_of = CAST(o.placed_at AS date)
),
user_agg AS (
  SELECT
    user_id,
    COUNT(*) AS lifetime_orders,
    SUM(is_noncancelled) AS noncancelled_orders,
    SUM(COALESCE(revenue_eur, 0)) AS lifetime_revenue_eur,
    SUM(is_refunded) AS refunded_orders
  FROM orders_scoped
  GROUP BY user_id
),
user_level AS (
  SELECT
    t.tenure_band AS tenure_band,
    lg.customer_group AS customer_group,
    ua.lifetime_orders AS lifetime_orders,
    ua.noncancelled_orders AS noncancelled_orders,
    ua.lifetime_revenue_eur AS lifetime_revenue_eur,
    ua.refunded_orders AS refunded_orders
  FROM eligible_users eu
  JOIN tenure t ON t.user_id = eu.user_id
  JOIN loyalty_group lg ON lg.user_id = eu.user_id
  JOIN user_agg ua ON ua.user_id = eu.user_id
)
SELECT
  tenure_band,
  customer_group AS "group",
  COUNT(*) AS customer_count,
  AVG(CAST(lifetime_orders AS NUMERIC(18,6))) AS avg_lifetime_orders,
  AVG(lifetime_revenue_eur) AS avg_lifetime_revenue_eur,
  (SUM(lifetime_revenue_eur) / NULLIF(SUM(noncancelled_orders), 0)) AS avg_order_value_eur,
  (100.0 * SUM(refunded_orders) / NULLIF(SUM(lifetime_orders), 0)) AS refund_rate_pct
FROM user_level
GROUP BY tenure_band, customer_group
ORDER BY
  CASE tenure_band
    WHEN 'under 6 months' THEN 1
    WHEN '6 to 12 months' THEN 2
    WHEN '12 to 24 months' THEN 3
    WHEN 'over 24 months' THEN 4
  END NULLS LAST,
  CASE customer_group
    WHEN 'non-member' THEN 1
    WHEN 'never-redeemed member' THEN 2
    WHEN 'ever-redeemed member' THEN 3
  END NULLS LAST

Firebolt{ "errors": [ { "description": "Line 51, 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": "3d36c7b6-f00c-412a-b6b2-92f5d9a77ad9", "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 __table37.grand_total Decimal(14, 4) Decimal64(size = 0), __table45.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. q376 on the start page, with every configuration.

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

Grace's whole session, 7 turns

The request, as Grace received it

Report request from Ingrid Solberg (analyst for the Head of Lifecycle Marketing):

Title: Apparel: never-redeemed vs ever-redeemed members vs non-members, by tenure
Question: Once we separate loyalty members into those who have actually redeemed a point at least once versus those who never have, do the engaged ones match or beat non-members of similar tenure, while the disengaged majority lags behind?
Grain: one row per combination of account-tenure band and customer group: 'never-redeemed member', 'ever-redeemed member', 'non-member', restricted to the Apparel and Footwear vertical
Columns:
- tenure_band
- group
- customer_count
- avg_lifetime_orders
- avg_lifetime_revenue_eur
- avg_order_value_eur
- refund_rate_pct
Details: Population: users (is_staff = false) in tenants with tenants.industry = 'Apparel & Footwear' who placed at least one order 2023-01-01 to 2025-12-31. 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' (same boundaries as prior tenure reports). Group: 'non-member' if no loyalty_accounts row; otherwise 'ever-redeemed member' if the user has at least one 'burn'-direction loyalty_transactions row, else 'never-redeemed member'. Revenue/AOV use non-cancelled orders converted to EUR via exchange_rates (as_of placed_at). avg_lifetime_orders and refund_rate_pct use all orders (any status); refund = payments.amount_refunded greater than 0 OR an order_returns row. Order rows by tenure_band (youngest to oldest), then group in the order 'non-member', 'never-redeemed member', 'ever-redeemed member'.

Check your query with the warehouse's dry_run tool, then hand it in with submit.

Grace calls dry_run

WITH apparel_tenants AS (
  SELECT id AS tenant_id
  FROM tenants
  WHERE industry = 'Apparel & Footwear'
),
eligible_users AS (
  SELECT DISTINCT u.id AS user_id, u.tenant_id, u.created_at
  FROM users u
  JOIN apparel_tenants t ON t.tenant_id = u.tenant_id
  WHERE u.is_staff = false
    AND EXISTS (
      SELECT 1 FROM orders o
      WHERE o.user_id = u.id
        AND o.tenant_id = u.tenant_id
        AND o.placed_at >= TIMESTAMP '2023-01-01'
        AND o.placed_at < TIMESTAMP '2026-01-01'
    )
),
loyalty_group AS (
  SELECT
    eu.user_id,
    CASE
      WHEN la.user_id IS NULL THEN 'non-member'
      WHEN lb.user_id IS NOT NULL THEN 'ever-redeemed member'
      ELSE 'never-redeemed member'
    END AS customer_group
  FROM eligible_users eu
  LEFT JOIN (SELECT DISTINCT user_id FROM loyalty_accounts) la
    ON la.user_id = eu.user_id
  LEFT JOIN (SELECT DISTINCT user_id FROM loyalty_transactions WHERE direction = 'burn') lb
    ON lb.user_id = eu.user_id
),
tenure AS (
  SELECT
    eu.user_id,
    CASE
      WHEN (DATE '2025-12-31' - eu.created_at::date) < 182 THEN 'under 6 months'
      WHEN (DATE '2025-12-31' - eu.created_at::date) < 365 THEN '6 to 12 months'
      WHEN (DATE '2025-12-31' - eu.created_at::date) < 730 THEN '12 to 24 months'
      ELSE 'over 24 months'
    END AS tenure_band
  FROM eligible_users eu
),
orders_scoped AS (
  SELECT
    o.id AS order_id,
    o.user_id,
    o.status,
    CASE WHEN o.status <> 'cancelled' THEN
      CASE WHEN o.currency_code = 'EUR' THEN o.grand_total
           ELSE o.grand_total / er.rate END
    ELSE NULL END AS revenue_eur,
    CASE WHEN o.status <> 'cancelled' THEN 1 ELSE 0 END AS is_noncancelled,
    CASE WHEN EXISTS (SELECT 1 FROM payments p WHERE p.order_id = o.id AND p.amount_refunded > 0)
         OR EXISTS (SELECT 1 FROM order_returns r WHERE r.order_id = o.id)
    THEN 1 ELSE 0 END AS is_refunded
  FROM orders o
  JOIN eligible_users eu ON eu.user_id = o.user_id AND eu.tenant_id = o.tenant_id
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
    AND er.quote_code = o.currency_code
    AND er.as_of = o.placed_at::date
),
user_agg AS (
  SELECT
    user_id,
    COUNT(*) AS lifetime_orders,
    SUM(is_noncancelled) AS noncancelled_orders,
    SUM(COALESCE(revenue_eur, 0)) AS lifetime_revenue_eur,
    SUM(is_refunded) AS refunded_orders
  FROM orders_scoped
  GROUP BY user_id
),
user_level AS (
  SELECT
    t.tenure_band,
    lg.customer_group,
    ua.lifetime_orders,
    ua.noncancelled_orders,
    ua.lifetime_revenue_eur,
    ua.refunded_orders
  FROM eligible_users eu
  JOIN tenure t ON t.user_id = eu.user_id
  JOIN loyalty_group lg ON lg.user_id = eu.user_id
  JOIN user_agg ua ON ua.user_id = eu.user_id
)
SELECT
  tenure_band,
  customer_group AS "group",
  COUNT(*) AS customer_count,
  ROUND(AVG(lifetime_orders)::numeric, 2) AS avg_lifetime_orders,
  ROUND(AVG(lifetime_revenue_eur)::numeric, 2) AS avg_lifetime_revenue_eur,
  ROUND((SUM(lifetime_revenue_eur) / NULLIF(SUM(noncancelled_orders), 0))::numeric, 2) AS avg_order_value_eur,
  ROUND((100.0 * SUM(refunded_orders) / NULLIF(SUM(lifetime_orders), 0))::numeric, 2) AS refund_rate_pct
FROM user_level
GROUP BY tenure_band, customer_group
ORDER BY
  CASE tenure_band
    WHEN 'under 6 months' THEN 1
    WHEN '6 to 12 months' THEN 2
    WHEN '12 to 24 months' THEN 3
    WHEN 'over 24 months' THEN 4
  END,
  CASE customer_group
    WHEN 'non-member' THEN 1
    WHEN 'never-redeemed member' THEN 2
    WHEN 'ever-redeemed member' THEN 3
  END

The warehouse answers

STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: tenure_band, group, customer_count, avg_lifetime_orders, avg_lifetime_revenue_eur, avg_order_value_eur, refund_rate_pct

GraceQuery compiles with exactly the requested grain and columns. Submitting.

Grace calls submit

WITH apparel_tenants AS (
  SELECT id AS tenant_id
  FROM tenants
  WHERE industry = 'Apparel & Footwear'
),
eligible_users AS (
  SELECT DISTINCT u.id AS user_id, u.tenant_id, u.created_at
  FROM users u
  JOIN apparel_tenants t ON t.tenant_id = u.tenant_id
  WHERE u.is_staff = false
    AND EXISTS (
      SELECT 1 FROM orders o
      WHERE o.user_id = u.id
        AND o.tenant_id = u.tenant_id
        AND o.placed_at >= TIMESTAMP '2023-01-01'
        AND o.placed_at < TIMESTAMP '2026-01-01'
    )
),
loyalty_group AS (
  SELECT
    eu.user_id,
    CASE
      WHEN la.user_id IS NULL THEN 'non-member'
      WHEN lb.user_id IS NOT NULL THEN 'ever-redeemed member'
      ELSE 'never-redeemed member'
    END AS customer_group
  FROM eligible_users eu
  LEFT JOIN (SELECT DISTINCT user_id FROM loyalty_accounts) la
    ON la.user_id = eu.user_id
  LEFT JOIN (SELECT DISTINCT user_id FROM loyalty_transactions WHERE direction = 'burn') lb
    ON lb.user_id = eu.user_id
),
tenure AS (
  SELECT
    eu.user_id,
    CASE
      WHEN (DATE '2025-12-31' - eu.created_at::date) < 182 THEN 'under 6 months'
      WHEN (DATE '2025-12-31' - eu.created_at::date) < 365 THEN '6 to 12 months'
      WHEN (DATE '2025-12-31' - eu.created_at::date) < 730 THEN '12 to 24 months'
      ELSE 'over 24 months'
    END AS tenure_band
  FROM eligible_users eu
),
orders_scoped AS (
  SELECT
    o.id AS order_id,
    o.user_id,
    o.status,
    CASE WHEN o.status <> 'cancelled' THEN
      CASE WHEN o.currency_code = 'EUR' THEN o.grand_total
           ELSE o.grand_total / er.rate END
    ELSE NULL END AS revenue_eur,
    CASE WHEN o.status <> 'cancelled' THEN 1 ELSE 0 END AS is_noncancelled,
    CASE WHEN EXISTS (SELECT 1 FROM payments p WHERE p.order_id = o.id AND p.amount_refunded > 0)
         OR EXISTS (SELECT 1 FROM order_returns r WHERE r.order_id = o.id)
    THEN 1 ELSE 0 END AS is_refunded
  FROM orders o
  JOIN eligible_users eu ON eu.user_id = o.user_id AND eu.tenant_id = o.tenant_id
  LEFT JOIN exchange_rates er
    ON er.base_code = 'EUR'
    AND er.quote_code = o.currency_code
    AND er.as_of = o.placed_at::date
),
user_agg AS (
  SELECT
    user_id,
    COUNT(*) AS lifetime_orders,
    SUM(is_noncancelled) AS noncancelled_orders,
    SUM(COALESCE(revenue_eur, 0)) AS lifetime_revenue_eur,
    SUM(is_refunded) AS refunded_orders
  FROM orders_scoped
  GROUP BY user_id
),
user_level AS (
  SELECT
    t.tenure_band,
    lg.customer_group,
    ua.lifetime_orders,
    ua.noncancelled_orders,
    ua.lifetime_revenue_eur,
    ua.refunded_orders
  FROM eligible_users eu
  JOIN tenure t ON t.user_id = eu.user_id
  JOIN loyalty_group lg ON lg.user_id = eu.user_id
  JOIN user_agg ua ON ua.user_id = eu.user_id
)
SELECT
  tenure_band,
  customer_group AS "group",
  COUNT(*) AS customer_count,
  ROUND(AVG(lifetime_orders)::numeric, 2) AS avg_lifetime_orders,
  ROUND(AVG(lifetime_revenue_eur)::numeric, 2) AS avg_lifetime_revenue_eur,
  ROUND((SUM(lifetime_revenue_eur) / NULLIF(SUM(noncancelled_orders), 0))::numeric, 2) AS avg_order_value_eur,
  ROUND((100.0 * SUM(refunded_orders) / NULLIF(SUM(lifetime_orders), 0))::numeric, 2) AS refund_rate_pct
FROM user_level
GROUP BY tenure_band, customer_group
ORDER BY
  CASE tenure_band
    WHEN 'under 6 months' THEN 1
    WHEN '6 to 12 months' THEN 2
    WHEN '12 to 24 months' THEN 3
    WHEN 'over 24 months' THEN 4
  END,
  CASE customer_group
    WHEN 'non-member' THEN 1
    WHEN 'never-redeemed member' THEN 2
    WHEN 'ever-redeemed member' THEN 3
  END

The warehouse answers

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

GraceSubmitted. Summary of key choices made explicit in the note to Ingrid: - **Tenure bands**: computed from day-count differences (182/365/730 days) between `users.created_at` and 2025-12-31, as a standard proxy for 6/12/24-month boundaries. - **Population**: non-staff users in Apparel & Footwear tenants with at least one order (any status) placed between 2023-01-01 and 2025-12-31. - **Grouping**: non-member (no `loyalty_accounts` row) vs never-redeemed (has account, no `burn` transaction) vs ever-redeemed (has account, at least one `burn` transaction). - **Revenue/AOV**: non-cancelled orders only, converted to EUR via `exchange_rates` keyed on `placed_at` date; AOV is the group's total revenue divided by total non-cancelled order count (not an average of per-customer ratios). - **Lifetime orders & refund rate**: computed over all orders regardless of status; refund = any `payments.amount_refunded > 0` or an `order_returns` row for that order.

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

Grace's transcript (288 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)

Apparel: never-redeemed vs ever-redeemed members vs non-members, by tenure

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

Aisha Rahman writes to Ingrid Solberg

Ingrid Solberg plans the night

Grace writes the SQL

The report comes back overnight

What came of it

It becomes benchmark query q376

Everything, as recorded