---
title: "Window Functions"
description: "A frame slides over ordered rows — running totals and ranks without collapsing."
source: https://vizlearn.app/cs/databases/sql-window-functions
access: premium (interactive player requires Lifetime; this doc is free)
---

# Window Functions

How do you calculate a running total or assign a rank while keeping every source row? A SQL window function reads a set of related rows, computes one value for the current row, and adds that value to the row. Unlike `GROUP BY`, it does not reduce each group to one result row.

**Core idea:** a window function can read several rows and still return one value to every original row id. It augments the relation; `GROUP BY` changes its grain.

An `OVER` specification answers three separate questions:

- `PARTITION BY` decides which rows may interact. Each partition restarts independently.
- `ORDER BY` inside `OVER` gives rows a logical order for the calculation.
- The function and, when relevant, its frame decide which ordered rows contribute to the current value.

That window-local `ORDER BY` does not guarantee the final presentation order. Add an outer query-level `ORDER BY` when row order matters to a caller.

## How a Running Window SUM Works

This lesson makes the frame explicit:

```sql
SELECT
  id,
  region,
  day,
  amount,
  SUM(amount) OVER (
    PARTITION BY region
    ORDER BY day, id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_sum
FROM sales
ORDER BY region, day, id;
```

The `ORDER BY day, id` inside `OVER` controls which rows enter each running prefix. The outer `ORDER BY region, day, id` controls the order returned to the caller. They solve different problems and may coexist in the same query.

For each current row, the `ROWS` frame begins at the first row in that row's region and ends at the current row. The running sum is the sum of `amount` across exactly those frame members.

## Running SUM Step by Step: Partition, Order, and Write Back

The default relation is deliberately interleaved:

| id   | region | day | amount |
| ---- | ------ | --: | -----: |
| `s1` | North  |   3 |     30 |
| `s2` | South  |   1 |     40 |
| `s3` | North  |   1 |     30 |
| `s4` | South  |   3 |     40 |
| `s5` | North  |   2 |     50 |
| `s6` | South  |   2 |     10 |
| `s7` | North  |   4 |     20 |

`PARTITION BY region` first forms two independent logical partitions. `ORDER BY day, id` then establishes each partition's calculation order:

- North: `s3 → s5 → s1 → s7`
- South: `s2 → s6 → s4`

The North frames grow as follows:

1. `s3` reads `[s3]`: `30 = 30`.
2. `s5` reads `[s3, s5]`: `30 + 50 = 80`.
3. `s1` reads `[s3, s5, s1]`: `30 + 50 + 30 = 110`.
4. `s7` reads `[s3, s5, s1, s7]`: `30 + 50 + 30 + 20 = 130`.

South starts again from zero. Its three values are 40, 50, and 90 in window order. Each value is then written back by row id, so the source-order teaching table becomes:

| id   | region | day | amount | running_sum |
| ---- | ------ | --: | -----: | ----------: |
| `s1` | North  |   3 |     30 |         110 |
| `s2` | South  |   1 |     40 |          40 |
| `s3` | North  |   1 |     30 |          30 |
| `s4` | South  |   3 |     40 |          90 |
| `s5` | North  |   2 |     50 |          80 |
| `s6` | South  |   2 |     10 |          50 |
| `s7` | North  |   4 |     20 |         130 |

The table keeps source order to make row identity easy to inspect. SQL does not promise that order without an outer `ORDER BY`.

## RANK and DENSE_RANK

Ranking uses the same partition-and-order structure but no aggregate row frame:

```sql
SELECT
  id,
  region,
  amount,
  RANK() OVER (
    PARTITION BY region
    ORDER BY amount DESC
  ) AS rank
FROM sales;
```

Rows with the same `ORDER BY` value are peers. Every row in a peer group receives the same result.

- `RANK = 1 +` the number of preceding rows with a strictly greater amount.
- `DENSE_RANK = 1 +` the number of distinct preceding amounts.

That distinction appears after a tie. North orders as `s5(50), s1(30), s3(30), s7(20)`. Both 30 rows receive 2. `RANK` assigns the next row 4 because three rows precede it; `DENSE_RANK` assigns 3 because only two distinct amounts precede it.

South orders as `s2(40), s4(40), s6(10)`. The two 40 rows are peers. The 10 row receives `RANK = 3` or `DENSE_RANK = 2`.

Across the original source ids, the complete outputs are:

| id   | RANK | DENSE_RANK |
| ---- | ---: | ---------: |
| `s1` |    2 |          2 |
| `s2` |    1 |          1 |
| `s3` |    2 |          2 |
| `s4` |    1 |          1 |
| `s5` |    1 |          1 |
| `s6` |    3 |          2 |
| `s7` |    4 |          3 |

The **Wide peers** preset makes the gap harder to miss: four West rows tie at amount 90. The next two rows receive ranks 5 and 6, but dense ranks 2 and 3.

## Window Functions Versus GROUP BY

Both operations can partition rows conceptually, but they produce different result grains.

| Operation         | Work done per region                             |                   Output cardinality |
| ----------------- | ------------------------------------------------ | -----------------------------------: |
| window function   | compute a value and write it to every source row | `n` source rows → `n` augmented rows |
| `GROUP BY region` | reduce each region to one aggregate row          |    `n` source rows → `p` region rows |

For the default data, the window result has seven rows while `GROUP BY region` has two. Replacing the running window sum with a grouped sum therefore does more than change syntax: it removes row identities such as `s1` and `s3` from the result grain.

Removing `PARTITION BY region` changes a different contract. In the Partition reset preset, the real global `ORDER BY day, id` sequence is `p2, p3, p1, p4`, with running sums `10, 110, 210, 215`. The first South row is still 10; the error becomes visible after rows from the two regions begin sharing one prefix. With partitioning, North is `100, 200` and South is `10, 15`.

## Window Function Edge Cases

- **Empty input:** no row means no partition, frame, peer group, or result row. A window function never invents a row.
- **One-row partition:** a running prefix frame contains only that row, and both ranking functions return 1.
- **Ties:** peer rows receive the same `RANK` or `DENSE_RANK`. Only the next value reveals whether gaps are preserved.
- **Duplicate frame-order values:** a `ROWS` frame depends on row positions. Add a deterministic tiebreaker such as `id` when several rows share the same day and their order would change intermediate running totals.
- **Missing outer order:** window-local order controls the calculation but not the order in which result rows are returned.

## Common Mistakes

- Treating `PARTITION BY` as if it collapses rows. It defines independent calculation scopes; it does not set the output grain.
- Assuming the running total follows source-table order. It follows the `ORDER BY` inside `OVER`.
- Forgetting the frame clause. Some database defaults use a value-based `RANGE` frame, which can include peers together and differ from an explicit row-by-row `ROWS` frame.
- Calculating `RANK` by counting distinct preceding values. That is `DENSE_RANK`; ordinary `RANK` counts preceding rows and can leave gaps.
- Expecting a stable final order because the window has `ORDER BY`. Use an outer `ORDER BY` for presentation.
- Reading the workbench as a physical sort or buffer plan. The query specifies logical semantics; the optimizer chooses an execution plan.

## A Note on Simplification: Logical Semantics, Not an Execution Plan

This lesson models logical partitions, window-local ordering, an explicit prefix `ROWS` frame, peer groups, ranking gaps, row-identity preservation, and result cardinality. It omits physical executor strategies, memory and spill behavior, collation, `NULL` ordering, numeric overflow and precision, named windows, `FILTER`, other frame bounds, `RANGE` and `GROUPS` frames, exclusion clauses, and dialect-specific defaults. Consult your database's documentation before relying on an implicit frame or a particular `NULL` order.