---
title: "Postgres Window Functions Cheatsheet"
slug: "postgres-window-functions"
category: "cheatsheets"
tags: ["cheatsheets", "postgres", "sql", "window-functions", "analytics", "query"]
status: "stable"
last_updated: 2026-05-14
summary: "Postgres window functions: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, FIRST_VALUE, LAST_VALUE, aggregate windows, PARTITION BY, frame clauses."
related:
  [
    "[[backend/postgres]]",
    "[[cheatsheets/postgres-explain]]",
    "[[cheatsheets/sql-joins]]",
    "[[cheatsheets/postgres-functions]]",
    "[[cheatsheets/postgres-types]]",
    "[[backend/prisma]]",
    "[[cheatsheets/index|Cheatsheets]]",
  ]
---

## Overview

Window functions compute a value for each row using a set of rows related to it (the "window"), without collapsing rows like `GROUP BY` does. They run after `WHERE`, `GROUP BY`, and `HAVING` but before `ORDER BY` and `LIMIT`. Use them for rankings, running totals, moving averages, and comparing each row to its neighbors. For query plan interpretation see [[cheatsheets/postgres-explain]].

The general syntax: `function_name([args]) OVER ([PARTITION BY ...] [ORDER BY ...] [frame_clause])`.

## Ranking functions

Ranking functions assign a position to each row within a partition.

| Function | Behavior on ties | Gaps after ties | Example output for scores 90,90,80 |
|---|---|---|---|
| `ROW_NUMBER()` | Arbitrary order within ties | No gaps | 1, 2, 3 |
| `RANK()` | Same rank for ties | Gap after tie group | 1, 1, 3 |
| `DENSE_RANK()` | Same rank for ties | No gaps | 1, 1, 2 |
| `NTILE(n)` | Distribute rows into n buckets | N/A | bucket number 1..n |
| `PERCENT_RANK()` | `(rank-1) / (total_rows-1)` | N/A | 0.0 to 1.0 |
| `CUME_DIST()` | `rows up to and including / total` | N/A | 0 < value <= 1.0 |

```sql
SELECT
  name,
  score,
  RANK()        OVER (ORDER BY score DESC) AS rank,
  DENSE_RANK()  OVER (ORDER BY score DESC) AS dense_rank,
  ROW_NUMBER()  OVER (ORDER BY score DESC) AS row_num
FROM results;
```

Use `DENSE_RANK` when you want to display "2nd place" without skipping numbers after ties. Use `ROW_NUMBER` when you need a unique identifier for pagination.

## Offset functions: LAG and LEAD

`LAG` and `LEAD` access a row at a given offset before or after the current row within the window.

| Function | Returns | Optional args |
|---|---|---|
| `LAG(col, offset, default)` | Value from `offset` rows before | `offset` defaults to 1; `default` if no row exists |
| `LEAD(col, offset, default)` | Value from `offset` rows after | Same defaults |

```sql
SELECT
  date,
  revenue,
  LAG(revenue, 1, 0)  OVER (ORDER BY date) AS prev_revenue,
  LEAD(revenue, 1, 0) OVER (ORDER BY date) AS next_revenue,
  revenue - LAG(revenue, 1, 0) OVER (ORDER BY date) AS delta
FROM daily_revenue;
```

The `default` argument prevents `NULL` from appearing when there is no prior or next row. Without it, the first row's `LAG` is `NULL`.

## Aggregate window functions

Any aggregate function works as a window function by adding an `OVER` clause. Rows are not collapsed.

| Pattern | Example | Returns |
|---|---|---|
| Running sum | `SUM(amount) OVER (ORDER BY date)` | Cumulative total at each row |
| Running average | `AVG(score) OVER (ORDER BY date)` | Cumulative average |
| Partition total | `SUM(amount) OVER (PARTITION BY dept)` | Dept total on every row in that dept |
| Count per partition | `COUNT(*) OVER (PARTITION BY category)` | Group size on every row |
| Max in partition | `MAX(score) OVER (PARTITION BY team)` | Team max on every row |

```sql
SELECT
  dept,
  employee,
  salary,
  SUM(salary)  OVER (PARTITION BY dept) AS dept_total,
  ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY dept), 1) AS pct_of_dept
FROM employees;
```

## PARTITION BY and ORDER BY in OVER

`PARTITION BY` divides rows into independent groups; `ORDER BY` determines row sequence within each partition.

| Clause | Effect when omitted |
|---|---|
| No `PARTITION BY` | Entire result set is one partition |
| No `ORDER BY` | Aggregate covers all rows in partition; ranking is undefined |
| Both `PARTITION BY` and `ORDER BY` | Standard partitioned running computation |

```sql
-- Running total per user, reset for each user
SELECT
  user_id,
  event_date,
  SUM(events) OVER (
    PARTITION BY user_id
    ORDER BY event_date
  ) AS running_total
FROM user_events;
```

## Frame clauses

A frame clause narrows the window further within the ordered partition. The default frame depends on whether `ORDER BY` is present.

| When | Default frame |
|---|---|
| No `ORDER BY` in `OVER` | `RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING` (all rows in partition) |
| `ORDER BY` present | `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` |

| Frame syntax | Covers |
|---|---|
| `ROWS BETWEEN n PRECEDING AND CURRENT ROW` | n rows before current (inclusive) |
| `ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING` | Current row and immediate neighbors |
| `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` | From partition start to current |
| `ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING` | From current to partition end |
| `RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW` | Rows within 7 days (date column required) |

```sql
-- 3-row moving average
SELECT
  date,
  revenue,
  AVG(revenue) OVER (
    ORDER BY date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3
FROM daily_revenue;
```

`ROWS` counts physical rows; `RANGE` uses value distances on the `ORDER BY` column and handles ties differently.

## Common gotchas

- Window functions cannot appear in `WHERE` or `HAVING`. Wrap the query in a CTE or subquery to filter on a window result.
- `ORDER BY` inside `OVER` is separate from the query's `ORDER BY`. The query can be sorted differently from the window.
- `FIRST_VALUE` and `LAST_VALUE` with the default frame (`UNBOUNDED PRECEDING TO CURRENT ROW`) will give `LAST_VALUE` of only the current row in the middle of a partition. Add `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING` to get the true last value.
- `AVG` window with `ORDER BY` produces a running average, not a partition average. Remove `ORDER BY` to get a partition-wide average on every row.
- Multiple `OVER` clauses with the same definition can be named with the `WINDOW` clause to avoid repetition: `WINDOW w AS (PARTITION BY dept ORDER BY salary)`.
- The `WindowAgg` node in `EXPLAIN` wraps a `Sort`. If the sort is large, check whether the same column is indexed and the plan can use an `Index Scan` to avoid the sort.

## Related

- [[backend/postgres]]
- [[cheatsheets/postgres-explain]]
- [[cheatsheets/sql-joins]]
- [[cheatsheets/postgres-functions]]
- [[cheatsheets/postgres-types]]
- [[backend/prisma]]
- [[cheatsheets/index|Cheatsheets]]