Window Functions
LEAD and LAG Functions
Peek at the next row or look back at the previous row β no self-joins needed.
- LAG β Access a value from a previous row in the result set
- LEAD β Access a value from a subsequent row in the result set
- Default Values β Provide fallback values when no row exists Essential for period-over-period comparisons, time series, and gap detection.
What Are LEAD and LAG?
DfLAG()
Returns the value of a specified column from a row that precedes the current row by a given offset. If no such row exists, returns a default value (or NULL).
DfLEAD()
Returns the value of a specified column from a row that follows the current row by a given offset. If no such row exists, returns a default value (or NULL).
Comparison
| Function | Direction | Default | Typical Use |
|---|---|---|---|
| LAG | Backward (previous rows) | NULL or specified | Period-over-period, change detection |
| LEAD | Forward (next rows) | NULL or specified | Forecasting, next-period lookup |
Syntax
-- Basic LAG syntax
SELECT
column_name,
LAG(column_name, offset, default) OVER (
PARTITION BY partition_column
ORDER BY sort_column
) AS previous_value
FROM table_name;
-- Basic LEAD syntax
SELECT
column_name,
LEAD(column_name, offset, default) OVER (
PARTITION BY partition_column
ORDER BY sort_column
) AS next_value
FROM table_name;
The offset parameter defaults to 1 if omitted. The default parameter defaults to NULL if omitted. Always specify the ORDER BY inside OVER() β LAG and LEAD without ordering produce unpredictable results.
LAG β Looking Back
Month-over-Month Revenue Change
-- Calculate revenue change from previous month
SELECT
month,
revenue,
LAG(revenue, 1, 0) OVER (ORDER BY month) AS prev_month_revenue,
revenue - LAG(revenue, 1, 0) OVER (ORDER BY month) AS revenue_change,
ROUND(
(revenue - LAG(revenue, 1, 0) OVER (ORDER BY month)) * 100.0 /
NULLIF(LAG(revenue, 1, 0) OVER (ORDER BY month), 0),
2
) AS pct_change
FROM monthly_revenue
ORDER BY month;
Detecting Gaps in Sequential Data
-- Find gaps between consecutive log entries
SELECT
log_id,
event_type,
created_at,
LAG(created_at) OVER (PARTITION BY event_type ORDER BY created_at) AS prev_event_time,
TIMESTAMPDIFF(MINUTE, LAG(created_at) OVER (PARTITION BY event_type ORDER BY created_at), created_at) AS minutes_since_last
FROM event_logs
ORDER BY event_type, created_at;
Two-Period Comparison
-- Compare current month to previous and year-ago month
SELECT
month,
revenue,
LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
LAG(revenue, 12) OVER (ORDER BY month) AS year_ago_month,
revenue - LAG(revenue, 12) OVER (ORDER BY month) AS yoy_change
FROM monthly_revenue
ORDER BY month;
LEAD β Looking Forward
Predicting Next Period
-- Show each month's revenue alongside the next month
SELECT
month,
revenue,
LEAD(revenue, 1) OVER (ORDER BY month) AS next_month_revenue,
LEAD(revenue, 1, revenue) OVER (ORDER BY month) AS expected_next_month
FROM monthly_revenue
ORDER BY month;
Identifying Forward-Looking Intervals
-- Calculate days until next order per customer
SELECT
customer_id,
order_date,
LEAD(order_date, 1) OVER (
PARTITION BY customer_id
ORDER BY order_date
) AS next_order_date,
DATEDIFF(
LEAD(order_date, 1) OVER (
PARTITION BY customer_id
ORDER BY order_date
),
order_date
) AS days_until_next
FROM orders
ORDER BY customer_id, order_date;
Advanced: Multiple Offsets
-- Revenue comparison across multiple periods
SELECT
month,
revenue,
LAG(revenue, 1) OVER (ORDER BY month) AS month_1_ago,
LAG(revenue, 2) OVER (ORDER BY month) AS month_2_ago,
LAG(revenue, 3) OVER (ORDER BY month) AS month_3_ago,
LEAD(revenue, 1) OVER (ORDER BY month) AS month_1_forward,
LEAD(revenue, 2) OVER (ORDER BY month) AS month_2_forward
FROM monthly_revenue
ORDER BY month;
LEAD and LAG with large offsets can be expensive on large datasets. If you need to access rows more than a few positions away, consider self-joins or subqueries instead.
Practical Pattern: Period-over-Period Analysis
DfPeriod-over-Period Analysis
A technique for comparing metrics across time intervals β month-over-month, quarter-over-quarter, or year-over-year β to identify trends, seasonality, and growth patterns.
-- Full period-over-period dashboard query
WITH monthly_metrics AS (
SELECT
DATE_FORMAT(order_date, '%Y-%m') AS month,
COUNT(*) AS order_count,
SUM(order_amount) AS revenue,
AVG(order_amount) AS avg_order_value
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
)
SELECT
month,
revenue,
LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
revenue - LAG(revenue, 1) OVER (ORDER BY month) AS absolute_change,
ROUND(
(revenue - LAG(revenue, 1) OVER (ORDER BY month)) * 100.0 /
NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0),
2
) AS pct_change,
CASE
WHEN revenue > LAG(revenue, 1) OVER (ORDER BY month) THEN 'Up'
WHEN revenue < LAG(revenue, 1) OVER (ORDER BY month) THEN 'Down'
ELSE 'Flat'
END AS trend
FROM monthly_metrics
ORDER BY month;
Quick Reference
| Parameter | Description | Default |
|---|---|---|
column | Column value to retrieve | Required |
offset | Number of rows ahead/behind | 1 |
default | Value if no row at offset | NULL |
PARTITION BY | Reset offset within groups | Optional |
ORDER BY | Define row sequence | Required |
Key Takeaways
LAGaccesses previous rows;LEADaccesses subsequent rows β both without self-joins- Always specify
ORDER BYinsideOVER()for deterministic results - Use default parameters to handle boundary rows where no previous/next row exists
- Period-over-period analysis is the most common use case for these functions
- For offsets beyond a few rows, consider self-joins for better performance