Description
Table: Employee
| Column Name | Type |
|---|---|
| id | int |
| month | int |
| salary | int |
- (
id,month) is the primary key (combination of columns with unique values) for this table. - Each row in the table indicates the
salaryof an employee in onemonthduring the year 2020.
Problem Statement
Write a solution to calculate the cumulative salary summary for every employee in a single unified table.
The cumulative salary summary for an employee can be calculated as follows:
- For each month that the employee worked, sum up the salaries in that month and the previous two months. This is their 3-month sum for that month. If an employee did not work for the company in previous months, their effective salary for those months is
0. - Do not include the 3-month sum for the most recent month that the employee worked for in the summary.
- Do not include the 3-month sum for any month the employee did not work.
Return the result table ordered by id in ascending order. In case of a tie, order it by month in descending order.
The result format is in the following example.
Example 1:
Input:
Employeetable:
| 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 |
| 1 | 7 | 90 |
| 1 | 8 | 90 |
Output:
| id | month | Salary |
|---|---|---|
| 1 | 7 | 90 |
| 1 | 4 | 130 |
| 1 | 3 | 90 |
| 1 | 2 | 50 |
| 1 | 1 | 20 |
| 2 | 1 | 20 |
| 3 | 3 | 100 |
| 3 | 2 | 40 |
Explanation: Employee ‘1’ has five salary records excluding their most recent month ‘8’:
- 90 for month ‘7’.
- 60 for month ‘4’.
- 40 for month ‘3’.
- 30 for month ‘2’.
- 20 for month ‘1’.
So the cumulative salary summary for this employee is:
| id | month | salary | Calculation |
|---|---|---|---|
| 1 | 7 | 90 | (90 + 0 + 0) |
| 1 | 4 | 130 | (60 + 40 + 30) |
| 1 | 3 | 90 | (40 + 30 + 20) |
| 1 | 2 | 50 | (30 + 20 + 0) |
| 1 | 1 | 20 | (20 + 0 + 0) |
Note that the 3-month sum for month ‘7’ is 90 because they did not work during month ‘6’ or month ‘5’.
Employee ‘2’ only has one salary record (month ‘1’) excluding their most recent month ‘2’.
| id | month | salary | Calculation |
|---|---|---|---|
| 2 | 1 | 20 | (20 + 0 + 0) |
Employee ‘3’ has two salary records excluding their most recent month ‘4’:
- 60 for month ‘3’.
- 40 for month ‘2’.
So the cumulative salary summary for this employee is:
| id | month | salary | Calculation |
|---|---|---|---|
| 3 | 3 | 100 | (60 + 40 + 0) |
| 3 | 2 | 40 | (40 + 0 + 0) |
Solution
In this, you essentially need running sum of salary for each employee. You can calculate running sum by using window function SUM(). One caveat to this idea is that you need sum of only last three months. With the help of LAG(salary, 1, 0) you will get previous month salary and similarly you can previous to previous month salary using LAG(salary, 2, 0). Here, it will replace the salary with 0 value if the salary was not present. The problem with this approach is that it needs salary to be present for all month. If, for example, previous month had no reported salary, it will take previous to previous month salary as previous month salary. This is where you can use RANGE BETWEEN clause in your window function. PARTITION BY id ORDER BY month RANGE BETWEEN 2 PRECEDING AND CURRENT ROW will give running sum for last three months.
Next, you also need to ignore the last month salary. This you can do using WHERE clause to filter records.
1WHERE (id, month) NOT IN (
2 SELECT id, MAX(month) FROM Employee GROUP BY id
3)
1SELECT id, month,
2 SUM(salary) OVER (
3 PARTITION BY id ORDER BY month RANGE BETWEEN 2 PRECEDING AND CURRENT ROW
4 ) AS Salary
5 FROM Employee
6 WHERE (id, month) NOT IN (
7 SELECT id, MAX(month) FROM Employee GROUP BY id
8 )
9 ORDER BY id, month DESC;
Comments