The A/B Verdict

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain
SQL
Difficulty
medium
Seniority
L4
Asked in
Google

Problem

Our experiment readouts keep disagreeing about how each test in experiments actually landed. For every experiment, count the outcomes that came in above zero and the ones that came in at or below zero, alongside the average outcome, listing the experiments alphabetically by name.

Variant A or Variant B. The conversion numbers pick the winner.

Tables

experiments

Columns: exp_id INTEGER, exp_name TEXT, variant TEXT, user_id INTEGER, outcome REAL, created TEXT, platform TEXT

experiments: 10 of 200 rows
exp_idexp_namevariantuser_idoutcomecreatedplatform
1053checkout_v3variant_a10011.32026-02-02android
1106onboard_flowvariant_b19722.62026-03-03web
1159pricing_tiervariant_c29433.92026-04-04iOS
1212search_rankholdout39145.22026-05-05Android
1265rec_model_bcontrol48856.52026-06-06NULL
1318notif_freqvariant_a585NULL2026-07-07WEB
1371home_layoutvariant_b68279.12026-08-08NULL
1424new_feed_algovariant_c77990.42026-09-09ios
1477checkout_v3holdout8761.72026-01-10android
1530onboard_flowcontrol973132026-02-11web

Expected output

First 5 of 8 rows
exp_namepositive_countnon_positive_countavg_outcome
checkout_v326046.00769230769231
home_layout24051.300000000000004
new_feed_algo16054.9
onboard_flow18044.91111111111111
pricing_tier26045.53076923076923

Worked solution and explanation

What this really is

This is a three-valued-logic test wearing an A/B readout costume. Counting positives is easy, and so is the average. The trap is the middle column: the natural shortcut is to call everything that is not positive non-positive, and roughly one row in eight has a NULL outcome. Those rows were never measured. Fold them into the non-positive bucket and every experiment looks worse than it did, which is exactly the kind of number that kills a feature that was actually working.

Building it

Step 1: Keep every row in the scan

All three numbers need the whole table. The moment you write WHERE outcome > 0 you have thrown away the rows the non-positive count and the average depend on, so the scan stays unfiltered and the predicates move inside the aggregates.

Step 2: Give each bucket its own predicate

SUM(CASE WHEN outcome > 0 THEN 1 ELSE 0 END) counts the wins. The non-positive count gets its own SUM(CASE WHEN outcome <= 0 THEN 1 ELSE 0 END), with <= so that an outcome of exactly zero lands there. A NULL fails both tests and drops to ELSE 0 in each.

Step 3: Let AVG skip the unmeasured rows

AVG(outcome) already skips NULL values, so the average is over measured outcomes only. Wrapping it in a COALESCE(outcome, 0) would drag every experiment's mean toward zero.

Step 4: Collapse to one row per experiment

GROUP BY exp_name gives one row per experiment, and ORDER BY exp_name lists them alphabetically. Since each name appears once in the output, that order is total and two correct answers cannot disagree on it.

Three metrics, one pass
SELECT exp_name,
       SUM(CASE WHEN outcome > 0 THEN 1 ELSE 0 END) AS positive_count,
       SUM(CASE WHEN outcome <= 0 THEN 1 ELSE 0 END) AS non_positive_count,
       AVG(outcome) AS avg_outcome
FROM experiments
GROUP BY exp_name
ORDER BY exp_name

Each bucket has its own predicate, so a NULL outcome lands in neither.

Complement shortcut
COUNT(*) - SUM(CASE WHEN outcome > 0 THEN 1 ELSE 0 END) treats every unmeasured row as a loss. With 12% NULL outcomes, each experiment's non-positive count is inflated by all of its blanks.
Explicit predicate
SUM(CASE WHEN outcome <= 0 THEN 1 ELSE 0 END) only counts rows that were measured and came in at or below zero. Positive plus non-positive now falls short of the row count by exactly the blanks, which is the honest answer.

Common follow-up questions