LEAD() and LAG() are window functions

 

LEAD() and LAG() are window functions used to access values from the next row or previous row without using a self-join.

Sample Employees Table

EmployeeID	EmployeeName	Salary
1	Alice	3000
2	Bob	4000
3	Charlie	2500
4	David	5000
5	Emma	3500

---

1. LAG() Example

Get the previous employee's salary:

SELECT
    EmployeeID,
    EmployeeName,
    Salary,
    LAG(Salary) OVER (
        ORDER BY EmployeeID
    ) AS PreviousSalary
FROM Employees;

Output

EmployeeID	EmployeeName	Salary	PreviousSalary
1	Alice	3000	NULL
2	Bob	4000	3000
3	Charlie	2500	4000
4	David	5000	2500
5	Emma	3500	5000

LAG() looks backward.

---

2. LEAD() Example

Get the next employee's salary:

SELECT
    EmployeeID,
    EmployeeName,
    Salary,
    LEAD(Salary) OVER (
        ORDER BY EmployeeID
    ) AS NextSalary
FROM Employees;

Output

EmployeeID	EmployeeName	Salary	NextSalary
1	Alice	3000	4000
2	Bob	4000	2500
3	Charlie	2500	5000
4	David	5000	3500
5	Emma	3500	NULL

LEAD() looks forward.

---

3. Compare Salary with Previous Employee

SELECT
    EmployeeID,
    EmployeeName,
    Salary,
    LAG(Salary) OVER (ORDER BY EmployeeID) AS PrevSalary,
    Salary - LAG(Salary) OVER (ORDER BY EmployeeID) AS Difference
FROM Employees;

Output

Employee	Salary	PrevSalary	Difference
Alice	3000	NULL	NULL
Bob	4000	3000	1000
Charlie	2500	4000	-1500
David	5000	2500	2500
Emma	3500	5000	-1500

Useful for salary growth/decline analysis.

---

4. Using Offset

Previous 2nd Employee Salary

SELECT
    EmployeeID,
    EmployeeName,
    Salary,
    LAG(Salary, 2) OVER (
        ORDER BY EmployeeID
    ) AS Salary2RowsBack
FROM Employees;

Output:

Employee	Salary	Salary2RowsBack
Alice	3000	NULL
Bob	4000	NULL
Charlie	2500	3000
David	5000	4000
Emma	3500	2500

---

5. Default Value Example

Instead of NULL:

SELECT
    EmployeeID,
    EmployeeName,
    Salary,
    LAG(Salary, 1, 0) OVER (
        ORDER BY EmployeeID
    ) AS PreviousSalary
FROM Employees;

Output:

Employee	Salary	PreviousSalary
Alice	3000	0
Bob	4000	3000
Charlie	2500	4000
David	5000	2500
Emma	3500	5000

---

Syntax

LAG

LAG(column_name, offset, default_value)
OVER (ORDER BY column_name)

LEAD

LEAD(column_name, offset, default_value)
OVER (ORDER BY column_name)

Interview Use Cases

• Compare current row with previous row.

• Find salary increase/decrease.

• Calculate month-over-month sales growth.

• Detect gaps in dates or sequences.

• Compare employee performance with previous/next employee.

• Find consecutive records (attendance, login history, transactions).