Problem: Find highest salary per department
Approach: Use window function (ROW_NUMBER)
Query:
SELECT *,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) as rn
FROM table
When to use: Top N / ranking problems
Why it matters: Used in 80% of real interview SQL problems
Problem: Remove duplicate records and keep the latest entry
Approach: Use ROW_NUMBER with partition and filter
Query:
SELECT * FROM
(SELECT *,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY created_at DESC) as rn
FROM table) t
WHERE rn = 1
When to use: Removing duplicates, keeping latest records
Why it matters: Very common in data pipelines and real datasets