Find Cumulative Salary of an Employee - Problem

Given an Employee table, calculate the cumulative salary summary for every employee.

The cumulative salary summary is calculated as follows:

  • For each month that the employee worked, sum up the salaries in that month and the previous two months (3-month sum)
  • If an employee did not work in previous months, their effective salary for those months is 0
  • Exclude the 3-month sum for the most recent month that the employee worked
  • Exclude months when the employee did not work

Return results ordered by id ascending, then by month descending.

Table Schema

Employee
Column Name Type Description
id PK int Employee ID
month PK int Month number (1-12)
salary int Monthly salary amount
Primary Key: (id, month)
Note: Each row represents an employee's salary for a specific month in 2020

Input & Output

Example 1 — Basic Cumulative Calculation
Input Table:
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 cumulative_salary
1 3 90
1 2 50
1 1 20
2 1 20
3 3 100
3 2 40
💡 Note:

For employee 1: months 1,2,3 are included (month 4 is most recent, so excluded). Month 3 cumulative = 20+30+40=90. For employee 2: only month 1 (month 2 is most recent). For employee 3: only month 3 (month 4 is most recent).
Example 2 — Single Month Employee
Input Table:
id month salary
1 1 100
2 1 200
2 3 300
Output:
id month cumulative_salary
2 1 200
💡 Note:

Employee 1 has only one month (month 1), so it's excluded as the most recent. Employee 2 has months 1 and 3, with month 3 being most recent, so only month 1 is included with cumulative salary of 200.

Constraints

  • 1 ≤ id ≤ 100
  • 1 ≤ month ≤ 12
  • 1 ≤ salary ≤ 10000
  • All salary values are positive integers
  • Each employee has at least one salary record

Visualization

Tap to expand
Find Cumulative Salary of an Employee INPUT: Employee Table Id Month Salary 1 1 20 1 2 30 1 3 40 1 4 60 2 1 10 2 2 20 2 3 30 3-Month Sliding Window M-2 M-1 M Sum salaries over window Exclude most recent month Order: id ASC, month DESC ALGORITHM STEPS 1 Self-Join Table Join Employee E1 with E2 where E1.id = E2.id 2 Define Window Range E2.month BETWEEN E1.month-2 AND E1.month 3 Exclude Recent Month Use subquery to find MAX(month) per employee 4 Aggregate and Order SUM(E2.salary) as total ORDER BY id, month DESC SELECT E1.id, E1.month, SUM(E2.salary) AS Salary FROM Employee E1 JOIN Employee E2 ON E1.id = E2.id GROUP BY E1.id, E1.month FINAL RESULT Id Month Salary 1 3 90 1 2 50 1 1 20 2 2 30 2 1 10 Calculation Breakdown: Emp1, M3: 40+30+20 = 90 Emp1, M2: 30+20+0 = 50 Emp1, M1: 20+0+0 = 20 Emp2, M2: 20+10+0 = 30 Emp2, M1: 10+0+0 = 10 OK - Verified Month 4 excluded (most recent) Key Insight: The 3-month cumulative sum uses a self-join with a month range condition. The key challenge is excluding the most recent month per employee using a NOT IN subquery with MAX(month). This requires careful handling of the join condition: E2.month BETWEEN E1.month-2 AND E1.month ensures we sum exactly 3 months maximum. TutorialsPoint - Find Cumulative Salary of an Employee | Optimal Solution

Related Problems

Asked in
Amazon 12 Microsoft 8 Google 6
23.4K Views
Medium Frequency
~25 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen