LAG()

Syntax

How it works

LAG() is a window function that looks backwards: for each row it returns the value of an expression from an earlier row in the same ordered window. Its mirror image is LEAD(), which looks forwards. Because it is a window function, it keeps every row instead of collapsing them the way an aggregate would.

The full form is LAG(expression, offset, default) OVER (PARTITION BY ... ORDER BY ...). The ORDER BY inside OVER() defines what "previous" means, so it is required. The optional PARTITION BY restarts the comparison for each group, for example per customer or per product, so LAG never reaches across a partition boundary. The offset defaults to 1 (the row immediately before) and default fills in the first row of each partition, which would otherwise be NULL.

The headline use is period over period change: subtract the previous value from the current one, as in total - LAG(total) OVER (ORDER BY month), to get month over month movement. The same pattern gives you percent change, the gap between consecutive events, or a comparison with the same day last week. LAG was standardised in SQL:2003 and is available in MySQL 8.0+, PostgreSQL, SQL Server 2012+ and SQLite 3.25+. See the window functions guide for the full family.

Examples

Compare each day with the previous day's value

SELECT day, revenue,
       LAG(revenue) OVER (ORDER BY day) AS prev_day
FROM daily_sales
ORDER BY day;

day        | revenue | prev_day
-----------+---------+---------
2026-01-01 |     100 |    NULL
2026-01-02 |     140 |     100
2026-01-03 |     130 |     140

Month over month difference (total minus previous)

SELECT month, total,
       total - LAG(total, 1, 0) OVER (ORDER BY month) AS mom_change
FROM monthly_totals
ORDER BY month;

month   | total | mom_change
--------+-------+-----------
2026-01 |  5000 |       5000
2026-02 |  6200 |       1200
2026-03 |  5900 |       -300

Percent change from the previous period

SELECT month, total,
       ROUND(
         100.0 * (total - LAG(total) OVER (ORDER BY month))
         / LAG(total) OVER (ORDER BY month), 1) AS pct_change
FROM monthly_totals
ORDER BY month;

month   | total | pct_change
--------+-------+-----------
2026-01 |  5000 |      NULL
2026-02 |  6200 |      24.0
2026-03 |  5900 |      -4.8

Gap in days between consecutive events per user

SELECT user_id, event_at,
       event_at - LAG(event_at)
         OVER (PARTITION BY user_id ORDER BY event_at) AS gap
FROM events
ORDER BY user_id, event_at;

user_id | event_at   | gap
--------+------------+-----
      1 | 2026-01-01 | NULL
      1 | 2026-01-04 |    3
      1 | 2026-01-05 |    1
      2 | 2026-02-10 | NULL

Look two rows back with a custom offset

SELECT day, revenue,
       LAG(revenue, 2, 0) OVER (ORDER BY day) AS two_days_ago
FROM daily_sales
ORDER BY day;

day        | revenue | two_days_ago
-----------+---------+-------------
2026-01-01 |     100 |            0
2026-01-02 |     140 |            0
2026-01-03 |     130 |          100

Common mistakes

-- No ORDER BY, so "previous" is undefined
SELECT day, revenue,
       LAG(revenue) OVER () AS prev_day
FROM daily_sales;

-- ORDER BY inside OVER() defines row order
SELECT day, revenue,
       LAG(revenue) OVER (ORDER BY day) AS prev_day
FROM daily_sales;

-- Window functions are not allowed in WHERE
SELECT month, total
FROM monthly_totals
WHERE total - LAG(total) OVER (ORDER BY month) > 0;

-- Compute LAG in a CTE, then filter the result
WITH m AS (
  SELECT month, total,
         total - LAG(total) OVER (ORDER BY month) AS change
  FROM monthly_totals
)
SELECT month, total
FROM m
WHERE change > 0;

-- First row is NULL, so the math yields NULL
SELECT month,
       total - LAG(total) OVER (ORDER BY month) AS mom_change
FROM monthly_totals;

-- Supply a default so the first row is handled
SELECT month,
       total - LAG(total, 1, 0) OVER (ORDER BY month) AS mom_change
FROM monthly_totals;

Performance

LAG() is evaluated in a single ordered pass over each partition, so its main cost is the sort implied by PARTITION BY and ORDER BY. A index that already matches that ordering lets the engine skip the sort and stream the rows in order, which is the biggest win you can get.

If you reference LAG() several times with the same OVER() clause, most optimisers compute the window once and reuse it, so repeating LAG(total) OVER (ORDER BY month) for both a difference and a percent change is usually fine. Keep the window definition identical so it can be shared, or move it into a CTE to make the intent clear.

Because the whole partition must be ordered before LAG can produce output, very large partitions can spill to disk. Narrow the work with a WHERE that runs before the window, and select only the columns you need so the sort buffers stay small.

Interview questions

SELECT month,
       total - LAG(total, 1, 0) OVER (ORDER BY month) AS mom_change
FROM monthly_totals
ORDER BY month;