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).

SUM() OVER() is a window function often used to calculate a running total without grouping rows.

 

SUM() OVER() is a window function often used to calculate a running total without grouping rows.

Sample Employees Table

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

Running Total of Salaries

SELECT
    EmployeeID,
    EmployeeName,
    Salary,
    SUM(Salary) OVER (
        ORDER BY EmployeeID
    ) AS RunningTotal
FROM Employees;

Output

EmployeeID	EmployeeName	Salary	RunningTotal
1	Alice	3000	3000
2	Bob	4000	7000
3	Charlie	2500	9500
4	David	5000	14500
5	Emma	3500	18000

How it works

For each row:

• Alice → 3000

• Alice + Bob → 7000

• Alice + Bob + Charlie → 9500

• Alice + Bob + Charlie + David → 14500

• Alice + Bob + Charlie + David + Emma → 18000

The window function is effectively doing:

SUM(Salary) OVER (
    ORDER BY EmployeeID
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

which means:

• Start from the first row (UNBOUNDED PRECEDING)

• Keep adding values up to the current row (CURRENT ROW)

Running Total by Department

Suppose the table has a department column:

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

SELECT
    EmployeeID,
    EmployeeName,
    Department,
    Salary,
    SUM(Salary) OVER (
        PARTITION BY Department
        ORDER BY EmployeeID
    ) AS DeptRunningTotal
FROM Employees;

Output

EmployeeID	EmployeeName	Department	Salary	DeptRunningTotal
1	Alice	HR	3000	3000
2	Bob	HR	4000	7000
5	Emma	HR	3500	10500
3	Charlie	IT	2500	2500
4	David	IT	5000	7500

Here, PARTITION BY Department restarts the running total for each department.

Formula:

SUM(column_name) OVER (
    [PARTITION BY partition_column]
    ORDER BY order_column
)

This is one of the most common SQL interview questions involving window functions.