Leetcode 579. Find Cumulative Salary of an Employee

drop table employees2

Create table employees2(Id int,[Month] int,salary int);

insert into employees2 values(1,1,20);
insert into employees2 values(2,1,20);
insert into employees2 values(1,2,30);
insert into employees2 values(2,2,30);
insert into employees2 values(3,2,40);
insert into employees2 values(1,3,40);
insert into employees2 values(3,3,60);
insert into employees2 values(1,4,60);
insert into employees2 values(3,4,70);

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:

with cumulativeSalary as(
select e1.Id,e1.Month,
isnull(e1.salary,0)+ISNULL(e2.salary,0)+ISNULL(e3.salary,0) as CumulativeSalary
from employees2 e1 
left join employees2 e2 on e1.Id=e2.Id and e2.Month=e1.Month-1
left join employees2 e3 on e3.Id=e2.Id and e3.Month=e2.Month-1
),MostRecentMonth as
(
select id,max(Month) as MaxMonth from employees2 group by Id having(count(*)>1)
)
select c.Id,c.Month,c.CumulativeSalary from cumulativeSalary c join MostRecentMonth m on c.Id=m.Id and m.MaxMonth>c.Month
order by Id asc, Month desc

 

Windows Function:

SELECT id, month, Salary
FROM
(
SELECT  id, 
        month, 
		-- Every 3 months. ROWS 2 PRECEDING indicates the number of rows or values to precede the current row (1 + 2)
        SUM(salary) OVER(PARTITION BY id  ORDER BY month ROWS 2 PRECEDING) as Salary, 
        DENSE_RANK() OVER(PARTITION BY id ORDER by month DESC) month_no
FROM Employee
)  src
--  exclude the most recent month
where month_no > 1
ORDER BY id , month desc