LAG, LEAD, FIRST_VALUE and LAST_VALUE
These window functions read a value from another row of the window: the one before, the
one after, the first or the last. The ORDER BY inside OVER decides which row that is.
Syntax
Order of execution: FROM → WHERE → GROUP BY → window functions → SELECT → ORDER BY
CodeSELECT column, LAG(column) OVER (PARTITION BY g ORDER BY s) AS previous, LEAD(column, 1, default) OVER (PARTITION BY g ORDER BY s) AS next, FIRST_VALUE(column) OVER (PARTITION BY g ORDER BY s) AS first, LAST_VALUE(column) OVER (PARTITION BY g ORDER BY s ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last FROM table_name;
| Function | Returns |
|---|---|
LAG(col, n, default) | col from n rows before (n is 1 if left out); default or NULL if there is none |
LEAD(col, n, default) | col from n rows after |
FIRST_VALUE(col) | col from the first row of the window |
LAST_VALUE(col) | col from the last row of the frame: see below |
NTH_VALUE(col, n) | col from the n-th row of the frame, or NULL if the frame has fewer rows (not in SQL Server) |
Always give LAG and LEAD an ORDER BY inside OVER: without it, "the row before" has no
meaning, and SQL Server and Oracle refuse the query.
Examples
The row before and after
Customer 3's orders, with the amounts of the orders next to each one.
CodeSELECT order_id, order_date, total_amount, LAG(total_amount) OVER (ORDER BY order_date, order_id) AS previous_amount, LEAD(total_amount) OVER (ORDER BY order_date, order_id) AS next_amount FROM orders WHERE customer_id = 3 ORDER BY order_date, order_id;
Changes from the row before
Subtract the previous value. A default replaces the NULL on the first row.
CodeSELECT order_id, total_amount, total_amount - LAG(total_amount) OVER (ORDER BY order_date, order_id) AS change, LAG(total_amount, 1, 0) OVER (ORDER BY order_date, order_id) AS previous_or_zero, LAG(total_amount, 2) OVER (ORDER BY order_date, order_id) AS two_orders_back FROM orders WHERE customer_id = 3 ORDER BY order_date, order_id;
Comparing with the row before
Label each row by comparing it with the previous one. Work out LAG in a subquery first, because
SELECT can't reuse its own alias, and handle the first row, whose previous value is NULL.
CodeSELECT order_id, total_amount, CASE WHEN previous_amount IS NULL THEN 'first order' WHEN total_amount > previous_amount THEN 'higher' WHEN total_amount < previous_amount THEN 'lower' ELSE 'same' END AS compared_with_previous FROM (SELECT order_id, order_date, total_amount, LAG(total_amount) OVER (ORDER BY order_date, order_id) AS previous_amount FROM orders WHERE customer_id = 3) t ORDER BY order_date, order_id;
Runs of consecutive days
In a run of consecutive dates, the date and its row number both go up by one, so the date minus the row number stays the same. That value names the run: group by it to get each run's first day, last day and length. Each date must appear once, so remove repeated dates first.
PostgreSQL and Oracle take days off with date - number; MySQL writes
DATE_SUB(date, INTERVAL n DAY) and SQL Server DATEADD(day, -n, date). With LAG instead, a new
run starts wherever the gap to the previous date is more than one day.
CodeWITH visits (visit_date) AS ( VALUES (DATE '2025-03-01'), (DATE '2025-03-02'), (DATE '2025-03-03'), (DATE '2025-03-07'), (DATE '2025-03-08') ), numbered AS ( SELECT visit_date, visit_date - ROW_NUMBER() OVER (ORDER BY visit_date)::int AS run_key FROM visits ) SELECT MIN(visit_date) AS run_start, MAX(visit_date) AS run_end, COUNT(*) AS days FROM numbered GROUP BY run_key ORDER BY run_start; -- 1 to 3 March, then 7 to 8 March
Per group
With PARTITION BY, the first row of each group has no previous row.
CodeSELECT customer_id, order_id, order_date, LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date, order_id) AS previous_date FROM orders WHERE customer_id IN (3, 4) ORDER BY customer_id, order_date, order_id;
The first row of each group
CodeSELECT customer_id, order_id, order_date, FIRST_VALUE(order_date) OVER (PARTITION BY customer_id ORDER BY order_date, order_id) AS first_order FROM orders WHERE customer_id IN (3, 4) ORDER BY customer_id, order_date, order_id;
LAST_VALUE needs a full frame
With ORDER BY, the frame ends at the current row, so LAST_VALUE returns the row itself.
Widen the frame to the whole group.
CodeSELECT order_id, order_date, LAST_VALUE(order_date) OVER (ORDER BY order_date, order_id) AS not_last, LAST_VALUE(order_date) OVER (ORDER BY order_date, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_order FROM orders WHERE customer_id = 3 ORDER BY order_date, order_id;
The n-th row with NTH_VALUE
NTH_VALUE reads any position, such as each customer's second order. Like LAST_VALUE, it needs
the full frame: with the default one, rows before the n-th can't see it yet.
CodeSELECT customer_id, order_id, order_date, NTH_VALUE(order_date, 2) OVER (PARTITION BY customer_id ORDER BY order_date, order_id) AS not_yet, NTH_VALUE(order_date, 2) OVER (PARTITION BY customer_id ORDER BY order_date, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_order FROM orders WHERE customer_id IN (3, 4) ORDER BY customer_id, order_date, order_id; -- not_yet is NULL on each customer's first row
Remember
LAGlooks back andLEADlooks forward, in the order of the window'sORDER BY.- The first row has no previous row: you get NULL unless you give a default.
- In PostgreSQL and Oracle, subtracting one date from another gives the number of days; MySQL and SQL Server use
DATEDIFF. - Date minus
ROW_NUMBERis the same for every day of a run; number distinct dates only. LAST_VALUEandNTH_VALUEneedROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING; for the last row,FIRST_VALUEwith the order reversed also works.
Practice
- Days Between Orders (Easy)
- Month-on-Month Revenue Change (Medium)
- Cheapest and Priciest Side by Side (Medium)
- Longest Order Streaks of 2025 (Hard)