LAG / LEAD

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
)

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;

Sample Data (monthly_sales table):

Result:

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, price_change_date;

Sample Data (product_price_history table):

Result:

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, review_date;

Sample Data (performance_reviews table):

Result:

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 trade_date;

Sample Data (stock_prices table):

Result:

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, order_date;

Sample Data:

Result:

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

Performance Tips

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

Best Practices

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