Welcome to Subscribe On Youtube
Formatted question description: https://leetcode.ca/all/579.html
579. Find Cumulative Salary of an Employee
Level
Hard
Description
The Employee table holds the salary information in a year.
Write a SQL to get the cumulative sum of an employee’s salary over a period of 3 months but exclude the most recent month.
The result should be displayed by ‘Id’ ascending, and then by ‘Month’ descending.
Example
Input
| Id | Month | Salary |
|----|-------|--------|
| 1 | 1 | 20 |
| 2 | 1 | 20 |
| 1 | 2 | 30 |
| 2 | 2 | 30 |
| 3 | 2 | 40 |
| 1 | 3 | 40 |
| 3 | 3 | 60 |
| 1 | 4 | 60 |
| 3 | 4 | 70 |
Output
| Id | Month | Salary |
|----|-------|--------|
| 1 | 3 | 90 |
| 1 | 2 | 50 |
| 1 | 1 | 20 |
| 2 | 1 | 20 |
| 3 | 3 | 100 |
| 3 | 2 | 40 |
Explanation
Employee ‘1’ has 3 salary records for the following 3 months except the most recent month ‘4’: salary 40 for month ‘3’, 30 for month ‘2’ and 20 for month ‘1’ So the cumulative sum of salary of this employee over 3 months is 90(40+30+20), 50(30+20) and 20 respectively.
| Id | Month | Salary |
|----|-------|--------|
| 1 | 3 | 90 |
| 1 | 2 | 50 |
| 1 | 1 | 20 |
Employee ‘2’ only has one salary record (month ‘1’) except its most recent month ‘2’.
| Id | Month | Salary |
|----|-------|--------|
| 2 | 1 | 20 |
Employ ‘3’ has two salary records except its most recent pay month ‘4’: month ‘3’ with 60 and month ‘2’ with 40. So the cumulative salary is as following.
| Id | Month | Salary |
|----|-------|--------|
| 3 | 3 | 100 |
| 3 | 2 | 40 |
Solution
First, for each employee, find the maximum month. Secondly, for each employee, find the entries that are before the maximum month. Thirdly, for each such entry, calculate the cumulative sum of salary. Each employee’s number of entries is limited to 3, sorted by Id in ascending order and then by Month in descending order.
# Write your MySQL query statement below
select temp2.Id, temp2.Month, (
select sum(e2.Salary) from Employee e2
where e2.Id = temp2.Id and e2.Month between temp2.Month - 2 and temp2.Month
order by e2.Month desc limit 3
) as Salary from (
select e1.Id, e1.Month, e1.Salary from Employee e1, (
select e.Id, max(e.Month) as Month from Employee e group by e.Id
) temp1
where e1.Id = temp1.Id and e1.Month < temp1.Month
order by e1.Id asc, e1.Month desc
) temp2;