SQL LAG and LEAD
Get the value from a previous or following row.
Syntax
LAG(col, n, default) OVER (ORDER BY …) LEAD(col, n, default) OVER (ORDER BY …)
Use them to compute changes over time, gaps between events, or "next" values.
Example
Query (on the shop database used in the SQL course)
WITH m AS ( SELECT strftime('%Y-%m', order_date) AS month, COUNT(*) AS orders FROM orders GROUP BY month ) SELECT month, orders, orders - LAG(orders) OVER (ORDER BY month) AS change FROM m;
Result
| month | orders | change |
|---|---|---|
| 2025-01 | 2 | NULL |
| 2025-02 | 3 | 1 |
| 2025-03 | 8 | 5 |
| 2025-04 | 5 | -3 |
| 2025-05 | 5 | 0 |
| 2025-06 | 2 | -3 |
| 2025-07 | 5 | 3 |
| 2025-08 | 6 | 1 |
| 2025-09 | 2 | -4 |
| 2025-10 | 6 | 4 |
| 2025-11 | 3 | -3 |
| 2025-12 | 4 | 1 |
12 rows
Good to know. The first row has no previous row: LAG gives NULL (or the default).
Related
Learn it step by step in the free lesson CTEs & Window Functions, with exercises that check your queries.