Window Functions: ROW_NUMBER, RANK, LEAD, LAG

A window function computes a value for each row based on a set of other rows related to it. GROUP BY collapses rows into one row per group; a window function leaves every row in place and attaches a calculation to it. That single difference is why windowing is so useful: you keep the detail and still get the summary.

The PostgreSQL documentation defines it the same way: “A window function performs a calculation across a set of table rows that are somehow related to the current row.” Windowing is also the youngest major feature in everyday SQL. PostgreSQL shipped it in version 8.4 in 2009, and SQLite only added it in release 3.25.0 in 2018, which is part of why it still separates candidates in interviews.

The anatomy of a window

function(...) OVER (
  PARTITION BY col1, col2   -- optional: defines the groups
  ORDER BY col3             -- optional: defines ordering within each group
  ROWS BETWEEN ... AND ...  -- optional: defines the frame
)

OVER is what turns an aggregate into a window function. PARTITION BYis “group by, but do not collapse rows”. ORDER BY orders the rows inside each partition. The frame clause is how far forward and backward the function can see; most of the time the default (current row and everything before, for ordered windows) is what you want.

ROW_NUMBER, RANK, DENSE_RANK

These three assign a number to each row inside a partition. They differ in how they handle ties.

-- sales table: (salesperson, region, amount)
SELECT
  salesperson,
  region,
  amount,
  ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC)  AS row_num,
  RANK()       OVER (PARTITION BY region ORDER BY amount DESC)  AS rnk,
  DENSE_RANK() OVER (PARTITION BY region ORDER BY amount DESC)  AS dense
FROM sales;

Three salespeople tied at $100 in a region:

ROW_NUMBER: 1, 2, 3 (arbitrary order among ties).
RANK: 1, 1, 1, then the next rank skips to 4.
DENSE_RANK: 1, 1, 1, then the next rank is 2 (no gaps).

ROW_NUMBER is what you want when you must pick exactly one row per group (top-N-per-group queries). RANK and DENSE_RANK are for leaderboards.

Top-N per group

The canonical pattern: the top three highest-paid employees per department.

SELECT *
FROM (
  SELECT
    employee,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
) ranked
WHERE rn <= 3;

Without windowing this forces an ugly correlated subquery. With windowing, it is three lines.

LEAD and LAG

LAG(col, n) reads the value of col from the row n positions earlier in the partition.LEAD looks forward. Use them for period-over-period comparisons without self-joining.

-- daily revenue, % change vs the previous day
SELECT
  day,
  revenue,
  LAG(revenue) OVER (ORDER BY day) AS revenue_prev,
  (revenue - LAG(revenue) OVER (ORDER BY day)) * 100.0
    / NULLIF(LAG(revenue) OVER (ORDER BY day), 0) AS pct_change
FROM daily_revenue;

Running totals

A running total is the default frame: “this row and everything before it in the partition order”.

SELECT
  day,
  revenue,
  SUM(revenue) OVER (ORDER BY day) AS running_total
FROM daily_revenue;

Add PARTITION BY month to reset the running total at the start of each month.

Common mistakes

Using a window function inside a WHERE clause. Window functions are evaluated after WHERE, so it will not compile. Wrap the query in a subquery or CTE and filter on the outer query.
Forgetting ORDER BY inside OVER. Ranks and running totals require ordering; without it the result is nondeterministic.
Using RANK where ROW_NUMBER was meant. Top-N per group needs ROW_NUMBER so you break ties.

Practice this live

SQL Protocol's campaign and Interview Mode currently drill the fundamentals that windowing builds on: aggregation with GROUP BY, joins, and subqueries. Get those automatic first; every window function above is one OVER clause away from an aggregate you already know. The examples on this page run as written in PostgreSQL and in SQLite 3.25 or later.

Window Functions Without the Hand-Waving