Using LAG and LEAD Functions to Compare Employee Salaries

Use LAG and LEAD Functions

Write a SQL query to compare an employee's salary with the previous and next employee's salary using LAG and LEAD.

Solution:

-- Compare an employee's salary with the previous and next employee's salary.
SELECT 
    EmployeeID,
    Name,
    Salary,
    LAG(Salary) OVER (ORDER BY Salary) AS PreviousSalary,
    LEAD(Salary) OVER (ORDER BY Salary) AS NextSalary
FROM Employees;

Explanation:

1. Purpose of the Query :

1. The goal is to demonstrate how to use the LAG and LEAD functions to access data from previous and next rows in a result set.

2. Key Components :

1. LAG(Salary) : Retrieves the salary from the previous row.
2. LEAD(Salary) : Retrieves the salary from the next row.
3. OVER (ORDER BY Salary) : Specifies the order of rows for comparison.

3. Why use LAG and LEAD? :

1. These functions allow you to perform row-based comparisons without self-joins, making queries more efficient and readable.

4. Real-World Application :

1. For example, in financial systems, you might use this query to analyze trends in salary changes over time.

Additional Notes:

• Ensure that the ordering (ORDER BY) is meaningful for the analysis.
• Use this exercise to teach how to compare rows without complex joins.

For more Practice: Solve these Related Problems:

• Write a SQL query to compare an employee's salary with the previous employee's salary within the same department.
• Write a SQL query to calculate the difference between an employee's salary and the next employee's salary based on seniority.
• Write a SQL query to identify employees whose salary is higher than both the previous and next employees' salaries.
• Write a SQL query to find the percentage change in salary between consecutive employees based on their hire date.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Query Nested JSON Arrays.
Next SQL Exercise: Create a Recursive CTE for Hierarchical Data.