SQL Window Functions Complete Guide: ROW_NUMBER, RANK, LAG, LEAD

Why Window Functions Matter

Window functions let you compute values across a set of rows related to the current row without collapsing the result set. Unlike GROUP BY, every row stays visible. This makes them indispensable for ranking, running totals, row comparisons, and percentile calculations.

Consider a table of monthly sales:

CREATE TABLE sales (
  id          SERIAL PRIMARY KEY,
  rep_name    TEXT NOT NULL,
  region      TEXT NOT NULL,
  sale_date   DATE NOT NULL,
  amount      NUMERIC(10,2) NOT NULL
);

The OVER Clause

Every window function requires an OVER() clause. An empty OVER() treats the entire result set as one window:

SELECT rep_name, amount,
       SUM(amount) OVER () AS grand_total
FROM sales;

Add PARTITION BY to create separate windows per group:

SELECT rep_name, region, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales;

Add ORDER BY inside OVER to define row ordering within the partition. This is required for ranking and offset functions.

ROW_NUMBER, RANK, and DENSE_RANK

These three functions assign an integer to each row within a partition based on the ORDER BY clause.

SELECT rep_name, 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_rnk
FROM sales;

The differences surface when there are ties:

amount	ROW_NUMBER	RANK	DENSE_RANK
500	1	1	1
500	2	1	1
400	3	3	2

• ROW_NUMBER always produces unique integers; tie-breaking is nondeterministic unless you add a secondary sort column.
• RANK leaves gaps after ties (1, 1, 3).
• DENSE_RANK never leaves gaps (1, 1, 2).

Practical Use: Top-N Per Group

Fetch the top 3 reps per region:

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

LAG and LEAD

LAG(expr, offset, default) reads a value from a previous row. LEAD reads from a subsequent row. Both respect the ORDER BY in the window.

SELECT rep_name,
       sale_date,
       amount,
       LAG(amount, 1, 0) OVER (
         PARTITION BY rep_name ORDER BY sale_date
       ) AS prev_amount,
       amount - LAG(amount, 1, 0) OVER (
         PARTITION BY rep_name ORDER BY sale_date
       ) AS change
FROM sales;

This is the cleanest way to compute period-over-period differences. No self-join needed.

Multi-Step Offsets

You can look further back or forward by changing the offset:

SELECT sale_date, amount,
       LAG(amount, 3)  OVER (ORDER BY sale_date) AS three_periods_ago,
       LEAD(amount, 1) OVER (ORDER BY sale_date) AS next_period
FROM sales
WHERE rep_name = 'Alice';

FIRST_VALUE and LAST_VALUE

These pull the first or last value in the current window frame:

SELECT rep_name, sale_date, amount,
       FIRST_VALUE(amount) OVER (
         PARTITION BY rep_name ORDER BY sale_date
       ) AS first_sale,
       LAST_VALUE(amount) OVER (
         PARTITION BY rep_name ORDER BY sale_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS last_sale
FROM sales;

Notice LAST_VALUE requires an explicit frame. The default frame with ORDER BY is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which means LAST_VALUE would just return the current row’s value without the override.

NTILE and PERCENT_RANK

NTILE(n) splits rows into n roughly equal buckets:

SELECT rep_name, amount,
       NTILE(4) OVER (ORDER BY amount) AS quartile
FROM sales;

PERCENT_RANK returns a value between 0 and 1 representing the relative rank:

SELECT rep_name, amount,
       ROUND(PERCENT_RANK() OVER (ORDER BY amount)::numeric, 2) AS pct_rank
FROM sales;

Frame Clauses Deep Dive

The frame clause controls exactly which rows the window function sees. It comes after ORDER BY inside OVER:

-- Running total (default with ORDER BY)
SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

-- 3-row moving average
AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

-- Cumulative to end
SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)

There are two frame types:

• ROWS counts physical rows.
• RANGE groups rows with the same ORDER BY value.

For most use cases, ROWS is more predictable.

Named Windows

When multiple columns reuse the same window definition, a WINDOW clause avoids repetition:

SELECT rep_name, sale_date, amount,
       ROW_NUMBER() OVER w AS rn,
       SUM(amount)  OVER w AS running_total,
       LAG(amount)  OVER w AS prev_amount
FROM sales
WINDOW w AS (PARTITION BY rep_name ORDER BY sale_date);

Performance Considerations

Window functions run after WHERE, GROUP BY, and HAVING but before ORDER BY and LIMIT. This means:

1. Filter rows before windowing using WHERE to reduce the working set.
2. If you need to filter on a window result (like WHERE rn = 1), wrap it in a CTE or subquery.
3. Ensure indexes exist on the columns used in PARTITION BY and ORDER BY.

CREATE INDEX idx_sales_rep_date ON sales (rep_name, sale_date);

Summary

Window functions eliminate self-joins, correlated subqueries, and procedural workarounds. The key functions to master are ROW_NUMBER for unique ordering, RANK/DENSE_RANK for tie-aware ranking, LAG/LEAD for row comparisons, and frame-aware aggregates like SUM OVER for running calculations. Start by replacing your next subquery-based ranking with a CTE and ROW_NUMBER — the clarity improvement alone justifies the switch.