Paradime | LAG / LEAD

SQL Keywords

LAG / LEAD

Feb 23, 2026

min read

Category: Window/Analytic Functions

Platform Support:

✅ Snowflake | ✅ BigQuery | ✅ Databricks

Description

LAG accesses data from a previous row in the result set without using a self-join. LEAD accesses data from a subsequent row. These functions are essential for comparing current values with previous/next values, calculating changes over time, and analyzing sequential data.

Syntax

LAG(column, offset, default_value) OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression
)

LEAD(column, offset, default_value) OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression
)

LAG(column, offset, default_value) OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression
)

LEAD(column, offset, default_value) OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression
)

LAG(column, offset, default_value) OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression
)

LEAD(column, offset, default_value) OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression
)

Parameters:

column - The column to access from previous/next row
offset - Number of rows back/forward (default: 1)
default_value - Value to return if offset goes beyond partition (default: NULL)

Platform-Specific Notes

All Platforms:

Standard SQL window function
Identical syntax and behavior across Snowflake, BigQuery, and Databricks
Requires ORDER BY clause
Returns NULL for first row (LAG) or last row (LEAD) by default

Example 1: Calculate Month-over-Month Change

All Platforms:

SELECT 
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) as previous_month_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) as month_over_month_change,
    ROUND((revenue - LAG(revenue, 1) OVER (ORDER BY month)) * 100.0 
          / LAG(revenue, 1) OVER (ORDER BY month), 2) as pct_change
FROM monthly_sales
ORDER BY month

SELECT 
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) as previous_month_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) as month_over_month_change,
    ROUND((revenue - LAG(revenue, 1) OVER (ORDER BY month)) * 100.0 
          / LAG(revenue, 1) OVER (ORDER BY month), 2) as pct_change
FROM monthly_sales
ORDER BY month

SELECT 
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) as previous_month_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) as month_over_month_change,
    ROUND((revenue - LAG(revenue, 1) OVER (ORDER BY month)) * 100.0 
          / LAG(revenue, 1) OVER (ORDER BY month), 2) as pct_change
FROM monthly_sales
ORDER BY month

Sample Data (monthly_sales table):

month	revenue
2024-01	50000
2024-02	55000
2024-03	52000
2024-04	58000
2024-05	62000

Result:

month	revenue	previous_month_revenue	month_over_month_change	pct_change
2024-01	50000	NULL	NULL	NULL
2024-02	55000	50000	5000	10.00
2024-03	52000	55000	-3000	-5.45
2024-04	58000	52000	6000	11.54
2024-05	62000	58000	4000	6.90

Example 2: Compare with Next Period (LEAD)

All Platforms:

SELECT 
    product_id,
    price_change_date,
    price as current_price,
    LEAD(price, 1) OVER (PARTITION BY product_id ORDER BY price_change_date) as next_price,
    LEAD(price_change_date, 1) OVER (PARTITION BY product_id ORDER BY price_change_date) as next_change_date,
    DATEDIFF(
        LEAD(price_change_date, 1) OVER (PARTITION BY product_id ORDER BY price_change_date),
        price_change_date
    ) as days_at_this_price
FROM product_price_history
ORDER BY product_id,

SELECT 
    product_id,
    price_change_date,
    price as current_price,
    LEAD(price, 1) OVER (PARTITION BY product_id ORDER BY price_change_date) as next_price,
    LEAD(price_change_date, 1) OVER (PARTITION BY product_id ORDER BY price_change_date) as next_change_date,
    DATEDIFF(
        LEAD(price_change_date, 1) OVER (PARTITION BY product_id ORDER BY price_change_date),
        price_change_date
    ) as days_at_this_price
FROM product_price_history
ORDER BY product_id,

SELECT 
    product_id,
    price_change_date,
    price as current_price,
    LEAD(price, 1) OVER (PARTITION BY product_id ORDER BY price_change_date) as next_price,
    LEAD(price_change_date, 1) OVER (PARTITION BY product_id ORDER BY price_change_date) as next_change_date,
    DATEDIFF(
        LEAD(price_change_date, 1) OVER (PARTITION BY product_id ORDER BY price_change_date),
        price_change_date
    ) as days_at_this_price
FROM product_price_history
ORDER BY product_id,

Sample Data (product_price_history table):

product_id	price_change_date	price
101	2024-01-01	999.99
101	2024-02-15	899.99
101	2024-03-01	849.99
102	2024-01-01	29.99
102	2024-03-10	24.99

Result:

product_id	price_change_date	current_price	next_price	next_change_date	days_at_this_price
101	2024-01-01	999.99	899.99	2024-02-15	45
101	2024-02-15	899.99	849.99	2024-03-01	15
101	2024-03-01	849.99	NULL	NULL	NULL
102	2024-01-01	29.99	24.99	2024-03-10	69
102	2024-03-10	24.99	NULL	NULL	NULL

Example 3: Running Difference with Multiple Offsets

All Platforms:

SELECT 
    employee_id,
    review_date,
    performance_score,
    LAG(performance_score, 1) OVER (PARTITION BY employee_id ORDER BY review_date) as previous_score,
    LAG(performance_score, 2) OVER (PARTITION BY employee_id ORDER BY review_date) as two_reviews_ago,
    performance_score - LAG(performance_score, 1) OVER (PARTITION BY employee_id ORDER BY review_date) as improvement,
    CASE 
        WHEN performance_score > LAG(performance_score, 1) OVER (PARTITION BY employee_id ORDER BY review_date) 
        THEN 'Improving'
        WHEN performance_score < LAG(performance_score, 1) OVER (PARTITION BY employee_id ORDER BY review_date) 
        THEN 'Declining'
        ELSE 'Stable'
    END as trend
FROM performance_reviews
ORDER BY employee_id,

SELECT 
    employee_id,
    review_date,
    performance_score,
    LAG(performance_score, 1) OVER (PARTITION BY employee_id ORDER BY review_date) as previous_score,
    LAG(performance_score, 2) OVER (PARTITION BY employee_id ORDER BY review_date) as two_reviews_ago,
    performance_score - LAG(performance_score, 1) OVER (PARTITION BY employee_id ORDER BY review_date) as improvement,
    CASE 
        WHEN performance_score > LAG(performance_score, 1) OVER (PARTITION BY employee_id ORDER BY review_date) 
        THEN 'Improving'
        WHEN performance_score < LAG(performance_score, 1) OVER (PARTITION BY employee_id ORDER BY review_date) 
        THEN 'Declining'
        ELSE 'Stable'
    END as trend
FROM performance_reviews
ORDER BY employee_id,

SELECT 
    employee_id,
    review_date,
    performance_score,
    LAG(performance_score, 1) OVER (PARTITION BY employee_id ORDER BY review_date) as previous_score,
    LAG(performance_score, 2) OVER (PARTITION BY employee_id ORDER BY review_date) as two_reviews_ago,
    performance_score - LAG(performance_score, 1) OVER (PARTITION BY employee_id ORDER BY review_date) as improvement,
    CASE 
        WHEN performance_score > LAG(performance_score, 1) OVER (PARTITION BY employee_id ORDER BY review_date) 
        THEN 'Improving'
        WHEN performance_score < LAG(performance_score, 1) OVER (PARTITION BY employee_id ORDER BY review_date) 
        THEN 'Declining'
        ELSE 'Stable'
    END as trend
FROM performance_reviews
ORDER BY employee_id,

Sample Data (performance_reviews table):

employee_id	review_date	performance_score
1001	2023-06-01	75
1001	2023-12-01	82
1001	2024-06-01	88
1002	2023-06-01	90
1002	2023-12-01	85
1002	2024-06-01	87

Result:

employee_id	review_date	performance_score	previous_score	two_reviews_ago	improvement	trend
1001	2023-06-01	75	NULL	NULL	NULL	Stable
1001	2023-12-01	82	75	NULL	7	Improving
1001	2024-06-01	88	82	75	6	Improving
1002	2023-06-01	90	NULL	NULL	NULL	Stable
1002	2023-12-01	85	90	NULL	-5	Declining
1002	2024-06-01	87	85	90	2	Improving

Example 4: Stock Price Analysis

All Platforms:

SELECT 
    symbol,
    trade_date,
    close_price,
    LAG(close_price, 1) OVER (PARTITION BY symbol ORDER BY trade_date) as prev_day_close,
    LAG(close_price, 5) OVER (PARTITION BY symbol ORDER BY trade_date) as five_days_ago,
    LEAD(close_price, 1) OVER (PARTITION BY symbol ORDER BY trade_date) as next_day_close,
    -- Calculate daily return
    (close_price - LAG(close_price, 1) OVER (PARTITION BY symbol ORDER BY trade_date)) 
        / LAG(close_price, 1) OVER (PARTITION BY symbol ORDER BY trade_date) * 100 as daily_return_pct,
    -- Calculate 5-day return
    (close_price - LAG(close_price, 5) OVER (PARTITION BY symbol ORDER BY trade_date)) 
        / LAG(close_price, 5) OVER (PARTITION BY symbol ORDER BY trade_date) * 100 as five_day_return_pct
FROM stock_prices
WHERE symbol = 'AAPL'
ORDER BY

SELECT 
    symbol,
    trade_date,
    close_price,
    LAG(close_price, 1) OVER (PARTITION BY symbol ORDER BY trade_date) as prev_day_close,
    LAG(close_price, 5) OVER (PARTITION BY symbol ORDER BY trade_date) as five_days_ago,
    LEAD(close_price, 1) OVER (PARTITION BY symbol ORDER BY trade_date) as next_day_close,
    -- Calculate daily return
    (close_price - LAG(close_price, 1) OVER (PARTITION BY symbol ORDER BY trade_date)) 
        / LAG(close_price, 1) OVER (PARTITION BY symbol ORDER BY trade_date) * 100 as daily_return_pct,
    -- Calculate 5-day return
    (close_price - LAG(close_price, 5) OVER (PARTITION BY symbol ORDER BY trade_date)) 
        / LAG(close_price, 5) OVER (PARTITION BY symbol ORDER BY trade_date) * 100 as five_day_return_pct
FROM stock_prices
WHERE symbol = 'AAPL'
ORDER BY

SELECT 
    symbol,
    trade_date,
    close_price,
    LAG(close_price, 1) OVER (PARTITION BY symbol ORDER BY trade_date) as prev_day_close,
    LAG(close_price, 5) OVER (PARTITION BY symbol ORDER BY trade_date) as five_days_ago,
    LEAD(close_price, 1) OVER (PARTITION BY symbol ORDER BY trade_date) as next_day_close,
    -- Calculate daily return
    (close_price - LAG(close_price, 1) OVER (PARTITION BY symbol ORDER BY trade_date)) 
        / LAG(close_price, 1) OVER (PARTITION BY symbol ORDER BY trade_date) * 100 as daily_return_pct,
    -- Calculate 5-day return
    (close_price - LAG(close_price, 5) OVER (PARTITION BY symbol ORDER BY trade_date)) 
        / LAG(close_price, 5) OVER (PARTITION BY symbol ORDER BY trade_date) * 100 as five_day_return_pct
FROM stock_prices
WHERE symbol = 'AAPL'
ORDER BY

Sample Data (stock_prices table):

symbol	trade_date	close_price
AAPL	2024-01-15	185.50
AAPL	2024-01-16	187.20
AAPL	2024-01-17	186.80
AAPL	2024-01-18	188.90
AAPL	2024-01-19	190.10
AAPL	2024-01-22	191.50

Result:

symbol	trade_date	close_price	prev_day_close	five_days_ago	next_day_close	daily_return_pct	five_day_return_pct
AAPL	2024-01-15	185.50	NULL	NULL	187.20	NULL	NULL
AAPL	2024-01-16	187.20	185.50	NULL	186.80	0.92	NULL
AAPL	2024-01-17	186.80	187.20	NULL	188.90	-0.21	NULL
AAPL	2024-01-18	188.90	186.80	NULL	190.10	1.12	NULL
AAPL	2024-01-19	190.10	188.90	NULL	191.50	0.64	NULL
AAPL	2024-01-22	191.50	190.10	185.50	NULL	0.74	3.23

Using Default Values

All Platforms:

SELECT 
    customer_id,
    order_date,
    order_total,
    -- Use 0 instead of NULL for first order
    LAG(order_total, 1, 0) OVER (PARTITION BY customer_id ORDER BY order_date) as previous_order,
    order_total - LAG(order_total, 1, 0) OVER (PARTITION BY customer_id ORDER BY order_date) as spending_change
FROM orders
ORDER BY customer_id,

SELECT 
    customer_id,
    order_date,
    order_total,
    -- Use 0 instead of NULL for first order
    LAG(order_total, 1, 0) OVER (PARTITION BY customer_id ORDER BY order_date) as previous_order,
    order_total - LAG(order_total, 1, 0) OVER (PARTITION BY customer_id ORDER BY order_date) as spending_change
FROM orders
ORDER BY customer_id,

SELECT 
    customer_id,
    order_date,
    order_total,
    -- Use 0 instead of NULL for first order
    LAG(order_total, 1, 0) OVER (PARTITION BY customer_id ORDER BY order_date) as previous_order,
    order_total - LAG(order_total, 1, 0) OVER (PARTITION BY customer_id ORDER BY order_date) as spending_change
FROM orders
ORDER BY customer_id,

Sample Data:

customer_id	order_date	order_total
101	2024-01-15	250.00
101	2024-02-10	180.00
102	2024-01-20	450.00

Result:

customer_id	order_date	order_total	previous_order	spending_change
101	2024-01-15	250.00	0.00	250.00
101	2024-02-10	180.00	250.00	-70.00
102	2024-01-20	450.00	0.00	450.00

Common Use Cases

Time-series analysis: Calculate changes over time
Financial analysis: Stock returns, revenue growth
Cohort analysis: Compare user behavior over time
Anomaly detection: Identify unusual changes
Trend analysis: Identify improving/declining patterns
Session analysis: Time between events

LAG vs LEAD Comparison

Aspect	LAG	LEAD
Direction	Looks backward (previous rows)	Looks forward (next rows)
First row	Returns NULL or default	Returns actual value
Last row	Returns actual value	Returns NULL or default
Use case	Compare with historical data	Predict or look ahead

Performance Tips

Use PARTITION BY: Limits window scope for better performance
Index ORDER BY columns: Improves sorting performance
Reuse window definitions: Define window once if using multiple times
Avoid in subqueries: Calculate in main query when possible
Use CTEs: Make complex LAG/LEAD queries more readable

Best Practices

Always specify ORDER BY: Required for meaningful results
Handle NULLs: Use default values or COALESCE
Use appropriate offset: 1 for immediate previous/next, higher for longer lookback
Combine with CASE: Create meaningful indicators
Document window logic: Complex partitioning should be explained

Interested to Learn More?
Try Out the Free 14-Days Trial

Start free trial

Learn

Feb 18, 2026

BigQuery Global Queries: How to Run Cross-Region SQL in 2026

Analytics

Feb 18, 2026

Context Engineering and AI Quality for Data Teams

Product

Feb 18, 2026

Accelerate Analytics Development with Paradime and Tableau

Product

Feb 18, 2026

Accelerate Analytics Development with Paradime and Tableau

Experience Analytics for the AI-Era

Start your 14-day trial today - it's free and no credit card needed

Start for free

Experience Analytics for the AI-Era

Start your 14-day trial today - it's free and no credit card needed

Start for free

Experience Analytics for the AI-Era

Start your 14-day trial today - it's free and no credit card needed

Start for free

Platform

Resources

ADD-ONs

Industries

About

Legal

Made with ❤️ in San Francisco ・ London

*dbt® and dbt Core® are federally registered trademarks of dbt Labs, Inc. in the United States and various jurisdictions around the world. Paradime is not a partner of dbt Labs. All rights therein are reserved to dbt Labs. Paradime is not a product or service of or endorsed by dbt Labs, Inc.

Start for free

Platform

Resources