LEAD vs LAG Function in SQL

Let's break down the LAG and LEAD functions with a simple example using a table of employee salaries. Here's a table called employees:

1. Create the Table

To create the employees table, you would use the following SQL statement:

CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(50),
salary DECIMAL(10, 2)
);

This creates a table with:

employee_id as an integer and the primary key (unique identifier).

name as a string (up to 50 characters).

salary as a decimal number to store the employee's salary.

1. Insert Sample Data

Now, let's insert the employee data into this table:

INSERT INTO employees (employee_id, name, salary) VALUES
(1, 'John', 5000.00),
(2, 'Jane', 6000.00),
(3, 'Bob', 5500.00),
(4, 'Alice', 6500.00),
(5, 'Tom', 6200.00);

This inserts the following rows into the employees table:

LAG Example (Look Back at Previous Row)

You want to see the salary of each employee along with the previous employee’s salary. This is where the LAG function is useful.

Query:

SELECT
employee_id,
name,
salary,
LAG(salary, 1, 0) OVER (ORDER BY employee_id) AS previous_salary
FROM employees;

Explanation:

LAG(salary, 1, 0): This tells SQL to look at the salary from the previous row (1 row behind). If there is no previous row, it will return 0 (default value).

OVER (ORDER BY employee_id): This defines how the rows are ordered. Here, we order by employee_id.

Result:

For John (first row), there is no previous row, so previous_salary is 0.

For Jane, her previous salary is John's salary (5000), and so on.

---

LEAD Example (Look Ahead to the Next Row)

Now, you want to see each employee’s salary along with the salary of the next employee. This is where the LEAD function comes into play.

Query:

SELECT
employee_id,
name,
salary,
LEAD(salary, 1, 0) OVER (ORDER BY employee_id) AS next_salary
FROM employees;

Explanation:

LEAD(salary, 1, 0): This tells SQL to look at the salary from the next row (1 row ahead). If there is no next row, it will return 0.

OVER (ORDER BY employee_id): Rows are ordered by employee_id.

Result:

For John, the next salary is Jane's salary (6000).

For Tom, since there is no next row, next_salary is 0.

---

Summary:

LAG helps you look back at the previous row's value.

LEAD helps you look ahead at the next row's value. Both are useful for analyzing trends, comparing values, or filling gaps in data.