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:
|
|
Explanation:
- The
LEFT JOIN
combines rows frome1
ande2
where theid
matches, and the month ofe2
is in the range ofe1.month - 2
toe1.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 andmonth
in descending order, as required.