LAG, LEAD & Frames
Advanced · 24 min read · ▶ live playground · ✦ checkpoint
LAG and LEAD let one row read values from nearby rows, so you can compute changes without joining a table to itself. Frames then shrink or widen what a window function can see: everything so far, the last three rows, peer groups, or the whole partition.
You already know the second dial: ORDER BY inside OVER controls the window calculation. Neighbor functions and frames both depend on that order, so write it like you mean it.
LAG and LEAD: previous and next rows
LAG(value) returns value from the previous row in the window. LEAD(value) returns value from the next row in the window. No previous row? No next row? The result is NULL.
SELECT ds.day, ds.branch, ds.revenue,
lag(ds.revenue) OVER (
PARTITION BY ds.branch
ORDER BY ds.day
) AS previous_revenue,
lead(ds.revenue) OVER (
PARTITION BY ds.branch
ORDER BY ds.day
) AS next_revenue
FROM daily_sales AS ds
WHERE ds.branch = 'center'
ORDER BY ds.day; day | branch | revenue | previous_revenue | next_revenue
------------+--------+---------+------------------+--------------
2026-03-02 | center | 830.00 | NULL | 905.50
2026-03-03 | center | 905.50 | 830.00 | 760.00
2026-03-04 | center | 760.00 | 905.50 | 915.00
2026-03-05 | center | 915.00 | 760.00 | 1040.00
2026-03-06 | center | 1040.00 | 915.00 | 1215.25
2026-03-07 | center | 1215.25 | 1040.00 | 1180.00
2026-03-08 | center | 1180.00 | 1215.25 | NULL
(7 rows)PARTITION BY ds.branch says each branch gets its own timeline. ORDER BY ds.day says previous and next mean "previous day" and "next day." The final ORDER BY ds.day only displays the grid in the same order.
Day-over-day deltas and growth
A delta is the difference between this row and a comparison row. A growth rate is that difference divided by the comparison value. Use LAG once in a small derived table, then do normal arithmetic outside it:
SELECT ordered.day, ordered.revenue, ordered.previous_revenue,
ordered.revenue - ordered.previous_revenue AS revenue_delta,
round(
100 * (ordered.revenue - ordered.previous_revenue)
/ NULLIF(ordered.previous_revenue, 0),
1
) AS pct_change
FROM (
SELECT ds.day, ds.revenue,
lag(ds.revenue) OVER (
PARTITION BY ds.branch
ORDER BY ds.day
) AS previous_revenue
FROM daily_sales AS ds
WHERE ds.branch = 'center'
) AS ordered
ORDER BY ordered.day; day | revenue | previous_revenue | revenue_delta | pct_change
------------+---------+------------------+---------------+------------
2026-03-02 | 830.00 | NULL | NULL | NULL
2026-03-03 | 905.50 | 830.00 | 75.50 | 9.1
2026-03-04 | 760.00 | 905.50 | -145.50 | -16.1
2026-03-05 | 915.00 | 760.00 | 155.00 | 20.4
2026-03-06 | 1040.00 | 915.00 | 125.00 | 13.7
2026-03-07 | 1215.25 | 1040.00 | 175.25 | 16.9
2026-03-08 | 1180.00 | 1215.25 | -35.25 | -2.9
(7 rows)The first row has NULL change because there is no previous center row. That is the honest answer, not a formatting problem.
Frames: the moving slice of a window
A frame is the slice of rows inside the current row's window that a window aggregate can use. PARTITION BY picks the group; ORDER BY lines it up; the frame says how much of that line is visible.
SELECT ds.day, ds.branch, ds.revenue,
sum(ds.revenue) OVER (
PARTITION BY ds.branch
ORDER BY ds.day
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total,
round(avg(ds.revenue) OVER (
PARTITION BY ds.branch
ORDER BY ds.day
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
), 2) AS three_day_avg
FROM daily_sales AS ds
WHERE ds.branch = 'center'
ORDER BY ds.day; day | branch | revenue | running_total | three_day_avg
------------+--------+---------+---------------+---------------
2026-03-02 | center | 830.00 | 830.00 | 830.00
2026-03-03 | center | 905.50 | 1735.50 | 867.75
2026-03-04 | center | 760.00 | 2495.50 | 831.83
2026-03-05 | center | 915.00 | 3410.50 | 860.17
2026-03-06 | center | 1040.00 | 4450.50 | 905.00
2026-03-07 | center | 1215.25 | 5665.75 | 1056.75
2026-03-08 | center | 1180.00 | 6845.75 | 1145.08
(7 rows)UNBOUNDED PRECEDING means "from the start of this partition." CURRENT ROW means "through this row." 2 PRECEDING means "the two rows before this one." Early rows use whatever exists; the first row's three-day average is a one-row average.
The default frame trap
If a window has ORDER BY and you don't write a frame, PostgreSQL uses RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE works with peer rows: rows that tie on the window's ORDER BY value. That sounds harmless until your ordered value has duplicates.
SELECT ct.check_id, ct.total,
sum(ct.total) OVER (ORDER BY ct.total) AS default_total,
sum(ct.total) OVER (
ORDER BY ct.total, ct.check_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS rows_total
FROM check_totals AS ct
ORDER BY ct.total, ct.check_id; check_id | total | default_total | rows_total
----------+-------+---------------+------------
1 | 5.00 | 5.00 | 5.00
2 | 8.00 | 21.00 | 13.00
3 | 8.00 | 21.00 | 21.00
4 | 10.00 | 41.00 | 31.00
5 | 10.00 | 41.00 | 41.00
6 | 13.00 | 54.00 | 54.00
(6 rows)ROWS, RANGE, and GROUPS in one grid
The three frame modes answer three different questions:
ROWScounts physical rows.RANGEcounts values within a distance on the ordered value, and includes peers.GROUPScounts peer groups.
SELECT ct.check_id, ct.total,
sum(ct.total) OVER (
ORDER BY ct.total, ct.check_id
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
) AS rows_frame,
sum(ct.total) OVER (
ORDER BY ct.total
RANGE BETWEEN 1 PRECEDING AND CURRENT ROW
) AS range_frame,
sum(ct.total) OVER (
ORDER BY ct.total
GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
) AS groups_frame
FROM check_totals AS ct
ORDER BY ct.total, ct.check_id; check_id | total | rows_frame | range_frame | groups_frame
----------+-------+------------+-------------+--------------
1 | 5.00 | 5.00 | 5.00 | 5.00
2 | 8.00 | 13.00 | 16.00 | 21.00
3 | 8.00 | 16.00 | 16.00 | 21.00
4 | 10.00 | 18.00 | 20.00 | 36.00
5 | 10.00 | 20.00 | 20.00 | 36.00
6 | 13.00 | 23.00 | 13.00 | 33.00
(6 rows)On check 4, ROWS sees check 3 plus check 4. RANGE sees totals from 9.00 through 10.00, so it sees the two 10.00 checks. GROUPS sees the previous peer group (8.00) plus the current peer group (10.00).
FIRST_VALUE, LAST_VALUE, and the frame fix
FIRST_VALUE(value) returns the first value in the frame. LAST_VALUE(value) returns the last value in the frame. That last sentence is the trap: with the default frame, "last" usually means "this row," not "the final row in the partition."
SELECT ds.day, ds.revenue,
first_value(ds.revenue) OVER (
PARTITION BY ds.branch
ORDER BY ds.day
) AS first_revenue,
last_value(ds.revenue) OVER (
PARTITION BY ds.branch
ORDER BY ds.day
) AS last_value_default,
last_value(ds.revenue) OVER (
PARTITION BY ds.branch
ORDER BY ds.day
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_revenue_fixed
FROM daily_sales AS ds
WHERE ds.branch = 'center'
ORDER BY ds.day; day | revenue | first_revenue | last_value_default | last_revenue_fixed
------------+---------+---------------+--------------------+--------------------
2026-03-02 | 830.00 | 830.00 | 830.00 | 1180.00
2026-03-03 | 905.50 | 830.00 | 905.50 | 1180.00
2026-03-04 | 760.00 | 830.00 | 760.00 | 1180.00
2026-03-05 | 915.00 | 830.00 | 915.00 | 1180.00
2026-03-06 | 1040.00 | 830.00 | 1040.00 | 1180.00
2026-03-07 | 1215.25 | 830.00 | 1215.25 | 1180.00
2026-03-08 | 1180.00 | 830.00 | 1180.00 | 1180.00
(7 rows)The fix is the full-partition frame: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. When you use LAST_VALUE, ask yourself: last in the frame, or last in the whole partition?
You can now compare rows to neighbors and control exactly how much of the ordered window an aggregate can see. Next lesson turns these pieces into patterns: top-N per group, dedup, gaps and islands, and session-style analysis.
Checkpoint
Answer all three to mark this lesson complete