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     |

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     |


b'
\n\n

Solution

\n
\n

Approach: Using OUTER JOIN and temporary tables [Accepted]

\n

Intuition

\n

Solve this issue by two steps. The first one is to get the cumulative sum of an employee\'s salary over a period of 3 months, and then exclude the most recent month from the result.

\n

Algorithm

\n

If you feel hard to work out how to get the cumulative sum of an employee\'s salary over a period of 3 months, think about 2 months as a start. By joining this Employee table with itself, you can get salary information for one more month.

\n
SELECT *\nFROM\n    Employee E1\n        LEFT JOIN\n    Employee E2 ON (E2.id = E1.id\n        AND E2.month = E1.month - 1)\nORDER BY E1.id ASC , E1. month DESC\n
\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
IdMonthSalaryIdMonthSalary
14601340
13401230
12301120
1120
22302120
2120
34703360
33603240
3240
>Note:
> - The blank value in the output is actually NULL in the database.
> - The first three columns are from E1, and the rest ones are from E2.
\n

Then we can add the salary to get the cumulative sum for 2 months.

\n
SELECT\n    E1.id,\n    E1.month,\n    (IFNULL(E1.salary, 0) + IFNULL(E2.salary, 0)) AS Salary\nFROM\n    Employee E1\n        LEFT JOIN\n    Employee E2 ON (E2.id = E1.id\n        AND E2.month = E1.month - 1)\nORDER BY E1.id ASC , E1.month DESC\n
\n
| id | month | Salary |\n|----|-------|--------|\n| 1  | 4     | 100    |\n| 1  | 3     | 70     |\n| 1  | 2     | 50     |\n| 1  | 1     | 20     |\n| 2  | 2     | 50     |\n| 2  | 1     | 20     |\n| 3  | 4     | 130    |\n| 3  | 3     | 100    |\n| 3  | 2     | 40     |\n
\n

Similarly, you can join this table one more time to get the cumulative sum for 3 months.

\n
SELECT\n    E1.id,\n    E1.month,\n    (IFNULL(E1.salary, 0) + IFNULL(E2.salary, 0) + IFNULL(E3.salary, 0)) AS Salary\nFROM\n    Employee E1\n        LEFT JOIN\n    Employee E2 ON (E2.id = E1.id\n        AND E2.month = E1.month - 1)\n        LEFT JOIN\n    Employee E3 ON (E3.id = E1.id\n        AND E3.month = E1.month - 2)\nORDER BY E1.id ASC , E1.month DESC\n;\n
\n
| id | month | Salary |\n|----|-------|--------|\n| 1  | 4     | 130    |\n| 1  | 3     | 90     |\n| 1  | 2     | 50     |\n| 1  | 1     | 20     |\n| 2  | 2     | 50     |\n| 2  | 1     | 20     |\n| 3  | 4     | 170    |\n| 3  | 3     | 100    |\n| 3  | 2     | 40     |\n
\n

In addition, we have to exclude the most recent month as required. If we have a temp table including every id and most recent month like below, then we can easily opt out these months by join it with the above table.

\n
| id | month |\n|----|-------|\n| 1  | 4     |\n| 2  | 2     |\n| 3  | 4     |\n
\n

Here is the code to generate this table.

\n
SELECT\n    id, MAX(month) AS month\nFROM\n    Employee\nGROUP BY id\nHAVING COUNT(*) > 1\n;\n
\n

At last, we can join them together and get the desired cumulative sum of an employee\'s salary over a period of 3 months excluding the most recent one.

\n

MySQL

\n
SELECT\n    E1.id,\n    E1.month,\n    (IFNULL(E1.salary, 0) + IFNULL(E2.salary, 0) + IFNULL(E3.salary, 0)) AS Salary\nFROM\n    (SELECT\n        id, MAX(month) AS month\n    FROM\n        Employee\n    GROUP BY id\n    HAVING COUNT(*) > 1) AS maxmonth\n        LEFT JOIN\n    Employee E1 ON (maxmonth.id = E1.id\n        AND maxmonth.month > E1.month)\n        LEFT JOIN\n    Employee E2 ON (E2.id = E1.id\n        AND E2.month = E1.month - 1)\n        LEFT JOIN\n    Employee E3 ON (E3.id = E1.id\n        AND E3.month = E1.month - 2)\nORDER BY id ASC , month DESC\n;\n
\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
idmonthSalary
1390
1250
1120
2120
33100
3240
\n

Note: Thank @xiaxin for providing this elegant solution.

\n
'