Find Cumulative Salary Of An Employee Problem
Description
LeetCode Problem 579.
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
1
2
3
4
5
6
7
8
9
10
11
| 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
1
2
3
4
5
6
7
8
| 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.
1
2
3
4
5
| 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’.
1
2
3
| 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.
1
2
3
4
| Id | Month | Salary |
|----|-------|--------|
| 3 | 3 | 100 |
| 3 | 2 | 40 |
MySQL Solution
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select e1.id, e1.month,
(ifnull(e1.salary, 0) + ifnull(e2.salary, 0) + ifnull(e3.salary, 0)) as Salary
from
(select id, max(month) as month
from Employee
group by id
having count(*) > 1) as maxmonth
left join
Employee e1 on (maxmonth.id = e1.id
and maxmonth.month > e1.month)
left join
Employee e2 on (e2.id = e1.id
and e2.month = e1.month - 1)
left join
Employee e3 on (e3.id = e1.id
and e3.month = e1.month - 2)
order by id, month desc