LAG and LEAD
- SQL course
- Advanced
- Lesson 16/81
- 4 min
Reach the previous and next row.
LAG and LEAD
LAG gets a value from the previous row, LEAD from the next. Useful in time series.
SELECT date, price,
LAG(price) OVER (ORDER BY date) AS prev,
LEAD(price) OVER (ORDER BY date) AS next
FROM stocks;This query gives the previous and next price.
date, price, LAG(price) OVER (ORDER BY date) AS prev, … is selected from stocks. LAG() is a window function: it computes a value for each row over the window defined in OVER (…), without collapsing rows.
LAG and LEAD — details
LAG and LEAD are used to compute changes in time series. You take the difference with the previous row.
LAG(price) returns yesterday's price and LEAD(price) tomorrow's; price - LAG(price) OVER (ORDER BY date) computes the daily change. The first row has no previous value, so the result is NULL.
Practice LAG and LEAD
Find the previous price with LAG(price) OVER (ORDER BY date).
Starter code
-- previous value
SELECT date, price,
LAG(price)Sample solution
SELECT date, price,
LAG(price) OVER (ORDER BY date) AS prev,
LEAD(price) OVER (ORDER BY date) AS next
FROM stocks;Expected output
date price prev 2026-01-01 100 NULL 2026-01-02 105 100
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 What does LAG do?
- Gets the next row
- Deletes them
- Counts
- Gets the previous row Correct answer
Explanation: LAG gets a value from the previous row.
2 Add LAG
SELECT date, price,
____(price) OVER (ORDER BY date) AS prev
FROM stocks;SELECT date, price,
LAG(price) OVER (ORDER BY date) AS prev
FROM stocks;Explanation: LAG returns the value from the previous row, so you can compare today's price with yesterday's.
3 LAG and LEAD: match the terms
| Term | Meaning |
|---|---|
LAG |
previous |
LEAD |
next |
time series |
date |
difference |
change |