Topic 37 of 52
LEAD() & LAG()
Overview
Relational Databases hate looking at other rows. A standard SELECT query evaluates Row 2 in complete isolation; it has no memory of Row 1. The LAG() and LEAD() Window Functions break this rule. They allow the current row to reach 'backwards' (LAG) or 'forwards' (LEAD) into adjacent rows and pull their data into the current row. This is the absolute best way to calculate Year-Over-Year growth or Time-Between-Events.
Syntax
sql
-- Syntax: LAG(column, offset, default_value) OVER (...)
SELECT
year,
revenue,
-- Reach back 1 row and pull the previous year's revenue!
LAG(revenue, 1) OVER (ORDER BY year ASC) AS prev_year_revenue,
-- Reach forward 1 row and pull next year's revenue!
LEAD(revenue, 1) OVER (ORDER BY year ASC) AS next_year_revenue
FROM annual_sales;Common Pitfalls
- Not handling the
NULLon the very first row. If youLAG(revenue, 1)on the 2010 row, there is no 2009 row to pull from, so it returnsNULL. Any math (likerevenue - LAG(revenue)) will instantly becomeNULL. Always use the third argument ofLAG()to provide a safe default:LAG(revenue, 1, 0). - Missing the
ORDER BY.LAG()andLEAD()are completely meaningless if the dataset isn't strictly sorted chronologically or sequentially. You must provide anORDER BYin theOVERclause.
Interview Questions
Q:
How would you calculate the exact number of days between a user's current purchase and their PREVIOUS purchase?
A:
Use LAG(purchase_date) OVER(PARTITION BY user_id ORDER BY purchase_date). This grabs their previous date. Then, simply subtract that LAG value from the current purchase_date.
Real-World Example
Calculating Month-Over-Month (MoM) revenue growth percentages.
example
sql
SELECT
month,
revenue,
LAG(revenue, 1) OVER (ORDER BY month) AS prev_revenue,
-- Calculate the growth percentage!
((revenue - LAG(revenue, 1) OVER (ORDER BY month))
/ LAG(revenue, 1) OVER (ORDER BY month)) * 100 AS growth_pct
FROM monthly_sales;Check Your Knowledge
Test your understanding of LEAD() & LAG() with these quick questions.