Mastering SQL LEAD and LAG Functions for Row Comparisons

Have you ever needed to answer questions like "What was the sales increase from yesterday?" or "Which customer's order amount dropped compared to their last purchase?"

These questions require comparing a row with its neighboring rows. Before window functions, you'd need complex self-joins. Now, SQL provides two elegant functions: LAG() and LEAD().

Understanding LAG and LEAD

Think of your data as a line of people:

• LAG() looks backward - it grabs data from the person behind you.
• LEAD() looks forward - it grabs data from the person in front of you.

-- LAG: Get the previous row's value
LAG(column_name) OVER (ORDER BY some_column)

-- LEAD: Get the next row's value
LEAD(column_name) OVER (ORDER BY some_column)

Interactive Example: Daily Sales Comparison

Let's track daily sales and calculate the day-over-day change.

Notice how the first row has NULL for prev_day_revenue - there's no row before it!

Handling NULL with Default Values

You can provide a default value as the third argument:

-- If there's no previous row, use 0 instead of NULL
LAG(revenue, 1, 0) OVER (ORDER BY sale_date)

The second argument 1 specifies how many rows to look back (default is 1).

Looking Multiple Rows Back

Want to compare with 7 days ago or 2 rows back? Just change the offset:

Using LEAD to Look Ahead

LEAD works exactly like LAG, but looks at future rows:

PARTITION BY: Restart for Each Group

When analyzing multiple groups (like different stores), use PARTITION BY to restart the comparison within each group:

Without PARTITION BY store_name, Store B's first row would incorrectly compare with Store A's last row!

Real-World Use Case: Calculating Growth Rate

Here's a practical example calculating percentage growth:

SELECT 
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) as prev_month,
  ROUND(
    (revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0 
    / LAG(revenue) OVER (ORDER BY month), 
    2
  ) as growth_percent
FROM monthly_revenue;

LAG vs LAG: Quick Reference

Function	Direction	Use Case
LAG(col)	Backward ←	Compare with previous row
LAG(col, 2)	2 rows back	Week-over-week, custom offset
LEAD(col)	Forward →	Preview next row's value
LEAD(col, 2)	2 rows ahead	Future forecasting

Best Practices

1. Always use ORDER BY: LAG/LEAD need a defined order to know which row is "previous" or "next".

2. Handle NULLs: The first row for LAG (and last for LEAD) will be NULL. Use COALESCE or the third parameter.

3. Use PARTITION BY wisely: When comparing within groups, always partition to avoid cross-group comparisons.

4. Performance: LAG/LEAD are efficient - they don't require self-joins or subqueries.

Conclusion

LAG and LEAD are essential tools for time-series analysis, trend detection, and row comparison. Instead of complex self-joins:

• Use LAG() to look at previous rows
• Use LEAD() to look at next rows
• Add PARTITION BY for group-level comparisons
• Set default values to handle edge cases

Master these functions and you'll write cleaner, faster SQL for any analytical reporting task.