The LAG and the LEAD are the two window functions that compare a row with its neighbours. The LAG looks backwards, and the LEAD looks forwards. They bring the value of the neighbouring row into the current row, and that turns a table of independent events into a series. That is what you need for the change, the gaps, the trends, and the period comparisons. Neither of them sums anything up, and neither of them changes the number of rows you get back.
The previous lesson in this module covered "How Do Frames Compute Running Totals and Moving Averages?", and the next lesson in this module is about "How Do You Reshape Rows Into Report Columns?".
Neither of them aggregates, and both of them depend entirely on the ORDER BY of the window deciding which row is the "next" one.
How do LAG and LEAD compare rows to the previous or the next?
Here are the six sales of Ada Whitfield, each one with the sale before it and the sale after it. Nothing is lost, because the sale itself stays in the row next to the two new columns.
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 NULLThe first row has no previous sale, and the last has no next one. Both come back as a NULL rather than as a zero or as an error. A zero would be a lie, because it would claim that the sale before was worth nothing.
That is the honest answer, because there is no previous sale to compare with. It is the reason why LAG and LEAD are so often wrapped in the 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. Change the ordering and you change the meaning of every value that comes back.
The window above partitions by the rep, so the "previous" means the previous sale by that rep and 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 window is what turns a list of sales into a series you can put on a chart.
The offset is the second argument, and it defaults to 1. Most of the time you leave it at one, and you reach for a bigger offset only when you want to skip a row.
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-02The LAG(s.sale_date) is the date of the previous row, and the LAG(s.sale_date, 2) would be the one before that.
The first row's cell is a NULL because there is no earlier row, and not because the date is missing.
How do you calculate the change between two rows?
You subtract the LAG from the current value, and the window does the comparison for you. You never have to join the table to itself, and you never have to write a correlated subquery.
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.0Carla'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 it against.
This pattern is the backbone of every "this month against last month" report, and it is far more readable than a correlated subquery that does the same job row by row. The window also gives you the right answer when the groups are different sizes, which a hand-written join usually does not.
How do you find the first row in each group?
The classic use of the LAG is spotting the start of a series. You 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. The window runs in the inner query, and the WHERE that looks for the NULL runs in the outer one.
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-20There is one row for each rep, and it is the day that rep made their first sale.
This is the "gap and islands" technique. The LAG marks the first row of each group, and a filter on the marker selects them. The name comes from the fact that you are walking a table with gaps in it, and marking where each island starts.
The same trick finds the end of a group, where the LEAD is a NULL. It finds the rows that break a sequence, where you compare the current date with the previous date plus a gap. And it finds the changes in a status. All of those questions are one comparison away from each other.
Quick reference
LAG(offset, default)reads a value from earlier rows, andLEAD(offset, default)reads a later one.- Both rely on the
ORDER BYof the window to decide what the "previous" and the "next" mean. - The first row of a partition has no previous value, and the last has no next one. Both are a
NULL. PARTITION BYkeeps each group's series separate, so you get a trend line per rep and not per company.- You find the first row of a group by looking for a
NULLprevious value in an outer query.
Try it yourself: lag drill. Write a query that shows, for each sale, the number of days since that rep's previous sale, using the LAG on the date. Then explain in one sentence why the days-since column must be computed in a window and not by joining the table to itself.