Find Cumulative Salary of an Employee - Problem
Imagine you're building a payroll analytics system for a company that needs to track employee earnings over time. Your task is to calculate a rolling 3-month salary summary for each employee.
Given an Employee table with salary records, you need to:
- Calculate cumulative salary: For each month an employee worked, sum their salary for that month plus the previous 2 months
- Handle missing months: If an employee didn't work in previous months, treat those salaries as 0
- Exclude most recent month: Don't include the 3-month sum for the employee's most recent working month
- Skip non-working months: Only include months where the employee actually worked
Table Schema:
| Column Name | Type |
|---|---|
| id | int |
| month | int |
| salary | int |
Return results ordered by id ascending, then by month descending for ties.
Input & Output
example_1.sql — Basic Case
$
Input:
Employee table:
+----+-------+--------+
| id | month | salary |
+----+-------+--------+
| 1 | 1 | 1000 |
| 1 | 2 | 2000 |
| 1 | 3 | 3000 |
| 1 | 4 | 4000 |
+----+-------+--------+
›
Output:
+----+-------+--------+
| id | month | salary |
+----+-------+--------+
| 1 | 3 | 6000 |
| 1 | 2 | 3000 |
| 1 | 1 | 1000 |
+----+-------+--------+
💡 Note:
Employee 1 worked for 4 months. We exclude month 4 (most recent). For month 3: sum(1000+2000+3000)=6000. For month 2: sum(1000+2000)=3000. For month 1: sum(1000)=1000.
example_2.sql — Multiple Employees
$
Input:
Employee table:
+----+-------+--------+
| id | month | salary |
+----+-------+--------+
| 1 | 1 | 1000 |
| 1 | 2 | 2000 |
| 2 | 1 | 5000 |
+----+-------+--------+
›
Output:
+----+-------+--------+
| id | month | salary |
+----+-------+--------+
| 1 | 1 | 1000 |
+----+-------+--------+
💡 Note:
Employee 1: Most recent month is 2, so we only include month 1 with salary 1000. Employee 2: Only worked 1 month, so after excluding the most recent month (1), no records remain.
example_3.sql — Gap in Employment
$
Input:
Employee table:
+----+-------+--------+
| id | month | salary |
+----+-------+--------+
| 1 | 1 | 1000 |
| 1 | 3 | 3000 |
| 1 | 7 | 7000 |
+----+-------+--------+
›
Output:
+----+-------+--------+
| id | month | salary |
+----+-------+--------+
| 1 | 3 | 4000 |
| 1 | 1 | 1000 |
+----+-------+--------+
💡 Note:
Employee didn't work in months 2,4,5,6. Most recent is month 7 (excluded). Month 3 sum: 1000+3000=4000 (only actual working months). Month 1 sum: 1000 (only this month).
Visualization
Tap to expand
Understanding the Visualization
1
Identify Active Periods
Find all months where each employee worked and received salary
2
Calculate Rolling Windows
For each active month, sum current + previous 2 months of earnings
3
Exclude Current Period
Remove the most recent month from each employee's results
4
Generate Report
Sort results by employee ID, then by month descending
Key Takeaway
🎯 Key Insight: Window functions transform a complex multi-pass problem into an elegant single-pass solution, leveraging the database engine's optimized sorting and partitioning capabilities.
Time & Space Complexity
Time Complexity
O(n log n)
Single scan with sorting for window function partitioning
⚡ Linearithmic
Space Complexity
O(n)
Space for intermediate results and window calculations
⚡ Linearithmic Space
Constraints
- 1 ≤ Employee.id ≤ 104
- 1 ≤ Employee.month ≤ 12
- 1 ≤ Employee.salary ≤ 106
- All data is from year 2020
- (id, month) combination is unique
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code