SQL Window Functions Cheat Sheet

Ranking, running totals and period-over-period without a self-join

Window functions compute across rows while keeping every row. They replace most self-joins and correlated subqueries, usually faster and always clearer.

Category: SQL Difficulty: Intermediate Version: 1.0 Updated: August 19, 2025 Author: Sabir

Functions

Function	Returns
`ROW_NUMBER()`	A unique sequential number; ties broken arbitrarily
`RANK()`	Ties share a rank, then the next value SKIPS (1,1,3)
`DENSE_RANK()`	Ties share a rank, no gaps (1,1,2)
`NTILE(4)`	Buckets the partition into quartiles
`LAG(x, 1), LEAD(x, 1)`	The previous and next row's value
`FIRST_VALUE(x), LAST_VALUE(x)`	Edge values — LAST_VALUE needs an explicit frame
`SUM(x) OVER (ORDER BY d)`	Running total

Anatomy

Clause	Controls
`PARTITION BY`	Restarts the calculation per group
`ORDER BY`	Row order inside the partition
`ROWS BETWEEN ... AND ...`	A frame counted in ROWS
`RANGE BETWEEN ... AND ...`	A frame counted in VALUES of the ORDER BY column
`ROWS UNBOUNDED PRECEDING AND CURRENT ROW`	Classic running total
`ROWS BETWEEN 6 PRECEDING AND CURRENT ROW`	Trailing 7-row moving average

ROWS is not RANGE

With an ORDER BY and no frame clause, the default is RANGE UNBOUNDED PRECEDING — which includes every PEER row sharing the current ORDER BY value. On a date column with several rows per day, a "running total" written that way jumps by the whole day at once. Write ROWS explicitly whenever ties are possible.

Code examples

Running total and period-over-period SQL

One pass, no self-join. Note the explicit ROWS frame.

SELECT
    order_date,
    region,
    revenue,

    SUM(revenue) OVER (
        PARTITION BY region
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,

    revenue - LAG(revenue) OVER (
        PARTITION BY region ORDER BY order_date
    ) AS vs_previous,

    DENSE_RANK() OVER (
        PARTITION BY region ORDER BY revenue DESC
    ) AS rank_in_region

FROM orders
ORDER BY region, order_date;

FAQs

Why is my running total jumping?

You omitted the frame clause, so it defaulted to RANGE, which includes every peer row sharing the current ORDER BY value. With several rows per day the total advances a whole day at a time. Write ROWS explicitly.

RANK or DENSE_RANK?

RANK leaves gaps after a tie (1, 1, 3); DENSE_RANK does not (1, 1, 2). Use RANK for competition placing and DENSE_RANK when you need contiguous levels.