SQL Window Functions Cheat Sheet — Every Function + Real Examples

Window functions are the tool for "the row I want plus something computed across other rows" queries — running totals, moving averages, per-group rankings, gap-and-island analysis, and the classic "top N per category." Everything below runs in PostgreSQL 11+, MySQL 8+, SQL Server 2012+, Oracle 12c+, and modern SQLite. Snowflake and BigQuery are near-identical with minor syntax quirks called out below.

The mental model

Window functions look at OTHER rows but do NOT collapse them. Compare:

• SELECT AVG(price) FROM orders GROUP BY user_id — one row per user. Original rows GONE.
• SELECT price, AVG(price) OVER (PARTITION BY user_id) FROM orders — every original row, plus the user's average alongside it.

The 4 clauses inside OVER()

fn(...) OVER (
  PARTITION BY col     -- split into groups
  ORDER BY col         -- order within each group
  ROWS BETWEEN ...     -- frame: which rows the function actually sees
)

Ranking functions

Function	What it returns	Ties
ROW_NUMBER()	1, 2, 3, 4...	Broken arbitrarily
RANK()	1, 2, 2, 4...	Same rank, skip next
DENSE_RANK()	1, 2, 2, 3...	Same rank, don't skip
NTILE(n)	Bucket 1..n	Divided as evenly as possible
PERCENT_RANK()	0.0 to 1.0	(rank - 1) / (total - 1)
CUME_DIST()	0.0 to 1.0	Rows ≤ this row / total rows

Top 3 orders per user — the classic pattern

SELECT * FROM (
  SELECT o.*, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders o
) t WHERE rn <= 3;

Offset / navigation functions

Function	What it returns
LAG(col, n, default)	Value from n rows earlier in the window
LEAD(col, n, default)	Value from n rows later
FIRST_VALUE(col)	First value in the ordered window
LAST_VALUE(col)	Last value — see framing warning below
NTH_VALUE(col, n)	nth value in the window

Day-over-day revenue change

SELECT day, revenue,
  revenue - LAG(revenue, 1, 0) OVER (ORDER BY day) AS delta,
  100.0 * (revenue - LAG(revenue) OVER (ORDER BY day))
        / LAG(revenue) OVER (ORDER BY day) AS pct_change
FROM daily_revenue;

Aggregate functions as windows

Any aggregate — SUM, AVG, MIN, MAX, COUNT, STRING_AGG, ARRAY_AGG — becomes a window function when followed by OVER().

Running total

SELECT day, revenue,
  SUM(revenue) OVER (ORDER BY day) AS running_total,
  SUM(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7day
FROM daily_revenue;

Frames — where beginners get burned

The default frame when you supply ORDER BY without an explicit frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That's usually what you want for running totals. But it also means LAST_VALUE() returns the current row's value, not the last value in the partition. To get the true last value:

LAST_VALUE(col) OVER (
  PARTITION BY g ORDER BY t
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

ROWS vs RANGE vs GROUPS

• ROWS — physical row count. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW = exactly 3 rows.
• RANGE — value-based. RANGE BETWEEN 30 PRECEDING AND CURRENT ROW on a date column = last 30 days by value, regardless of how many rows.
• GROUPS — peer-group based. Postgres 11+, SQL Server 2022+.

The "gap and island" pattern

Find consecutive streaks (login streaks, contiguous number sequences):

SELECT user_id, MIN(day) AS streak_start, MAX(day) AS streak_end, COUNT(*) AS streak_days
FROM (
  SELECT user_id, day,
    day - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day) * INTERVAL '1 day' AS grp
  FROM daily_logins
) t
GROUP BY user_id, grp;

Common pitfalls

• Window functions run AFTER WHERE but BEFORE ORDER BY. You cannot filter on a window function result in the same query's WHERE. Wrap in a subquery or CTE.
• Cannot use in GROUP BY — they operate on the already-grouped rows.
• MySQL < 8 does not support them. Emulate with self-joins if you must (painful).
• DISTINCT inside window aggregates is not portable. Available in Postgres and some others; use a subquery for cross-database code.

Related

SQL CTEs and recursive queries · SQL Formatter to pretty-print your window queries.