A window function computes a value across a set of related rows (the "window") without collapsing them into one row per group the way GROUP BY does — every input row still appears in the output, now with an extra computed column alongside it:
SELECT name, dept, salary,
RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS salary_rank
FROM Employees;
-- Bob (60000) ranks 1st in Sales, Alice (50000) ranks 2nd
-- Carol (70000) ranks 1st in IT; Dave's NULL salary sorts to the end of IT and gets the last rank
PARTITION BY divides rows into groups the way GROUP BY would, but OVER keeps every individual row in the result instead of collapsing each group into one — this is the core distinction from a plain aggregate query. RANK(), DENSE_RANK(), and ROW_NUMBER() differ only in how they treat ties: RANK() leaves a gap after a tie (1, 1, 3), DENSE_RANK() doesn't (1, 1, 2), and ROW_NUMBER() breaks ties arbitrarily to always produce a unique sequential number.
A running total is the same idea with a plain aggregate instead of a ranking function:
SELECT name, salary,
SUM(salary) OVER (ORDER BY id) AS running_total
FROM Employees;
Window functions are also the standard, efficient answer to "top N rows per group" — a query shape that's awkward to express correctly with a plain GROUP BY:
SELECT name, dept, salary FROM (
SELECT name, dept, salary,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
FROM Employees
) ranked
WHERE rn = 1;
-- the highest earner in each department, one row per dept