# The A/B Verdict > Variant A or Variant B. The conversion numbers pick the winner. Canonical URL: 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. ## Tables ### experiments - exp_id INTEGER - exp_name TEXT - variant TEXT - user_id INTEGER - outcome REAL - created TEXT - platform TEXT Sample rows (10 of 200): | exp_id | exp_name | variant | user_id | outcome | created | platform | | --- | --- | --- | --- | --- | --- | --- | | 1053 | checkout_v3 | variant_a | 100 | 11.3 | 2026-02-02 | android | | 1106 | onboard_flow | variant_b | 197 | 22.6 | 2026-03-03 | web | | 1159 | pricing_tier | variant_c | 294 | 33.9 | 2026-04-04 | iOS | | 1212 | search_rank | holdout | 391 | 45.2 | 2026-05-05 | Android | | 1265 | rec_model_b | control | 488 | 56.5 | 2026-06-06 | NULL | | 1318 | notif_freq | variant_a | 585 | NULL | 2026-07-07 | WEB | | 1371 | home_layout | variant_b | 682 | 79.1 | 2026-08-08 | NULL | | 1424 | new_feed_algo | variant_c | 779 | 90.4 | 2026-09-09 | ios | | 1477 | checkout_v3 | holdout | 876 | 1.7 | 2026-01-10 | android | | 1530 | onboard_flow | control | 973 | 13 | 2026-02-11 | web | ## Expected output First 5 of 8 rows: | exp_name | positive_count | non_positive_count | avg_outcome | | --- | --- | --- | --- | | checkout_v3 | 26 | 0 | 46.00769230769231 | | home_layout | 24 | 0 | 51.300000000000004 | | new_feed_algo | 16 | 0 | 54.9 | | onboard_flow | 18 | 0 | 44.91111111111111 | | pricing_tier | 26 | 0 | 45.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. > **Not positive is not the same as non-positive** > > Every positive/negative split has a third state hiding in it. `NULL > 0` is unknown and `NULL <= 0` is unknown too, so a row with no outcome belongs to neither count. Give each bucket its own predicate and the database sorts the unknowns out for you. --- ### 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** ```sql 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. > **A strict less-than drops the zeros** > > Candidates who write `outcome < 0` for the second bucket quietly lose every zero. Zero is not positive; it belongs on the non-positive side. > **The gap between the counts is the evidence** > > The senior tell is asking what a blank `outcome` means before writing anything, then checking that `positive_count + non_positive_count` does not equal the row count. That gap is proof the unknowns were handled, not swept. > **One scan beats three filtered queries** > > Two million rows, one sequential scan, one hash aggregate over about 40 groups. Three separate filtered queries joined back together would read the table three times for the same answer. ## Common follow-up questions - How would you add a column for the unmeasured outcomes per experiment? _(Tests `SUM(CASE WHEN outcome IS NULL THEN 1 ELSE 0 END)` as a third explicit bucket.)_ - How would you split these metrics by `variant` as well? _(Tests extending the grain to `exp_name, variant`, or pivoting per-variant columns.)_ - What share of measured outcomes were positive per experiment? _(Tests dividing by the measured count, not `COUNT(*)`, and guarding against a zero denominator.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_a_b_verdict) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.