LAG() and LEAD()

Have you ever looked at a financial report that shows “Month-over-Month Growth” or “Previous Day Sales”? To calculate these metrics, you need to compare data from the current row with data from a previous row.

Historically, this required complex Self Joins. With Window Functions, it’s trivial using LAG() and LEAD().

Looking Backwards with LAG()

LAG() allows you to access data from a previous row in the same result set without using a join.

You pass the column you want to look at into LAG(). You must also provide an ORDER BY clause inside the OVER() window so the database knows what “previous” actually means.

SELECT 
  date, 
  total_revenue, 
  LAG(total_revenue) OVER(ORDER BY date ASC) AS prev_revenue
FROM daily_sales;

For the very first date in the table, prev_revenue will be NULL because there is no previous day. But for day 2, prev_revenue will contain the total revenue from day 1!

Once you have both numbers on the same row, you can easily do math: total_revenue - LAG(total_revenue) OVER(...) AS daily_difference.

Looking Forwards with LEAD()

LEAD() does the exact opposite. It looks ahead to the next row.

SELECT 
  date, 
  total_revenue, 
  LEAD(total_revenue) OVER(ORDER BY date ASC) AS next_revenue
FROM daily_sales;

Specifying the Offset

By default, LAG() and LEAD() look 1 row back or forward. You can change this by passing a second argument (the offset).

LAG(total_revenue, 7) would look exactly 7 rows back. If your data is daily, this would effectively compare today to the same day last week!

[!NOTE] LAG and LEAD are the secret weapons of Data Analysts. Mastering them allows you to perform complex time-series analysis directly in the database without needing Python or Excel.

Try using LAG() in the interactive lab to set up a day-over-day sales comparison!