Find Cumulative Salary of an Employee

To calculate the 3-month sum for each employee, you can use a self-join on the Employee table and filter out the most recent month for each employee. Then, you can use conditional aggregation to calculate the sum for each month and the previous two months.

Here’s the query:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

SELECT e1.id, e1.month, 
       COALESCE(SUM(e2.salary), 0) AS Salary
FROM Employee e1
LEFT JOIN Employee e2 
ON e1.id = e2.id AND e2.month BETWEEN e1.month - 2 AND e1.month
WHERE (e1.id, e1.month) NOT IN (
  SELECT id, MAX(month) 
  FROM Employee 
  GROUP BY id
)
GROUP BY e1.id, e1.month
ORDER BY e1.id ASC, e1.month DESC;

Explanation:

  • The LEFT JOIN combines rows from e1 and e2 where the id matches, and the month of e2 is in the range of e1.month - 2 to e1.month. This way, it includes the salary of the current month and the previous two months.
  • The subquery in the WHERE clause filters out the most recent month for each employee.
  • The COALESCE function is used to return 0 if there are no matching rows for the previous two months.
  • Finally, the result is ordered by id in ascending order and month in descending order, as required.