DB Olympics

Queries · q374 · Head of Lifecycle Marketing · week 2, night 2

Apparel order value breakdown: subtotal, discount and total, member vs non-member

“Is the large average-order-value gap seen among Apparel members (28-40% lower than non-members across every tenure band) explained by heavier discounting at checkout, or do members simply buy cheaper products before any discount is applied?”

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

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

    Night 1 answered Aisha's Q1 cleanly: within Apparel and controlling for tenure, order counts and refund rates between members and non-members are essentially indistinguishable in every tenure band (refund rates all cluster 33-40% regardless of segment) - the refund-rate and order-frequency 'penalty' really was mostly mix artifact. But a new, robust finding emerged: average order value is 28-40% lower for members than non-members in every single tenure band within Apparel - consistent and large, so that's the real driver of lower member revenue, and it needs explaining before I call it a verdict. Report 2 answered Q2 well: redemption rate rises steadily with cohort age (4% for 2-month-old cohorts up to ~32-37% for cohorts over 2 years old) and then plateaus around that 32-37% level even for the oldest 2023 cohorts - so it's both a timing lag (explains why young cohorts drag the blended rate down) and a structural ceiling (most points, even given 3 years, never get redeemed) - meaning the liability will keep growing in euros as we add members, while each cohort's own eventual redemption converges to roughly a third of what's issued. Report 3 gave a clean, important negative result for Q3: order_id is populated on 100% of 'earn' transactions but 0% of 343 'burn' transactions - the data model genuinely never records which order (if any) a redemption applied to, so the order_id-based gaming test was never going to work; I need a different join (same-user, same-day proximity) before I can call this inconclusive for good. Tonight: (1) retry the gaming test using date-proximity matching instead of order_id; (2) decompose the Apparel AOV gap into subtotal vs discount to see if heavier discounting (consistent with points use) explains it; (3) start on Q4 by comparing top-revenue members against the rest of the member base to see if better performers use the programme differently.

    Apparel order value breakdown: subtotal, discount and total, member vs non-member
    Question
    Is the large average-order-value gap seen among Apparel members (28-40% lower than non-members across every tenure band) explained by heavier discounting at checkout, or do members simply buy cheaper products before any discount is applied?
    One row per
    loyalty segment (member vs non-member), restricted to the Apparel and Footwear vertical
    Columns
    • segment
    • order_count
    • avg_subtotal_eur
    • avg_discount_total_eur
    • avg_grand_total_eur
    • discount_rate_pct

  4. 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_orders AS (
  SELECT o.id, o.tenant_id, o.user_id, o.currency_code, o.subtotal, o.discount_total, o.grand_total, o.placed_at
  FROM orders o
  JOIN tenants t ON t.id = o.tenant_id
  JOIN users u ON u.id = o.user_id
  WHERE t.industry = 'Apparel & Footwear'
    AND u.is_staff = false
    AND o.status <> 'cancelled'
    AND o.placed_at >= DATE '2023-01-01'
    AND o.placed_at < DATE '2026-01-01'
),
user_segment AS (
  SELECT ao.*,
    CASE WHEN la.user_id IS NOT NULL THEN 'loyalty member' ELSE 'non-member' END AS segment
  FROM apparel_orders ao
  LEFT JOIN (SELECT DISTINCT user_id FROM loyalty_accounts) la ON la.user_id = ao.user_id
),
fx_converted AS (
  SELECT us.segment,
    us.subtotal / CASE WHEN us.currency_code = 'EUR' THEN 1 ELSE er.rate END AS subtotal_eur,
    us.discount_total / CASE WHEN us.currency_code = 'EUR' THEN 1 ELSE er.rate END AS discount_total_eur,
    us.grand_total / CASE WHEN us.currency_code = 'EUR' THEN 1 ELSE er.rate END AS grand_total_eur
  FROM user_segment us
  LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = us.currency_code AND er.as_of = CAST(us.placed_at AS DATE)
),
segment_agg AS (
  SELECT segment,
    COUNT(*) AS order_count,
    AVG(subtotal_eur) AS avg_subtotal_eur,
    AVG(discount_total_eur) AS avg_discount_total_eur,
    AVG(grand_total_eur) AS avg_grand_total_eur,
    SUM(discount_total_eur) AS sum_discount_eur,
    SUM(subtotal_eur) AS sum_subtotal_eur
  FROM fx_converted
  GROUP BY segment
)
SELECT
  segment,
  order_count,
  ROUND(avg_subtotal_eur, 2) AS avg_subtotal_eur,
  ROUND(avg_discount_total_eur, 2) AS avg_discount_total_eur,
  ROUND(avg_grand_total_eur, 2) AS avg_grand_total_eur,
  ROUND(100.0 * sum_discount_eur / NULLIF(sum_subtotal_eur,0), 2) AS discount_rate_pct
FROM segment_agg
ORDER BY CASE WHEN segment = 'loyalty member' THEN 0 ELSE 1 END, segment

    “Query compiles and returns the expected columns.”

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

    “Submitted. The report computes, per segment (loyalty member vs non-member) within Apparel & Footwear tenants, the order count and EUR-converted averages of subtotal, discount total and grand total, plus the aggregate discount rate (sum of discounts ÷ sum of subtotals) — which directly separates "heavier discounting" from "cheaper pre-discount baskets" as the driver of the AOV gap.”

  5. The report comes back overnight

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

    segment        | order_count | avg_subtotal_eur | avg_discount_total_eur | avg_grand_total_eur | discount_rate_pct
---------------+-------------+------------------+------------------------+---------------------+------------------
loyalty member |        3411 |           150.48 |                   6.97 |              171.92 |              4.63
non-member     |       12795 |           183.56 |                   6.61 |              196.75 |               3.6
(2 rows)

  6. What came of it

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

    It builds on:

  7. It becomes benchmark query q374

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

    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
    • made explicita selected t.col gets AS col, its standard name made explicit (9×)
    • 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_orders AS (  SELECT o.id, o.tenant_id, o.user_id, o.currency_code, o.subtotal, o.discount_total, o.grand_total, o.placed_at  SELECT o.id AS id, o.tenant_id AS tenant_id, o.user_id AS user_id, o.currency_code AS currency_code, o.subtotal AS subtotal, o.discount_total AS discount_total, o.grand_total AS grand_total, o.placed_at AS placed_at  FROM orders o  JOIN tenants t ON t.id = o.tenant_id  JOIN users u ON u.id = o.user_id  WHERE t.industry = 'Apparel & Footwear'    AND u.is_staff = false    AND o.status <> 'cancelled'    AND o.placed_at >= DATE '2023-01-01'    AND o.placed_at < DATE '2026-01-01'),user_segment AS (  SELECT ao.*,    CASE WHEN la.user_id IS NOT NULL THEN 'loyalty member' ELSE 'non-member' END AS segment  FROM apparel_orders ao  LEFT JOIN (SELECT DISTINCT user_id FROM loyalty_accounts) la ON la.user_id = ao.user_id),fx_converted AS (  SELECT us.segment,  SELECT us.segment AS segment,    us.subtotal / CASE WHEN us.currency_code = 'EUR' THEN 1 ELSE er.rate END AS subtotal_eur,    us.discount_total / CASE WHEN us.currency_code = 'EUR' THEN 1 ELSE er.rate END AS discount_total_eur,    us.grand_total / CASE WHEN us.currency_code = 'EUR' THEN 1 ELSE er.rate END AS grand_total_eur  FROM user_segment us  LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = us.currency_code AND er.as_of = CAST(us.placed_at AS DATE)),segment_agg AS (  SELECT segment,    COUNT(*) AS order_count,    AVG(subtotal_eur) AS avg_subtotal_eur,    AVG(discount_total_eur) AS avg_discount_total_eur,    AVG(grand_total_eur) AS avg_grand_total_eur,    SUM(discount_total_eur) AS sum_discount_eur,    SUM(subtotal_eur) AS sum_subtotal_eur  FROM fx_converted  GROUP BY segment)SELECT  segment,  order_count,  ROUND(avg_subtotal_eur, 2) AS avg_subtotal_eur,  ROUND(avg_discount_total_eur, 2) AS avg_discount_total_eur,  ROUND(avg_grand_total_eur, 2) AS avg_grand_total_eur,  ROUND(100.0 * sum_discount_eur / NULLIF(sum_subtotal_eur,0), 2) AS discount_rate_pct  avg_subtotal_eur AS avg_subtotal_eur,  avg_discount_total_eur AS avg_discount_total_eur,  avg_grand_total_eur AS avg_grand_total_eur,  100.0 * sum_discount_eur / NULLIF(sum_subtotal_eur,0) AS discount_rate_pctFROM segment_aggORDER BY CASE WHEN segment = 'loyalty member' THEN 0 ELSE 1 END, segmentORDER BY CASE WHEN segment = 'loyalty member' THEN 0 ELSE 1 END NULLS LAST, segment NULLS LAST

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

    WITH apparel_orders AS (
  SELECT o.id AS id, o.tenant_id AS tenant_id, o.user_id AS user_id, o.currency_code AS currency_code, o.subtotal AS subtotal, o.discount_total AS discount_total, o.grand_total AS grand_total, o.placed_at AS placed_at
  FROM orders o
  JOIN tenants t ON t.id = o.tenant_id
  JOIN users u ON u.id = o.user_id
  WHERE t.industry = 'Apparel & Footwear'
    AND u.is_staff = false
    AND o.status <> 'cancelled'
    AND o.placed_at >= DATE '2023-01-01'
    AND o.placed_at < DATE '2026-01-01'
),
user_segment AS (
  SELECT ao.*,
    CASE WHEN la.user_id IS NOT NULL THEN 'loyalty member' ELSE 'non-member' END AS segment
  FROM apparel_orders ao
  LEFT JOIN (SELECT DISTINCT user_id FROM loyalty_accounts) la ON la.user_id = ao.user_id
),
fx_converted AS (
  SELECT us.segment AS segment,
    us.subtotal / CASE WHEN us.currency_code = 'EUR' THEN 1 ELSE er.rate END AS subtotal_eur,
    us.discount_total / CASE WHEN us.currency_code = 'EUR' THEN 1 ELSE er.rate END AS discount_total_eur,
    us.grand_total / CASE WHEN us.currency_code = 'EUR' THEN 1 ELSE er.rate END AS grand_total_eur
  FROM user_segment us
  LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = us.currency_code AND er.as_of = CAST(us.placed_at AS DATE)
),
segment_agg AS (
  SELECT segment,
    COUNT(*) AS order_count,
    AVG(subtotal_eur) AS avg_subtotal_eur,
    AVG(discount_total_eur) AS avg_discount_total_eur,
    AVG(grand_total_eur) AS avg_grand_total_eur,
    SUM(discount_total_eur) AS sum_discount_eur,
    SUM(subtotal_eur) AS sum_subtotal_eur
  FROM fx_converted
  GROUP BY segment
)
SELECT
  segment,
  order_count,
  avg_subtotal_eur AS avg_subtotal_eur,
  avg_discount_total_eur AS avg_discount_total_eur,
  avg_grand_total_eur AS avg_grand_total_eur,
  100.0 * sum_discount_eur / NULLIF(sum_subtotal_eur,0) AS discount_rate_pct
FROM segment_agg
ORDER BY CASE WHEN segment = 'loyalty member' THEN 0 ELSE 1 END NULLS LAST, segment NULLS LAST
    100 ms1 sHyper307 msDuckDB247 msGizmoSQL211 msclickhouse_brew319 msFirebolterrorDataFusion469 msClickHouseerror
    • Firebolt{ "errors": [ { "description": "Line 20, Column 17: 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": "cb2bd97d-82e8-4f05-827c-bb86b0471992", "query_lab
    • ClickHouseCode: 69. DB::Exception: Decimal result's scale is less than argument's one: while executing function divide on arguments __table3.subtotal Decimal(14, 4) Decimal64(size = 0), if(equals(__table3.currency_code, 'EUR'_String), 1_UInt8, __table10.rate) Nullable(Decimal(18, 6)) Nullable(size = 0, Decima

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

    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 order value breakdown: subtotal, discount and total, member vs non-member
Question: Is the large average-order-value gap seen among Apparel members (28-40% lower than non-members across every tenure band) explained by heavier discounting at checkout, or do members simply buy cheaper products before any discount is applied?
Grain: one row per loyalty segment (member vs non-member), restricted to the Apparel and Footwear vertical
Columns:
- segment
- order_count
- avg_subtotal_eur
- avg_discount_total_eur
- avg_grand_total_eur
- discount_rate_pct
Details: Population: non-cancelled orders placed 2023-01-01 to 2025-12-31 by users (is_staff = false) belonging to tenants with tenants.industry = 'Apparel & Footwear'. Segment = 'loyalty member' if the user has a loyalty_accounts row, else 'non-member'. avg_subtotal_eur, avg_discount_total_eur and avg_grand_total_eur are averages of orders.subtotal, orders.discount_total and orders.grand_total respectively, each converted to EUR via exchange_rates (order currency_code to EUR, rate as_of placed_at). discount_rate_pct = (sum of discount_total in EUR) divided by (sum of subtotal in EUR) for the segment, as a percentage (an aggregate rate, not an average of per-order ratios). Order rows: 'loyalty member' first, then 'non-member'.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Grace calls dry_run
      WITH apparel_orders AS (
  SELECT o.id, o.tenant_id, o.user_id, o.currency_code, o.subtotal, o.discount_total, o.grand_total, o.placed_at
  FROM orders o
  JOIN tenants t ON t.id = o.tenant_id
  JOIN users u ON u.id = o.user_id
  WHERE t.industry = 'Apparel & Footwear'
    AND u.is_staff = false
    AND o.status <> 'cancelled'
    AND o.placed_at >= DATE '2023-01-01'
    AND o.placed_at < DATE '2026-01-01'
),
user_segment AS (
  SELECT ao.*,
    CASE WHEN la.user_id IS NOT NULL THEN 'loyalty member' ELSE 'non-member' END AS segment
  FROM apparel_orders ao
  LEFT JOIN (SELECT DISTINCT user_id FROM loyalty_accounts) la ON la.user_id = ao.user_id
),
fx_converted AS (
  SELECT us.segment,
    us.subtotal / CASE WHEN us.currency_code = 'EUR' THEN 1 ELSE er.rate END AS subtotal_eur,
    us.discount_total / CASE WHEN us.currency_code = 'EUR' THEN 1 ELSE er.rate END AS discount_total_eur,
    us.grand_total / CASE WHEN us.currency_code = 'EUR' THEN 1 ELSE er.rate END AS grand_total_eur
  FROM user_segment us
  LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = us.currency_code AND er.as_of = CAST(us.placed_at AS DATE)
),
segment_agg AS (
  SELECT segment,
    COUNT(*) AS order_count,
    AVG(subtotal_eur) AS avg_subtotal_eur,
    AVG(discount_total_eur) AS avg_discount_total_eur,
    AVG(grand_total_eur) AS avg_grand_total_eur,
    SUM(discount_total_eur) AS sum_discount_eur,
    SUM(subtotal_eur) AS sum_subtotal_eur
  FROM fx_converted
  GROUP BY segment
)
SELECT
  segment,
  order_count,
  ROUND(avg_subtotal_eur, 2) AS avg_subtotal_eur,
  ROUND(avg_discount_total_eur, 2) AS avg_discount_total_eur,
  ROUND(avg_grand_total_eur, 2) AS avg_grand_total_eur,
  ROUND(100.0 * sum_discount_eur / NULLIF(sum_subtotal_eur,0), 2) AS discount_rate_pct
FROM segment_agg
ORDER BY CASE WHEN segment = 'loyalty member' THEN 0 ELSE 1 END, segment
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: segment, order_count, avg_subtotal_eur, avg_discount_total_eur, avg_grand_total_eur, discount_rate_pct
    • GraceQuery compiles and returns the expected columns.
    • Grace calls submit
      WITH apparel_orders AS (
  SELECT o.id, o.tenant_id, o.user_id, o.currency_code, o.subtotal, o.discount_total, o.grand_total, o.placed_at
  FROM orders o
  JOIN tenants t ON t.id = o.tenant_id
  JOIN users u ON u.id = o.user_id
  WHERE t.industry = 'Apparel & Footwear'
    AND u.is_staff = false
    AND o.status <> 'cancelled'
    AND o.placed_at >= DATE '2023-01-01'
    AND o.placed_at < DATE '2026-01-01'
),
user_segment AS (
  SELECT ao.*,
    CASE WHEN la.user_id IS NOT NULL THEN 'loyalty member' ELSE 'non-member' END AS segment
  FROM apparel_orders ao
  LEFT JOIN (SELECT DISTINCT user_id FROM loyalty_accounts) la ON la.user_id = ao.user_id
),
fx_converted AS (
  SELECT us.segment,
    us.subtotal / CASE WHEN us.currency_code = 'EUR' THEN 1 ELSE er.rate END AS subtotal_eur,
    us.discount_total / CASE WHEN us.currency_code = 'EUR' THEN 1 ELSE er.rate END AS discount_total_eur,
    us.grand_total / CASE WHEN us.currency_code = 'EUR' THEN 1 ELSE er.rate END AS grand_total_eur
  FROM user_segment us
  LEFT JOIN exchange_rates er ON er.base_code = 'EUR' AND er.quote_code = us.currency_code AND er.as_of = CAST(us.placed_at AS DATE)
),
segment_agg AS (
  SELECT segment,
    COUNT(*) AS order_count,
    AVG(subtotal_eur) AS avg_subtotal_eur,
    AVG(discount_total_eur) AS avg_discount_total_eur,
    AVG(grand_total_eur) AS avg_grand_total_eur,
    SUM(discount_total_eur) AS sum_discount_eur,
    SUM(subtotal_eur) AS sum_subtotal_eur
  FROM fx_converted
  GROUP BY segment
)
SELECT
  segment,
  order_count,
  ROUND(avg_subtotal_eur, 2) AS avg_subtotal_eur,
  ROUND(avg_discount_total_eur, 2) AS avg_discount_total_eur,
  ROUND(avg_grand_total_eur, 2) AS avg_grand_total_eur,
  ROUND(100.0 * sum_discount_eur / NULLIF(sum_subtotal_eur,0), 2) AS discount_rate_pct
FROM segment_agg
ORDER BY CASE WHEN segment = 'loyalty member' THEN 0 ELSE 1 END, segment
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • GraceSubmitted. The report computes, per segment (loyalty member vs non-member) within Apparel & Footwear tenants, the order count and EUR-converted averages of subtotal, discount total and grand total, plus the aggregate discount rate (sum of discounts ÷ sum of subtotals) — which directly separates "heavier discounting" from "cheaper pre-discount baskets" as the driver of the AOV gap.

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