Window Functions in SQL: ROW_NUMBER, RANK, Running Totals & More

A window function computes a value for each row using a set of related rows — its “window” — without collapsing those rows the way GROUP BY does. That’s what makes them the workhorse of analytics: running totals, rankings, “compare each month to the previous one,” top-N-per-group — all of it. And it’s also what makes them hard to picture, because the calculation for one row depends on rows around it.

So instead of describing it, here’s a tool. Pick a function, a PARTITION BY, and an ORDER BY, and watch the sales table re-sort, the partitions tint, and each value compute. Hover any row to highlight the exact window it was calculated from. The SQL updates live.

Play with it before reading on — switch to SUM() running and hover a row to see the frame grow; switch to LAG() and watch the first row of each partition go NULL. The rest of this guide is just names for what you’re seeing.

The anatomy: OVER (PARTITION BY … ORDER BY …)

Every window function has the same shape:

function() OVER (PARTITION BY column ORDER BY column)

• OVER is what makes it a window function instead of a plain aggregate.
• PARTITION BY splits the rows into independent groups — the calculation restarts for each one (in the visualizer, that’s the color change between West and East). Omit it and the whole table is one window.
• ORDER BY sets the order inside each partition, which is what “running,” “previous,” and “rank” are all relative to.

Crucially, unlike GROUP BY, the rows don’t disappear — you get one output row per input row, with the computed column alongside.

Ranking: ROW_NUMBER, RANK, DENSE_RANK

These number the rows within each partition:

• ROW_NUMBER() — a plain 1, 2, 3…, always unique. Great for “give me the first order per customer” (filter WHERE row_num = 1).
• RANK() — ties get the same rank, then the next rank skips: 1, 1, 3. Use it when ties should genuinely share a place.
• DENSE_RANK() — ties share a rank but the next is +1: 1, 1, 2. No gaps.

Switch the visualizer to ORDER BY amount and toggle between RANK and DENSE_RANK to watch the gap appear and disappear. Ana and Dana both sold 120, so they tie: RANK gives them 2 and 2 and then jumps to 4, while DENSE_RANK gives 2 and 2 and continues at 3. ROW_NUMBER ignores the tie entirely and just counts 1, 2, 3, 4 — which is why it’s the wrong choice when ties are meaningful.

Running totals & moving averages: SUM/AVG OVER

Add ORDER BY to an aggregate and it becomes cumulative — computed from the start of the partition up to the current row:

SUM(amount) OVER (PARTITION BY region ORDER BY month) AS running_total

This is the classic “revenue to date” column. Hover (or tap, or tab to) a row under SUM() running and the highlighted frame shows exactly which rows were added. AVG() the same way gives a running average.

One subtlety worth knowing: with a bare ORDER BY, the default frame is RANGE UNBOUNDED PRECEDING AND CURRENT ROW, which includes every row that ties on the ordering value — so tied rows share one running total. Try ORDER BY amount with SUM() and you’ll see Ana and Dana (both 120) report the same total. If you want strictly positional accumulation instead, say so explicitly:

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

Narrow that frame further — ROWS BETWEEN 2 PRECEDING AND CURRENT ROW — and you get a moving average.

Looking sideways: LAG and LEAD

LAG() and LEAD() pull a value from another row relative to the current one, in ORDER BY order:

LAG(amount)  OVER (PARTITION BY region ORDER BY month) AS prev_amount
LEAD(amount) OVER (PARTITION BY region ORDER BY month) AS next_amount

LAG reaches back one row (the first row of each partition has nothing behind it → NULL); LEAD reaches forward. They’re how you compute month-over-month change without a self-join: amount - LAG(amount) OVER (...).

From standard functions to specialized analytics

The seven functions above cover the vast majority of real work, and every major database — PostgreSQL, SQL Server, Oracle, MySQL 8+, Snowflake, BigQuery — supports them with the same OVER() syntax. Where teams reach beyond them is in specialized analytics: cumulative profit with custom aggregation rules, behavioral analytics with weighting factors, funnel stages tracked dynamically, or time-series logic that a stock function can’t express.

That’s when you compose window functions with frame clauses (ROWS BETWEEN n PRECEDING AND CURRENT ROW), stack them in subqueries or views, or push logic into user-defined functions. The reliability concern grows too — reprocessing analytical pipelines demands idempotent transformations so a re-run produces identical windows. If you’re weighing how much of this belongs in SQL versus application code, our take on Python vs. SQL maps the trade-offs.

Frequently asked questions

What is a window function in SQL? A function that computes a value for each row over a related set of rows (its “window”), defined by OVER (PARTITION BY … ORDER BY …), without collapsing rows the way GROUP BY does.

What’s the difference between a window function and GROUP BY? GROUP BY returns one row per group. A window function returns one row per input row, with the aggregate computed alongside — so you keep the detail and the summary.

What’s the difference between RANK and DENSE_RANK? Both give ties the same rank. RANK then skips (1, 1, 3); DENSE_RANK doesn’t (1, 1, 2).

How do I compute a running total in SQL? SUM(amount) OVER (PARTITION BY group ORDER BY sort_column) — the ORDER BY makes the sum cumulative up to each row.

How do I compare a row to the previous one? LAG(column) OVER (ORDER BY …) returns the previous row’s value; subtract to get the change. LEAD() looks forward.

Make the window a node, not a nightmare

Window logic that grows across many queries is exactly the kind of thing that belongs in a governed pipeline. ET1 is a visual, asynchronous ETL tool where partitioning, ordering, and aggregation are nodes on a canvas with a live preview — so a running total or a rank-per-group is something you can see and reuse, not re-derive.

Keep learning: GROUP BY and aggregates (the non-window cousin), views to save a windowed query, and joins.