AI Hints
Homeโ€บSQL Tutorialโ€บRunning Totals and Trends
SQL ยท Lesson 67 of 126

LAG and LEAD

Looking at the previous row.

โฑ 2 min readโ–ถ Video at 1:35:14๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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.

LAG and LEAD
From the video: LAG and LEAD
  • 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.

query.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚  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.

LAG(col)the previous row
LEAD(col)the next row
LAG(col, 2)two rows back
Use formonth over month

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
Next up: Moving Averages and NTILE โ€” smoothing and splitting