Employee Rolling Payroll Summary

Hard

A payroll analytics service stores monthly compensation snapshots in an Employee table: id: employee identifier month: month index in the year (1 to 12) salary: salary paid in that month The pair (id, month) is unique. Your task is to build a rolling payroll summary for each employee: For each employee month m, compute a 3-month window sum: salary(m) + salary(m-1) + salary(m-2) If the employee has no record for m-1 or m-2, that missing month contributes 0. Do not output any month the employee did not work. Exclude each employee's latest month from the final result. Return rows with exactly these columns: id month rolling_salary Sort the final table by id ascending, then month descending. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Employee:
| id | month | salary |
|----|-------|--------|
| 1  | 1     | 20     |
| 2  | 1     | 20     |
| 1  | 2     | 30     |
| 2  | 2     | 30     |
| 3  | 2     | 40     |
| 1  | 3     | 40     |
| 3  | 3     | 60     |
| 1  | 4     | 60     |
| 3  | 4     | 70     |
| 1  | 7     | 90     |
| 1  | 8     | 90     |

Output

[
  {"id":1,"month":7,"rolling_salary":90},
  {"id":1,"month":4,"rolling_salary":130},
  {"id":1,"month":3,"rolling_salary":90},
  {"id":1,"month":2,"rolling_salary":50},
  {"id":1,"month":1,"rolling_salary":20},
  {"id":2,"month":1,"rolling_salary":20},
  {"id":3,"month":3,"rolling_salary":100},
  {"id":3,"month":2,"rolling_salary":40}
]

Explanation

For each employee, the most recent month is excluded first, then a 3-month backward sum is computed over existing salary rows only.

Example 2

Input

Employee:
| id | month | salary |
|----|-------|--------|
| 10 | 1     | 100    |
| 10 | 4     | 300    |
| 10 | 5     | 50     |
| 11 | 2     | 40     |
| 11 | 3     | 60     |
| 11 | 6     | 10     |

Output

[
  {"id":10,"month":4,"rolling_salary":300},
  {"id":10,"month":1,"rolling_salary":100},
  {"id":11,"month":3,"rolling_salary":100},
  {"id":11,"month":2,"rolling_salary":40}
]

Explanation

Month gaps are not filled with synthetic rows. Missing prior months only contribute zero to the rolling total.

Example 3

Input

Employee:
| id | month | salary |
|----|-------|--------|
| 21 | 6     | 5000   |
| 22 | 12    | 4000   |

Output

[]

Explanation

Each employee has only one row, and that row is their latest month, so everything is excluded.

Constraints

1 <= number of rows in Employee <= 10,000
1 <= id <= 1,000,000,000
1 <= month <= 12
0 <= salary <= 1,000,000,000
(id, month) is unique
Exclude the latest month per employee from output
Do not emit rows for months missing in source data
Return columns exactly as: id, month, rolling_salary
Sort by id ASC, month DESC
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns id, month, rolling_salary

Employee: | id | month | salary | |----|-------|--------| | 1 | 1 | 20 | | 2 | 1 | 20 | | 1 | 2 | 30 | | 2 | 2 | 30 | | 3 | 2 | 40 | | 1 | 3 | 40 | | 3 | 3 | 60 | | 1 | 4 | 60 | | 3 | 4 | 70 | | 1 | 7 | 90 | | 1 | 8 | 90 |

[ {"id":1,"month":7,"rolling_salary":90}, {"id":1,"month":4,"rolling_salary":130}, {"id":1,"month":3,"rolling_salary":90}, {"id":1,"month":2,"rolling_salary":50}, {"id":1,"month":1,"rolling_salary":20}, {"id":2,"month":1,"rolling_salary":20}, {"id":3,"month":3,"rolling_salary":100}, {"id":3,"month":2,"rolling_salary":40} ]

Employee: | id | month | salary | |----|-------|--------| | 10 | 1 | 100 | | 10 | 4 | 300 | | 10 | 5 | 50 | | 11 | 2 | 40 | | 11 | 3 | 60 | | 11 | 6 | 10 |