SQL Interview Prep · Lesson

LAG and LEAD for Adjacent Rows

Accessing previous and next row values without a self-join.

LAG and LEAD for Adjacent Rows is a free SQL Interview Prep lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Question Interviewers Ask

One of the most common analyst interview prompts is: "Compare each row to the one before it without a self-join." Think month-over-month revenue, a user's previous login, or the next event in a sequence.

The clean answer is the LAG and LEAD window functions. They let a row peek at a neighboring row's value while keeping every detail row intact. In this lesson you will build a precise mental model of how they navigate adjacent rows.

What LAG and LEAD Do

LAG(col) returns the value of col from the previous row. LEAD(col) returns the value from the next row. "Previous" and "next" are defined entirely by the ORDER BY inside the OVER clause.

LAG looks backward.
LEAD looks forward.

Both are offset window functions: they never collapse rows, they just attach a neighbor's value to the current row.

Basic LAG Syntax

Here is the canonical shape. We have a sales table with a month and revenue. We want each row to also show the previous month's revenue.

The OVER (ORDER BY month) tells the engine how to define "previous." The first row has no predecessor, so prev_revenue is NULL there.

SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_revenue
FROM sales
ORDER BY month;

Reading the Result

For data 2024-01 = 100, 2024-02 = 130, 2024-03 = 120, the query returns:

Jan: revenue 100, prev_revenue NULL
Feb: revenue 130, prev_revenue 100
Mar: revenue 120, prev_revenue 130

Each row pulled the value from the row directly above it in the ordered set. No self-join, no subquery, no row loss.

LEAD Looks Forward

LEAD is the mirror image. Use it when a row needs to know what comes next, for example the next purchase date to compute time between orders.

The last row in the ordered set has no successor, so its LEAD result is NULL.

SELECT
  month,
  revenue,
  LEAD(revenue) OVER (ORDER BY month) AS next_revenue
FROM sales
ORDER BY month;

The Offset Argument

Both functions take an optional second argument: how many rows to jump. LAG(col, 2) goes back two rows, LEAD(col, 3) jumps three rows forward.

Interviewers use this to ask for, say, "revenue two months ago" or "the value three rows down." The default offset is 1.

SELECT
  month,
  revenue,
  LAG(revenue, 2) OVER (ORDER BY month) AS revenue_2_months_ago
FROM sales
ORDER BY month;

The Default Value Argument

A third argument supplies a replacement when there is no neighbor row, instead of getting NULL. The signature is LAG(col, offset, default).

This is handy when a downstream calculation cannot tolerate NULL, for example treating the missing previous value as 0 so a difference still computes.

SELECT
  month,
  revenue,
  LAG(revenue, 1, 0) OVER (ORDER BY month) AS prev_revenue
FROM sales
ORDER BY month;

PARTITION BY Resets the Window

Real data rarely has one global series. You usually compare within each customer, product, or region. PARTITION BY restarts the LAG/LEAD calculation at the start of every partition.

That means the first row of each partition gets NULL from LAG, never leaking a value across the boundary into a different customer's data.

SELECT
  customer_id,
  order_date,
  amount,
  LAG(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS prev_amount
FROM orders;

Worked Example: Days Between Orders

A frequent task is measuring the gap between a customer's consecutive orders. Pull the previous order date with LAG, then subtract.

Each customer's first order yields NULL because there is no prior date to subtract. This is exactly the kind of per-customer comparison interviewers expect window functions to solve.

SELECT
  customer_id,
  order_date,
  order_date - LAG(order_date) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS days_since_prev
FROM orders;

Why Not a Self-Join?

The pre-window-function answer was a correlated self-join: join the table to itself on "the row whose date is the largest below this one." It works but is verbose, error-prone with ties, and often slower.

LAG/LEAD express intent in one line.
They are evaluated in a single ordered pass.
Ties are resolved deterministically by your ORDER BY.

Saying "I would use LAG instead of a self-join" signals fluency.

Common Pitfall: Missing ORDER BY

Without an ORDER BY in the OVER clause, "previous row" is undefined. Some engines reject it, others return unpredictable results. Always order the window.

Also remember the ordering inside OVER is independent of the query's outer ORDER BY. The window decides which row is the neighbor; the outer clause only decides display order.

Quick Check

Test your understanding of offset window functions.

Recap

You now know the offset window functions:

LAG(col) reads the previous row, LEAD(col) reads the next, defined by the window's ORDER BY.
Optional arguments: LAG(col, offset, default).
PARTITION BY resets the navigation per group, so boundary rows are NULL.
They replace clumsy self-joins for comparing adjacent rows.

Next we apply this to the guaranteed analyst question: period-over-period change.

Frequently asked questions

Is the “LAG and LEAD for Adjacent Rows” lesson free?

Yes — the full text of “LAG and LEAD for Adjacent Rows” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “LAG and LEAD for Adjacent Rows”?

Accessing previous and next row values without a self-join. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “LAG and LEAD for Adjacent Rows” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Interview Prep