Formatted question description: https://leetcode.ca/all/579.html

579. Find Cumulative Salary of an Employee

Level

Hard

Description

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

First, for each employee, find the maximum month. Secondly, for each employee, find the entries that are before the maximum month. Thirdly, for each such entry, calculate the cumulative sum of salary. Each employee’s number of entries is limited to 3, sorted by Id in ascending order and then by Month in descending order.

# Write your MySQL query statement below
select temp2.Id, temp2.Month, (
    select sum(e2.Salary) from Employee e2
        where e2.Id = temp2.Id and e2.Month between temp2.Month - 2 and temp2.Month
        order by e2.Month desc limit 3
) as Salary from (
    select e1.Id, e1.Month, e1.Salary from Employee e1, (
        select e.Id, max(e.Month) as Month from Employee e group by e.Id
    ) temp1
        where e1.Id = temp1.Id and e1.Month < temp1.Month
        order by e1.Id asc, e1.Month desc
) temp2;

All Problems

All Solutions