Home / SQL / Reference / Window functions

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

monthorderschange
2025-012NULL
2025-0231
2025-0385
2025-045-3
2025-0550
2025-062-3
2025-0753
2025-0861
2025-092-4
2025-1064
2025-113-3
2025-1241

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.