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

Handling NULL Values and Edge Cases

Manage NULLs at partition boundaries and use default parameters to handle missing previous or next rows.

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.