SQL Window Functions: ROW_NUMBER, RANK, and LAG

Window functions let you compute aggregates and rankings across a set of rows without collapsing them. A regular GROUP BY reduces ten rows to one. A window function lets all ten rows stay, while each one carries the aggregate or rank that describes its place in the group. Once you internalize that distinction, problems that used to need ugly self-joins fall apart in three lines of SQL.

The OVER clause

Every window function ends with an OVER (...) clause that defines the window: which rows to consider for each calculation. The clause has two main parts.

• PARTITION BY slices the table into groups. The function restarts at each group.
• ORDER BY orders rows inside a partition, which matters for ranking and for running totals.

Either part can be omitted. With no PARTITION BY, the window is the entire result set. With no ORDER BY, ranking functions are not meaningful, but plain aggregates still work.

A small sample dataset

We will use a sales table for every example.

CREATE TABLE sales (
  id          INT PRIMARY KEY,
  region      TEXT,
  salesperson TEXT,
  sold_at     DATE,
  amount      NUMERIC
);

INSERT INTO sales VALUES
  (1, 'East', 'Ana',  '2026-01-05',  500),
  (2, 'East', 'Ana',  '2026-01-12',  300),
  (3, 'East', 'Ben',  '2026-01-08',  900),
  (4, 'East', 'Ben',  '2026-01-20',  400),
  (5, 'West', 'Cleo', '2026-01-03',  700),
  (6, 'West', 'Cleo', '2026-01-18',  700),
  (7, 'West', 'Dan',  '2026-01-09',  200),
  (8, 'West', 'Dan',  '2026-01-25', 1100);

ROW_NUMBER, RANK, and DENSE_RANK

These three functions assign a sequence to rows inside each partition. They differ only in how they handle ties.

SELECT
  region,
  salesperson,
  amount,
  ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn,
  RANK()       OVER (PARTITION BY region ORDER BY amount DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS dr
FROM sales;

• ROW_NUMBER always produces 1, 2, 3, 4 with no gaps and no ties. Two rows with the same amount still get distinct numbers; the database picks an order.
• RANK gives ties the same number, then skips. Two rows tied for 1 are followed by rank 3.
• DENSE_RANK gives ties the same number but does not skip. Two rows tied for 1 are followed by rank 2.

A common task is “top 2 sales per region”. ROW_NUMBER plus a subquery solves it cleanly.

SELECT region, salesperson, amount
FROM (
  SELECT
    region, salesperson, amount,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
  FROM sales
) ranked
WHERE rn <= 2;

Aggregates as window functions

Any standard aggregate, like SUM, AVG, COUNT, MIN, MAX, can be used as a window function by adding an OVER clause. The rows are not collapsed.

SELECT
  region,
  salesperson,
  amount,
  SUM(amount) OVER (PARTITION BY region) AS region_total,
  amount * 100.0 / SUM(amount) OVER (PARTITION BY region) AS pct_of_region
FROM sales;

Each row now knows its region total and its share of that total. With a plain GROUP BY you would lose the salesperson-level detail.

Running totals

Adding ORDER BY to a window changes its meaning. Aggregates become cumulative from the start of the partition up to the current row.

SELECT
  region,
  sold_at,
  amount,
  SUM(amount) OVER (
    PARTITION BY region
    ORDER BY sold_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM sales
ORDER BY region, sold_at;

The ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW clause makes the frame explicit. In most databases this is the default once you add ORDER BY, but writing it out documents intent and avoids subtle differences between engines.

Moving averages

Change the frame to a fixed-size window around the current row and you get a moving average.

SELECT
  region,
  sold_at,
  amount,
  AVG(amount) OVER (
    PARTITION BY region
    ORDER BY sold_at
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS three_row_avg
FROM sales
ORDER BY region, sold_at;

This averages each sale with its immediate neighbors in time, which smooths out spikes.

LAG and LEAD

LAG looks back; LEAD looks forward. Both return a value from another row in the same partition, ordered by your ORDER BY.

SELECT
  salesperson,
  sold_at,
  amount,
  LAG(amount)  OVER (PARTITION BY salesperson ORDER BY sold_at) AS prev_amount,
  LEAD(amount) OVER (PARTITION BY salesperson ORDER BY sold_at) AS next_amount,
  amount - LAG(amount) OVER (PARTITION BY salesperson ORDER BY sold_at) AS delta
FROM sales
ORDER BY salesperson, sold_at;

The delta column shows how each sale compares to the previous sale by the same person. The first row in each partition returns NULL for LAG. You can supply a default with LAG(amount, 1, 0).

FIRST_VALUE and LAST_VALUE

When you want every row to carry a value from the boundary of its partition, use FIRST_VALUE or LAST_VALUE.

SELECT
  region,
  salesperson,
  amount,
  FIRST_VALUE(salesperson) OVER (
    PARTITION BY region ORDER BY amount DESC
  ) AS top_seller
FROM sales;

Be careful with LAST_VALUE: the default frame ends at the current row, so you usually need to widen it.

SELECT
  region,
  salesperson,
  amount,
  LAST_VALUE(salesperson) OVER (
    PARTITION BY region
    ORDER BY amount DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS bottom_seller
FROM sales;

When to use window functions vs GROUP BY

Use GROUP BY when you want one row per group. Use a window function when you want every row plus a group-level summary on each. They also compose: you can GROUP BY to a daily total and then apply a window over those daily totals to get a running total over time.

Window functions can also remove the need for self-joins. Suppose you want to find sales that exceed the previous sale by the same person. Without window functions you would join sales to itself by salesperson and date. With LAG, the comparison is a single column in a single pass. Pair that with the indexing strategies in SQL indexes and performance so the underlying ORDER BY does not become a bottleneck.

Wrap up

Window functions extend SQL with a small, powerful vocabulary: OVER, PARTITION BY, ORDER BY, frame specs, and the ranking and offset functions. Reach for them whenever you need a per-row answer that depends on the surrounding rows. Top N per group, running totals, deltas to the previous row, share of total: all collapse to a few lines once you stop fighting GROUP BY and let the window do the work. Combine them with joins, as covered in SQL joins, and you can express almost any analytical query without leaving the database.