LAG and LEAD are the two window functions that compare a row with its neighbours: LAG looks backwards and LEAD looks forwards. They bring the neighbouring row's value into the current row, which turns a table of independent events into a series — and that is what you need for change, gaps, trends and period comparisons. Neither of them aggregates, and both depend entirely on the window's ORDER BY deciding which row is "next".
How do LAG and LEAD compare rows to previous or next?
Ada Whitfield's six sales, each with the sale before it and the sale after it:
SELECT r.rep_name, s.sale_date,
ROUND(s.quantity * s.unit_price * (1 - s.discount), 2) AS revenue,
LAG(ROUND(s.quantity * s.unit_price * (1 - s.discount), 2)) OVER w AS previous_sale,
LEAD(ROUND(s.quantity * s.unit_price * (1 - s.discount), 2)) OVER w AS next_sale
FROM sales s JOIN reps r ON r.rep_id = s.rep_id
WHERE r.rep_id = 1
WINDOW w AS (PARTITION BY r.rep_id ORDER BY s.sale_date)
ORDER BY s.sale_date;
rep_name sale_date revenue previous_sale next_sale
------------- ---------- ------- ------------- ---------
Ada Whitfield 2025-03-03 320.0 NULL 1008.9
Ada Whitfield 2025-03-05 1008.9 320.0 285.0
Ada Whitfield 2025-03-24 285.0 1008.9 240.0
Ada Whitfield 2025-04-14 240.0 285.0 585.0
Ada Whitfield 2025-05-02 585.0 240.0 480.0
Ada Whitfield 2025-05-19 480.0 585.0 NULL
The first row has no previous sale and the last has no next one, and both come back as NULL rather than as a zero or an error. That is the honest answer — there is no previous sale — and it is why LAG and LEAD are so often wrapped in COALESCE when a report needs a number in every cell.
What does the window decide?
LAG and LEAD take an offset and an ordering, and the ordering is what makes them meaningful. The window above partitions by rep, so "previous" means the previous sale by that rep, not the previous sale in the table. Remove the partition and the same function would compare Ada's first sale with Bruno's last, which is meaningless for a per-rep trend.
The offset is the second argument and defaults to one:
SELECT s.sale_date,
LAG(s.sale_date) OVER (ORDER BY s.sale_date) AS previous_date
FROM sales s WHERE s.rep_id = 1
ORDER BY s.sale_date;
sale_date previous_date
---------- -------------
2025-03-03 NULL
2025-03-05 2025-03-03
2025-03-24 2025-03-05
2025-04-14 2025-03-24
2025-05-02 2025-04-14
2025-05-19 2025-05-02
LAG(s.sale_date) is the previous row's date, and LAG(s.sale_date, 2) would be the one before that. The first row's cell is NULL because there is no earlier row, not because the date is missing.
How do you calculate the change between two rows?
Subtract LAG from the current value, and the window does the comparison for you:
SELECT r.rep_name, s.sale_date,
ROUND(s.quantity * s.unit_price * (1 - s.discount), 2) AS revenue,
LAG(ROUND(s.quantity * s.unit_price * (1 - s.discount), 2)) OVER w AS previous_sale,
ROUND(s.quantity * s.unit_price * (1 - s.discount)
- LAG(s.quantity * s.unit_price * (1 - s.discount)) OVER w, 2) AS change
FROM sales s JOIN reps r ON r.rep_id = s.rep_id
WHERE r.rep_id = 3
WINDOW w AS (PARTITION BY r.rep_id ORDER BY s.sale_date)
ORDER BY s.sale_date;
rep_name sale_date revenue previous_sale change
----------- ---------- ------- ------------- ------
Carla Nunes 2025-03-10 769.5 NULL NULL
Carla Nunes 2025-04-02 384.0 769.5 -385.5
Carla Nunes 2025-04-18 240.0 384.0 -144.0
Carla Nunes 2025-05-07 525.0 240.0 285.0
Carla Nunes 2025-05-23 585.0 525.0 60.0
Carla's first sale is the biggest and the next two decline sharply before the series recovers. The change column is empty on the first row, again because there is nothing to compare against. This pattern is the backbone of every "this month against last month" report, and it is far more readable than a correlated subquery doing the same job row by row.
How do you find the first row in each group?
The classic use of LAG is spotting the start of a series: filter for the rows where there is no previous row. That needs the window to see every row, so the filtering happens one level out:
SELECT rep_name, sale_date FROM (
SELECT r.rep_name, s.sale_date,
LAG(s.sale_date) OVER (PARTITION BY r.rep_id ORDER BY s.sale_date) AS previous_date
FROM sales s JOIN reps r ON r.rep_id = s.rep_id
) WHERE previous_date IS NULL
ORDER BY rep_name;
rep_name sale_date
------------- ----------
Ada Whitfield 2025-03-03
Bruno Kessler 2025-03-07
Carla Nunes 2025-03-10
Dev Patel 2025-03-12
Elif Demir 2025-03-14
Farid Haddad 2025-03-18
Greta Vogel 2025-03-20
One row per rep — the day each of them made their first sale. This is the "gap and islands" technique: LAG marks the first row of each group, and a filter on the marker selects them. The same trick finds the end of a group (LEAD is NULL), rows that break a sequence (compare the current date with the previous date plus a gap), and changes in a status.
Quick reference
- LAG(offset, default) reads a value from earlier rows; LEAD(offset, default) reads a later one.
- Both rely on the window's ORDER BY to decide what "previous" and "next" mean.
- The first row of a partition has no previous value and the last has no next one, both NULL.
- PARTITION BY keeps each group's series separate — a trend line per rep, not per company.
- Filter for the first row of a group by looking for a NULL previous value in an outer query.
Lag drill: write a query that shows, for each sale, the number of days since that rep's previous sale — using LAG on the date — and then explain in one sentence why the days-since column must be computed in a window rather than by joining the table to itself.