Window Functions

A window function calculates something across a set of related rows without collapsing them into one — unlike GROUP BY, every original row survives, now carrying an extra calculated value alongside it.

ROW_NUMBER: numbering rows

SELECT name, price,
       ROW_NUMBER() OVER (ORDER BY price DESC) AS rank_by_price
FROM products;

   name   | price | rank_by_price 
----------+-------+---------------
 Notebook |  4.50 |             1
 Pen      |  1.20 |             2
(2 rows)

The OVER (...) clause is what makes this a window function rather than a regular one — it defines the "window" of rows the calculation considers, and ORDER BY price DESC inside it controls the numbering order, independent of any ORDER BY on the query as a whole.

PARTITION BY: restarting per group

SELECT name, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

  name  | department  | salary | dept_rank 
--------+-------------+--------+-----------
 Alex   | Engineering |  91000 |         1
 Ava    | Engineering |  85000 |         2
 Noah   | Marketing   |  67000 |         1
 Priya  | Marketing   |  62000 |         2
(4 rows)

PARTITION BY department splits the rows into separate groups, and the ranking restarts from 1 within each one — Engineering and Marketing each get their own independent ranking, without a GROUP BY collapsing anything away.

Running totals

SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

 order_date | amount | running_total 
------------+--------+---------------
 2026-01-02 |  50.00 |         50.00
 2026-01-05 |  30.00 |         80.00
 2026-01-09 |  20.00 |        100.00
(3 rows)

SUM(amount) OVER (ORDER BY order_date) takes an ordinary aggregate function and, by adding OVER, turns it into a running total — each row shows the sum of itself plus every row before it in the specified order, rather than one single collapsed total for the whole table.