LEAD vs LAG in SQL

LEAD vs LAG in SQL (Track Changes Like a Pro)

After mastering ranking functions, the next must-know SQL window functions for analytics and interviews are
👉 LEAD() and LAG().

These functions help you compare a row with its previous or next row—a very common requirement in time-series analysis, reporting, and analytics.

🧠 Problem Statement

👉 Given employee salaries ordered by year, find the salary change compared to the previous year.

Sample data:

🔙 Using LAG() (Previous Row)

SELECT year,
       salary,
       LAG(salary) OVER (ORDER BY year) AS prev_salary
FROM employee_salary;

📌 Output Explanation

• LAG() fetches the value from the previous row
• First row returns NULL (no previous record)

Result logic:

• 2021 → NULL
• 2022 → 70,000
• 2023 → 80,000

➕ Calculate Salary Increment (Real Use Case)

SELECT year,
       salary,
       salary - LAG(salary) OVER (ORDER BY year) AS salary_increment
FROM employee_salary;

✔ Commonly used in:

• Year-over-year growth
• Revenue comparison
• Trend analysis

🔜 Using LEAD() (Next Row)

SELECT year,
       salary,
       LEAD(salary) OVER (ORDER BY year) AS next_salary
FROM employee_salary;

📌 What LEAD() Does

• Fetches the next row’s value
• Last row returns NULL

Useful for:

• Forecast comparisons
• Finding upcoming changes
• Session analysis

🆚 LEAD vs LAG (Quick Comparison)

💡 Interview Tip

👉 If a problem mentions:

• Previous value → think LAG()
• Next value → think LEAD()

These functions avoid self-joins, making queries:

• Cleaner
• Faster
• Easier to read

💬 Interview Question for You

How would you find consecutive login days for users using LAG()?

Think gaps, dates, and window functions 👀