How Do LEAD and LAG Compare Rows?

LAG looks backwards and LEAD looks forwards. Both take an expression, an offset and an ordering, and they return the value of that expression from a neighbouring row of the same partition.

CREATE TABLE monthly_sales (
  month TEXT PRIMARY KEY,
  revenue DECIMAL(12, 2) NOT NULL
);

INSERT INTO monthly_sales (month, revenue) VALUES
  ('2026-01', 30000.00),
  ('2026-02', 64000.00),
  ('2026-03', 46500.00);

SELECT month,
       revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_month,
       LEAD(revenue) OVER (ORDER BY month) AS next_month
FROM monthly_sales
ORDER BY month;

Three rows come back. January has a NULL previous month and 64000.00 as the next; February has 30000.00 behind it and 46500.00 ahead; March has 64000.00 behind it and NULL ahead.

The NULLs are not errors. The first row of a partition has no previous row and the last has no next one, so the function has nothing to return and produces NULL. The default offset is 1, so LAG(x) is the previous row and LAG(x, 2) is the one two rows back. A third argument gives the value to use when there is no such row, so LAG(revenue, 1, 0) returns 0 instead of NULL for the first month.

Two things to be careful about. The ORDER BY inside the window is what defines "previous" and "next", and it has to be a total order — if two rows share the same month, which one is the previous row is not determined, so add a tiebreaker. And the expression you are lagging is evaluated in the frame of the current row, which is why you can lag a computed value such as LAG(revenue - LAG(revenue)) OVER (...) for a chained difference — though that is clearer as two separate window expressions.

LAG gives you the previous value, and subtracting it gives the change. Both are window expressions over the same ordering.

CREATE TABLE monthly_sales (
  month TEXT PRIMARY KEY,
  revenue DECIMAL(12, 2) NOT NULL
);

INSERT INTO monthly_sales (month, revenue) VALUES
  ('2026-01', 30000.00),
  ('2026-02', 64000.00),
  ('2026-03', 46500.00);

SELECT month,
       revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
       ROUND(revenue - LAG(revenue) OVER (ORDER BY month), 2) AS change
FROM monthly_sales
ORDER BY month;

Three rows come back. January has no previous month and a NULL change. February shows 30000.00 as the previous revenue and a change of 34000.00. March shows 64000.00 and a change of -17500.00.

Note that the same window expression appears twice. That is not an accident and not an error — you cannot refer to one window function's alias from another expression in the same SELECT list, because the aliases do not exist yet at that point in the evaluation. So the LAG has to be written out again for the subtraction.

When you need the previous value in several places — previous revenue, previous change, and a percentage change — compute it once in a subquery and reference the alias. That is both clearer and faster, because the engine evaluates the window once.

Two gaps worth knowing about. The first row always has a NULL previous value, so a percentage change computed from it is NULL rather than zero. And if a month is missing from the data entirely, LAG returns the last month that exists rather than the month before the one you wanted — January to March gives March a comparison against January. If that matters, generate a complete calendar and left join the revenue onto it, so gaps become explicit NULL rows.