# Window Functions

> Rank rows and compute running totals, neighboring values, and window frames.

---

LLMS index: [llms.txt](/en/llms.txt)

---

Window functions compute a value for each row using a *window* — a set of related rows defined by an `OVER (...)` clause. Unlike `GROUP BY`, which collapses rows, window functions add a per-row result alongside the original columns. They are the right tool for rankings, running totals, moving averages, percentile bands, "previous row" comparisons — anything you used to do in your application code by sorting and iterating.

## What you'll learn {#learn}

- The shape of a window function: `f() OVER (PARTITION BY ... ORDER BY ...)`
- The most useful functions: `row_number`, `rank`, `dense_rank`, `lag`, `lead`, `sum`, `avg`, `first_value`, `last_value`
- Partitioning vs ordering inside `OVER`
- Reusable windows with `WINDOW name AS (...)`
- The frame clause: `ROWS BETWEEN ... AND ...`

## Example {#example}

```sql {num="39.1" caption="Window Functions"}
-- Numbering rows within each partition
SELECT customer_id, id AS order_id, total,
       row_number() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rn,
       rank()       OVER (PARTITION BY customer_id ORDER BY total DESC) AS rnk
FROM orders_example
ORDER BY customer_id, rn;

-- "Most expensive order per customer" — keep rn = 1
WITH ranked AS (
  SELECT *,
         row_number() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rn
  FROM orders_example
)
SELECT customer_id, id, total FROM ranked WHERE rn = 1;

-- lag / lead: look at previous / next row in the partition
SELECT customer_id, id, total,
       lag(total)  OVER (PARTITION BY customer_id ORDER BY id) AS prev_total,
       lead(total) OVER (PARTITION BY customer_id ORDER BY id) AS next_total
FROM orders_example
ORDER BY customer_id, id;

-- Running total (a sum is also a window function)
SELECT customer_id, id, total,
       sum(total) OVER (PARTITION BY customer_id ORDER BY id) AS running_total
FROM orders_example
ORDER BY customer_id, id;

-- Reusable window
SELECT id, total,
       sum(total) OVER w AS running,
       avg(total) OVER w AS running_avg
FROM orders_example
WINDOW w AS (PARTITION BY customer_id ORDER BY id)
ORDER BY customer_id, id;

-- Frame clause: 3-row trailing average
SELECT id, total,
       avg(total) OVER (
         PARTITION BY customer_id
         ORDER BY id
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS trailing_avg
FROM orders_example
ORDER BY customer_id, id;
```

The window is defined by three pieces:

1. **`PARTITION BY`** — splits the rows into independent groups. Like `GROUP BY`, but each group keeps its rows.
2. **`ORDER BY`** — orders rows inside each partition. Determines `row_number`, `lag`, running totals, etc.
3. **Frame clause** (optional) — `ROWS BETWEEN ... AND ...` or `RANGE BETWEEN ...`. Defines which rows around the current row are in the window. Defaults differ by function family: ranking functions ignore the frame; with `ORDER BY`, the default frame includes all rows through the current row's last peer; without `ORDER BY`, it covers the partition.

The differences between `row_number`, `rank`, and `dense_rank` matter when there are ties. `row_number` always assigns 1, 2, 3, ... with ties broken arbitrarily. `rank` assigns 1, 2, 2, 4 (gap after a tie). `dense_rank` assigns 1, 2, 2, 3 (no gap).

The "top-N per group" pattern (`PARTITION BY group_col ORDER BY rank_col DESC + filter rn = 1`) is the canonical use of `row_number`. It replaces convoluted self-joins and subqueries.

> [!NOTE] Sample data
> Chapter 38 archives order 3. The output here follows that chapter order and has two remaining orders. Rerunning chapter 29 restores all three original orders.

## Run the example {#run}

To run (requires `orders_example`):

```text
$ psql -f source/window-functions.sql postgres
 customer_id | order_id | total | rn | rnk
-------------+----------+-------+----+-----
           1 |        1 | 50.00 |  1 |   1
           1 |        2 | 30.00 |  2 |   2
(2 rows)
 ...
```

## Common pitfalls {#pitfalls}

- Window functions are not allowed in `WHERE` or `HAVING` (they are computed *after* those clauses). Wrap the query in a CTE or subquery and filter outside.
- The default frame for `sum() OVER (ORDER BY ...)` is `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` — peer rows with equal `ORDER BY` values are summed into the same total. Use `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` to be strict about row-by-row running.
- Without `ORDER BY` inside `OVER`, `lag` / `lead` use an unspecified row order and PostgreSQL does not warn. Specify the order and any tie-breaker.
- Performance: window functions sort the partition. With a matching index on `(partition_col, order_col)`, the sort can be skipped.

> [!TIP] Tip
> "Show the change vs. previous row" is `total - lag(total) OVER (ORDER BY ...)`. "Show as percent of total" is `100.0 * total / sum(total) OVER ()`. Both replace a self-join or subquery with a single readable expression.

> [!NOTE] Try it
> Add `ntile(4)` to put each order into quartiles: `ntile(4) OVER (ORDER BY total)`. Then compute "today's total vs. yesterday's" on a time-series table by `lag(daily_total) OVER (ORDER BY day)`. Then try `first_value(total) OVER (PARTITION BY customer_id ORDER BY id)` to see the customer's first order amount on every row.

## Downloads {#downloads}

SQL file: [window-functions.sql](/examples/source/window-functions.sql) · [All lesson files](/examples/examples.zip) · [Setup guide](/en/#prerequisites)