# Both Arms of the Trial > Some features were tried both ways. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 ## 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'. ## 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`. > **Trick to solving** > > "More assignments in A than in B" at the group level is a comparison of two conditional counts. Keep every row in the group, count each variant independently, and compare them in HAVING. > > 1. Group by the test (`exp_name`) > 2. Count each variant: `SUM(CASE WHEN variant = 'variant_a' THEN 1 ELSE 0 END)` and the same for `variant_b` > 3. `HAVING` the `variant_a` count strictly greater than the `variant_b` count **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 **Conditional counts compared in HAVING** ```sql 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) ``` > **Cost analysis** > > The query does one full scan of `experiments` and aggregates in a single pass; there is no self-join, so the plan stays linear in the row count. A covering index on (`exp_name`, variant) would let the engine build the per-test counts without touching the wide row, cutting I/O materially as the table grows. > **Interviewers watch for** > > The tell is whether you reach for two conditional counts and compare them, rather than filtering rows by variant. A candidate who writes WHERE variant = '`variant_a`' has already made the comparison impossible; a candidate who compares total group row counts is answering a different question. Placing the comparison in HAVING, after aggregation, is the senior move. > **Common pitfall** > > Two mistakes dominate. First, using a non-strict comparison (>=) and letting evenly split tests slip in, when the ask is strictly more in `variant_a`. Second, comparing raw group row counts instead of per-variant counts, which ignores how the traffic actually split between the two arms. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/both_arms_of_the_trial) - [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.