Window Functions

OVER() computes across related rows without collapsing them — ranks, running totals, lag.

SQL · Advanced · ~4 min

In plain English

Compute something across a group of rows without collapsing them — a running total, a rank, a comparison to the previous row — while keeping every row.

Why it's worth your time

It's the single biggest step up in SQL skill, and it turns queries that needed self-joins into one clean line.

If you remember three things

OVER (PARTITION BY … ORDER BY …) defines the window
Rows are kept, unlike GROUP BY
ROW_NUMBER / RANK / LAG / LEAD / running SUM cover most needs

Overview

A window function computes a value across a set of rows related to the current row — defined by OVER(PARTITION BY … ORDER BY …) — while keeping every input row in the output. Unlike GROUP BY, it doesn't collapse rows. It powers ranking (ROW_NUMBER, RANK), running and moving aggregates (SUM/AVG OVER), and row-to-row access (LAG, LEAD).

In an interview

Window functions run an aggregate over a 'window' of rows defined by OVER(), without collapsing the result the way GROUP BY does. PARTITION BY groups the window, ORDER BY sequences it. Use them for ROW_NUMBER/RANK, running totals, moving averages, and LAG/LEAD comparisons — every row survives, enriched with a new column.

Production defaults

Deduplication: ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at DESC) then keep row 1
Period comparison: LAG(value) OVER (ORDER BY date) instead of a self-join
Ranking: ROW_NUMBER for a strict order, RANK when ties should share a position
Frames: specify ROWS BETWEEN explicitly for running totals; the default frame surprises people

What breaks

Running total looks wrong at ties — The default frame is RANGE, which lumps tied rows together. Use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
Can't filter on the window result — Window functions run after WHERE. Wrap the query in a CTE and filter outside.

Watch it explained

Intermediate SQL Tutorial | Partition By — Alex The Analyst, 4:14