ROW_NUMBER() Ranking
Overview
ROW_NUMBER() is the absolute most frequently tested Window Function in Data Engineering interviews. It assigns a unique, sequential integer (1, 2, 3...) to every row, based on an ORDER BY clause. When combined with PARTITION BY, it restarts the count at 1 for every new bucket. It is the ultimate tool for solving the 'Top N per Group' problem (e.g., 'Find the Top 3 highest paid employees in EVERY department').
Syntax
-- Syntax: ROW_NUMBER() OVER ( [PARTITION BY] ORDER BY )
SELECT
department,
emp_name,
salary,
-- Assigns #1 to the highest paid in the Dept, #2 to the second, etc.
-- When the Dept changes, the counter instantly resets to 1!
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS rank_in_dept
FROM employees;Common Pitfalls
- Using
ROW_NUMBER()to handle exact ties.ROW_NUMBER()guarantees strict sequential uniqueness. If Alice and Bob both make exactly $100,000,ROW_NUMBER()will randomly assign one as #1 and the other as #2 (non-deterministic behavior). If ties are important, you MUST useRANK()orDENSE_RANK(). - Trying to filter by it immediately.
WHERE ROW_NUMBER() = 1crashes. You must wrap the entire query in a CTE first.
Interview Questions
You wrap a ROW_NUMBER() function in a CTE, partitioning by user_id and ordering by login_date DESC. In the outer query, you simply SELECT * FROM CTE WHERE row_num = 1.
Real-World Example
The classic interview answer: Finding the top 3 highest paid employees in every single department simultaneously.
-- Step 1: Calculate the ranks
WITH RankedEmployees AS (
SELECT
department, emp_name, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees
)
-- Step 2: Filter the ranks!
SELECT *
FROM RankedEmployees
WHERE rank <= 3;Check Your Knowledge
Test your understanding of ROW_NUMBER() Ranking with these quick questions.