Window Function

A window function performs a calculation across a set of rows related to the current row, without collapsing them. Unlike GROUP BY, it keeps every row and adds the computed value alongside.

Written with an OVER clause, window functions power rankings (ROW_NUMBER, RANK, DENSE_RANK), running totals (SUM() OVER (ORDER BY ...)), and row-to-row comparisons (LAG, LEAD).

PARTITION BY divides rows into groups the function restarts on — for example, ranking salaries within each department while still returning every employee row.

Example

SELECT first_name, department_id, salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
FROM   employees;