Window functions calculate across a set of related rows without collapsing them into one, the way GROUP BY does. Every input row keeps its place in the output — the function just adds a calculated value alongside it.
Assigns a unique, sequential number to each row within a window.
ROW_NUMBER() OVER (ORDER BY column);SELECT name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM Employee;Common Mistake
Forgetting to PARTITION BY when the numbering should restart per group instead of running across the whole result.
Ranks rows, giving the same rank to ties and skipping the next number.
RANK() OVER (ORDER BY column);SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) AS rank
FROM Employee;Common Mistake
Expecting RANK to produce consecutive numbers when ties exist — it deliberately doesn't.
Like RANK, but never skips a number after a tie.
DENSE_RANK() OVER (ORDER BY column);SELECT name, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
FROM Employee;Common Mistake
Using DENSE_RANK when standard competition ranking (with gaps, i.e. RANK) was actually intended.
Splits rows into a fixed number of roughly equal groups.
NTILE(n) OVER (ORDER BY column);SELECT name, salary,
NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM Employee;Common Mistake
Assuming every group has exactly the same size when the row count doesn't divide evenly by n.
Reads a value from a previous or following row without a self-join.
LAG(column) OVER (ORDER BY column);
LEAD(column) OVER (ORDER BY column);SELECT name, salary,
LAG(salary) OVER (ORDER BY hire_date) AS prev_salary
FROM Employee;Common Mistake
Forgetting ORDER BY inside OVER(), which makes "previous" and "next" row undefined.
Window functions are one of the more advanced tools in this section — the next lesson covers another: subqueries, a query nested inside another query.