Balance of Arms

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

Domain

SQL

Difficulty

hard

Seniority

L4

Asked in

ESPN

Problem

An experimentation platform checks that each test's arms are balanced before readout, and treats every arm that isn't 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 to three decimals. Leave the ratio empty for any experiment whose control and holdout arms drew the same number of unique users, since those splits get reviewed on a separate track.

Every test has two sides. Count who actually landed on each.

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 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 set 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, because the same person lands on many assignment and outcome events so COUNT(*) silently inflates every arm; and recognizing the splits the team pulls out of the ratio. Miss the distinct count and every ratio is skewed by whoever was busiest; miss the guard and you report a number for a split the team never wanted scored.

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 seed, checkout_v3's control arm has 4 rows but only 2 distinct user_id, and its treatment side has 22 rows but 11 distinct users. COUNT(*) or COUNT(CASE WHEN ... THEN 1 END) would report 4 and 22 as the arm sizes. COUNT(DISTINCT CASE WHEN ... THEN user_id END) collapses the duplicate events back to people and gives the real 2 and 11.

Step 3: Blank the ratio when the baselines tie

The control and holdout arms are the two untreated baselines. When they come out the same size, the team reviews that split on a separate track and does not want a ratio here: new_feed_algo drew 2 distinct users into each, and search_rank drew 3 into each. Blank those with NULLIF(control_users, holdout_users): when the two counts match it returns null and the division collapses to null, otherwise it returns the control count and the ratio computes as treatment over control. Multiply the numerator by 1.0 first so SQLite does real division instead of integer truncation.

The solution

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),
               COUNT(DISTINCT CASE WHEN variant = 'holdout' THEN user_id END)
           ), 3
       ) AS treatment_to_control_ratio
FROM experiments
GROUP BY exp_name
;

Counts rows (wrong)

COUNT(CASE WHEN variant = 'control' THEN 1 END) reports checkout_v3 as 4 control and 22 treatment, because every assignment and outcome row is tallied, not every user.

Counts users (right)

COUNT(DISTINCT CASE WHEN variant = 'control' THEN user_id END) reports checkout_v3 as 2 control and 11 treatment, collapsing repeat rows back to people.

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.)