SQL Window Functions

The analytics superpower most people skip: compute running totals, rankings, and row-to-row comparisons without collapsing your rows.

The idea

What they do: Compute across a set of rows related to the current row — without GROUP BY collapsing them.
Syntax: func() OVER (PARTITION BY … ORDER BY …)
PARTITION BY: Restart the calculation per group (like GROUP BY, but rows stay).
ORDER BY (in OVER): Defines row order for running/ranking functions.

ROW_NUMBER(): 1,2,3,4 — unique, no ties
RANK(): 1,2,2,4 — ties share, gaps after
DENSE_RANK(): 1,2,2,3 — ties share, no gaps
NTILE(4): Split rows into 4 buckets (quartiles)
Top-N per group: Wrap in a subquery: WHERE rn <= 3 after ROW_NUMBER() OVER (PARTITION BY g ORDER BY x DESC)

LAG / LEAD: LAG(sales) OVER (ORDER BY day) — previous/next row’s value (row-to-row diffs)
Running total: SUM(amt) OVER (ORDER BY day)
Moving average: AVG(x) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
First / last in window: FIRST_VALUE(x) · LAST_VALUE(x)
Share of total: x / SUM(x) OVER () — percent of grand total

ROWS vs RANGE: ROWS = physical rows; RANGE = rows with the same ORDER BY value (peers). They differ when there are ties.
Default frame trap: With ORDER BY and no explicit frame, the default is RANGE … UNBOUNDED PRECEDING TO CURRENT ROW — LAST_VALUE then returns the current row, not the last. Set the frame explicitly.
Running total window: For a true cumulative sum, add ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
WHERE can’t see windows: Window functions run after WHERE/GROUP BY — filter on their result in an outer query or a CTE.
QUALIFY (some engines): Snowflake/BigQuery/DuckDB let you QUALIFY row_number() OVER (…) = 1 instead of a subquery.