SQL Window Functions: ROW_NUMBER, RANK, and LAG/LEAD Guide

What are Window Functions?

Window functions perform calculations across a set of rows that are related to the current row — without collapsing them into a single output row like GROUP BY does.

They're called 'window' functions because they look through a 'window' of rows to compute a value. They're one of the most powerful features in SQL and a common topic in technical interviews.

ROW_NUMBER

ROW_NUMBER assigns a unique sequential number to each row within a partition.

SELECT
  name,
  gpa,
  ROW_NUMBER() OVER (ORDER BY gpa DESC) AS rank
FROM students;

This ranks students by GPA. Each student gets a unique number — no ties.

RANK and DENSE_RANK

RANK and DENSE_RANK handle ties differently than ROW_NUMBER:

SELECT
  name,
  gpa,
  RANK() OVER (ORDER BY gpa DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY gpa DESC) AS dense_rank
FROM students;

• RANK skips numbers after ties (1, 2, 2, 4)
• DENSE_RANK doesn't skip (1, 2, 2, 3)

LAG and LEAD

LAG looks at previous rows, LEAD looks at next rows:

SELECT
  name,
  gpa,
  LAG(gpa) OVER (ORDER BY name) AS prev_gpa,
  LEAD(gpa) OVER (ORDER BY name) AS next_gpa
FROM students
ORDER BY name;

These are incredibly useful for comparing a row to its neighbors — for example, calculating day-over-day changes in metrics.

Practical Use Cases

Window functions shine in real-world scenarios:

• Top-N per group — Find the top 3 students per department
• Running totals — Calculate cumulative revenue over time
• Moving averages — Smooth out fluctuations in time series data
• Gap analysis — Find missing sequences in data

Practice these patterns in the Ira SQL Pro playground with our built-in sample data.