Handling NULL Values and Edge Cases
You'll learn how to safely handle situations where LAG or LEAD functions reach the boundaries of your data partitions or encounter missing values.
When using LAG or LEAD, you often hit edge cases: the first row has no previous value, the last row has no next value, and sometimes your data simply contains NULLs. By default, these functions return NULL when they can't find a value, which can break calculations or produce misleading results.
The Problem
Imagine calculating month-over-month growth. For January (the first row in your partition), LAG returns NULL because there's no previous month. If you divide by NULL, your entire calculation becomes NULL. Similarly, if your data has missing months, LAG might return NULL even mid-dataset.
The Solution: Default Parameters
Both LAG and LEAD accept optional default values as their third parameter:
LAG(column_name, offset, default_value) OVER (...)
LEAD(column_name, offset, default_value) OVER (...)
Example:
SELECT
month,
revenue,
LAG(revenue, 1, 0) OVER (ORDER BY month) AS prev_revenue,
revenue - LAG(revenue, 1, revenue) OVER (ORDER BY month) AS change
FROM sales;
Here, LAG(revenue, 1, 0) returns 0 instead of NULL for the first row, preventing division errors. Alternatively, LAG(revenue, 1, revenue) returns the current revenue as a safe fallback, making the change calculation return 0 for the first row.
Think of It Like a Safety Net
Default parameters are like having a backup answer when the question doesn't apply—"What did I eat yesterday?" gets "nothing recorded" instead of breaking your diet tracker.
Key Takeaway: Always specify default values in LAG and LEAD to handle partition boundaries and missing data gracefully, preventing NULL propagation in downstream calculations.