# Balance of Arms > Every test has two sides. Count who actually landed on each. Canonical URL: Domain: SQL · Difficulty: hard · Seniority: L4 ## Problem An experimentation platform needs to confirm each test's groups are balanced before readout, and treats every arm that is not the control group as treatment. For each experiment, find the unique users in control, the unique users in treatment, and the treatment-to-control ratio, leaving the ratio empty when an experiment has no control users. ## Worked solution and explanation ### What this is really asking Strip the costume and this is a per-experiment count of distinct people with a derived denominator: for each test you need the unique users in the one control arm against the unique users across every other arm combined. Anyone can write the CASE expressions that fork control from treatment. Two things separate the candidates: counting distinct user_id instead of rows (the same person shows up on many assignment and outcome events, so COUNT(*) silently doubles your groups), and guarding the divide when an experiment has zero control users. Miss the first and every ratio is inflated by whoever was busiest; miss the second and the query either dies on a divide-by-zero or returns a meaningless number for a test that never had a control arm. --- ### The forks that decide it #### Step 1: Treatment is everything that is not control Look at the variant column in the sample: it holds control, holdout, variant_a, variant_b, variant_c. Only the literal 'control' is the denominator; everything else, holdout included, is a treatment arm. So the control predicate is variant = 'control' and the treatment predicate is variant <> 'control'. Enumerating a fixed list of treatment names instead breaks the moment a new arm ships. #### Step 2: Count users, not rows A user appears on one row per event, not one row per experiment. In the sample, checkout_v3 control has three rows but only two distinct user_id (100 shows up twice), and its treatment side has four rows but three distinct users (311 shows up twice). COUNT(*) or COUNT(CASE WHEN ... THEN 1 END) would report 3 and 4, giving a ratio of about 1.33. COUNT(DISTINCT CASE WHEN ... THEN user_id END) collapses the duplicate events back to people and gives the real 2 and 3. #### Step 3: Guard the empty-control divide home_layout in the sample has no control rows, so its control count is 0 and the ratio is undefined. Wrap the denominator in NULLIF(control_count, 0) so the division produces null exactly when there is no control group, which is the behavior the prompt asks for. Multiply the numerator by 1.0 first so SQLite performs real division instead of integer truncation. --- ### The solution **Conditional distinct counts with a guarded ratio** ```sql SELECT exp_name, COUNT(DISTINCT CASE WHEN variant = 'control' THEN user_id END) AS control_users, COUNT(DISTINCT CASE WHEN variant <> 'control' THEN user_id END) AS treatment_users, ROUND( COUNT(DISTINCT CASE WHEN variant <> 'control' THEN user_id END) * 1.0 / NULLIF(COUNT(DISTINCT CASE WHEN variant = 'control' THEN user_id END), 0), 3 ) AS treatment_to_control_ratio FROM experiments GROUP BY exp_name ORDER BY exp_name; ``` *One pass over the table; both arms counted in the same GROUP BY.* > **Trick to solving** > > The whole problem collapses to COUNT(DISTINCT CASE WHEN THEN user_id END). The CASE selects the arm, DISTINCT counts people, and you compute both the control and treatment counts in a single scan. The ratio is just those two expressions divided, with NULLIF protecting the denominator. **Counts rows (wrong)** COUNT(CASE WHEN variant = 'control' THEN 1 END) reports checkout_v3 as 3 control and 4 treatment, ratio about 1.33, because users 100 and 311 are counted on every event row. **Counts users (right)** COUNT(DISTINCT CASE WHEN variant = 'control' THEN user_id END) reports checkout_v3 as 2 control and 3 treatment, ratio 1.5, with duplicate events collapsed to people. > **Common pitfall** > > Using COUNT(*) or COUNT(CASE ... THEN 1 END) counts assignment and outcome events, not users. One heavy user with many rows quietly skews the balance, which is the opposite of what a balance check is supposed to surface. > **Interviewers watch for** > > The NULLIF guard offered without prompting is the senior tell: it shows you pictured the experiment that never got a control arm. Naming a skewed ratio (a 70/30 split where 50/50 was expected) as a sample-ratio-mismatch signal worth investigating is the follow-up that earns the level. > **Cost analysis** > > One sequential scan, no joins. Both conditional distinct counts are produced in the same GROUP BY pass, so even at hundreds of millions of assignment rows this stays a single aggregation; the dominant cost is the per-experiment distinct sets, which the engine builds during the same grouping. --- ## Common follow-up questions - How far from the designed split would a ratio have to drift before you treated it as a data quality problem? _(Tests sample ratio mismatch (SRM) awareness.)_ - How would you decide whether an observed imbalance is statistically significant rather than noise? _(Tests statistical testing knowledge.)_ - How would the counts change if a user could be assigned to more than one arm of the same experiment? _(Tests intent-to-treat reasoning.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/balance_of_arms) - [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.