Rank Scores with Gaps
ASKED IN INTERVIEWMeta
Problem Statement
Meta needs to rank users based on their engagement scores. If two users have the same score, they should both receive the same rank. However, the next rank in the sequence must be skipped to account for the tie (e.g., 1, 2, 2, 4).
Rules:
- Rank must be calculated based on the score in descending order.
- Use the RANK() logic (with gaps).
- Output columns: user_id, score, rank.
- Results must be sorted by rank in ascending order.
Tests your understanding of
Window Functions, Ranking and Sorting
Input Tables
Scores
| user_id(INTEGER) | score(DECIMAL) |
|---|---|
| 1 | 4.5 |
| 2 | 4.5 |
| 3 | 3.85 |
| 4 | 4 |
| 5 | 5 |
User_Profiles
| user_id(INTEGER) | username(VARCHAR) |
|---|---|
| 1 | meta_dev |
| 5 | zuck |
Activity_Logs
| log_id(INTEGER) | user_id(INTEGER) | last_login(TIMESTAMP) |
|---|---|---|
| 1001 | 1 | 2026-02-09 12:00:00 |
Expected Output
| user_id(INTEGER) | score(DECIMAL) | rank(INTEGER) |
|---|---|---|
| 5 | 5 | 1 |
| 1 | 4.5 | 2 |
| 2 | 4.5 | 2 |
| 4 | 4 | 4 |
| 3 | 3.85 | 5 |
Tags
MediumASKED IN INTERVIEWWindow FunctionsRankingSorting
20-25 min
58%