LAG and LEAD

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.

Exercise LAG and LEAD

Find the previous price with LAG(price) OVER (ORDER BY date).

Show example solution

SELECT date, price,
  LAG(price) OVER (ORDER BY date) AS prev,
  LEAD(price) OVER (ORDER BY date) AS next
FROM stocks;

Test yourself

What does LAG do?
- Gets the next row
- Deletes them
- Counts
- Gets the previous row
Show answer

Gets the previous row — LAG gets a value from the previous row.

Add LAG

SELECT date, price,
  ____(price) OVER (ORDER BY date) AS prev
FROM stocks;

Show answer

SELECT date, price,
  LAG(price) OVER (ORDER BY date) AS prev
FROM stocks;

LAG and LEAD: match the terms

LAG

previous
LEAD

next
time series

date
difference

change

Next lesson: PARTITION BY

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies