Window functions

GROUP BY collapses many rows into one. A window function computes something across a set of related rows — the window — and still returns every row. The OVER clause defines the window:

SUM(amount) OVER (PARTITION BY customer_id ORDER BY day)

• PARTITION BY splits the rows into independent groups (no partition means the whole result is one window).
• ORDER BY orders the rows inside each partition, which ranking and running totals need.

Window functions are evaluated after WHERE, GROUP BY and HAVING, and before the final ORDER BY.

Ranking and ties

Three functions number rows, and they differ only on ties:

1CREATE TABLE scores (player text NOT NULL, points int NOT NULL);2INSERT INTO scores VALUES3  ('Nino', 90), ('Ana', 85), ('Luka', 85), ('Mari', 70), ('Giorgi', 60);

1SELECT2  player,3  points,4  ROW_NUMBER() OVER (ORDER BY points DESC, player) AS row_number,5  RANK()       OVER (ORDER BY points DESC)         AS rank,6  DENSE_RANK() OVER (ORDER BY points DESC)         AS dense_rank7FROM scores8ORDER BY points DESC, player;

• ROW_NUMBER() gives every row a different number; between tied rows the choice is arbitrary unless the window's ORDER BY breaks the tie.
• RANK() gives tied rows the same number and then skips: 1, 2, 2, 4.
• DENSE_RANK() gives tied rows the same number and does not skip: 1, 2, 2, 3.

"Top earner per department" therefore depends on what should happen to a tie. ROW_NUMBER() = 1 returns exactly one person per department and silently drops the other; RANK() = 1 returns both.

Running totals and the default frame

With an ORDER BY in the window, an aggregate becomes cumulative. What it adds up is the frame, and when you write no frame the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — which includes every row that ties with the current one on the ORDER BY:

1CREATE TABLE payments (2  id     int PRIMARY KEY,3  day    date NOT NULL,4  amount int  NOT NULL5);6INSERT INTO payments VALUES7  (1, '2024-03-01', 100),8  (2, '2024-03-02', 50),9  (3, '2024-03-02', 30),10  (4, '2024-03-03', 20);

1SELECT2  id,3  day,4  amount,5  SUM(amount) OVER (ORDER BY day) AS default_frame,6  SUM(amount) OVER (ORDER BY day, id7                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_frame,8  amount - LAG(amount) OVER (ORDER BY day, id) AS change9FROM payments10ORDER BY day, id;

Payments 2 and 3 share a day, so with the default frame both see 180, the total including each other. Spell the frame out with ROWS, and order by something unique, to get a true row-by-row running total.

Neighbours, and filtering

LAG(x) and LEAD(x) return x from the previous or next row in the window — the tool for "change since last time". They return NULL where there is no such row.

A window function cannot appear in WHERE — PostgreSQL says "window functions are not allowed in WHERE" — because WHERE runs first. Compute it in a subquery or CTE and filter outside:

WITH ranked AS (
  SELECT department, name, salary,
         RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS r
  FROM employees
)
SELECT department, name, salary FROM ranked WHERE r = 1;

Common mistakes

• ROW_NUMBER for a question that allows ties. One of the tied rows disappears, and which one can change between runs.
• A running total without a unique order or a ROWS frame. Tied rows get the same, larger total, as payments 2 and 3 show.
• Filtering on the window in WHERE or HAVING. Neither can see it; wrap the query.
• Forgetting . The ranking runs across the whole table instead of restarting for each department.