Using LEAD() and LAG() in SQL for Comparative Queries

Table of Contents

1. Introduction
2. What Are LEAD() and LAG() Functions?
3. Why Use These Functions?
4. Syntax of LEAD() and LAG()
5. Basic LAG() Example
6. Basic LEAD() Example
7. Comparing Current and Previous/Next Rows
8. Using PARTITION BY in LEAD()/LAG()
9. Using ORDER BY Correctly
10. Calculating Differences (Growth, Change)
11. Real-World Example: Sales Trends Over Time
12. Real-World Example: Employee Salary Change
13. Handling NULLs in Missing Rows
14. Using LAG()/LEAD() with Multiple Columns
15. Nesting with CASE for Conditional Differences
16. Performance Considerations
17. Common Mistakes and Debugging Tips
18. Best Practices for Comparative Window Queries
19. Other Useful Lead-Lag Variants (FIRST_VALUE, LAST_VALUE)
20. Summary and What’s Next

1. Introduction

When analyzing time series or sequential data, we often need to compare a row with its previous or next row. SQL’s LEAD() and LAG() window functions make such comparisons easy and elegant — without writing complex subqueries or joins.

2. What Are LEAD() and LAG() Functions?

• LAG() accesses data from a previous row
• LEAD() accesses data from a following row

Both functions are part of SQL’s window functions category and help track changes, trends, and comparisons across rows.

3. Why Use These Functions?

• Calculate month-over-month sales change
• Find salary change between job roles
• Track movement in rankings
• Compare consecutive values in logs or events

4. Syntax of LEAD() and LAG()

sqlCopyEditLEAD(column, offset, default) OVER (
  PARTITION BY column
  ORDER BY column
)

LAG(column, offset, default) OVER (
  PARTITION BY column
  ORDER BY column
)

• offset = how many rows ahead/behind (default: 1)
• default = value if there’s no row (e.g., first/last row)

5. Basic LAG() Example

sqlCopyEditSELECT name, salary,
  LAG(salary, 1) OVER (ORDER BY hire_date) AS previous_salary
FROM employees;

Adds a column showing the previous employee’s salary by hire date.

6. Basic LEAD() Example

sqlCopyEditSELECT name, salary,
  LEAD(salary, 1) OVER (ORDER BY hire_date) AS next_salary
FROM employees;

Adds a column showing the next employee’s salary.

7. Comparing Current and Previous/Next Rows

sqlCopyEditSELECT name, salary,
  salary - LAG(salary) OVER (ORDER BY hire_date) AS salary_diff
FROM employees;

Calculates difference from the previous row.

8. Using PARTITION BY in LEAD()/LAG()

sqlCopyEditSELECT department, name, salary,
  LAG(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS prev_salary
FROM employees;

Applies comparison within each department.

9. Using ORDER BY Correctly

ORDER BY defines the row sequence for comparison.
Wrong ordering leads to incorrect lead/lag logic.

sqlCopyEdit-- Correct: Compare salaries over time
ORDER BY hire_date

-- Wrong: Alphabetical order may not reflect real timeline
ORDER BY name

10. Calculating Differences (Growth, Change)

sqlCopyEditSELECT product_id, month, sales,
  sales - LAG(sales) OVER (PARTITION BY product_id ORDER BY month) AS change
FROM monthly_sales;

This shows monthly change in sales per product.

11. Real-World Example: Sales Trends Over Time

sqlCopyEditSELECT store_id, sale_date, revenue,
  revenue - LAG(revenue) OVER (PARTITION BY store_id ORDER BY sale_date) AS revenue_diff
FROM store_sales;

Helps in building trend analysis dashboards.

12. Real-World Example: Employee Salary Change

sqlCopyEditSELECT employee_id, salary, title, effective_date,
  LAG(salary) OVER (PARTITION BY employee_id ORDER BY effective_date) AS previous_salary,
  salary - LAG(salary) OVER (PARTITION BY employee_id ORDER BY effective_date) AS raise
FROM employee_history;

Detects salary increments or demotions over time.

13. Handling NULLs in Missing Rows

Use the default argument:

sqlCopyEditLAG(salary, 1, 0) OVER (...) -- Defaults to 0 instead of NULL

This is useful when you want to calculate metrics like growth percentages safely.

14. Using LAG()/LEAD() with Multiple Columns

Each column must have its own LAG()/LEAD():

sqlCopyEditSELECT id, month,
  LAG(sales) OVER (ORDER BY month) AS prev_sales,
  LAG(profit) OVER (ORDER BY month) AS prev_profit
FROM business_data;

15. Nesting with CASE for Conditional Differences

sqlCopyEditSELECT name, department, salary,
  CASE
    WHEN salary > LAG(salary) OVER (PARTITION BY department ORDER BY salary)
    THEN 'Increased'
    WHEN salary < LAG(salary) OVER (PARTITION BY department ORDER BY salary)
    THEN 'Decreased'
    ELSE 'Same'
  END AS trend
FROM employees;

Labels each salary change as an increase, decrease, or same.

16. Performance Considerations

• Ensure appropriate indexes on ORDER BY columns
• Use filters or limits with large datasets
• Avoid using LAG()/LEAD() in WHERE directly — use CTEs or subqueries

17. Common Mistakes and Debugging Tips

18. Best Practices for Comparative Window Queries

• Always define ORDER BY explicitly
• Use PARTITION BY when comparing within groups
• Alias calculated differences clearly (sales_diff, rank_gap)
• Wrap complex lead/lag logic inside CTEs for reusability
• Test edge cases (first/last row, NULLs)

19. Other Useful Lead-Lag Variants

• FIRST_VALUE() – First value in window
• LAST_VALUE() – Last value in window
• NTH_VALUE(column, n) – nth value in the window

These work with OVER(PARTITION BY … ORDER BY …) just like LEAD() and LAG().

20. Summary and What’s Next

LEAD() and LAG() are invaluable for comparing sequential rows in SQL — perfect for detecting trends, changes, and patterns. They simplify logic that would otherwise require self-joins or subqueries, making SQL both cleaner and more powerful.