LAG and LEAD
Looking at the previous row.
Helen always asks the same question at the end of the month. Did we do better than last month? To answer that, each row needs to see the row before it. Lag does exactly that.

- Better than last month?
- Each row sees the one before
- Meet LAG
LAG
Lag of orders gives the orders value from the previous row, in the order you choose. Subtract it from this month, and you get the change. Run it. January has no previous month here, so it's empty. May jumped by thirty orders. June dropped by fifteen.
WITH monthly AS (
SELECT strftime('%Y-%m', order_date) AS month, COUNT(*) AS orders
FROM orders
WHERE order_date >= '2025-01-01'
GROUP BY month
)
SELECT month,
orders,
LAG(orders) OVER (ORDER BY month) AS last_month,
orders - LAG(orders) OVER (ORDER BY month) AS change
FROM monthly
LIMIT 6;โโโโโโโโโโโฌโโโโโโโโโฌโโโโโโโโโโโโโฌโโโโโโโโโ โ month โ orders โ last_month โ change โ โโโโโโโโโโโผโโโโโโโโโผโโโโโโโโโโโโโผโโโโโโโโโค โ 2025-01 โ 69 โ โ โ โ 2025-02 โ 75 โ 69 โ 6 โ โ 2025-03 โ 61 โ 75 โ -14 โ โ 2025-04 โ 73 โ 61 โ 12 โ โ 2025-05 โ 103 โ 73 โ 30 โ โ 2025-06 โ 88 โ 103 โ -15 โ โโโโโโโโโโโดโโโโโโโโโดโโโโโโโโโโโโโดโโโโโโโโโ
LEAD, the opposite
Lead is the mirror image of lag. It looks at the next row instead of the previous one. Both can also take a number. Lag of orders, two, looks two rows back. These are perfect for comparing months, years, or one visit to the next.
Summary
Lag looks back, and lead looks forward. Subtract, and you get the change. Next, smoothing out bumpy numbers.
Key takeaways- LAG() looks back
- LEAD() looks forward
- this - LAG(this) = change