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 |
💡 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
Understanding the Visualization
1
Input
Employee salary records by month
2
Rolling Sum
Calculate 3-month cumulative totals
3
Filter
Exclude most recent month per employee
Key Takeaway
🎯 Key Insight: Use window functions with frame specification to handle rolling calculations efficiently
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code