LAG() and LEAD()

LAG() and LEAD() are window functions - meaning they look across neighboring rows without collapsing the results - that pull a value from the row before or after the current one into the same row.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Comparing Retail Orders with LAG() and LEAD()

SELECT order_id, customer_id, order_date, revenue,
       LAG(revenue, 1, 0) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order_revenue,
       LEAD(revenue, 1, 0) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_order_revenue
FROM orders
ORDER BY customer_id, order_date

Result:

order_id	customer_id	order_date	revenue	prev_order_revenue	next_order_revenue
O2001	C101	2024-01-05	120.0	0	95.5
O2002	C101	2024-02-14	95.5	120.0	210.75
O2003	C101	2024-03-22	210.75	95.5	180.0
O2004	C101	2024-04-30	180.0	210.75	340.25
O2005	C101	2024-05-18	340.25	180.0	0

Step by step

AS gives a column a friendlier name in the result.

SELECT order_id, customer_id, order_date, revenue,
       LAG(revenue, 1, 0) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order_revenue,
       LEAD(revenue, 1, 0) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_order_revenue

FROM tells SQL which table to pull the data from.

SELECT order_id, customer_id, order_date, revenue,
       LAG(revenue, 1, 0) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order_revenue,
       LEAD(revenue, 1, 0) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_order_revenue
FROM orders

ORDER BY puts the rows in your chosen order.

SELECT order_id, customer_id, order_date, revenue,
       LAG(revenue, 1, 0) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order_revenue,
       LEAD(revenue, 1, 0) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_order_revenue
FROM orders
ORDER BY customer_id, order_date;

Practice

Try each one in your head first, then open the answer.

1. Using orders, write a query that adds the previous row's revenue value to each row, ordered by order_date.

SELECT *, LAG(revenue) OVER (ORDER BY order_date) FROM orders

2. Using store_sales, write a query that adds the next row's revenue value to each row, ordered by sale_date.

SELECT *, LEAD(revenue) OVER (ORDER BY sale_date) FROM store_sales

3. Using products, write a query that adds the previous row's price value to each row, ordered by price.

SELECT *, LAG(price) OVER (ORDER BY price) FROM products

4. Using employees, write a query that adds the next row's salary value to each row, ordered by hire_date.

SELECT *, LEAD(salary) OVER (ORDER BY hire_date) FROM employees

5. Using reviews, write a query that adds the previous row's rating value to each row, ordered by review_date.

SELECT *, LAG(rating) OVER (ORDER BY review_date) FROM reviews

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed