PostgreSQL Window Functions: A Fast, No-Fluff Reference

Window functions compute across a set of rows related to the current row without collapsing them like GROUP BY. This post assumes you know SQL. It is a dense reference with one schema and runnable examples.

Schema

Three tables: departments → employees → sales. Here is a the ER diagram.

CREATE TABLE departments (
    id   SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE employees (
    id            SERIAL PRIMARY KEY,
    name          TEXT NOT NULL,
    department_id INT NOT NULL REFERENCES departments(id),
    salary        NUMERIC(10,2) NOT NULL,
    hire_date     DATE NOT NULL
);

CREATE TABLE sales (
    id          SERIAL PRIMARY KEY,
    employee_id INT NOT NULL REFERENCES employees(id),
    sale_date   DATE NOT NULL,
    amount      NUMERIC(10,2) NOT NULL
);

INSERT INTO departments (name) VALUES
('Engineering'), ('Sales'), ('Marketing');

INSERT INTO employees (name, department_id, salary, hire_date) VALUES
('Alice', 1, 9000, '2020-01-15'),
('Bob',   1, 8000, '2020-06-01'),
('Carol', 1, 8000, '2021-03-10'),
('Dave',  1, 6500, '2022-09-01'),
('Eve',   2, 7000, '2019-11-20'),
('Frank', 2, 5500, '2021-07-05'),
('Grace', 2, 5500, '2022-01-17'),
('Heidi', 3, 6000, '2020-08-12'),
('Ivan',  3, 4800, '2023-02-01');

INSERT INTO sales (employee_id, sale_date, amount) VALUES
(5, '2025-01-10', 1200), (5, '2025-02-14', 1500),
(5, '2025-03-20',  900), (5, '2025-04-18', 1800),
(6, '2025-01-22',  800), (6, '2025-02-25', 1100),
(6, '2025-03-30', 1100), (6, '2025-04-12',  700),
(7, '2025-01-05',  500), (7, '2025-02-08', 1300),
(7, '2025-03-15',  600), (7, '2025-04-28', 1000);

The data has deliberate salary ties (Bob/Carol, Frank/Grace) and repeated sale amounts, so the ranking and frame behavior is visible.

Anatomy

function() OVER (
    [PARTITION BY ...]   -- split rows into groups
    [ORDER BY ...]       -- order within each group
    [frame_clause]       -- which rows around the current row to use
)

• PARTITION BY is like GROUP BY without collapsing rows. Omit it and the whole result set is one partition.
• ORDER BY defines row order inside the partition. It also changes the default frame (see Frames).
• Reuse definitions with a named window: WINDOW w AS (PARTITION BY ... ORDER BY ...).
• Window functions run after WHERE/GROUP BY/HAVING. You can't filter on them directly. Wrap them in a CTE or subquery. Postgres has no QUALIFY.

Ranking: ROW_NUMBER, RANK, DENSE_RANK

SELECT d.name AS dept, e.name, e.salary,
       ROW_NUMBER() OVER w AS rn,
       RANK()       OVER w AS rnk,
       DENSE_RANK() OVER w AS drnk
FROM employees e
JOIN departments d ON d.id = e.department_id
WINDOW w AS (PARTITION BY e.department_id ORDER BY e.salary DESC);

Engineering result:

name	salary	rn	rnk	drnk
Alice	9000	1	1	1
Bob	8000	2	2	2
Carol	8000	3	2	2
Dave	6500	4	4	3

• ROW_NUMBER is always unique. Ties are broken arbitrarily unless you add a tiebreaker to ORDER BY.
• RANK gives ties the same rank and leaves gaps (1, 2, 2, 4).
• DENSE_RANK gives ties the same rank with no gaps (1, 2, 2, 3).

Top-N per group. Filter in an outer query:

SELECT * FROM (
    SELECT name, department_id, salary,
           ROW_NUMBER() OVER (PARTITION BY department_id
                              ORDER BY salary DESC, hire_date) AS rn
    FROM employees
) t
WHERE rn <= 2;

The same ROW_NUMBER() ... WHERE rn = 1 pattern is the cleanest way to dedupe.

Offsets: LAG and LEAD

SELECT employee_id, sale_date, amount,
       LAG(amount)        OVER w AS prev_amount,
       LEAD(amount)       OVER w AS next_amount,
       LAG(amount, 1, 0)  OVER w AS prev_or_zero,
       ROUND(100.0 * (amount - LAG(amount) OVER w)
             / NULLIF(LAG(amount) OVER w, 0), 1) AS pct_change
FROM sales
WINDOW w AS (PARTITION BY employee_id ORDER BY sale_date);

• Signature: LAG(expr [, offset [, default]]). The default offset is 1, and the default value is NULL.
• The first row per partition gets NULL from LAG, and the last gets NULL from LEAD.
• Always guard growth math with NULLIF(..., 0).

Value functions: FIRST_VALUE, LAST_VALUE, NTH_VALUE

SELECT name, department_id, salary,
       FIRST_VALUE(name) OVER w AS top_earner,
       LAST_VALUE(name)  OVER (w ROWS BETWEEN UNBOUNDED PRECEDING
                                          AND UNBOUNDED FOLLOWING) AS lowest_earner,
       NTH_VALUE(name, 2) OVER (w ROWS BETWEEN UNBOUNDED PRECEDING
                                           AND UNBOUNDED FOLLOWING) AS second_earner
FROM employees
WINDOW w AS (PARTITION BY department_id ORDER BY salary DESC, hire_date);

The classic trap. With ORDER BY, the default frame ends at the current row. A bare LAST_VALUE(x) OVER (ORDER BY ...) returns the current row's value, not the last one in the partition. Widen the frame to UNBOUNDED FOLLOWING.

NTH_VALUE returns NULL when the frame doesn't yet contain an Nth row. It has the same frame sensitivity as LAST_VALUE.

Aggregates as windows: SUM, AVG, COUNT

SELECT employee_id, sale_date, amount,
       SUM(amount) OVER (PARTITION BY employee_id ORDER BY sale_date) AS running_total,
       AVG(amount) OVER (PARTITION BY employee_id ORDER BY sale_date
                         ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)   AS moving_avg_2,
       COUNT(*)    OVER (PARTITION BY employee_id)                   AS n_sales,
       ROUND(100.0 * amount /
             SUM(amount) OVER (PARTITION BY employee_id), 1)         AS pct_of_total
FROM sales;

• With ORDER BY, aggregates become cumulative (running totals, running averages).
• Without ORDER BY, they cover the whole partition, which is useful for share-of-total.
• COUNT(col) skips NULLs, while COUNT(*) doesn't.

Bucketing: NTILE

SELECT name, salary,
       NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

Nine rows into four buckets gives sizes 3, 2, 2, 2. When rows don't divide evenly, the first buckets get the extra rows. NTILE is based on row position, not value, so equal salaries can land in different buckets. Use PERCENT_RANK or CUME_DIST if you need value-aware cutoffs.

Frames

The frame is the subset of the partition the function sees for each row.

{ ROWS | RANGE | GROUPS } BETWEEN <start> AND <end> [ EXCLUDE ... ]
<start>/<end>: UNBOUNDED PRECEDING | n PRECEDING | CURRENT ROW | n FOLLOWING | UNBOUNDED FOLLOWING

Defaults:

Clause present	Default frame
ORDER BY	RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
no ORDER BY	the entire partition

The three modes differ in how they treat peers (rows tied on the ORDER BY key):

SELECT name, salary,
  SUM(salary) OVER (ORDER BY salary ROWS   BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_sum,
  SUM(salary) OVER (ORDER BY salary RANGE  BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_sum,
  SUM(salary) OVER (ORDER BY salary GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW)         AS groups_sum
FROM employees;

• ROWS counts physical rows, so ties are split arbitrarily.
• RANGE includes all peers of the current row, so tied rows get the same value. This is why the default running total "jumps" on ties.
• GROUPS (PG 11+) counts peer groups.

Range with offsets works on dates and intervals, which gives a true time-based rolling window:

SELECT employee_id, sale_date, amount,
       SUM(amount) OVER (PARTITION BY employee_id
                         ORDER BY sale_date
                         RANGE BETWEEN INTERVAL '30 days' PRECEDING AND CURRENT ROW)
         AS rolling_30d
FROM sales;

EXCLUDE CURRENT ROW is handy for "compare to everyone else":

AVG(salary) OVER (PARTITION BY department_id
                  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
                  EXCLUDE CURRENT ROW) AS avg_of_peers

Cheat sheet

Need	Use
Unique sequence / dedupe	ROW_NUMBER()
Ranking with gaps / no gaps	RANK() / DENSE_RANK()
Previous / next row	LAG() / LEAD()
First / last / Nth in group	FIRST_VALUE / LAST_VALUE / NTH_VALUE (mind the frame)
Running total / moving average	SUM/AVG() OVER (ORDER BY ... ROWS ...)
Share of total	x / SUM(x) OVER (PARTITION BY ...)
Equal-size buckets	NTILE(n)
Time-based rolling window	RANGE BETWEEN INTERVAL '...' PRECEDING AND CURRENT ROW

Performance notes

• A window sort is driven by PARTITION BY + ORDER BY. An index matching that column order can let Postgres skip the sort.
• Windows sharing the same definition are computed in one pass, so use a named WINDOW clause to make that explicit.
• Filter before you window. Fewer rows into the window node means a cheaper sort.
• Check EXPLAIN (ANALYZE) for the WindowAgg node and any disk-spilling sorts.