Not Providing Default Values - Causes NULL Issues

-- Missing default value causes NULL in first/last rows

SELECT date, revenue,

LAG(revenue) OVER (ORDER BY date) as prev_revenue,

revenue - LAG(revenue) OVER (ORDER BY date) as growth

FROM monthly_sales;

-- PROBLEM: First row has NULL prev_revenue, growth calculation fails

-- Provide default value to handle edge cases

SELECT date, revenue,

LAG(revenue, 1, 0) OVER (ORDER BY date) as prev_revenue,

revenue - LAG(revenue, 1, 0) OVER (ORDER BY date) as growth

FROM monthly_sales;

-- SUCCESS: First row gets 0 as prev_revenue, calculations work

LAG returns NULL when there's no previous row, LEAD returns NULL when there's no next row. This breaks arithmetic operations and makes results inconsistent. Always provide meaningful default values.

Always use the third parameter: LAG(column, offset, default_value)

High

NULL values in first/last rows break calculations and formatting

Wrong ORDER BY - Breaks Time Series Logic

-- Incorrect ordering breaks time-based analysis

SELECT month, sales,

LAG(sales, 1, 0) OVER (ORDER BY sales DESC) as prev_sales

FROM monthly_data;

-- PROBLEM: Ordered by sales amount, not chronologically

-- Correct chronological ordering for time series

SELECT month, sales,

LAG(sales, 1, 0) OVER (ORDER BY month) as prev_month_sales

FROM monthly_data;

-- SUCCESS: Ordered by time, LAG gets actual previous month

LAG and LEAD are position-based, not value-based. If you ORDER BY sales amount, LAG gets the row with the next-highest sales, not the previous time period. Always ORDER BY your time/sequence column.

For time series: ORDER BY date/time columns, not by the values you're analyzing

Critical

Ordering by wrong column makes LAG/LEAD meaningless for time analysis

Forgetting PARTITION BY for Grouped Analysis

-- Missing PARTITION BY mixes different groups

SELECT product_id, month, sales,

LAG(sales, 1, 0) OVER (ORDER BY month) as prev_sales

FROM product_monthly_sales;

-- PROBLEM: Gets previous month from different product!

-- PARTITION BY keeps comparisons within same group

SELECT product_id, month, sales,