MySQL Query to Find Salary difference from Previous Row

Find the difference between Current and Previous Salary

Write a MySQL query to find the difference between the current salary and the previous salary for each employee using a window function.

Note:

The LAG() window function in MySQL allows you to access the value of a column from a previous row within the same result set without using a self-join. It is commonly used to compare the current row's value with a preceding row's value, based on a specified order. For example, you can use LAG() to calculate the difference between a current month's sales and the previous month's sales. The function can also accept an optional offset and default value if the preceding row does not exist.

The OVER() clause in MySQL defines a window or subset of rows for window functions like RANK(), SUM(), or LAG() to operate on. This is done without collapsing the result set. It allows you to perform calculations across a set of rows related to the current row, while still returning individual rows. You can specify partitioning (using PARTITION BY) to divide data into groups and ordering (using ORDER BY) to define the sequence of rows within each group. The OVER() clause is essential for performing advanced analytical tasks, such as running totals, moving averages, or rankings, efficiently.

Solution:

-- Begin the SELECT statement to define the columns to be retrieved from the Employees table
SELECT 
    -- Retrieve the EmployeeID column to uniquely identify each employee
    EmployeeID, 
    -- Retrieve the Name column to display the employee's name
    Name, 
    -- Retrieve the Salary column to show the current salary of the employee
    Salary,
    -- Calculate the difference in Salary between the current row and the previous row
    -- The LAG function returns the Salary from the previous row, ordered by EmployeeID
    -- Subtracting the previous Salary from the current Salary gives the SalaryDifference
    Salary - LAG(Salary, 1) OVER (ORDER BY EmployeeID) AS SalaryDifference
-- Specify the source table containing the employee records
FROM Employees;

Explanation:

• Purpose of the Query:
• The goal is to calculate the difference between the current salary and the previous salary for each employee.
• This demonstrates the use of the LAG() window function to access data from a previous row.
• Key Components:
• LAG(Salary, 1) OVER (ORDER BY EmployeeID): Retrieves the salary from the previous row.
• Salary - LAG(Salary, 1): Calculates the difference between the current salary and the previous salary.
• Why use Window Functions?:
• Window functions allow you to access data from other rows in the result set, making it easier to perform row-to-row comparisons.
• Real-World Application:
• For example, in a company, you might want to analyze salary changes over time to identify trends or anomalies.

For more Practice: Solve these Related Problems:

• Write a MySQL query to compute the difference between an employee’s current and next salary.
• Write a MySQL query to find employees whose salary increased by more than 20% from their previous salary.
• Write a MySQL query to identify employees who have had a salary decrease.
• Write a MySQL query to calculate the percentage change in salary from the previous salary.

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

Previous MySQL Exercise: Calculate Moving Average of Sales.
Next MySQL Exercise: Calculate Cumulative Percentage of Total Sales.