# 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.

![](https://cdn.hashnode.com/uploads/covers/6437a45c54a76290a679588c/c0a82fc8-0eca-478e-bb35-81fe810ea7e0.png align="center")

```sql
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
);
```

```sql
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

```sql
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](#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

```sql
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:

```sql
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

```sql
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

```sql
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

```sql
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 `NULL`s, while `COUNT(*)` doesn't.

## Bucketing: NTILE

```sql
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.

```plaintext
{ 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):

```sql
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:

```sql
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":

```sql
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.