How to Use LAG and LEAD in BigQuery
Last updated August 30, 2026 · By the SaturnSQL team
LAG(col) OVER (ORDER BY sort_col) reads the previous row and LEAD(col) the next one, which turns row-to-row comparisons like day-over-day change or time-between-events into a single expression.
SELECT
day,
revenue,
revenue - LAG(revenue) OVER (ORDER BY day) AS change_vs_yesterday
FROM daily_revenue
ORDER BY day;Offsets and defaults
LAG and LEAD take two optional arguments: how many rows to look across, and what to return when there is no such row. The first row of a window has no previous row, so a bare LAG returns NULL there; a default of 0 keeps arithmetic from going NULL with it.
SELECT
day,
revenue,
LAG(revenue, 7, 0) OVER (ORDER BY day) AS revenue_week_ago
FROM daily_revenue;Time between events per user
SELECT
user_id,
event_time,
TIMESTAMP_DIFF(
LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time),
event_time,
MINUTE
) AS minutes_to_next_event
FROM events;Mind the gaps
LAG(revenue) is "the previous row", not "yesterday". If dates are missing, the previous row might be from three days ago. When the calendar matters, join onto a date spine built with GENERATE_DATE_ARRAY first so every day has a row.
Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free