Window Functions

Ranking, running totals, moving averages and row-to-row differences without collapsing rows.

A window function computes a value over a set of rows related to the current row — but unlike GROUP BY, every row stays in the result. They answer questions that are awkward otherwise: rank within a group, difference from the previous reading, running totals, moving averages.

Ranking, time series and running totals

rank() OVER (PARTITION BY dept_id ORDER BY salary DESC) numbers each person within their department.

salary - avg(salary) OVER (PARTITION BY dept_id) compares each row with its department average.

lag(value) is the previous reading in time order; the first reading has no previous one, so delta is empty (NULL).

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW defines a 3-reading moving window.

sum(hours) OVER (... ORDER BY ...) is a running total.

The OVER clause

SELECT ..., last_value(value) OVER (ORDER BY taken_at) ...

SELECT ..., last_value(value) OVER (ORDER BY taken_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) ...

With ORDER BY, the default frame ends at the current row, so last_value returns the current row's value. Specify the whole partition as the frame.

What is the difference between rank() and row_number()?

row_number() gives every row a distinct number. rank() gives tied rows the same rank and then skips numbers (1, 1, 3).

How do you get the highest-paid employee per department with a window function?

WITH r AS (SELECT *, row_number() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employees) SELECT * FROM r WHERE rn = 1;