Leetcode - 579. Find Cumulative Salary of an Employee

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     |

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     |

---

---

Make clear: get the cumulative sum of an employee's salary over a period of 3 months 是指每个月都计算与其相邻的三个月的总和，如果不满三个月则计算全部的总和。

思路：

1. 首先exclude the most recent month

select e1.Id, e1.Month, e1.Salary
from Employee e1, 
(select Id, max(Month) as max_month
from Employee
group by Id
order by Month desc) e2
where e1.Id = e2.Id
and e1.Month != e2.max_month

得到table如下：

Id	Month	Salary
1	1	20
2	1	20
1	2	30
3	2	40
1	3	40
3	3	60

2. 通过self join on a.Id = b.Id and a.Month >= b.Month 得到table如下：

Id1	Month1	Salary1	Id2	Month2	Salary2
1	1	20	1	1	20
1	2	30	1	1	20
1	2	30	1	2	30
1	3	40	1	1	20
1	3	40	1	2	30
1	3	40	1	3	40
2	1	20	2	1	20
3	2	40	3	2	40
3	3	60	3	2	40
3	3	60	3	3	60

可以看到，如果group by id1 and Month1的话，sum(Salary2)则为cumulative salary。但要注意的是，只cumulative相邻的三个月，即Month1和Month2的差值应小于3.

coding 如下：

select a.Id as id, a.Month as month, sum(b.Salary) as Salary
from 

(select e1.Id, e1.Month, e1.Salary
from Employee e1, 
(select Id, max(Month) as max_month
from Employee
group by Id
order by Month desc) e2
where e1.Id = e2.Id
and e1.Month != e2.max_month) a,

(select e1.Id, e1.Month, e1.Salary
from Employee e1, 
(select Id, max(Month) as max_month
from Employee
group by Id
order by Month desc) e2
where e1.Id = e2.Id
and e1.Month != e2.max_month) b

where a.Id = b.Id
and a.Month >= b.Month
and a.Month - b.Month < 3
group by a.Id, a.Month
order by a.Id, a.Month desc