Users Outperforming Control

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

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

Walmart

Problem

The outperformer list an analyst pulled from ab_results shows some treatment_a users twice, because their results were logged more than once. List each treatment_a user just once if their value beats the average control value for their own test, with their treatment value and that control average.

Treatment beat control. For these users.

Tables

ab_results

Columns: result_id INTEGER, test_name TEXT, variant TEXT, user_id INTEGER, metric TEXT, value REAL, created TEXT

Expected output

Worked solution and explanation

Why this problem exists in real interviews

This tests correlated subqueries or window functions for cross-variant comparison. Interviewers check whether you can compute a baseline from one group and compare individual rows from another group against it.

Break down the requirements

Step 1: Compute control average per test

Compute the per-test control baseline: a derived table averages value over all control rows, grouped by test_name. Use LOWER(variant)='control' so the case variants in the seed (CONTROL / Control / control) are all counted as one control group.

Step 2: Filter treatment_a users exceeding the control average

Join each treatment_a row to its test's baseline on test_name, keeping only rows whose value exceeds the average control value for the same test.

Step 3: Return user, value, and control average

Collapse the seed's duplicate treatment_a rows (each user appears twice with the same value, one row carrying an inflated 10000xxx result_id) down to one row per user via GROUP BY user_id with MAX(value); a user belongs to a single test so the control average is constant within the group.

The solution

SELECT
    t.user_id,
    MAX(t.value) AS treatment_value,
    c.avg_control_value
FROM ab_results t
JOIN (
    SELECT test_name, AVG(value) AS avg_control_value
    FROM ab_results
    WHERE LOWER(variant) = 'control'
    GROUP BY test_name
) c ON t.test_name = c.test_name
WHERE t.variant = 'treatment_a'
  AND t.value > c.avg_control_value
GROUP BY t.user_id, c.avg_control_value
;

Common follow-up questions

• How would you compute the lift percentage for each user? (Calculate `(value - control_avg) / control_avg * 100` for percentage lift.)
• What if there are multiple treatment variants? (Filter to each variant separately or add variant to the output for comparison.)
• How would you test statistical significance instead of just comparing to the mean? (Tests knowledge of confidence intervals, t-tests, or bootstrap methods beyond simple SQL.)