SQL Window Functions: ROW_NUMBER, RANK, LAG & LEAD Explained

SQL window functions solve a problem every analyst hits: you need to rank rows, compare a value to the previous row, or calculate a running total — but GROUP BY collapses the detail you need. That is exactly the problem window functions solve. They let you perform calculations across related rows without losing the individual row context. Whether you're building leaderboards, detecting trends, or doing period-over-period comparisons, window functions are the tool that turns a clunky multi-step query into a single elegant statement.

In this guide you'll learn the four most-used window functions — ROW_NUMBER, RANK, DENSE_RANK, LAG, and LEAD — with real SQL examples on the Chinook music-store database that you can run yourself.

Table of Contents

• How Window Functions Work
• ROW_NUMBER — Assign Sequential Numbers
• RANK and DENSE_RANK — Handle Ties
• LAG — Look at the Previous Row
• LEAD — Look at the Next Row
• Quick Comparison Cheat Sheet
• Common Mistakes to Avoid
• Practice Makes Perfect
• Summary
• Frequently Asked Questions

How Window Functions Work

A window function performs a calculation across a set of rows that are somehow related to the current row. Unlike GROUP BY, it does not collapse rows — every row in your result keeps its identity.

The basic syntax looks like this:

function_name() OVER (
  PARTITION BY column   -- optional: divides rows into groups
  ORDER BY column       -- defines the row order inside each group
)

• PARTITION BY splits the data into independent windows (like separate GROUP BY buckets, but without collapsing).
• ORDER BY determines the sequence within each partition.

Think of it this way: PARTITION BY decides which rows belong together, and ORDER BY decides how they're arranged.

ROW_NUMBER — Assign Sequential Numbers

ROW_NUMBER() assigns a unique, consecutive integer to every row within its partition. Even when two rows have identical values, they get different numbers.

Numbering tracks within each album

SELECT
  a.Title AS Album,
  t.Name  AS Track,
  t.Milliseconds,
  ROW_NUMBER() OVER (
    PARTITION BY t.AlbumId
    ORDER BY t.Milliseconds DESC
  ) AS rn
FROM Track t
JOIN Album a ON a.AlbumId = t.AlbumId
WHERE a.AlbumId IN (1, 4)
ORDER BY a.Title, rn;

Each album starts numbering from 1 again — that's PARTITION BY in action. A classic use case is picking the top-N per group: wrap the query in a CTE and filter WHERE rn <= N.

Try it yourself — Practice ranking tracks per genre with the Chinook database →

RANK and DENSE_RANK — Handle Ties

When rows share the same value, ROW_NUMBER still forces a unique number. RANK and DENSE_RANK behave differently:

• RANK — tied rows get the same number, then the next number skips (1, 1, 3).
• DENSE_RANK — tied rows get the same number, and the next number is consecutive (1, 1, 2).

Ranking customers by total spending

SELECT
  c.FirstName || ' ' || c.LastName AS Customer,
  ROUND(SUM(i.Total), 2) AS TotalSpent,
  RANK()       OVER (ORDER BY SUM(i.Total) DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY SUM(i.Total) DESC) AS dense_rank
FROM Customer c
JOIN Invoice i ON i.CustomerId = c.CustomerId
GROUP BY c.CustomerId, c.FirstName, c.LastName
ORDER BY TotalSpent DESC
LIMIT 8;

Notice how RANK jumps from 4 to 6 (skipping 5) while DENSE_RANK moves from 4 to 5. Choose RANK when you care about absolute position ("she finished 4th out of 100"), and DENSE_RANK when you care about the level ("she's in the 4th tier").

Try it yourself — Build a customer spending leaderboard →

LAG — Look at the Previous Row

LAG(column, offset, default) returns the value from a previous row in the same partition. The default offset is 1 (one row back).

This is invaluable for period-over-period comparisons — month-over-month revenue, consecutive sensor readings, or detecting changes between events.

Comparing each invoice to the customer's previous invoice

SELECT
  c.FirstName || ' ' || c.LastName AS Customer,
  i.InvoiceDate,
  i.Total,
  LAG(i.Total) OVER (
    PARTITION BY i.CustomerId
    ORDER BY i.InvoiceDate
  ) AS PrevTotal,
  ROUND(i.Total - LAG(i.Total) OVER (
    PARTITION BY i.CustomerId
    ORDER BY i.InvoiceDate
  ), 2) AS Diff
FROM Invoice i
JOIN Customer c ON c.CustomerId = i.CustomerId
WHERE i.CustomerId = 1
ORDER BY i.InvoiceDate
LIMIT 6;

The first row returns NULL because there is no previous invoice — you can supply a default value as the third argument: LAG(i.Total, 1, 0).

Try it yourself — Compare previous customer invoice totals →

LEAD — Look at the Next Row

LEAD is the mirror image of LAG — it looks forward instead of backward. Use it when you need to know what comes next: the next delivery date, the next price point, or the next track on an album.

Peeking at the next track's duration

SELECT
  t.Name AS Track,
  t.Milliseconds / 1000 AS Seconds,
  LEAD(t.Milliseconds / 1000) OVER (
    PARTITION BY t.AlbumId
    ORDER BY t.TrackId
  ) AS NextTrackSeconds,
  LEAD(t.Name) OVER (
    PARTITION BY t.AlbumId
    ORDER BY t.TrackId
  ) AS NextTrack
FROM Track t
WHERE t.AlbumId = 1
LIMIT 5;

Combining LAG and LEAD together is powerful for detecting anomalies — if both the previous and next values are much lower than the current one, you may have found a spike.

Try it yourself — Identify the duration of the next track →

Quick Comparison Cheat Sheet

All five support PARTITION BY to create independent groups. Without it, the entire result set is treated as one partition.

Common Mistakes to Avoid

1. Forgetting ORDER BY inside OVER()

Without an ORDER BY, ranking functions return non-deterministic results — the database can number rows in any order.

-- Wrong: non-deterministic ranking
SELECT Name, ROW_NUMBER() OVER () AS rn FROM Track;

-- Right: explicit ordering
SELECT Name, ROW_NUMBER() OVER (ORDER BY Name) AS rn FROM Track;

2. Using ROW_NUMBER when you need RANK

If two students scored 95 and you use ROW_NUMBER, one gets rank 1 and the other rank 2. Use RANK or DENSE_RANK when ties should share the same position.

-- Wrong: arbitrary tie-breaking
ROW_NUMBER() OVER (ORDER BY Score DESC)

-- Right: tied scores share a rank
RANK() OVER (ORDER BY Score DESC)

3. Filtering on a window function in WHERE

Window functions are calculated after WHERE. You cannot filter on them directly — use a subquery or CTE.

-- Wrong: syntax error
SELECT Name, RANK() OVER (ORDER BY Total DESC) AS rnk
FROM Invoice
WHERE rnk <= 3;

-- Right: use a CTE
WITH ranked AS (
  SELECT Name, RANK() OVER (ORDER BY Total DESC) AS rnk
  FROM Invoice
)
SELECT * FROM ranked WHERE rnk <= 3;

4. Confusing LAG offset with negative numbers

LAG(col, 2) means two rows back, not LAG(col, -2). Negative offsets are not standard SQL. Use LEAD to look forward.

5. Not handling NULLs from LAG/LEAD

The first row in a partition always returns NULL from LAG. Guard against it with COALESCE or the third argument:

-- Provide a default of 0 when there is no previous row
LAG(Total, 1, 0) OVER (ORDER BY InvoiceDate)
-- Or use COALESCE
COALESCE(LAG(Total) OVER (ORDER BY InvoiceDate), 0)

Practice Makes Perfect

The best way to internalize window functions is to write them yourself. Here are exercises that target exactly the functions covered in this guide:

Join 500+ SQL learners already practicing on SQiLs — Browse all exercises →

Summary

• Window functions calculate values across related rows without collapsing your result set.
• ROW_NUMBER gives each row a unique number — perfect for top-N-per-group queries.
• RANK and DENSE_RANK handle ties differently: RANK leaves gaps, DENSE_RANK does not.
• LAG looks backward and LEAD looks forward — essential for trend and change detection.
• Always include ORDER BY inside OVER(), and use a CTE when you need to filter on the result.
• Once you're comfortable with these five functions, explore aggregate window functions like SUM() OVER() and AVG() OVER() for running totals and moving averages.

Ready to put this into practice? Start solving exercises on SQiLs →

Frequently Asked Questions

What is the difference between ROW_NUMBER and RANK in SQL?

ROW_NUMBER always assigns unique consecutive integers, even when rows have identical values — ties are broken arbitrarily. RANK gives tied rows the same number but then skips subsequent numbers, so you might see 1, 1, 3. Use ROW_NUMBER when you need exactly one winner per position, and RANK when ties should be visible.

Can I use window functions with GROUP BY?

Yes. The window function runs after aggregation, so you can GROUP BY to get totals and then use RANK() OVER(...) on those totals. This is how you build a leaderboard from aggregated data — group first, rank second.

How do LAG and LEAD handle the first and last rows?

LAG returns NULL for the first row in a partition (there is no previous row), and LEAD returns NULL for the last row. You can supply a default value as the third argument — for example LAG(Total, 1, 0) — or wrap the call in COALESCE.

Do window functions work in SQLite?

Yes. SQLite added window function support in version 3.25.0 (2018). All five functions covered here — ROW_NUMBER, RANK, DENSE_RANK, LAG, and LEAD — work in SQLite, which is what powers the SQiLs practice environment.