1. Home
  2. Blog
  3. Databases & SQL

Databases & SQL

SQL LAG and LEAD Explained: Compare Each Row With Its Neighbours

SQL LAG reads a value from the previous row and LEAD reads from the next row, with no self join. Learn the syntax, partitions, offsets and defaults through five tested examples: change, percent growth, next values, two-back comparisons and time gaps.

8 min read
SQL LAG and LEAD: compare rows with window functions

Key takeaways

  • SQL LAG returns a value from an earlier row and LEAD from a later row, in the order you define with OVER (ORDER BY ...).
  • PARTITION BY restarts the comparison for each group, so one region never borrows another region’s numbers.
  • Edge rows get NULL unless you pass a default as the third argument.
  • You cannot use LAG in WHERE. Compute it in a subquery or CTE, then filter outside.

“How much did revenue change from last month?” “How long since this user last logged in?” “Which day did the price drop?” All of these compare a row with its neighbour. Before window functions, you answered them with a self join on a calculated row number, which was slow to write and easy to get wrong. SQL LAG and its twin LEAD do it in one expression.

Every query below ran on SQLite 3.50.4. The same syntax works in PostgreSQL, MySQL 8+, SQL Server 2012+, Oracle, and warehouses such as Snowflake and BigQuery.

What do LAG and LEAD do?

Both are window functions. They look at other rows in a defined order without collapsing the result, so every input row still appears in the output. LAG looks backwards, LEAD looks forwards. If window functions are new to you, start with our SQL window functions guide.

Syntax

LAG(expression [, offset [, default]]) OVER (
    [PARTITION BY group_columns]
    ORDER BY sort_columns
)
  • expression: the column or calculation to fetch.
  • offset: how many rows back (or forward for LEAD). Defaults to 1.
  • default: the value to return when that row does not exist. Defaults to NULL.
  • PARTITION BY: optional groups that each get their own sequence.
  • ORDER BY: required in practice. Without it, “previous” has no meaning.

Sample data

CREATE TABLE sales(region TEXT, month TEXT, revenue INT);
INSERT INTO sales VALUES
 ('North','2026-07',120), ('North','2026-08',150), ('North','2026-09',135),
 ('North','2026-10',180), ('South','2026-07',90),  ('South','2026-08',90),
 ('South','2026-09',110);

Example 1: previous value and change

We define the window once with a named WINDOW clause, then reuse it. This keeps the query short and guarantees every function uses the same order:

SELECT region, month, revenue,
       LAG(revenue) OVER w AS prev,
       revenue - LAG(revenue) OVER w AS change
FROM sales
WINDOW w AS (PARTITION BY region ORDER BY month);

The output appears in the combined table under example 2. The first row of each region has no previous month, so prev and change are NULL there. That is correct: there is nothing to compare with. The named WINDOW clause works in PostgreSQL, MySQL 8, SQLite and SQL Server 2022. In older versions, repeat the full OVER (...) each time.

Example 2: percent growth

Percent change is the most common reporting use. Multiply by 100.0 to force decimal division:

SELECT region, month, revenue,
  LAG(revenue) OVER w AS prev,
  revenue - LAG(revenue) OVER w AS change,
  ROUND(100.0 * (revenue - LAG(revenue) OVER w) / LAG(revenue) OVER w, 1) AS pct,
  LEAD(month) OVER w AS next_month
FROM sales
WINDOW w AS (PARTITION BY region ORDER BY month);
region month revenue prev change pct next_month
North 2026-07 120 NULL NULL NULL 2026-08
North 2026-08 150 120 30 25.0 2026-09
North 2026-09 135 150 -15 -10.0 2026-10
North 2026-10 180 135 45 33.3 NULL
South 2026-07 90 NULL NULL NULL 2026-08
South 2026-08 90 90 0 0.0 2026-09
South 2026-09 110 90 20 22.2 NULL

Notice that South July shows NULL, not North October’s 180. PARTITION BY region stopped values leaking across regions. If the previous value can be zero, guard the division with NULLIF(LAG(revenue) OVER w, 0) so you get NULL instead of an error.

Example 3: the next value with LEAD

The next_month column above comes from LEAD(month). It is NULL on the last row of each region. LEAD is handy for “valid until” ranges: if each row is a price that started on a date, LEAD(start_date) gives the date the next price took over, which is when this one ended. It also powers “what happened next” analysis, such as the step a user took after viewing a page.

Example 4: offset and default value

SELECT month, revenue,
       LAG(revenue, 1, 0) OVER (ORDER BY month) AS prev0,
       LAG(revenue, 2)    OVER (ORDER BY month) AS two_back
FROM sales WHERE region = 'North';
month revenue prev0 two_back
2026-07 120 0 NULL
2026-08 150 120 NULL
2026-09 135 150 120
2026-10 180 135 150

The default 0 replaced the NULL in the first row. Be careful with defaults, though. A default of 0 makes the first month look like growth from nothing, which can distort averages. NULL is often the more honest answer. An offset of 2 compares with two rows back, useful for “same quarter last year” style comparisons when each row is a quarter: use an offset of 4.

Example 5: time gaps between events

LAG works on any type, including dates. Here we measure minutes between logins for each user:

SELECT user, ts,
       ROUND((julianday(ts) - julianday(
         LAG(ts) OVER (PARTITION BY user ORDER BY ts))) * 1440) AS gap_min
FROM logins;
user ts gap_min
u1 2026-11-01 09:00 NULL
u1 2026-11-01 09:20 20.0
u1 2026-11-01 11:05 105.0
u2 2026-11-01 10:00 NULL
u2 2026-11-01 10:10 10.0

This is the basis of sessionisation: if the gap is over 30 minutes, start a new session. Date arithmetic differs by database. PostgreSQL subtracts timestamps directly, and SQL Server uses DATEDIFF(minute, LAG(ts) OVER (...), ts). See our SQL date functions guide for each dialect.

Filtering on LAG results

Suppose you want only the months where revenue fell. The obvious query fails:

SELECT revenue FROM sales WHERE LAG(revenue) OVER (ORDER BY month) > 1;
-- OperationalError: misuse of window function LAG()

Window functions run after WHERE, so they cannot appear in it. Wrap the query and filter outside:

SELECT * FROM (
  SELECT region, month, revenue,
         LAG(revenue) OVER (PARTITION BY region ORDER BY month) AS prev
  FROM sales
) WHERE revenue < prev;
-- North | 2026-09 | 135 | 150

Only North September dropped. A CTE works the same way and reads better in longer queries.

LAG vs a self join

The old approach numbered rows and joined the table to itself on a.rn = b.rn + 1. It still works, but it is longer, reads the table twice, and breaks easily when you add partitions. LAG states the intent directly, and databases can compute it in a single sorted pass. Keep the self join for genuine row-to-row relationships, such as employees and their managers.

Testing tip: when you test reports built on LAG, include a group with only one row, a group with a repeated value, and a previous value of zero. Those three cases catch most edge bugs.

Common mistakes

  1. Missing PARTITION BY, so groups borrow values from each other.
  2. Ordering by the wrong column, or by a column with ties. Add a tiebreaker such as an ID for a deterministic result.
  3. Using LAG in WHERE. Use a subquery or CTE.
  4. Assuming LAG skips gaps. It returns the previous row, not the previous calendar month. If a month is missing, the comparison spans two months. Fill gaps first with a calendar table.
  5. Integer division in percent calculations. Multiply by 100.0, not 100.
  6. Careless defaults that create fake growth.

Related functions: FIRST_VALUE and LAST_VALUE

LAG compares with the row next door. Sometimes you want to compare with the start of the period instead, for example growth since the first month. FIRST_VALUE does that:

SELECT region, month, revenue,
       revenue - FIRST_VALUE(revenue) OVER w AS since_first,
       LAST_VALUE(revenue) OVER w AS last_default,
       LAST_VALUE(revenue) OVER (PARTITION BY region ORDER BY month
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_full
FROM sales
WINDOW w AS (PARTITION BY region ORDER BY month);

For North, since_first came out as 0, 30, 15 and 60, all measured against July’s 120. The surprise is LAST_VALUE. With the default frame, last_default simply repeated each row’s own revenue (120, 150, 135, 180), because the default window frame stops at the current row. Only last_full, with the frame widened to UNBOUNDED FOLLOWING, returned the true last value, 180 for North and 110 for South. LAG and LEAD ignore the frame, so they do not have this trap. Our running total guide explains window frames in more detail.

Final thoughts

SQL LAG and LEAD turn “compare with the previous row” into one readable expression. Define the order, partition by your groups, decide what edge rows should show, and filter in an outer query. With the five patterns above, you can build month over month reports, price histories and session analysis without a single self join.

FAQ

What does LAG do in SQL?

LAG is a window function that returns a value from a previous row in the order you define, by default the row directly before, without a self join.

What is the difference between LAG and LEAD?

LAG looks at earlier rows and LEAD looks at later rows. Both take the same arguments: an expression, an optional offset and an optional default.

Why does LAG return NULL on the first row?

There is no earlier row to read, so LAG returns NULL. Pass a third argument to use a default value instead.

Can I use LAG in a WHERE clause?

No. Window functions run after WHERE. Compute LAG in a subquery or CTE, then filter in the outer query.

Which databases support LAG and LEAD?

PostgreSQL, MySQL 8 and later, SQL Server 2012 and later, Oracle, SQLite 3.25 and later, and cloud warehouses such as Snowflake and BigQuery.

Keep learning

Work with Stack Quora

Need help with testing, WordPress or SEO?

We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.

Keep reading

More guides on testing, AI & modern development

Enjoyed this article? Pick a topic and jump straight into our newest tutorials.

Artificial Intelligence

Jaccard Similarity: Formula, Python, MinHash

Jaccard similarity measures overlap between two sets as intersection over union. Work the formula, compute it in Python for words…

Read article →
Software Development

Unix Timestamp Explained: Seconds vs ms

A Unix timestamp counts seconds since 1 January 1970 UTC. Learn seconds vs milliseconds, converting in Python, JavaScript and SQL,…

Read article →
DevOps

Git Hooks: Pre-commit and Commit-msg Examples

Git hooks run scripts at key moments such as commit and push. See tested pre-commit, commit-msg and prepare-commit-msg hooks, sharing…

Read article →
Test Automation

API Testing in Python: Pytest + Requests Suite

Build an API testing suite in Python with pytest and requests: fixtures, status codes, headers, JSON schema validation, CRUD flows,…

Read article →
Software Testing

Test Design Techniques: 9 Methods + Examples

Test design techniques help you write fewer tests that catch more bugs. Learn 9 black-box, white-box and experience-based methods with…

Read article →
Databases & SQL

SQL Temporary Table: Create, Use and Drop It

SQL temporary tables store interim results for one session. Learn syntax for SQL Server, PostgreSQL, MySQL, Oracle and SQLite, session…

Read article →
Databases & SQL

SQL CROSS JOIN: Every Combination + Examples

SQL CROSS JOIN returns every combination of rows from two tables. See the row math, product variants, calendar grids that…

Read article →
Software Development

JavaScript Date: Format, Parse, Timezone Bugs

JavaScript Date explained with tested Node.js code: zero-based months, the date-only UTC parsing trap, toISOString, getTimezoneOffset, Intl formatting, date math…

Read article →
Software Development

Python Requests: GET, POST, Timeouts, Retries

Python requests explained with code tested against a local API: GET with params, POST json vs data, headers and auth,…

Read article →
Software Development

Python Deepcopy vs Copy: Fix Nested Lists

Python deepcopy vs shallow copy explained with tested code: aliasing, nested lists, the [[0]*3]*3 trap, memo and cycles, custom __deepcopy__,…

Read article →
Artificial Intelligence

TF-IDF Explained: Formula and Worked Example

TF-IDF scores a word by how often it appears in a document and how rare it is across all documents….

Read article →
Software Development

Base64 Encoding Explained, With Python Code

Base64 encoding turns bytes into 64 safe text characters. See the bit-level steps, padding, URL-safe base64, the 33% size cost,…

Read article →
DevOps

Git Tag: Create, Push and Delete Release Tags

Git tag explained with tested commands: annotated vs lightweight tags, listing and sorting, pushing with follow-tags, deleting local and remote…

Read article →
Test Automation

Pytest Coverage: pytest-cov and Branch Gaps

Measure pytest coverage with pytest-cov: term-missing reports, branch coverage, HTML reports, pyproject settings and a fail-under gate for CI, with…

Read article →
Software Testing

Error Guessing in Testing: Checklist + Demo

Error guessing uses tester experience to predict where code breaks. Get a ready checklist of risky inputs, a step by…

Read article →
Databases & SQL

SQL LAG and LEAD: Compare Rows, 5 Examples

SQL LAG and LEAD read values from the previous or next row without a self join. Five tested examples: change,…

Read article →
Databases & SQL

SQL STRING_AGG: Combine Rows Into One String

SQL STRING_AGG joins values from many rows into one string per group. See syntax for PostgreSQL, SQL Server, MySQL GROUP_CONCAT,…

Read article →
Software Development

JavaScript Classes: Private Fields and Extends

JavaScript classes explained with tested Node.js code: constructors, methods, #private fields, getters, static members, extends and super, plus the errors…

Read article →
Artificial Intelligence

BM25 Explained: How Keyword Search Ranks Results, With Python Code and Hybrid Search

BM25 explained with a pure Python implementation we ran: the formula, IDF, term saturation, the k1 and b parameters, why…

Read article →
DevOps

Git Bisect Explained: Find the Commit That Broke Your Code, Manually or Automatically

Git bisect explained with a tested repository: find the one bad commit among 41 in 5 test runs, automate it…

Read article →
Test Automation

pytest Markers Explained: Custom Marks, skip, skipif, xfail and -m Filters

pytest markers explained with a tested suite: register custom marks, run only smoke tests with -m, combine filters with and,…

Read article →
Software Testing

Positive vs Negative Testing: Differences, 24 Real Test Cases and a Checklist

Positive vs negative testing explained with a real signup form: 6 positive and 18 negative test cases run in pytest,…

Read article →
Databases & SQL

SQL ORDER BY Multiple Columns: ASC, DESC, NULLS LAST, Case and Custom Order

SQL ORDER BY with multiple columns explained with tested queries: mixed ASC and DESC, tie-breakers, NULLS FIRST and LAST, case-insensitive…

Read article →
Databases & SQL

SQL Running Total: SUM OVER, Per-Group Totals, Moving Sums and the RANGE Trap

How to calculate a SQL running total with SUM() OVER: per-group totals, daily cumulative sums, moving sums, percent of total,…

Read article →
Artificial Intelligence

Cosine Similarity Explained: Formula, Worked Example and Python Code

Cosine similarity explained simply: the formula, a worked example by hand, pure Python and NumPy code, cosine vs Euclidean distance…

Read article →
DevOps

Git Squash Commits: 4 Easy Ways, Tested Step by Step (rebase -i, reset, merge)

Git squash commits explained with 4 tested methods: interactive rebase, reset –soft, merge –squash and fixup with autosquash. Before-and-after logs,…

Read article →
Test Automation

pytest parametrize Explained: 7 Patterns with Tested Examples

pytest parametrize explained with 7 tested patterns: basic cases, readable ids, stacked decorators, pytest.param with xfail and skip, indirect fixtures…

Read article →
Software Testing

Retesting vs Regression Testing: 8 Key Differences with a Real Bug Fix Example

Retesting vs regression testing explained with a real bug fix run in pytest: retesting confirmed the fix, the regression run…

Read article →
Artificial Intelligence

RAG Chunking Strategies Explained: Fixed, Overlap, Sentence, Recursive and Headings, Measured

RAG chunking strategies compared on one document with tiktoken 0.14: fixed-size, overlap, sentence packing, recursive splitting and heading-based chunks, measuring…

Read article →
DevOps

Git Stash Explained: Save, Apply, Pop and Untracked Files, Tested

Git stash explained with commands we ran on Git 2.56: push with a message, untracked files, list and show, apply…

Read article →
Software Testing

Pairwise Testing Explained: How All-Pairs Cuts 216 Tests to 13, Tested

Pairwise testing explained with a generator we ran in Python 3.13: a checkout with 216 combinations covered by 13 tests,…

Read article →
Test Automation

Test Doubles Explained: Dummy, Stub, Spy, Mock and Fake, with Tested Python Code

Test doubles explained with one Python example run in pytest 9.2: dummy, stub, spy, mock and fake, when to use…

Read article →
Artificial Intelligence

LLM Context Window Explained: Tokens, Limits and How to Fit More, Measured

The LLM context window explained with token counts we measured using tiktoken: words vs tokens for prose, code and Hindi,…

Read article →
DevOps

Git Cherry-Pick Explained: Copy the Commit You Need, Safely, Tested

Git cherry-pick explained with commands we ran in Git 2.56: copying a hotfix to a release branch, new hashes and…

Read article →
Test Automation

Snapshot Testing Explained: How It Works, When It Helps and When It Hurts

Snapshot testing explained with a pytest and syrupy demo we ran: creating snapshots, reading the diff after a code change,…

Read article →
Software Testing

Mutation Testing Explained: Why 100% Coverage Still Missed Our Bug

Mutation testing explained with a small mutation tool we built and ran: 14 mutants, a test suite with 100% line…

Read article →