Window Functions

A window function computes a value across a set of related rows — much like an aggregate does — but without collapsing those rows into one. Every input row stays in the result, each one now carrying an extra computed value calculated from its "window" of related rows.

The key distinction from GROUP BY

This is the single most important thing to understand about window functions, so it's worth seeing side by side against the same data.

GROUP BY collapses rows

SQL

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

department | avg_salary
-----------+-----------
sales      |    62000.00
engineering|    88000.00

A window function keeps every row

SQL

SELECT
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees;

name  | department  | salary  | dept_avg_salary
------+-------------+---------+----------------
Alice | sales       |  70000  |        62000.00
Bob   | sales       |  54000  |        62000.00
Carol | engineering | 95000   |        88000.00
Dave  | engineering | 81000   |        88000.00

Every individual employee row survives, each annotated with their department's average — exactly the kind of "compare this row to its group" result that plain GROUP BY can never produce, because grouping necessarily throws away the individual rows.

The OVER clause

OVER (PARTITION BY ... ORDER BY ...) defines the window for each row:

PARTITION BY — splits rows into independent groups, like GROUP BY does, but without collapsing them. Omit it to treat the whole result set as one partition.
ORDER BY (inside OVER) — defines the order used for ranking, running totals, and LAG/LEAD. This is separate from any outer ORDER BY on the query.

A curated tour of key window functions

Function	What it does
`ROW_NUMBER()`	Sequential number per row within its partition (1, 2, 3, ... no ties)
`RANK()`	Rank with gaps after ties (1, 2, 2, 4, ...)
`DENSE_RANK()`	Rank without gaps after ties (1, 2, 2, 3, ...)
`LAG(col, n)`	Value of `col` from `n` rows before the current row
`LEAD(col, n)`	Value of `col` from `n` rows after the current row
`SUM()` / `AVG() OVER (...)`	Running total / running average, typically with `ORDER BY` in the window

Worked example: ranking products by sales within each category

product_sales

SQL

SELECT
  category,
  product,
  sales,
  ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS row_num,
  RANK()       OVER (PARTITION BY category ORDER BY sales DESC) AS rank,
  DENSE_RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS dense_rank
FROM product_sales;

category    | product   | sales | row_num | rank | dense_rank
------------+-----------+-------+---------+------+-----------
electronics | Laptop    |  9000 |       1 |    1 |          1
electronics | Tablet    |  7500 |       2 |    2 |          2
electronics | Charger   |  7500 |       3 |    2 |          2
electronics | Cable     |  1200 |       4 |    4 |          3
furniture   | Desk      |  4200 |       1 |    1 |          1
furniture   | Chair     |  3100 |       2 |    2 |          2

Tablet and Charger tie at 7500 in sales. ROW_NUMBER() breaks the tie arbitrarily (2 and 3), RANK() gives them both rank 2 and then skips to 4, and DENSE_RANK() gives them both rank 2 and continues at 3 with no gap.

Running totals with LAG/LEAD and SUM OVER

SQL

SELECT
  order_date,
  amount,
  LAG(amount)  OVER (ORDER BY order_date) AS previous_day_amount,
  amount - LAG(amount) OVER (ORDER BY order_date) AS change_from_previous,
  SUM(amount)  OVER (ORDER BY order_date) AS running_total
FROM daily_sales
ORDER BY order_date;

order_date | amount | previous_day_amount | change_from_previous | running_total
-----------+--------+----------------------+-----------------------+---------------
2024-01-01 |   500  |                 NULL |                  NULL |            500
2024-01-02 |   620  |                  500 |                    120|           1120
2024-01-03 |   410  |                  620 |                   -210|           1530

Adding ORDER BY inside OVER without a frame clause makes SUM() accumulate from the start of the partition up through the current row — a running total — rather than summing the whole partition at once.

Note

PostgreSQL has excellent, fully-featured window function support, including every standard frame clause option (ROWS, RANGE, and GROUPS framing, plus UNBOUNDED PRECEDING, CURRENT ROW, and offset-based frame bounds) — there is essentially no window-function feature from the SQL standard that PostgreSQL is missing.

Tip

Window functions are evaluated after WHERE, GROUP BY, and HAVING, but before ORDER BY and LIMIT on the outer query — which is why you can safely use WHERE to filter rows first and still window over exactly the rows you expect.

Window functions compute a value across related rows without collapsing them — every input row survives.
PARTITION BY splits rows into groups; ORDER BY inside OVER defines ranking/running-total order.
ROW_NUMBER, RANK, and DENSE_RANK all handle ties differently — pick based on whether you want gaps.
LAG/LEAD reach to neighboring rows; SUM/AVG() OVER (ORDER BY ...) build running totals and averages.