SQL window functions: a cheat sheet with worked results

A window function calculates a value for each row using a set of related rows (its window) without collapsing them into one row the way GROUP BY does. All the examples use this table:

CREATE TABLE sales (id INTEGER PRIMARY KEY, region TEXT, rep TEXT, sold_on TEXT, amount INTEGER);
INSERT INTO sales (region, rep, sold_on, amount) VALUES
 ('north', 'Ana', '2025-01-05', 120), ('north', 'Ben', '2025-01-09', 200), ('north', 'Ana', '2025-01-15', 250),
 ('south', 'Cy',  '2025-01-06',  90), ('south', 'Di',  '2025-01-12', 300), ('south', 'Cy',  '2025-01-20', 150);

The shape

function(args) OVER (
    PARTITION BY column     -- optional: restart the calculation for each group
    ORDER BY column         -- optional: order within the group
    ROWS BETWEEN ... AND ...  -- optional: which rows form the window (the frame)
)

PARTITION BY is like GROUP BY that keeps every row. ORDER BY decides sequence. The frame, when given, limits the window to a number of rows around the current one.

Ranking

SELECT region, rep, amount,
       RANK()       OVER w AS rank,
       DENSE_RANK() OVER w AS dense_rank
FROM sales
WINDOW w AS (PARTITION BY region ORDER BY amount DESC)
ORDER BY region, amount DESC;

When two rows tie on the ORDER BY value, ROW_NUMBER() picks between them arbitrarily. Add a second sort column (ORDER BY amount DESC, id) whenever the numbering must be repeatable.

Running totals and shares

SELECT region, sold_on, amount,
       SUM(amount) OVER (PARTITION BY region ORDER BY sold_on) AS running_total,
       ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY region), 1) AS pct_of_region
FROM sales
ORDER BY region, sold_on;

With an ORDER BY the sum covers the rows from the start of the partition up to the current row. Without one it covers the whole partition, which is what you want for a share of total.

Previous and next rows

SELECT region, sold_on, amount,
       amount - LAG(amount) OVER (PARTITION BY region ORDER BY sold_on) AS change
FROM sales
ORDER BY region, sold_on;

The first row of each partition has no previous row, so LAG returns NULL there (pass a third argument to use a default instead). For north the changes are NULL, 80 and 50; for south they are NULL, 210 and -150.

Moving averages and frames

SELECT sold_on, amount,
       AVG(amount) OVER (ORDER BY sold_on ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS avg_2
FROM sales
ORDER BY sold_on;

ROWS BETWEEN 1 PRECEDING AND CURRENT ROW averages the current row and the one before it, so the first row averages only itself.

Pitfalls

• You cannot filter on a window function in WHERE. Window functions are calculated after WHERE. Put the query in a subquery or common table expression and filter outside it, as the deduplication guide does with rn = 1.
• LAST_VALUE surprises people. With an ORDER BY the default frame ends at the current row, so LAST_VALUE returns the current row's own value. Give it an explicit frame ending at UNBOUNDED FOLLOWING.
• ROWS and RANGE differ. ROWS counts physical rows. RANGE treats rows with equal sort values as one group, which changes running totals when there are ties.
• Support. Window functions arrived in SQLite 3.25 and have been in PostgreSQL, SQL Server, Oracle, BigQuery and Snowflake for years. MySQL has them from version 8.0.