← SQL roadmap

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

FunctionUse
ROW_NUMBER()De-duplicate, top-1 per group
RANK() / DENSE_RANK()Top-N with ties
LAG() / LEAD()Compare with previous/next
SUM() OVERRunning 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.

Practise SQL with the AI mentor

Lesson test

Take an 8-question test on “Window Functions” — concepts, output prediction, bug spotting and interview questions.

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.