# Who Moved the Needle > In every experiment, a handful of users carry the result. Find them. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## 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. ## 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** ```sql 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 ORDER BY rnk ASC, variant ASC, user_id ASC ``` > **Trick to solving** > > The whole problem turns on one function choice. DENSE_RANK is the only ranking function that both keeps tied users together and never skips a number, so a top-ten cutoff includes every genuine top-ten standing. Everything else in the query is plumbing around that one decision. > **Interviewers watch for** > > A strong candidate totals value before ranking and reaches for DENSE_RANK without prompting, then explains why the rnk <= 10 filter has to live in an outer query. Reaching straight for ROW_NUMBER, or ranking raw rows with no GROUP BY, is the tell that someone memorized a template instead of reasoning about ties. > **Common pitfall** > > ROW_NUMBER returns a clean ten rows but breaks ties arbitrarily, silently dropping a user who is genuinely tied for a spot. RANK preserves ties but leaves gaps, so two ties at the top can push the real third-best standing to rank three and out of a tight cutoff. And putting WHERE rnk <= 10 in the inner query fails outright: window functions cannot appear in WHERE. > **Cost analysis** > > The table is around 6,000,000 rows. The test_name filter is the lever: a single experiment is a thin slice, so an index on (test_name, user_id, value) lets the engine read only that slice, hash-aggregate the per-user totals, then sort the deduped rows for the window. Without it you scan all six million rows before any of the cheap work begins. **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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/who_moved_the_needle) - [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.