Find Cumulative Salary of an Employee

hard database sql

Problem

Employee(id, month, salary). For each employee, compute cumulative salary over the last 3 months excluding the most-recent month.

Inpute=[(1,1,20),(1,2,30),(1,3,40),(1,4,60),(1,7,90)]

Output[(1,4,30+40+60=130),(1,3,20+30+40=90),(1,2,20+30=50),(1,1,20)]

Drop the most recent month (7) and roll a 3-month window over the rest.

rows (compact)

SELECT a.id, a.month, SUM(b.salary) AS Salary
FROM Employee a JOIN Employee b
  ON a.id = b.id AND b.month BETWEEN a.month - 2 AND a.month
WHERE (a.id, a.month) NOT IN (SELECT id, MAX(month) FROM Employee GROUP BY id)
GROUP BY a.id, a.month
ORDER BY a.id, a.month DESC;

Explanation

For each month of an employee, we want the total salary of that month plus the two months before it — a rolling 3-month sum. The clever part is doing this with a self-join, where the table joins to a second copy of itself.

We alias the table as a and b. Row a is the "current" month, and we pull every row b of the same employee whose month falls in the window b.month BETWEEN a.month - 2 AND a.month. Summing b.salary over that matched group gives the cumulative total for a.

The WHERE (a.id, a.month) NOT IN (SELECT id, MAX(month) ...) clause throws away each employee's most recent month, which the problem asks us to exclude.

Finally GROUP BY a.id, a.month collapses the joined rows back into one row per (employee, month), and we order newest-first.

Example: for month 4, the window covers months 2, 3, 4 giving 30 + 40 + 60 = 130. Month 7 is the latest, so it is dropped entirely.

Time: O(n) Space: O(n)