🔷 SQL: Window Functions — The #1 Interview Topic
Window functions compute a value for each row using OTHER rows, without collapsing the table like GROUP BY. Think "add a column that knows about its neighbors."
Syntax: FUNCTION() OVER (PARTITION BY ... ORDER BY ...)
- PARTITION BY = restart calculation per group (like GROUP BY but keeps all rows)
- ORDER BY = what order the window sees rows in
The three ranking functions
Given sales values: 100, 100, 90, 80:
| Function | Output | How it handles ties |
|---|---|---|
ROW_NUMBER() |
1, 2, 3, 4 | Always unique, ties get arbitrary order |
RANK() |
1, 1, 3, 4 | Same rank for ties, skips next |
DENSE_RANK() |
1, 1, 2, 3 | Same rank for ties, no skip |
When to use which:
- ROW_NUMBER() — need exactly N rows per group
- RANK() — ties share rank, gaps okay (sports standings)
- DENSE_RANK() — ties share rank, no gaps ("second highest salary")
Practice Questions
Q: You need the second-highest salary per department. Which ranking function and why?