A window function is marked by the OVER clause, which defines the set of rows (the "window") the function operates across — optionally partitioned into groups with PARTITION BY, similar in spirit to GROUP BY but without collapsing rows. Common window functions include ROW_NUMBER (a unique sequential number per row), RANK (ties share a rank, leaving a gap afterward), DENSE_RANK (ties share a rank, no gap), and running aggregates like SUM() OVER (ORDER BY ...) for a running total.
PARTITION BY: grouping without collapsing
Every original row survives — unlike GROUP BY, which would collapse each department into one row, PARTITION BY just tells the aggregate which rows to average within, per row.
SELECT
name, department, salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees;
-- EVERY employee row is kept, each showing their own department's averageROW_NUMBER, RANK, DENSE_RANK
These three differ only in how they handle ties — a common source of confusion until seen side by side.
SELECT
name, score,
ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num, -- always unique: 1,2,3,4
RANK() OVER (ORDER BY score DESC) AS rank, -- ties share a rank, then GAP: 1,2,2,4
DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank -- ties share a rank, NO gap: 1,2,2,3
FROM scores;Running totals and moving calculations
Adding ORDER BY inside OVER turns an aggregate into a running calculation — each row sees the aggregate computed up to and including itself, in the specified order.
SELECT
order_date, amount,
SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;
-- each row's running_total is the sum of every amount up to that date