Course contentsShow
← Data Science
Lesson 1026 of 2,14526. Advanced SQLFree lesson

Calculating Period-over-Period Changes

Use LAG to compute differences or percent changes between consecutive time periods or records.

Calculating Period-over-Period Changes

You'll learn how to use LAG to measure how much a value has changed from one time period to the next.

Now that you can access previous row values with LAG, one of its most powerful applications is computing differences or percent changes between consecutive time periods. This is essential for tracking growth rates, detecting trends, and analyzing business metrics over time.

The Core Idea

Think of comparing your bank balance this month versus last month. The difference tells you if you're saving or spending. In SQL, LAG fetches "last month's" value, then you subtract it from the current value to see the change.

For absolute change:

current_value - LAG(current_value) OVER (ORDER BY time_period)

For percent change:

(current_value - LAG(current_value) OVER (ORDER BY time_period)) 
  / LAG(current_value) OVER (ORDER BY time_period) * 100

Practical Example

SELECT 
    month,
    revenue,
    revenue - LAG(revenue) OVER (ORDER BY month) AS revenue_change,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month)) 
        / LAG(revenue) OVER (ORDER BY month) * 100, 
        2
    ) AS pct_change
FROM monthly_sales
ORDER BY month;

This shows each month's revenue alongside its dollar change and percentage change from the previous month. The first row will have NULL for changes since there's no prior period.

Handling NULL Values

Always consider that the first row's LAG returns NULL. You might use COALESCE or filter these out depending on your analysis needs.

Key Takeaway: Use LAG with subtraction to calculate absolute period-over-period changes, and divide by the lagged value (times 100) to compute percentage changes—essential techniques for time-series and trend analysis.