Resolve Ties for Top-10 Users in SQL Query
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
Oculus_Scores
+---------+-------+
| user_id | score |
+---------+-------+
| u1 | 95 |
| u2 | 92 |
| u3 | 90 |
| u4 | 90 |
| u5 | 88 |
+---------+-------+
##### Scenario
Meta DSPA onsite – SQL screen on Oculus dataset; need top-10 leaderboard when ranks 10 and 11 are tied.
##### Question
Table Oculus_Scores(user_id, score) holds daily user scores. Write a SQL query that returns exactly the top-10 users by score even when users ranked 10 and 11 have identical scores. Explain your logic.
##### Hints
Use DENSE_RANK vs ROW_NUMBER; trim with LIMIT or sub-query so only first 10 rows are emitted after ordering.
Overview: This question evaluates proficiency in SQL ranking and tie-resolution techniques for generating deterministic top-N leaderboards, focusing on handling identical scores while producing an exact set of rows.
Table Oculus_Scores(user_id, score) holds daily user scores. Write a SQL query that returns exactly the top 10 users by score, ordered from highest to lowest. When users ranked 10 and 11 have the same score, your query must still return only 10 users by applying a deterministic tie-breaker (for example, user_id). If fewer than 10 users exist, return all of them.
Tables
Oculus_Scores(user_id VARCHAR, score INTEGER)
Hints
- Use a window function to assign an ordering position per user.
- Compare DENSE_RANK and ROW_NUMBER: which guarantees exactly 10 rows when there are ties at the cutoff?