Window functions

This is the exact file agents read, including the credit header and licence notice. Save it as window-functions/SKILL.md.

---
name: window-functions
description: >
  Write, review and debug SQL window functions across PostgreSQL, MySQL 8+, SQLite 3.25+, SQL Server and other engines: running totals, moving averages, ranking, top-N-per-group, row-to-row deltas, percent-of-total and gaps-and-islands, with correct PARTITION BY, ORDER BY and frame clauses. Use it when a query uses OVER, PARTITION BY, ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE or an aggregate with OVER, or when someone asks for a "running total", "moving average", "rank within each group", "top 3 per category", "previous row's value", "month-over-month change", "percent of total", or says "my running total is wrong" or "my SUM OVER is slow". Do NOT use it for one-row-per-group aggregation with GROUP BY, for joins (see sql-joins), for subquery or CTE structure (see subqueries-and-ctes), or for recursive hierarchy walks.
license: Free to use and adapt under CC BY 4.0, with credit to Afeez Adebayo. Link afeezadebayo.com, https://www.linkedin.com/in/afeez-adebayo/ and this skill's page at https://afeezadebayo.com/skills-md/data/sql-querying/window-functions/.
---

# Window functions

## When to use this, and when not to

Use this skill for a calculation that spans a set of related rows but keeps every row in the output: a running total, a moving average, a rank within a group, the top N rows per group, the difference from the previous row, each row's share of its group's total, or runs of consecutive values. The triggers are the `OVER` clause and any of `PARTITION BY`, `ROW_NUMBER`, `RANK`, `DENSE_RANK`, `NTILE`, `LAG`, `LEAD`, `FIRST_VALUE`, `LAST_VALUE`, or an ordinary aggregate followed by `OVER`.

Do not use it when the answer is one row per group with no detail rows kept: that is `GROUP BY`. Do not use it for joining tables (`sql-joins`), for structuring nested queries (`subqueries-and-ctes`), or for walking a hierarchy (recursive CTEs). The defining feature of a window function is that it adds a column computed across other rows without collapsing the current row.

## Inputs the agent must have before starting

1. The SQL engine and version. Window functions need PostgreSQL (all supported versions), MySQL 8.0+, MariaDB 10.2+, SQLite 3.25+ (2018), or SQL Server 2012+. Frame and `QUALIFY` support vary (see the appendix).
2. The question and the table, with the grain of one input row.
3. The partition: the group the calculation resets within. It may be the whole table (no `PARTITION BY`).
4. The ordering: the column or columns that define sequence for running, ranking and offset functions, including a tiebreaker column that makes the order unique.
5. The frame needed: the whole partition, everything up to the current row (running), or a sliding window of n rows.
6. Whether the result is then filtered on the window value (top N, rank = 1), which needs the query wrapped.

If the ordering column has ties and the person has not given a tiebreaker, ask for one. Without it, ranking and "latest row" results are not reproducible.

## The workflow, step by step

### 1. Confirm you want to keep every row

If the output should be one row per group, use `GROUP BY`. Use a window function only when the detail rows stay and you are adding a cross-row column. Mixing the two intentions is the most common reason a result has the wrong number of rows.

### 2. Set the partition

`PARTITION BY` divides rows into groups that the function restarts for. Omit it to treat the whole result as one partition. The partition is "per customer", "per store", "per category": the thing the running total or rank resets within.

### 3. Set the order, with a tiebreaker

Add `ORDER BY` inside `OVER` for anything sequential: running totals, `LAG`/`LEAD`, and all ranking functions. Decision rule: tied rows are processed in an unspecified order, so append a unique tiebreaker (a primary key, a timestamp plus an id) to make `ROW_NUMBER`, "latest row" and running results deterministic. `row_number() OVER (PARTITION BY dept ORDER BY salary DESC)` is non-deterministic across equal salaries; `ORDER BY salary DESC, emp_id` is not.

### 4. Specify the frame explicitly for ordered aggregates

This is the step that most queries get wrong. When you use an aggregate (`SUM`, `AVG`, `COUNT`, `MIN`, `MAX`) with `ORDER BY` and no frame clause, the default frame is:

```
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
```

That default includes the current row and all its peers (rows equal on the `ORDER BY` key), so tied rows all receive the same cumulative value rather than a true row-by-row running total. It is also slower, because the engine cannot tell in advance how many rows each frame covers and often spills to a spool.

Decision rule: state the frame explicitly whenever you use an ordered aggregate.

- Running total: `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`.
- Trailing moving average of n rows: `ROWS BETWEEN n-1 PRECEDING AND CURRENT ROW`.
- Centred moving average: `ROWS BETWEEN k PRECEDING AND k FOLLOWING`.

`ROWS` counts physical rows from the current row. `RANGE` works on value peers. `GROUPS` counts peer groups. Use `ROWS` unless you specifically want ties folded together, in which case `RANGE` is correct and deliberate. On one SQL Server row-mode test of two million rows, the implicit `RANGE` running total ran about four times slower than the explicit `ROWS` form (roughly 20 seconds versus 4), because `RANGE` forced an on-disk spool; the exact numbers are engine and version specific, but the direction holds. Adding `ORDER BY` to an unframed aggregate can also change the result, not just the speed, so make the frame explicit rather than relying on the default.

Ranking and offset functions (`ROW_NUMBER`, `RANK`, `DENSE_RANK`, `NTILE`, `LAG`, `LEAD`, `PERCENT_RANK`, `CUME_DIST`) ignore the frame and always see the whole partition. Do not add a frame clause to them.

### 5. Pick the right ranking function

| Function | Ties | Gaps after ties | Use for |
|---|---|---|---|
| `ROW_NUMBER()` | Broken arbitrarily | No | Top-N-per-group, deduplication, "one row per key" |
| `RANK()` | Share a rank | Yes (skips) | Competition ranking where ties skip numbers |
| `DENSE_RANK()` | Share a rank | No | Ranking where the next distinct value is the next number |
| `NTILE(n)` | n equal buckets | n/a | Quartiles, deciles; unreliable on small partitions |

For the top N rows per group, compute `ROW_NUMBER()` in a subquery or CTE, then filter `WHERE rn <= N` outside it (step 7). For deduplication, keep `rn = 1`.

### 6. Use offsets and shares correctly

`LAG(col, 1)` and `LEAD(col, 1)` read the previous and next row within the partition and return `NULL` at the edges; pass a default (`LAG(col, 1, 0)`) or wrap in `COALESCE` when a number is needed. A row's share of its group is `col / SUM(col) OVER (PARTITION BY grp)`; guard against divide-by-zero with `NULLIF(SUM(col) OVER (...), 0)`. For month-over-month change, subtract the `LAG` value from the current value.

### 7. Filter on a window result by wrapping the query

Window functions run after `WHERE`, `GROUP BY` and `HAVING`, so they cannot appear in those clauses. To filter on a rank or a row number, compute it in a subquery or CTE and filter in the outer query:

```sql
SELECT * FROM (
    SELECT p.*,
           ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY revenue DESC) AS rn
    FROM products p
) ranked
WHERE rn <= 3;
```

On engines that support `QUALIFY` (Snowflake, BigQuery, DuckDB, Teradata), `QUALIFY rn <= 3` does this inline without the wrapper. PostgreSQL, MySQL, SQLite and SQL Server do not have `QUALIFY`; use the subquery.

### 8. Name a reused window

When several functions share the same `OVER` specification, define it once with a `WINDOW` clause and reference it by name. The `WINDOW` clause goes after `WHERE`, `GROUP BY` and `HAVING`:

```sql
SELECT sum(amount) OVER w, avg(amount) OVER w
FROM payments
WINDOW w AS (PARTITION BY store_id ORDER BY paid_at
             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
```

### 9. Make it perform

An explicit `ROWS` frame avoids the spool that the default `RANGE` frame can force. The `PARTITION BY` and `ORDER BY` drive a sort, so an index on `(partition_key, order_key)` can let the engine skip it. Run `EXPLAIN` and check whether a sort or a spool dominates.

### 10. Verify before handing over

Confirm the ordering is deterministic (a tiebreaker is present), the frame is explicit for every ordered aggregate, edge `NULL`s from `LAG`/`LEAD` are handled, any filter on a window value is correctly wrapped, and the headline numbers reproduce on a re-run.

## Worked example, end to end

Daily sales per store in `sales(store_id, sale_date, amount)`, one row per sale. The question: a running total of revenue per store by day, a 7-day trailing average, and each store ranked by its total revenue.

A first attempt at the running total is subtly wrong:

```sql
SELECT store_id, sale_date, amount,
       SUM(amount) OVER (PARTITION BY store_id ORDER BY sale_date) AS running
FROM sales;
```

With several sales on the same `sale_date`, the default `RANGE` frame treats them as peers, so every sale on that date shows the same cumulative figure (the total through the end of that day) rather than a true running total, and the query is slower than it needs to be. The corrected version states the frame and adds a tiebreaker so the order is deterministic:

```sql
SELECT store_id, sale_date, amount,
       SUM(amount) OVER (
           PARTITION BY store_id
           ORDER BY sale_date, id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running,
       AVG(amount) OVER (
           PARTITION BY store_id
           ORDER BY sale_date, id
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS avg_7row
FROM sales;
```

To rank stores by total revenue, aggregate first, then rank, keeping every store row:

```sql
WITH store_totals AS (
    SELECT store_id, SUM(amount) AS revenue
    FROM sales GROUP BY store_id
)
SELECT store_id, revenue,
       DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank
FROM store_totals;
```

For the top three products per category, use `ROW_NUMBER` in a CTE and filter outside, as in step 7. Each pattern keeps the detail rows and adds exactly one computed column.

## Templates you can use verbatim

```sql
-- Running total (always frame it)
SUM(amount) OVER (PARTITION BY k ORDER BY ts, id
                  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

-- Trailing 7-row moving average
AVG(amount) OVER (PARTITION BY k ORDER BY ts, id
                  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

-- Top N per group: compute then filter
SELECT * FROM (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY grp ORDER BY metric DESC, id) AS rn
    FROM t
) s WHERE rn <= :n;

-- Deduplicate: keep one row per key
SELECT * FROM (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY natural_key ORDER BY updated_at DESC, id) AS rn
    FROM t
) s WHERE rn = 1;

-- Row-to-row change
value - LAG(value, 1, 0) OVER (PARTITION BY k ORDER BY ts, id) AS delta

-- Share of group total (divide-by-zero safe)
value / NULLIF(SUM(value) OVER (PARTITION BY k), 0) AS share

-- Named window reused across functions
SELECT first_value(price) OVER w, last_value(price) OVER w
FROM t
WINDOW w AS (PARTITION BY k ORDER BY ts
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);
```

A note on `LAST_VALUE`: with the default running frame it returns the current row, not the partition's last row. Use `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING` (as above) to get the true last value.

Prompts that should trigger this skill: "Running total of revenue by day per store", "Rank customers by spend within each region", "Show the top 3 products in each category", "Month-over-month growth per account", "Each row's percentage of its group total".

## Failure modes and anti-patterns

1. **The default RANGE frame gives a wrong or slow running total.** Spot it when same-key tied rows all show the same cumulative value, or when a `SUM OVER (ORDER BY ...)` is unexpectedly slow. Fix: add `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` and a unique tiebreaker.
2. **Non-deterministic ranking.** Spot it when `ROW_NUMBER` or "latest row" results change between runs on tied data. Fix: add a unique tiebreaker to the window `ORDER BY`.
3. **Filtering on a window function in WHERE.** Spot it as an error like "window functions are not allowed in WHERE". Fix: wrap the query in a subquery or CTE and filter the computed column outside, or use `QUALIFY` where supported.
4. **A window function used where GROUP BY was meant.** Spot it when the result has one row per input instead of one per group. Fix: use `GROUP BY` for a collapsed summary, and keep the window function only when detail rows must stay.
5. **Edge NULLs and tiny NTILE buckets.** Spot `LAG`/`LEAD` NULLs at partition edges feeding into arithmetic, and `NTILE(100)` on a two-row partition returning buckets 1 and 2 rather than true percentiles. Fix: supply a default or `COALESCE` for offsets, and use coarse buckets on small partitions.

## Quality bar

- Every ordered aggregate has an explicit frame; no ordered `SUM`/`AVG`/`COUNT` relies on the default `RANGE`.
- The window `ORDER BY` is deterministic (a unique tiebreaker is present) wherever order affects the result.
- The right ranking function is used for the tie behaviour required, and top-N uses `ROW_NUMBER` filtered outside the window.
- `LAG`/`LEAD` edge `NULL`s are handled; share-of-total guards divide-by-zero.
- Any filter on a window value is wrapped in a subquery or CTE (or uses `QUALIFY` on a supporting engine).
- `GROUP BY` is used instead where a collapsed summary was the real intent.
- The plan was checked with `EXPLAIN`, and an index on `(partition, order)` is considered where the sort dominates.

## Data handling and safety

Window functions read data and produce reports, so they are low risk on their own. Do not paste real personal, health or financial values into prompts or shared logs; refer to columns by name and type. When a window query feeds a destructive statement (for example, deleting all but the latest row per key), show the `SELECT` that lists the affected rows first, report the count, and require explicit human confirmation before running the delete.

## Appendix: engine-specific notes (verify before relying on these)

- **Availability**: PostgreSQL (all supported versions), MySQL 8.0+, MariaDB 10.2+, SQLite 3.25+ (2018), SQL Server 2012+, Oracle. MySQL 5.7 and earlier have no window functions.
- **Default frame**: `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` with `ORDER BY`, whole partition without, in PostgreSQL, MySQL and SQL Server alike. Make it explicit regardless.
- **GROUPS frame**: supported in PostgreSQL 11+; not in MySQL 8.0 or SQL Server (which support `ROWS` and `RANGE` only).
- **QUALIFY**: Snowflake, BigQuery, DuckDB and Teradata only. PostgreSQL, MySQL, SQLite and SQL Server need the subquery wrapper.
- **Named windows (`WINDOW` clause)**: PostgreSQL, MySQL 8.0+, SQLite, MariaDB. SQL Server has no `WINDOW` clause; repeat the `OVER` specification.
- **`RANGE` with numeric or interval offsets** (for example `RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW`): PostgreSQL 11+ and recent others; check your engine and version.
- **`FILTER (WHERE ...)` on window aggregates**: PostgreSQL supports it; most other engines do not, so use `CASE` inside the aggregate instead.

## Sources

- PostgreSQL 18 documentation, "Window Functions" tutorial and "Window Function Calls": no row collapsing, execution after WHERE/GROUP BY/HAVING, default frame wording, the `WINDOW` clause, filtering via a sub-select. Verified 29 October 2026.
- MySQL 8.0 Reference Manual, "Window Function Frame Specification": default `RANGE` vs `ROWS`, peers, functions that ignore frames, and the note that adding `ORDER BY` can change `SUM()` results. Verified 29 October 2026.
- Itzik Ben-Gan, "The Curious Case of Window Functions" / framing performance, sqlperformance.com: measured `RANGE` vs `ROWS` running-total costs and the recommendation to state the frame explicitly. Verified 29 October 2026.
- ThoughtSpot (formerly Mode) SQL tutorial, "Window Functions": OVER, PARTITION BY, ranking functions, NTILE, LAG/LEAD, and the small-partition NTILE caution. Verified 29 October 2026.
- SQLite, MariaDB and Microsoft SQL Server documentation for window-function availability, `QUALIFY` absence and `GROUPS` support. Verified 29 October 2026.