Both Arms of the Trial

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

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Meta

Problem

Our experimentation platform logs one record per user assignment, tagged with the feature test and the variant the user landed in. Find the tests that routed more assignments to 'variant_a' than to 'variant_b'.

Some features were tried both ways.

Tables

experiments

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

Expected output

Worked solution and explanation

What this problem really is

This is a comparison of two subpopulations inside one group, wearing an experiment-tracking costume. For each test the real question is simple: did more assignments land in 'variant_a' than in 'variant_b'? On this data every test ran both arms, so anyone who reaches for WHERE variant = 'variant_a' has already deleted every variant_b row and can no longer compare the two arms at all. The move that works is to keep all of a test's rows inside the group and count each variant separately, then compare those two counts. Get it wrong and you either return nothing, or you fall back to raw row totals and flag tests that were never actually skewed toward variant_a.

WHERE (wrong)

WHERE variant = 'variant_a' throws away every variant_b row before grouping, so within a test there is no variant_b left to compare against. You can count one arm but never weigh it against the other.

Conditional counts (right)

Keep all rows in the group and use SUM(CASE WHEN ...) per variant. Both arms' rows stay in the group, so HAVING can compare the two counts directly.

Building it step by step

Step 1: Group by the test

GROUP BY exp_name collapses all assignment records for a test into a single row, so the two-arm comparison is asked once per test.

Step 2: Count each arm conditionally

SUM(CASE WHEN variant = 'variant_a' THEN 1 ELSE 0 END) counts the variant_a assignments in each group, and the same expression for variant_b counts its assignments. Rows of other variants (variant_c, holdout, control) contribute 0 to both and drop out of the comparison. Note this counts assignment records, so a user assigned twice counts twice.

Step 3: Compare the two counts

HAVING the variant_a count > the variant_b count keeps only the tests skewed toward variant_a. An even split (equal counts) is excluded by the strict greater-than. Return exp_name and order it alphabetically.

The solution

SELECT exp_name
FROM experiments
GROUP BY exp_name
HAVING SUM(CASE WHEN variant = 'variant_a' THEN 1 ELSE 0 END) > SUM(CASE WHEN variant = 'variant_b' THEN 1 ELSE 0 END)

Common follow-up questions

• How would you change this to flag only tests where `variant_a` drew at least twice as many assignments as `variant_b`? (Tests turning a strict comparison into a magnitude threshold.)
• If a user can be re-assigned, how would you compare distinct users per arm instead of raw assignment records? (Tests whether the candidate switches from counting records to counting distinct users.)
• With millions of rows and only a handful of distinct variants, what index would keep this single-pass aggregate cheap? (Tests indexing intuition on the grouping and filter columns.)