LAG / LEAD / FIRST_VALUE / LAST_VALUE
Reach across rows in the same partition — compute deltas, sessionize events, and forward-fill missing data without self-joins.
TL;DR
Four window functions for “reach across rows in the partition without a self-join”:
LAG(col, n=1, default=NULL)— value ofcolfrom the rownback in the partition.LEAD(col, n=1, default=NULL)— same, n rows ahead.FIRST_VALUE(col)— value ofcolin the first row of the frame.LAST_VALUE(col)— value ofcolin the last row of the frame.
LAG and LEAD are the workhorses for time-series differencing
(“delta from previous event”), session detection (“gap > 30 min”),
prev/next state (“previous status”), and a dozen other patterns. The
self-join equivalent of any of these is at least 5x more code and usually
slower.
FIRST_VALUE / LAST_VALUE have a famous gotcha: by default, the frame
is “start through current row”, so LAST_VALUE returns the current row,
not the partition’s last row. Always specify the frame.
A worked example
CREATE TABLE events (
user_id int, ts timestamptz, event text, value numeric
);
INSERT INTO events VALUES
(1, '2026-04-01 10:00', 'view', NULL),
(1, '2026-04-01 10:05', 'click', NULL),
(1, '2026-04-01 10:10', 'add', 50),
(1, '2026-04-01 11:30', 'view', NULL), -- new session: 80-min gap
(1, '2026-04-01 11:35', 'buy', 50),
(2, '2026-04-01 09:00', 'view', NULL),
(2, '2026-04-01 09:01', 'buy', 20);LAG — previous event’s timestamp
SELECT user_id, ts, event,
LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) AS prev_ts,
ts - LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) AS gap
FROM events
ORDER BY user_id, ts;+---------+------------------+-------+------------------+----------+
| user_id | ts | event | prev_ts | gap |
+---------+------------------+-------+------------------+----------+
| 1 | 2026-04-01 10:00 | view | NULL | NULL |
| 1 | 2026-04-01 10:05 | click | 2026-04-01 10:00 | 00:05:00 |
| 1 | 2026-04-01 10:10 | add | 2026-04-01 10:05 | 00:05:00 |
| 1 | 2026-04-01 11:30 | view | 2026-04-01 10:10 | 01:20:00 |
| 1 | 2026-04-01 11:35 | buy | 2026-04-01 11:30 | 00:05:00 |
| 2 | 2026-04-01 09:00 | view | NULL | NULL |
| 2 | 2026-04-01 09:01 | buy | 2026-04-01 09:00 | 00:01:00 |
+---------+------------------+-------+------------------+----------+The first row of each partition has no predecessor — LAG returns the
default (NULL). To use 0 instead: LAG(ts, 1, '1970-01-01'::timestamptz).
Sessionization — flag a new session when gap > 30 min
WITH gaps AS (
SELECT user_id, ts, event,
CASE WHEN ts - LAG(ts) OVER (PARTITION BY user_id ORDER BY ts)
> INTERVAL '30 minutes'
OR LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) IS NULL
THEN 1 ELSE 0 END AS is_new_session
FROM events
)
SELECT user_id, ts, event,
SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY ts) AS session_id
FROM gaps
ORDER BY user_id, ts;+---------+------------------+-------+------------+
| user_id | ts | event | session_id |
+---------+------------------+-------+------------+
| 1 | 2026-04-01 10:00 | view | 1 |
| 1 | 2026-04-01 10:05 | click | 1 |
| 1 | 2026-04-01 10:10 | add | 1 |
| 1 | 2026-04-01 11:30 | view | 2 | ← new session
| 1 | 2026-04-01 11:35 | buy | 2 |
| 2 | 2026-04-01 09:00 | view | 1 |
| 2 | 2026-04-01 09:01 | buy | 1 |
+---------+------------------+-------+------------+The pattern: LAG measures gaps; CASE flags session boundaries; a
running SUM over the flags assigns sequential session IDs. This is the
canonical SQL sessionization. No self-join required.
LAG / LEAD with offsets and defaults
LAG(col) -- 1 row back, default NULL
LAG(col, 3) -- 3 rows back
LAG(col, 1, 0) -- 1 row back, default 0 if no previous row
LEAD(col, 1, 'unknown') -- 1 row ahead, default 'unknown'The default argument is the value returned when the offset goes out of
the partition’s bounds. Hugely useful for “first event ever” features
where you want 0 instead of NULL for the boundary.
FIRST_VALUE and LAST_VALUE — the frame trap
-- WRONG: LAST_VALUE returns the current row
SELECT user_id, ts, event,
FIRST_VALUE(event) OVER (PARTITION BY user_id ORDER BY ts) AS first_event,
LAST_VALUE(event) OVER (PARTITION BY user_id ORDER BY ts) AS last_event
FROM events;first_event correctly returns the user’s first event. last_event
returns the current row’s event — because the default frame for an
ordered window is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,
and the “last” row of that frame is the current row.
Fix: specify the full-partition frame:
LAST_VALUE(event) OVER (
PARTITION BY user_id
ORDER BY ts
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_eventOr use FIRST_VALUE with a reversed sort (often clearer):
FIRST_VALUE(event) OVER (PARTITION BY user_id ORDER BY ts DESC) AS last_eventThis is the most-cited window-function gotcha. Memorize it.
NTH_VALUE
NTH_VALUE(col, n) — value of col in the n-th row of the frame. Same
frame issues as FIRST_VALUE/LAST_VALUE. Less common; useful for “value
of the second purchase” without writing a CTE.
Forward-fill / last-non-null with LAG
A frequent need: a sparse time-series with NULLs you want to forward-fill.
-- daily snapshots, occasionally missing
CREATE TABLE prices (day date, price numeric);
INSERT INTO prices VALUES
('2026-04-01', 100), ('2026-04-02', NULL), ('2026-04-03', NULL),
('2026-04-04', 110), ('2026-04-05', NULL);
-- forward-fill: copy the last non-null price forward
WITH grouped AS (
SELECT day, price,
COUNT(price) OVER (ORDER BY day) AS grp
FROM prices
)
SELECT day,
FIRST_VALUE(price) OVER (PARTITION BY grp ORDER BY day) AS filled
FROM grouped
ORDER BY day;The trick: COUNT(price) OVER (ORDER BY day) increments only on
non-NULL rows, so each NULL run shares a grp value with the last
non-NULL row. Within each group, FIRST_VALUE(price) is the non-NULL
seed. Output:
+------------+--------+
| day | filled |
+------------+--------+
| 2026-04-01 | 100 |
| 2026-04-02 | 100 |
| 2026-04-03 | 100 |
| 2026-04-04 | 110 |
| 2026-04-05 | 110 |
+------------+--------+This is one of those “I needed this for years before someone showed it to me” tricks. Keep it.
Common pitfalls
LAST_VALUEreturns the current row unless you specify the full partition frame. See above.LAG/LEADover a window with noORDER BYis an error in Postgres (rightly — “previous in what order?”).- Using
LAGwithPARTITION BYthat doesn’t include all the identifying columns gives wrong answers. If you care about per-user per-product gaps, partition by both. - Forgetting the
defaultarg. First row’sLAGis NULL by default. DownstreamLAG(...) > 5filters NULL out — usually fine, sometimes a bug. - NULL values in the ordering column.
LAGfollows the same NULL ordering rules asORDER BY. Pin them withNULLS FIRST/LAST.
Production patterns for ML
1. Time-since-last-event features. A standard churn / engagement feature:
SELECT user_id, ts,
EXTRACT(EPOCH FROM (ts - LAG(ts) OVER (PARTITION BY user_id ORDER BY ts)))
AS seconds_since_prev,
EXTRACT(EPOCH FROM (LEAD(ts) OVER (PARTITION BY user_id ORDER BY ts) - ts))
AS seconds_until_next
FROM events;LEAD looks forward — useful for label generation (“did the user return
within 7 days?”).
2. State-change detection. “Did this user’s plan change?”:
SELECT user_id, day, plan,
LAG(plan) OVER (PARTITION BY user_id ORDER BY day) AS prev_plan,
CASE WHEN plan <> LAG(plan) OVER (PARTITION BY user_id ORDER BY day)
THEN 1 ELSE 0 END AS is_change
FROM user_plan_daily;Sum is_change over a window to count plan changes per user — a strong
churn signal.
3. Forward-fill latest known feature value. Common when joining sparse feature snapshots to dense event streams:
WITH dense AS (
SELECT day, COALESCE(score, LAG(score) IGNORE NULLS
OVER (ORDER BY day)) AS score
FROM user_score_snapshots
)
SELECT * FROM dense;IGNORE NULLS is supported by Snowflake, BigQuery, DuckDB, Oracle.
Postgres doesn’t have it directly — use the COUNT-based trick from above.
Resources
- PostgreSQL — window function reference — postgresql.org/docs
- Snowflake — IGNORE NULLS — docs.snowflake.com (search “IGNORE NULLS”)
- Mode Analytics — window functions — mode.com/sql-tutorial
- PostgreSQL documentation — postgresql.org/docs