Who Moved the Needle

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: Spotify

Problem

We're compiling a leaderboard for the onboarding_v3 experiment, where a user's standing is the total metric value they drove. Return the users in the top ten standings, biggest first, along with the variant each was assigned.

In every experiment, a handful of users carry the result. Find them.

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 5 of 10 rows
rnk	variant	user_id	total_value
1	treatment_a	4950	197.4
2	treatment_b	8442	183.8
3	treatment_a	2040	175.4
5	Control	9024	148.2
8	CONTROL	9606	112.6

Worked solution and explanation

What this really is

Beneath the leaderboard costume this is a top-N-with-ties problem, and it is decided almost entirely by which ranking function you reach for. When two users tie on total value near the cutoff, the real question is whether your 'top ten' keeps both of them and whether the genuine eleventh-best user still lands at rank eleven instead of getting bumped off the board. Reach for RANK and a pair of ties at the top leaves a gap that shoves a real top-ten standing out of the results. Reach for ROW_NUMBER and you return exactly ten tidy rows but break the tie on a coin flip, hiding the fact that two users are actually level. DENSE_RANK is the only one that keeps tied users together and keeps the ranks contiguous. The second, quieter trap: you must total each user's measurements into one number before you rank, or the window ranks raw rows and the same user surfaces twice.

Reading the requirements

Step 1: Scope to the one test

Filter WHERE test_name = 'onboarding_v3' first. The table holds millions of rows across thousands of experiments, so this filter is the single largest cost reducer in the plan, and it has to happen before any aggregation or ranking or you rank the wrong population entirely.

Step 2: Total each user before ranking

One user can log several measurements inside the test. GROUP BY variant, user_id with SUM(value) gives one total per user, and ranking by SUM(value) rather than raw value is what stops a user from landing on the board more than once. SUM quietly ignores NULL measurements, which matches the intent here.

Step 3: `DENSE_RANK`, then filter in an outer query

DENSE_RANK() OVER (ORDER BY SUM(value) DESC) keeps tied users on the same rank with no gaps. Because window functions are illegal in WHERE, wrap the ranked query in a subquery and filter rnk <= 10 on the outside. That can return more than ten rows when users tie, which is precisely what 'top ten standings' means.

The solution

Scope, total, `DENSE_RANK`, then slice

SELECT rnk, variant, user_id, total_value
FROM (
  SELECT variant, user_id, SUM(value) AS total_value,
    DENSE_RANK() OVER (ORDER BY SUM(value) DESC) AS rnk
  FROM ab_results
  WHERE test_name = 'onboarding_v3'
  GROUP BY variant, user_id
)
WHERE rnk <= 10

RANK at a top-3 cutoff: Two users tie for first, so ranks come out 1, 1, 3, 4. Filtering rank <= 3 keeps the two firsts and the single third, and the fourth row is gone even though only three distinct standings exist. The gap ate a real result.
`DENSE_RANK` at a top-3 cutoff: The same tie comes out 1, 1, 2, 3. Filtering rnk <= 3 keeps three distinct standings including that fourth row, so no genuine top-three performer is lost to a gap.

Common follow-up questions

Why `DENSE_RANK` and not RANK here? (RANK leaves gaps after ties (1, 1, 3, ...). On a top-ten cutoff, two ties at rank one push the next standing to three and you can lose the actual third-best user. `DENSE_RANK` keeps ranks contiguous.)
How would you adapt this to the top ten per metric instead of overall? (Adds PARTITION BY metric to the window; the candidate should note that filtering rnk <= 10 then returns up to ten per metric rather than ten overall.)
What happens to users whose value entries are all NULL? (SUM ignores NULLs, so a user with only NULL measurements totals to NULL and sorts last. The candidate should propose filtering value IS NOT NULL or stating the NULL handling explicitly.)