LAG and LEAD are SQL analytic functions that allow you to access data from a previous or subsequent row in a query result set. LAG provides access to a previous row, while LEAD provides access to the following row.
For example, imagine you have a table of sales data with columns for date, product, and revenue. (to be used in subsequent examples)
| date | product | revenue |
|---|---|---|
| 2022-01-01 | A | 100 |
| 2022-01-01 | B | 200 |
| 2022-01-02 | A | 150 |
| 2022-01-02 | B | 300 |
| 2022-01-03 | A | 200 |
| 2022-01-03 | B | 250 |
Using the LAG and LEAD functions, we can retrieve the revenue from the previous or next day for each row in the table.
LAG
LAG retrieves information from the preceding row within the same query output without requiring a self-join.
SYNTAX
LAG (scalar_expression [,offset] [,default])
OVER ( [ partition_by_clause ] order_by_clause )For example, to retrieve the revenue from the previous day, we can use the following SQL query:
SELECT date, product, revenue, LAG(revenue) OVER (PARTITION BY product ORDER BY date) AS prev_day_revenue
FROM sales_dataThis will give us the following result:
| date | product | revenue | prev_day_revenue |
|---|---|---|---|
| 2022-01-01 | A | 100 | NULL |
| 2022-01-02 | A | 150 | 100 |
| 2022-01-03 | A | 200 | 150 |
| 2022-01-01 | B | 200 | NULL |
| 2022-01-02 | B | 300 | 200 |
| 2022-01-03 | B | 250 | 300 |
LEAD
LEAD retrieves information from the subsequent row within the same query output without requiring a self-join.
For example, to retrieve the revenue from the next day, we can use the LEAD function as follows:
SELECT date, product, revenue, LEAD(revenue) OVER (PARTITION BY product ORDER BY date) AS next_day_revenue
FROM sales_dataThis will give us the following result:
| date | product | revenue | next_day_revenue |
|---|---|---|---|
| 2022-01-01 | A | 100 | 150 |
| 2022-01-02 | A | 150 | 200 |
| 2022-01-03 | A | 200 | NULL |
| 2022-01-01 | B | 200 | 300 |
| 2022-01-02 | B | 300 | 250 |
| 2022-01-03 | B | 250 | NULL |
Note:
The first and last rows have NULL values for the previous and next day revenue, respectively, since there is no data for the days before or after them.