Skip to content
Tutorials Center
My learning

Topics

Lesson 52 of 125 ยท Window Functions

LAG, LEAD and Comparing Rows

Use LAG and LEAD to compare each row with the previous or next one, compute month-over-month change and gaps between events, and use FIRST_VALUE and LAST_VALUE.

Many questions are about change: How much did revenue grow compared with last month? How many days passed between a customer's orders? When did a price change? Each of these compares a row with a neighboring row, which plain SQL can only do with awkward self joins.

The offset window functions make it direct. LAG reads a value from a previous row, LEAD from a following row, and FIRST_VALUE, LAST_VALUE and NTH_VALUE read from fixed positions in the window. Combined with PARTITION BY and ORDER BY, they handle period-over-period reports, time between events and change detection in a few lines.

LAG: looking at the previous row

LAG(expression) returns the value of expression from the row just before the current one, in the window's ORDER BY order. The first row has no previous row, so it gets NULL.

Here is monthly revenue next to the previous month's revenue, with the change in money and in percent:

SQL Month-over-month change ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

January has NULL in the last three columns because there's no December 2024 in the data. April shows the steepest fall (-95.3%), and May's rebound reads as +1450.2%, a reminder that percentages measured from a tiny base look dramatic. This is the standard way to build "vs. previous period" columns for dashboards.

Guard against division by zero. If the previous value can be 0, wrap it: / NULLIF(LAG(revenue) OVER (...), 0). Dividing by NULL gives NULL instead of an error or a misleading number. See COALESCE, IFNULL and NULLIF.

Offset and default value

LAG and LEAD take up to three arguments:

SQL
LAG(expression, offset, default) OVER (...)
  • offset is how many rows to look back (default 1). LAG(revenue, 3) reads the value three rows earlier, handy for quarter-over-quarter in monthly data or "same month last year" with LAG(x, 12).
  • default replaces the NULL returned when that row doesn't exist.
SQL Offsets and defaults ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Notice that the default (0) only fills missing rows. If a row exists but its value is NULL, LAG returns that NULL; the default doesn't replace it.

LEAD: looking at the next row

LEAD mirrors LAG: it reads from the following row, and the last row gets NULL (or the default). It's useful whenever you think about "what happened next" or "how long until the next event".

This query lists hires in the Support department with the date of the next hire and the gap in days:

SQL Days until the next hire ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

julianday() turns a date into a day number, so subtracting two of them gives the number of days between the dates. (See Date and Time Functions.) Support's longest quiet period is between Mateo Dubois in October 2019 and Pablo Kaya in June 2025: 2,070 days.

LAG(x) OVER (ORDER BY d) and LEAD(x) OVER (ORDER BY d DESC) return the same values. Pick whichever reads more naturally for the question.

Gaps between events per group

With PARTITION BY, the "previous row" is the previous row in the same group. That makes it easy to measure time between orders for each customer:

SQL Days between a customer's orders ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Each customer's first order has no previous order, so days_since_previous is NULL. Wrap this query in a CTE and AVG(days_since_previous) per customer gives their average re-order interval, a common metric for subscription and retail businesses.

Detecting changes

A neat trick: compare each row with the previous one and keep only the rows where something changed. This turns a noisy log into a clean history. The example builds a small price log in which the price is recorded every day, even when it stays the same:

SQL Keep only rows where the price changed

Press Run (or Ctrl + Enter) to see the result here.

Five rows remain: each product's starting price, plus every real change (the kettle's sale on June 3, the kettle's return to 39 on June 5 and the toaster's drop on June 4). The previous_price IS NULL test keeps the first row of each product, because price <> NULL is never true. This pattern is also the first step of the "gaps and islands" technique covered in Percentiles, Medians and Statistics.

FIRST_VALUE, LAST_VALUE and NTH_VALUE

These functions return the value from a fixed position in the window frame: the first row, the last row, or the n-th row.

SQL Compare with the top earner ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

FIRST_VALUE works as expected: the top earner of each department appears on every row. But look at second_salary in the first row of each department: it's NULL. That's a hint about the frame, and it matters even more for LAST_VALUE.

The LAST_VALUE trap

With ORDER BY in the window and no explicit frame, the frame ends at the current row. So "the last row of the frame" is the current row itself, and LAST_VALUE just echoes the current value. Likewise NTH_VALUE(x, 2) is NULL on the first row, because the frame has only one row there.

To look at the whole partition, extend the frame to the end:

SQL LAST_VALUE with a full frame ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

last_value_default just repeats each row's own salary, while lowest_in_dept correctly shows 62,000 on every Sales row. A simpler alternative for "lowest in the group" is MIN(salary) OVER (PARTITION BY ...) or FIRST_VALUE with the opposite sort order, which don't depend on the frame end. Window Frames in Depth explains frames properly.

Function Uses the frame? Typical use
LAG(x, n, d) / LEAD(x, n, d) No, counts rows in the partition Previous/next values, change, gaps
FIRST_VALUE(x) Yes Baseline, best value, first event
LAST_VALUE(x) Yes (set the frame end!) Latest value, final state
NTH_VALUE(x, n) Yes Second best, runner-up

When LAG is the wrong tool

LAG counts rows, not time. That's exactly right when every period has a row, but it quietly gives wrong answers when periods are missing. If a product had no sales in March, LAG(revenue) on the April row returns February's revenue, and your "month-over-month" column compares April with February without saying so.

You have two safe options:

  • Fill the gaps first. Generate every month with a recursive CTE, LEFT JOIN the data to it and use COALESCE(revenue, 0), so every month has a row and LAG lines up again.
  • Join on the period instead. A self join on "the month before" never mixes up periods, because it matches by value rather than position:
SQL
-- Compare each month with the calendar month before it, missing or not
SELECT
  cur.month,
  cur.revenue,
  prev.revenue AS previous_month
FROM monthly AS cur
LEFT JOIN monthly AS prev
  ON prev.month = strftime('%Y-%m', date(cur.month || '-01', '-1 month'));

The join version is also how row comparisons were written before window functions existed. It's slower on large tables, since the database has to match rows instead of reading them in order, so prefer LAG on gap-free data. A RANGE frame over a numeric date (see Window Frames in Depth) is a third option for time-based windows.

Dialect notes

LAG, LEAD, FIRST_VALUE, LAST_VALUE and NTH_VALUE are standard and work the same in SQLite, PostgreSQL, MySQL 8, SQL Server 2012+ and Oracle, with two exceptions to know:

  • SQL Server has no NTH_VALUE. Use ROW_NUMBER() in a CTE and pick row n.
  • IGNORE NULLS (skip NULL values when looking back, e.g. "last known price") is standard but not universal. Oracle, SQL Server 2022, Snowflake and BigQuery accept it; SQLite and MySQL don't.
SQL
-- Oracle, SQL Server 2022+, Snowflake: carry the last non-NULL reading forward
SELECT
  reading_time,
  temperature,
  LAST_VALUE(temperature) IGNORE NULLS OVER (ORDER BY reading_time) AS last_known
FROM sensor_readings;

In SQLite, you can emulate "last known value" by counting non-NULL values with a running COUNT(temperature) (which ignores NULLs) to form groups, then taking MAX(temperature) per group.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does LAG(price) OVER (ORDER BY day) return on the first row?

    There's no previous row, so LAG returns NULL unless you give a default as the third argument.
  2. 2. Monthly data has one row per month. Which expression gives the value from the same month last year?

    The second argument is the offset, so LAG(x, 12) looks back twelve rows. That's only "a year ago" if no months are missing.
  3. 3. Why does LAST_VALUE(salary) OVER (ORDER BY salary DESC) just return each row's own salary?

    Extend the frame with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to see the whole partition.
  4. 4. In the price-change query, why is previous_price IS NULL in the WHERE clause?

    The first row of each partition has no previous price. price <> NULL is unknown, so without the extra test the starting price would be dropped.

Summary

  • LAG(x) reads the previous row's value and LEAD(x) the next row's, in the window's ORDER BY order.
  • The optional arguments are an offset (LAG(x, 12)) and a default for missing rows (LAG(x, 1, 0)).
  • With PARTITION BY, neighbors are found only within the same group: perfect for time between a customer's orders.
  • Compare with LAG and filter to keep only rows where a value changed.
  • FIRST_VALUE, LAST_VALUE and NTH_VALUE read from the window frame; give LAST_VALUE a frame that ends at UNBOUNDED FOLLOWING.
  • IGNORE NULLS and NTH_VALUE aren't available everywhere; check your database.

Next: Window Frames in Depth, where you'll control exactly which rows each window includes.