sql
SELECT day,
revenue,
LAG(revenue) OVER (ORDER BY day) AS prev_revenue
FROM daily_revenue;Notes
`LAG` is the easiest way to compare the current row to the previous row.
SQL Window Functions: LAG, LEAD, Deltas, and Time-Series Comparisons
Compare rows to prior or next rows with LAG and LEAD for change detection, retention, and trend analysis.
Export
Copy the compact sheet, download it, or print it.
Download
`D` dense toggle · `C` copy all
LAG and LEAD basics
Reach backward or forward without a self join.
sql
SELECT day,
revenue,
LEAD(revenue) OVER (ORDER BY day) AS next_revenue
FROM daily_revenue;Notes
Use `LEAD` for forward-looking comparisons or interval construction.
sql
SELECT day,
revenue,
LAG(revenue, 1, 0) OVER (ORDER BY day) AS prev_revenue_or_zero
FROM daily_revenue;Notes
The third argument is the default when an offset row does not exist.
sql
SELECT month,
signups,
LAG(signups, 2) OVER (ORDER BY month) AS signups_2_months_ago
FROM monthly_signups;Notes
Offsets larger than 1 are useful for month-over-month or quarter-over-quarter comparisons.
Time-series patterns
Practical delta and retention examples.
sql
SELECT day,
revenue,
revenue - LAG(revenue) OVER (ORDER BY day) AS revenue_change
FROM daily_revenue;Notes
A simple difference often matters more than the raw value.
sql
SELECT day,
revenue,
ROUND(
100.0 * (revenue - LAG(revenue) OVER (ORDER BY day))
/ NULLIF(LAG(revenue) OVER (ORDER BY day), 0),
2
) AS pct_change
FROM daily_revenue;Notes
Use `NULLIF` to avoid division by zero.
sql
SELECT user_id,
event_time,
event_time - LAG(event_time) OVER (
PARTITION BY user_id
ORDER BY event_time
) AS time_since_prev
FROM user_events;Notes
The exact subtraction syntax varies by dialect, but the window pattern stays the same.
sql
SELECT customer_id,
billing_month,
status,
LEAD(status) OVER (
PARTITION BY customer_id
ORDER BY billing_month
) AS next_status
FROM subscription_history;Notes
Useful in churn modeling, handoff workflows, and lifecycle analysis.
More in SQL Window Functions
SQL Window Functions: Performance, EXPLAIN, and Dialect Notes
How to tune window queries, reduce sorts, structure indexes, and account for cross-dialect differences.
SQL Window Functions: Deduplication, Gaps and Islands, and Sessionization
Use ROW_NUMBER, LAG, and running groups to deduplicate rows, detect gaps, and form islands or sessions.
SQL Window Functions: Percentiles, Bucketing, and Distribution Analysis
Percent rank, cumulative distribution, percentile-style bucketing, and segmentation patterns.
SQL Window Functions: FIRST_VALUE, LAST_VALUE, NTH_VALUE, and Positional Analytics
Positional window functions for first, last, and nth values inside ordered partitions.
SQL Window Functions: Running Totals, Moving Averages, and Cumulative Metrics
Rolling metrics, cumulative sums, moving averages, and period-over-period analytics with frame clauses.
SQL Window Functions: Ranking, Top N per Group, and Percent Rank
Ranking functions such as ROW_NUMBER, RANK, DENSE_RANK, NTILE, PERCENT_RANK, and CUME_DIST.