LAG & LEAD: Looking into the Past and Future

It's **Day 64**, and we're becoming time travelers. `LAG` and `LEAD` allow you to access data from the previous or next row without doing a self-join.

Comparing to Yesterday: LAG

How do you calculate if revenue went up or down compared to yesterday?

SELECT

sale_date,

revenue,

LAG(revenue) OVER(ORDER BY sale_date) as prev_day_revenue

FROM daily_sales;

Looking Ahead: LEAD

`LEAD` is the same, but it pulls from the **next** row. This is useful for things like "How long did it take for this user to buy their *next* item?"

SELECT

user_id,

order_date,

LEAD(order_date) OVER(PARTITION BY user_id ORDER BY order_date) as next_order_date

FROM orders;

Real-World Analytics: Churn

If a user hasn't logged in for 30 days since their *last* login, are they churned? You use `LAG` to compare the current login date to the previous one to find these gaps.

Your Task for Today

Calculate the day-over-day change in revenue for a hypothetical sales table using `LAG`.

*Day 65: Calculating Running Totals with SQL.*