DB Olympics

Queries · q057 · Head of Growth Marketing · week 1, night 3

Do loyalty program members behave differently from other shoppers?

“Among shoppers who actually place orders, do loyalty-program members order more often or spend more per order than non-members?”

Manager · asked the questionChloé MarchettiHead of Growth Marketing. Joined from a direct-to-consumer brand. Wants to sell merchants more of Tidewick's marketing tools.
Analyst · planned the reportSofie NilsenStoryteller: looks for the two or three findings that matter and digs until each is explained.
Engineer · wrote the SQLFelixJunior engineer who nests subqueries in FROM and WHERE (IN, EXISTS, correlated subqueries) rather than CTEs.

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

  1. Chloé Marchetti writes to Sofie Nilsen

    The manager, at the start of week 1

    Subject: What I need to get oriented this week

    Hi Sofie,

    Thrilled to be here — here's where my head's at. I know you've only got a handful of report slots a night, so I've ordered these by what I need most. Please push back if any of these are too broad or should split into more than one report.

    1. How are merchants actually acquiring shoppers, and through which channels? I want to understand where traffic and new customers are coming from across our merchant base — which channels show up most, and whether that's shifted recently. This is really just orientation for me: I don't yet know what "normal" looks like here.
    2. Are the coupons and promotions merchants run actually paying off? I'd like a sense of how much discounting is happening, how often codes get used, and whether promoted orders look healthy (not just cheaper). I'm trying to figure out if we should be telling merchants to promote more carefully — this is relevant for your product-pitch angle too.
    3. What does our email/campaign activity look like, and is anyone opening or clicking? I want a baseline read on send volume and engagement so I know whether campaigns are a strength or a weak spot for merchants on the platform.
    4. Is anyone actually using loyalty and referrals, and does it seem to matter? I keep hearing these are "nice to have" features. I want to know whether merchants who use them have any different shopper behavior, so I know whether to invest in pushing adoption.
    5. (If there's room this week) Do we run experiments/A-B tests at all, and does anyone act on the results? Lower priority — just curious whether this is a muscle we have or one I need to build from scratch.

    What I need this for: I'm meeting the leadership team Friday and want a grounded, numbers-based view of growth marketing's current state before I propose anything. No need for polish — I just want to know what's really happening so I don't propose things that are already working, or miss things that are quietly broken.

    Thanks for taking this on — let me know if you want me to cut this list down.

    Chloé

  2. Sofie Nilsen plans the night

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

    The Jan/Jul discount-rate report flagged that two B2B wholesalers (Castell Fasteners, OfficeHub Nordic) run discounts on 65-95% of orders even outside the spike months — that's almost certainly negotiated/contract pricing, not promotional codes. That's a real caveat to last night's 'discounted orders look healthier' finding (higher AOV, more items, fewer returns), since B2B bulk orders would naturally look that way regardless of discounting. Before I tell Chloé 'discounting is healthy,' I need to check it holds in consumer verticals, not just because B2B is mixed in. That's tonight's top priority. With that resolved, I'm moving on to orientation baselines for Q3 (email/campaign engagement) and Q4 (loyalty), since two nights remain and those questions haven't been touched yet. Direct-traffic concentration (Report 1 last night) came back as a straightforward big-merchant-volume story, not an anomaly, so I'm not spending a slot chasing it further.

    Do loyalty program members behave differently from other shoppers?
    Question
    Among shoppers who actually place orders, do loyalty-program members order more often or spend more per order than non-members?
    One row per
    shopper segment: 'loyalty member' vs 'non-member'
    Columns
    • segment - 'loyalty member' (user_id appears in loyalty_accounts) or 'non-member'
    • users_count - count of distinct users in that segment who placed at least one order with placed_at in 2025
    • orders_count_2025 - count of those users' orders placed_at in 2025
    • avg_orders_per_user - orders_count_2025 / users_count, rounded to 2 decimals
    • avg_order_value_eur - average orders.grand_total converted to EUR across that segment's 2025 orders, rounded to 0 decimals
    • total_revenue_eur - sum of orders.grand_total converted to EUR for that segment's 2025 orders, rounded to 0 decimals

  3. Felix 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)
    SELECT segment, users_count, orders_count_2025, avg_orders_per_user, avg_order_value_eur, total_revenue_eur
FROM (
  SELECT
    CASE WHEN EXISTS (
      SELECT 1 FROM loyalty_accounts la
      WHERE la.user_id = o.user_id AND la.tenant_id = o.tenant_id
    ) THEN 'loyalty member' ELSE 'non-member' END AS segment,
    COUNT(DISTINCT o.user_id) AS users_count,
    COUNT(*) AS orders_count_2025,
    ROUND(COUNT(*)::numeric / COUNT(DISTINCT o.user_id), 2) AS avg_orders_per_user,
    ROUND(AVG(o.grand_total / COALESCE(er.rate, CASE WHEN o.currency_code = 'EUR' THEN 1 END))::numeric, 0) AS avg_order_value_eur,
    ROUND(SUM(o.grand_total / COALESCE(er.rate, CASE WHEN o.currency_code = 'EUR' THEN 1 END))::numeric, 0) AS total_revenue_eur
  FROM orders o
  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)
  WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'
  GROUP BY CASE WHEN EXISTS (
      SELECT 1 FROM loyalty_accounts la
      WHERE la.user_id = o.user_id AND la.tenant_id = o.tenant_id
    ) THEN 'loyalty member' ELSE 'non-member' END
) seg
ORDER BY CASE WHEN segment = 'loyalty member' THEN 0 ELSE 1 END

    “Query compiles with the expected columns. Submitting.”

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

    “Submitted. The report classifies every 2025 order-placing user as a loyalty member (any loyalty_accounts row for that user/tenant) or non-member, then compares order frequency and EUR-converted spend between the two segments, exactly as requested.”

  4. The report comes back overnight

    As Sofie Nilsen saw it, from the small dataset the agents worked on

    segment        | users_count | orders_count_2025 | avg_orders_per_user | avg_order_value_eur | total_revenue_eur
---------------+-------------+-------------------+---------------------+---------------------+------------------
loyalty member |         862 |              2232 |                2.59 |                 191 |            427047
non-member     |        5297 |             19961 |                3.77 |                 281 |           5618101
(2 rows)

  5. What came of it

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

    Later queries that build on this report:

  6. It becomes benchmark query q057

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

    • spellingcast (3×)
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision; bare NUMERIC cast that only served the rounding removed; intended fraction: integer numerator cast to NUMERIC(18,6) before the division
    • numericsdocs/NUMERICS.md: x / exchange rate cast to NUMERIC(18,6) (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
    The benchmark text, against the submitted SQL
    SELECT segment, users_count, orders_count_2025, avg_orders_per_user, avg_order_value_eur, total_revenue_eurFROM (  SELECT    CASE WHEN EXISTS (      SELECT 1 FROM loyalty_accounts la      WHERE la.user_id = o.user_id AND la.tenant_id = o.tenant_id    ) THEN 'loyalty member' ELSE 'non-member' END AS segment,    COUNT(DISTINCT o.user_id) AS users_count,    COUNT(*) AS orders_count_2025,    ROUND(COUNT(*)::numeric / COUNT(DISTINCT o.user_id), 2) AS avg_orders_per_user,    ROUND(AVG(o.grand_total / COALESCE(er.rate, CASE WHEN o.currency_code = 'EUR' THEN 1 END))::numeric, 0) AS avg_order_value_eur,    ROUND(SUM(o.grand_total / COALESCE(er.rate, CASE WHEN o.currency_code = 'EUR' THEN 1 END))::numeric, 0) AS total_revenue_eur    CAST(COUNT(*) AS numeric(18,6)) / COUNT(DISTINCT o.user_id) AS avg_orders_per_user,    AVG(CAST(o.grand_total / COALESCE(er.rate, CASE WHEN o.currency_code = 'EUR' THEN 1 END) AS NUMERIC(18,6))) AS avg_order_value_eur,    SUM(CAST(o.grand_total / COALESCE(er.rate, CASE WHEN o.currency_code = 'EUR' THEN 1 END) AS NUMERIC(18,6))) AS total_revenue_eur  FROM orders o  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)  WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'  GROUP BY CASE WHEN EXISTS (      SELECT 1 FROM loyalty_accounts la      WHERE la.user_id = o.user_id AND la.tenant_id = o.tenant_id    ) THEN 'loyalty member' ELSE 'non-member' END) segORDER BY CASE WHEN segment = 'loyalty member' THEN 0 ELSE 1 ENDORDER BY CASE WHEN segment = 'loyalty member' THEN 0 ELSE 1 END NULLS LAST

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

    SELECT segment, users_count, orders_count_2025, avg_orders_per_user, avg_order_value_eur, total_revenue_eur
FROM (
  SELECT
    CASE WHEN EXISTS (
      SELECT 1 FROM loyalty_accounts la
      WHERE la.user_id = o.user_id AND la.tenant_id = o.tenant_id
    ) THEN 'loyalty member' ELSE 'non-member' END AS segment,
    COUNT(DISTINCT o.user_id) AS users_count,
    COUNT(*) AS orders_count_2025,
    CAST(COUNT(*) AS numeric(18,6)) / COUNT(DISTINCT o.user_id) AS avg_orders_per_user,
    AVG(CAST(o.grand_total / COALESCE(er.rate, CASE WHEN o.currency_code = 'EUR' THEN 1 END) AS NUMERIC(18,6))) AS avg_order_value_eur,
    SUM(CAST(o.grand_total / COALESCE(er.rate, CASE WHEN o.currency_code = 'EUR' THEN 1 END) AS NUMERIC(18,6))) AS total_revenue_eur
  FROM orders o
  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)
  WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'
  GROUP BY CASE WHEN EXISTS (
      SELECT 1 FROM loyalty_accounts la
      WHERE la.user_id = o.user_id AND la.tenant_id = o.tenant_id
    ) THEN 'loyalty member' ELSE 'non-member' END
) seg
ORDER BY CASE WHEN segment = 'loyalty member' THEN 0 ELSE 1 END NULLS LAST
    100 ms1 sHyper195 msDuckDB187 msGizmoSQL186 msclickhouse_brew870 msFirebolterrorDataFusionerrorClickHouseerror
    • Firebolt{ "errors": [ { "description": "Line 11, Column 28: 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": "7872c208-1363-43ca-89ed-672847a745a8", "query_lab
    • DataFusionThis feature is not implemented: Physical plan does not support logical expression Exists(Exists { subquery: <subquery>, negated: false })
    • ClickHouseCode: 48. DB::Exception: Correlated subqueries in aggregation keys are not supported. (NOT_IMPLEMENTED)

    Scale factor 1000, c8a.24xlarge, after the last load. q057 on the start page, with every configuration.

  7. 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

    Felix's whole session, 7 turns
    • The request, as Felix received it
      Report request from Sofie Nilsen (analyst for the Head of Growth Marketing):

Title: Do loyalty program members behave differently from other shoppers?
Question: Among shoppers who actually place orders, do loyalty-program members order more often or spend more per order than non-members?
Grain: one row per shopper segment: 'loyalty member' vs 'non-member'
Columns:
- segment - 'loyalty member' (user_id appears in loyalty_accounts) or 'non-member'
- users_count - count of distinct users in that segment who placed at least one order with placed_at in 2025
- orders_count_2025 - count of those users' orders placed_at in 2025
- avg_orders_per_user - orders_count_2025 / users_count, rounded to 2 decimals
- avg_order_value_eur - average orders.grand_total converted to EUR across that segment's 2025 orders, rounded to 0 decimals
- total_revenue_eur - sum of orders.grand_total converted to EUR for that segment's 2025 orders, rounded to 0 decimals
Details: Source: orders (placed_at in 2025, all tenants) left joined to loyalty_accounts on user_id (and tenant_id) to classify each ordering user as a member (has any loyalty_accounts row) or non-member. Restrict to users who placed at least one 2025 order so the comparison is among active shoppers, not all signups. Convert grand_total to EUR via exchange_rates as in prior money reports. Exactly 2 output rows, ordered 'loyalty member' then 'non-member'.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Felix calls dry_run
      SELECT segment, users_count, orders_count_2025, avg_orders_per_user, avg_order_value_eur, total_revenue_eur
FROM (
  SELECT
    CASE WHEN EXISTS (
      SELECT 1 FROM loyalty_accounts la
      WHERE la.user_id = o.user_id AND la.tenant_id = o.tenant_id
    ) THEN 'loyalty member' ELSE 'non-member' END AS segment,
    COUNT(DISTINCT o.user_id) AS users_count,
    COUNT(*) AS orders_count_2025,
    ROUND(COUNT(*)::numeric / COUNT(DISTINCT o.user_id), 2) AS avg_orders_per_user,
    ROUND(AVG(o.grand_total / COALESCE(er.rate, CASE WHEN o.currency_code = 'EUR' THEN 1 END))::numeric, 0) AS avg_order_value_eur,
    ROUND(SUM(o.grand_total / COALESCE(er.rate, CASE WHEN o.currency_code = 'EUR' THEN 1 END))::numeric, 0) AS total_revenue_eur
  FROM orders o
  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)
  WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'
  GROUP BY CASE WHEN EXISTS (
      SELECT 1 FROM loyalty_accounts la
      WHERE la.user_id = o.user_id AND la.tenant_id = o.tenant_id
    ) THEN 'loyalty member' ELSE 'non-member' END
) seg
ORDER BY CASE WHEN segment = 'loyalty member' THEN 0 ELSE 1 END
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: segment, users_count, orders_count_2025, avg_orders_per_user, avg_order_value_eur, total_revenue_eur
    • FelixQuery compiles with the expected columns. Submitting.
    • Felix calls submit
      SELECT segment, users_count, orders_count_2025, avg_orders_per_user, avg_order_value_eur, total_revenue_eur
FROM (
  SELECT
    CASE WHEN EXISTS (
      SELECT 1 FROM loyalty_accounts la
      WHERE la.user_id = o.user_id AND la.tenant_id = o.tenant_id
    ) THEN 'loyalty member' ELSE 'non-member' END AS segment,
    COUNT(DISTINCT o.user_id) AS users_count,
    COUNT(*) AS orders_count_2025,
    ROUND(COUNT(*)::numeric / COUNT(DISTINCT o.user_id), 2) AS avg_orders_per_user,
    ROUND(AVG(o.grand_total / COALESCE(er.rate, CASE WHEN o.currency_code = 'EUR' THEN 1 END))::numeric, 0) AS avg_order_value_eur,
    ROUND(SUM(o.grand_total / COALESCE(er.rate, CASE WHEN o.currency_code = 'EUR' THEN 1 END))::numeric, 0) AS total_revenue_eur
  FROM orders o
  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)
  WHERE o.placed_at >= DATE '2025-01-01' AND o.placed_at < DATE '2026-01-01'
  GROUP BY CASE WHEN EXISTS (
      SELECT 1 FROM loyalty_accounts la
      WHERE la.user_id = o.user_id AND la.tenant_id = o.tenant_id
    ) THEN 'loyalty member' ELSE 'non-member' END
) seg
ORDER BY CASE WHEN segment = 'loyalty member' THEN 0 ELSE 1 END
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • FelixSubmitted. The report classifies every 2025 order-placing user as a loyalty member (any `loyalty_accounts` row for that user/tenant) or non-member, then compares order frequency and EUR-converted spend between the two segments, exactly as requested.

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

    Felix's transcript (265 KB) · Sofie Nilsen's transcript (793 KB) · Chloé Marchetti's transcript (263 KB) · the night's plan (5 KB) · the night's reports (5 KB) · the week's memo (3 KB)