SQL

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.

Intermediate 1 min read 14 Entries Version 1.0 Sabir Updated 2
Download PDF Export Markdown Export HTML

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

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

SQL windows.sql Download 
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;

Frequently asked questions

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.

Was this cheat sheet useful?

Comments

No comments yet — be the first.

Need a different cheat sheet? Tell us what you would like to see and we will build it — free.
Request a cheat sheet