DB Olympics

Queries · q060 · Head of Growth Marketing · week 1, night 4

Do we run A/B tests, and do we act on the results?

“How many A/B tests has the platform run, in what state are they, and how often does a test actually end with a declared winner?”

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

    Last night's industry breakdown overturned my earlier 'discounted orders are healthier' read: that was Simpson's-paradox-driven by B2B contract pricing (huge baskets, 40+ items/order) and near-free subscription trial offers in Food & Coffee/Pet Supplies (avg order value ~6-8 EUR). Within actual consumer retail verticals (apparel, beauty, electronics, home, sports), discounted orders have equal-or-lower AOV and meaningfully HIGHER return rates than full-price ones — the opposite of the pooled story. That's resolved and ready for the memo, no further query needed there. The loyalty report is the one open thread: members show lower order frequency and AOV than non-members, which is counterintuitive, and I suspect the same composition trap (loyalty concentrated in low-AOV subscription verticals) — worth one check before I tell Chloé 'loyalty doesn't move the needle.' Email engagement (29.9% open, 2.4% click, healthy bounce/unsubscribe) looked like a normal, non-alarming baseline with only one channel actually used, so I'm not spending a slot chasing it further. With this being the last night, I'm using the remaining two slots to close out Chloé's still-open questions: the referral half of Q4, and a first look at Q5 (A/B tests), both as compact baseline reports for the Friday memo.

    Do we run A/B tests, and do we act on the results?
    Question
    How many A/B tests has the platform run, in what state are they, and how often does a test actually end with a declared winner?
    One row per
    ab_tests.status value
    Columns
    • status - ab_tests.status (draft, running, paused, completed, archived)
    • tests_count - count of ab_tests in that status, all tenants, all-time
    • share_of_tests_pct - tests_count / total tests across all statuses * 100, rounded to 1 decimal
    • tests_with_winner_declared_count - count of tests in that status where winner_variant_id is not null
    • avg_variants_per_test - average number of linked ab_variants rows per test in that status, rounded to 1 decimal
    • avg_duration_days - average of (ended_at - started_at) in days for tests in that status where both are not null, rounded to 1 decimal

  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
  t.status,
  t.tests_count,
  ROUND(t.tests_count * 100.0 / (SELECT COUNT(*) FROM ab_tests), 1) AS share_of_tests_pct,
  t.tests_with_winner_declared_count,
  ROUND(t.avg_variants_per_test, 1) AS avg_variants_per_test,
  ROUND(t.avg_duration_days, 1) AS avg_duration_days
FROM (
  SELECT
    a.status,
    COUNT(*) AS tests_count,
    COUNT(*) FILTER (WHERE a.winner_variant_id IS NOT NULL) AS tests_with_winner_declared_count,
    AVG(vc.variant_count) AS avg_variants_per_test,
    AVG(CASE WHEN a.started_at IS NOT NULL AND a.ended_at IS NOT NULL
         THEN EXTRACT(EPOCH FROM (a.ended_at - a.started_at)) / 86400.0 END) AS avg_duration_days
  FROM ab_tests a
  LEFT JOIN (
    SELECT ab_test_id, COUNT(*) AS variant_count
    FROM ab_variants
    GROUP BY ab_test_id
  ) vc ON vc.ab_test_id = a.id
  GROUP BY a.status
) t
ORDER BY t.tests_count DESC, t.status ASC

    “Query compiles and returns the expected columns. Submitting.”

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

    “Submitted. The report groups ab_tests by status (draft/running/paused/completed/archived) with counts, share of total, winner-declared counts, average linked variants per test, and average duration in days for tests with both start and end timestamps — ordered by test count descending, status ascending as tie-break.”

  4. The report comes back overnight

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

    status    | tests_count | share_of_tests_pct | tests_with_winner_declared_count | avg_variants_per_test | avg_duration_days
----------+-------------+--------------------+----------------------------------+-----------------------+------------------
completed |        1036 |               99.6 |                              646 |                   2.0 |               3.0
running   |           4 |                0.4 |                                0 |                   2.0 |              NULL
(2 rows)

  5. It becomes benchmark query q060

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

    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
    • numericsdocs/NUMERICS.md: AVG over an integer computed as a decimal: AVG(CAST(x AS NUMERIC(18,6)))
    • numericsdocs/NUMERICS.md: EXTRACT(EPOCH FROM (b - a)) cast to NUMERIC(18,6)
    • made explicita selected t.col gets AS col, its standard name made explicit (4×)
    • placeholderseconds between
    • 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
    SELECT  t.status,  t.tests_count,  ROUND(t.tests_count * 100.0 / (SELECT COUNT(*) FROM ab_tests), 1) AS share_of_tests_pct,  t.tests_with_winner_declared_count,  ROUND(t.avg_variants_per_test, 1) AS avg_variants_per_test,  ROUND(t.avg_duration_days, 1) AS avg_duration_days  t.status AS status,  t.tests_count AS tests_count,  t.tests_count * 100.0 / (SELECT COUNT(*) FROM ab_tests) AS share_of_tests_pct,  t.tests_with_winner_declared_count AS tests_with_winner_declared_count,  t.avg_variants_per_test AS avg_variants_per_test,  t.avg_duration_days AS avg_duration_daysFROM (  SELECT    a.status,    a.status AS status,    COUNT(*) AS tests_count,    COUNT(*) FILTER (WHERE a.winner_variant_id IS NOT NULL) AS tests_with_winner_declared_count,    AVG(vc.variant_count) AS avg_variants_per_test,    AVG(CAST(vc.variant_count AS NUMERIC(18,6))) AS avg_variants_per_test,    AVG(CASE WHEN a.started_at IS NOT NULL AND a.ended_at IS NOT NULL         THEN EXTRACT(EPOCH FROM (a.ended_at - a.started_at)) / 86400.0 END) AS avg_duration_days         THEN {{seconds_between(a.started_at, a.ended_at)}} / 86400.0 END) AS avg_duration_days  FROM ab_tests a  LEFT JOIN (    SELECT ab_test_id, COUNT(*) AS variant_count    FROM ab_variants    GROUP BY ab_test_id  ) vc ON vc.ab_test_id = a.id  GROUP BY a.status) tORDER BY t.tests_count DESC, t.status ASCORDER BY t.tests_count DESC NULLS FIRST, t.status ASC NULLS LAST

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

    SELECT
  t.status AS status,
  t.tests_count AS tests_count,
  t.tests_count * 100.0 / (SELECT COUNT(*) FROM ab_tests) AS share_of_tests_pct,
  t.tests_with_winner_declared_count AS tests_with_winner_declared_count,
  t.avg_variants_per_test AS avg_variants_per_test,
  t.avg_duration_days AS avg_duration_days
FROM (
  SELECT
    a.status AS status,
    COUNT(*) AS tests_count,
    COUNT(*) FILTER (WHERE a.winner_variant_id IS NOT NULL) AS tests_with_winner_declared_count,
    AVG(CAST(vc.variant_count AS NUMERIC(18,6))) AS avg_variants_per_test,
    AVG(CASE WHEN a.started_at IS NOT NULL AND a.ended_at IS NOT NULL
         THEN CAST(EXTRACT(EPOCH FROM (a.ended_at - a.started_at)) AS NUMERIC(18,6)) / 86400.0 END) AS avg_duration_days
  FROM ab_tests a
  LEFT JOIN (
    SELECT ab_test_id, COUNT(*) AS variant_count
    FROM ab_variants
    GROUP BY ab_test_id
  ) vc ON vc.ab_test_id = a.id
  GROUP BY a.status
) t
ORDER BY t.tests_count DESC NULLS FIRST, t.status ASC NULLS LAST
    1 ms10 ms100 msHyper4.2 msDuckDB4.6 msGizmoSQL7.9 msclickhouse_brew50.1 msFirebolt22.4 msDataFusion29.9 msClickHouse8.8 ms

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

  6. 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 we run A/B tests, and do we act on the results?
Question: How many A/B tests has the platform run, in what state are they, and how often does a test actually end with a declared winner?
Grain: one row per ab_tests.status value
Columns:
- status - ab_tests.status (draft, running, paused, completed, archived)
- tests_count - count of ab_tests in that status, all tenants, all-time
- share_of_tests_pct - tests_count / total tests across all statuses * 100, rounded to 1 decimal
- tests_with_winner_declared_count - count of tests in that status where winner_variant_id is not null
- avg_variants_per_test - average number of linked ab_variants rows per test in that status, rounded to 1 decimal
- avg_duration_days - average of (ended_at - started_at) in days for tests in that status where both are not null, rounded to 1 decimal
Details: Source: ab_tests left joined to ab_variants (count per ab_test_id) for the variants average; no date filter, all-time, all tenants, since this is a first look at whether the capability is used at all. Order by tests_count descending, tie-break status ascending.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Felix calls dry_run
      SELECT
  t.status,
  t.tests_count,
  ROUND(t.tests_count * 100.0 / (SELECT COUNT(*) FROM ab_tests), 1) AS share_of_tests_pct,
  t.tests_with_winner_declared_count,
  ROUND(t.avg_variants_per_test, 1) AS avg_variants_per_test,
  ROUND(t.avg_duration_days, 1) AS avg_duration_days
FROM (
  SELECT
    a.status,
    COUNT(*) AS tests_count,
    COUNT(*) FILTER (WHERE a.winner_variant_id IS NOT NULL) AS tests_with_winner_declared_count,
    AVG(vc.variant_count) AS avg_variants_per_test,
    AVG(CASE WHEN a.started_at IS NOT NULL AND a.ended_at IS NOT NULL
         THEN EXTRACT(EPOCH FROM (a.ended_at - a.started_at)) / 86400.0 END) AS avg_duration_days
  FROM ab_tests a
  LEFT JOIN (
    SELECT ab_test_id, COUNT(*) AS variant_count
    FROM ab_variants
    GROUP BY ab_test_id
  ) vc ON vc.ab_test_id = a.id
  GROUP BY a.status
) t
ORDER BY t.tests_count DESC, t.status ASC
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: status, tests_count, share_of_tests_pct, tests_with_winner_declared_count, avg_variants_per_test, avg_duration_days
    • FelixQuery compiles and returns the expected columns. Submitting.
    • Felix calls submit
      SELECT
  t.status,
  t.tests_count,
  ROUND(t.tests_count * 100.0 / (SELECT COUNT(*) FROM ab_tests), 1) AS share_of_tests_pct,
  t.tests_with_winner_declared_count,
  ROUND(t.avg_variants_per_test, 1) AS avg_variants_per_test,
  ROUND(t.avg_duration_days, 1) AS avg_duration_days
FROM (
  SELECT
    a.status,
    COUNT(*) AS tests_count,
    COUNT(*) FILTER (WHERE a.winner_variant_id IS NOT NULL) AS tests_with_winner_declared_count,
    AVG(vc.variant_count) AS avg_variants_per_test,
    AVG(CASE WHEN a.started_at IS NOT NULL AND a.ended_at IS NOT NULL
         THEN EXTRACT(EPOCH FROM (a.ended_at - a.started_at)) / 86400.0 END) AS avg_duration_days
  FROM ab_tests a
  LEFT JOIN (
    SELECT ab_test_id, COUNT(*) AS variant_count
    FROM ab_variants
    GROUP BY ab_test_id
  ) vc ON vc.ab_test_id = a.id
  GROUP BY a.status
) t
ORDER BY t.tests_count DESC, t.status ASC
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • FelixSubmitted. The report groups `ab_tests` by status (draft/running/paused/completed/archived) with counts, share of total, winner-declared counts, average linked variants per test, and average duration in days for tests with both start and end timestamps — ordered by test count descending, status ascending as tie-break.

    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 (260 KB) · Sofie Nilsen's transcript (793 KB) · Chloé Marchetti's transcript (263 KB) · the night's plan (5 KB) · the night's reports (4 KB) · the week's memo (3 KB)