# Window Functions — PostgreSQL

Source: https://www.skillbyai.com/en/postgresql/a-window

> Calculations across related rows.

## OVER (PARTITION BY ... ORDER BY ...)

**Window functions** compute values across a set of rows related to the current row, without collapsing them like GROUP BY: rankings (`rank`, `row_number`, `dense_rank`), running totals (`sum(...) OVER (ORDER BY ...)`), moving averages, and comparisons with neighbours (`lag`, `lead`). `PARTITION BY` restarts the calculation per group; `ORDER BY` inside OVER defines order and, for aggregates, a running frame.

## Powerful PostgreSQL features

Window functions compute across rows, JSONB stores flexible data, and upserts make writes idempotent.

![Three ideas: window functions, JSONB, upserts.](assets/figures/postgresql/section-4-map.svg) — Figure 4.1 — Windows, JSONB and upserts.

## Ranking orders and per-customer running totals, run

I ran this with psql against PostgreSQL 16.2 (a local server started with the pgserver Python package), on a fresh database loaded with the shop schema from the first section. Each order keeps its row. rank() orders all orders by value (two orders tie at rank 2 and rank 3 is skipped); the running total restarts per customer: Asha 1,259 then 2,758, Ravi 1,200 then 2,699.

```sql
SELECT o.id, c.name, o.ordered_on,
       sum(oi.qty * p.price) AS total,
       rank() OVER (ORDER BY sum(oi.qty * p.price) DESC) AS rank_by_value,
       sum(sum(oi.qty * p.price)) OVER (PARTITION BY c.name ORDER BY o.ordered_on) AS customer_running_total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
GROUP BY o.id, c.name
ORDER BY o.id;
```

Output:

```
 id | name  | ordered_on |  total  | rank_by_value | customer_running_total 
----+-------+------------+---------+---------------+------------------------
  1 | Asha  | 2026-09-01 | 1259.00 |             4 |                1259.00
  2 | Asha  | 2026-09-15 | 1499.00 |             2 |                2758.00
  3 | Ravi  | 2026-09-10 | 1200.00 |             5 |                1200.00
  4 | Meera | 2026-09-20 | 1798.00 |             1 |                1798.00
  5 | Ravi  | 2026-09-21 | 1499.00 |             2 |                2699.00
(5 rows)
```

## Pick the right ranking function

row_number never ties, rank skips after ties, dense_rank does not skip; choose by what "top N" means for you.

**Quiz:** How do window functions differ from GROUP BY?

- [ ] They require temporary tables
- [ ] They delete rows
- [ ] They only work on JSON
- [x] They compute across rows while keeping every row in the result

*Answer:* They compute across rows while keeping every row in the result. Aggregates without collapsing.