Largest A/B Test by Participants

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: L3
Asked in: ESPN

Problem

The data science team is identifying which A/B test drew the widest audience. Return the test with the most unique participants, along with that participant count; if several tests drew the same audience, take the one whose name comes first alphabetically.

The biggest experiment ever run.

Tables

ab_results

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

ab_results: 10 of 200 rows
result_id	test_name	variant	user_id	metric	value	created
237	new_homepage	treatment_a	100	click_rate	13.7	2026-02-02
274	pricing_page	treatment_b	197	revenue	27.4	2026-03-03
311	onboarding_v3	Control	294	bounce	41.1	2026-04-04
348	search_algo_b	CONTROL	391	time_on_page	54.8	2026-05-05
385	rec_engine_v2	control	488	signup	68.5	2026-06-06
422	checkout_flow_v2	treatment_a	585	conv_rate	82.2	2026-07-07
459	new_homepage	treatment_b	682	click_rate	95.9	2026-08-08
496	pricing_page	Control	779	revenue	9.6	2026-09-09
533	onboarding_v3	CONTROL	876	bounce	23.3	2026-01-10
570	search_algo_b	control	973	time_on_page	NULL	2026-02-11

Expected output

First 1 of 1 rows
test_name	unique_participants
new_homepage	17

Worked solution and explanation

Why this problem exists in real interviews

Eight million rows in ab_results and the grain is per-event, not per-person. A power tester who triggered the conversion metric forty times shows up as forty rows. Anyone who writes COUNT(*) is counting telemetry pings and calling it audience reach.

Break down the requirements

Step 1: Aggregate to the human

Group by test_name and reduce per-event rows with COUNT(DISTINCT user_id). A given user_id may appear across variant values or many metric rows in the same test.

Step 2: Rank and slice

Sort descending on the distinct count and take the top entry with LIMIT 1. Several tests can tie on participant count, so add test_name ASC as a tiebreaker to make the winner deterministic: on a tie, the alphabetically earliest name wins.

The solution

TOP TEST BY UNIQUE PARTICIPANTS

SELECT test_name,
       COUNT(DISTINCT user_id) AS unique_participants
FROM ab_results
GROUP BY test_name
ORDER BY unique_participants DESC, test_name LIMIT 1

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

How would you also report the variant split for that winning test? (Tests per-variant aggregation in one pass.)
If someone appears under two `variant` values for the same test, do you count them once? (Probes whether you push back on ambiguous prompts.)
How would you rewrite this if `ab_results` were partitioned by month and you only cared about the last 30 days? (Tests partition pruning on event tables.)