Find Cumulative Salary of an Employee

Find Cumulative Salary of an Employee

What is this problem about?

The Find Cumulative Salary of an Employee coding problem is a SQL task. You are given an Employee table with id, month, and salary. For each employee, you need to calculate the cumulative salary for the last 3 months (current month + 2 previous months), excluding the most recent month they worked. The output should be grouped by employee ID and month, sorted descending by both.

Why is this asked in interviews?

Amazon and other companies use this "Hard" SQL problem to evaluate your proficiency with Window Functions. It tests your ability to handle complex temporal constraints (the 3-month window) and conditional filtering (excluding the most recent month). It’s a realistic data reporting task used in payroll and performance analysis.

Algorithmic pattern used

This problem uses SQL Window Functions (SUM() OVER) and Subqueries.

1. Use a subquery to find the maximum month for each employee using MAX(month) OVER(PARTITION BY id).
2. Filter the main table to exclude rows where month == max_month.
3. Calculate the running sum using: SUM(salary) OVER(PARTITION BY id ORDER BY month RANGE BETWEEN 2 PRECEDING AND CURRENT ROW).
   • Note: In some SQL dialects, you use ROWS or manual joins if RANGE is not supported for specific windows.

Example explanation

Employee 1 Data: (Jan, 20), (Feb, 30), (Mar, 40), (Apr, 50).

1. Max month is Apr. Exclude it.
2. Remaining: Jan, Feb, Mar.
3. Cumulative Jan (3-month window): 20.
4. Cumulative Feb: 20 + 30 = 50.
5. Cumulative Mar: 20 + 30 + 40 = 90. Final rows for ID 1: (Mar, 90), (Feb, 50), (Jan, 20).

Common mistakes candidates make

• Including the last month: Forgetting the requirement to exclude the employee's most recent activity.
• Static Windows: Using ROWS BETWEEN instead of RANGE BETWEEN when months might be missing (though the problem usually implies contiguous months).
• Sorting order: Miscalculating the descending sort requirement in the final output.

Interview preparation tip

Practice using OVER (PARTITION BY ... ORDER BY ...) clauses. They are the most powerful tool in SQL for calculating running totals, averages, and rankings within specific groups.