Topic 24 of 52 · Advanced
Window Functions
ROW_NUMBER, RANK, LAG/LEAD and running totals without collapsing rows.
Concept
A window function computes across a set of related rows (the window) but keeps every row, unlike GROUP BY.
OVER (PARTITION BY ... ORDER BY ...) defines the window. ROW_NUMBER gives 1,2,3; RANK skips after ties (1,1,3); DENSE_RANK doesn't (1,1,2).
LAG/LEAD read previous/next rows; SUM() OVER (ORDER BY ...) gives running totals; frames like ROWS BETWEEN 6 PRECEDING AND CURRENT ROW give moving averages.
Cheat sheet
| Function | Use |
|---|---|
| ROW_NUMBER() | De-duplicate, top-1 per group |
| RANK() / DENSE_RANK() | Top-N with ties |
| LAG() / LEAD() | Compare with previous/next |
| SUM() OVER | Running total |
| NTILE(n) | Quartiles / buckets |
Code (SQL · MySQL 8)
-- Top 3 salaries per department
SELECT * FROM (
SELECT name, dept_id, salary,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) rnk
FROM employees
) t WHERE rnk <= 3;
-- Month-over-month growth and running total
SELECT m, revenue,
revenue - LAG(revenue) OVER (ORDER BY m) AS change,
SUM(revenue) OVER (ORDER BY m) AS running_total
FROM monthly_revenue;Remember
- Windows can't be used in WHERE — wrap in a subquery/CTE.
- Pick ROW_NUMBER vs RANK vs DENSE_RANK based on tie handling.
- Default frame with ORDER BY is RANGE UNBOUNDED PRECEDING to CURRENT ROW.
Interview questions
Delete duplicate rows keeping one.
Use ROW_NUMBER() OVER (PARTITION BY dup_cols ORDER BY id) and delete rows where the number > 1 (via a CTE/derived table joined back on id).
Find users who logged in 3 consecutive days.
Subtract ROW_NUMBER() (in days) from login_date per user; equal results form a streak — group by that and keep counts ≥ 3.
Lesson test
Take an 8-question test on “Window Functions” — concepts, output prediction, bug spotting and interview questions.