SQL - Find Cumulative Salary of an Employee

Find Cumulative Salary of an Employee

Hard

 

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

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.

## This code is not what the question asked! this is to show the difference!

SELECT 
    Id, Month, Salary
FROM  
   (SELECT 
        Id, Month, Salary,
        @cum := IF(@prev = Id, @cum + Salary, Salary) as cumsum, 
        @prev := Id
    FROM 
        Employee, 
        (SELECT @curr:= null, @prev:= null, @cum := 0)f
    WHERE 
       (Id, Month) NOT IN 
               (SELECT 
                   Id,
                   MAX(Month)
               FROM 
                   Employee
               GROUP BY 
                   Id)
        Id = 1
    ORDER BY 
        Id, Month ) temp
ORDER BY 
    Id, Month DESC

The above query will provide following result:

{"headers": ["Id", "Month", "Salary"], "values": [[1, 4, 150.0], [1, 3, 90.0], [1, 2, 50.0], [1, 1, 20.0], [2, 1, 20.0], [3, 3, 100.0], [3, 2, 40.0]]}

When the CORRECT ANSWER is followed:

{"headers": ["id", "month", "Salary"], "values": [[1, 4, 130], [1, 3, 90], [1, 2, 50], [1, 1, 20], [2, 1, 20], [3, 3, 100], [3, 2, 40]]}

 

THIS IS CALLED MOVING TOTAL

 

By saying "most recent 3 months", the contributor refers to a window-size of maximum 3 months. for example, employe1's salary of month 1,2,3,4,5. you should exclude 5 first, then calculate 1, 1 and 2, 1 and 2 and 3, 2and 3 and 4 --- 3 months max. That's how you get 130 rather than 150 in one of the testcase.

 

Here is my easy solution to deal with that problem!

SELECT
    e.Id, e.Month, 
    SUM(e2.Salary) as Salary 
FROM 
    Employee e JOIN Employee e2
    ON (e.Id = e2.Id AND e.Month >= e2.Month)
WHERE 
    (e.Id, e.Month) NOT IN 
        (SELECT Id,MAX(Month) FROM Employee GROUP BY Id)
    AND 
    e.Month - e2.Month < 3
GROUP BY 
    1,2
ORDER BY 
    1,2 DESC

Now, let's take a look at how this generates the solution!

 

the JOINed table looks like this:

From this table, I start stipulating the conditions for the calculation!

 - Condition 1.

Id, Month NOT IN (SELECT MAX(month) FROM Employee GROUP BY Id)

- Condition 2.

e.Month - e2.Month < 3