How do LEAD and LAG work?
They let you reach into the rows on either side of the current one and pick a value out of them.
LAG looks backwards and LEAD looks forwards. Both of them take the same three arguments. The first is the value you want. The second is how many rows away to look, and it is one unless you say otherwise. The third is what to give back when there is no row there, and it is nothing unless you say otherwise.
CREATE TABLE monthly_sales (
month TEXT PRIMARY KEY,
revenue DECIMAL(12, 2) NOT NULL
);
INSERT INTO monthly_sales (month, revenue) VALUES
('2026-01', 12000.00),
('2026-02', 14500.00),
('2026-03', 9800.00);
SELECT
month,
revenue,
-- The month before this one.
LAG(revenue) OVER (ORDER BY month) AS previous_revenue,
-- The month after this one.
LEAD(revenue) OVER (ORDER BY month) AS next_revenue
FROM monthly_sales
ORDER BY month;
month revenue previous_revenue next_revenue
2026-01 12000.00 (missing) 14500.00
2026-02 14500.00 12000.00 9800.00
2026-03 9800.00 14500.00 (missing)
So what are the details that catch people out? Three.
The first one is that the order inside the window is the order that LAG and LEAD follow, and it is not necessarily the order that the rows come out in. The order by at the end of the query sorts the answer, and the order inside the window decides which row is before which. They are two different things, and you often want both.
The second one is that the first row has no row before it, so LAG gives back nothing for it, and the last row has no row after it, so LEAD gives back nothing for it.
The third one is that you can give a value to use instead of nothing, which makes the answer neater when you do arithmetic with it.
-- Treat the first month as having no revenue before it.
LAG(revenue, 1, 0) OVER (ORDER BY month) AS previous_revenue
The next question in this topic puts them to work.