Cumulative Salary
ASKED IN INTERVIEWPayroll
Problem Statement
Find the 3-month rolling total salary for every employee.
Rules:
- Columns: employee_id, salary_month, rolling_three_month_salary.
- Rolling Sum: Current month + 2 months prior.
- Window resets per employee.
- Sort by employee_id ASC, salary_month ASC.
Tests your understanding of
Window Functions, Rolling Sum, Finance and Analytics
Input Tables
employees
| employee_id(INTEGER) | name(VARCHAR) |
|---|---|
| 1 | John |
| 2 | Jane |
salaries
| employee_id(INTEGER) | salary_month(INTEGER) | amount(DECIMAL) |
|---|---|---|
| 1 | 1 | 2000 |
| 1 | 2 | 3000 |
| 1 | 3 | 4000 |
| 1 | 4 | 5000 |
departments
| dept_id(INTEGER) | dept_name(VARCHAR) |
|---|---|
| 10 | Finance |
Expected Output
| employee_id(INTEGER) | salary_month(INTEGER) | rolling_three_month_salary(DECIMAL) |
|---|---|---|
| 1 | 1 | 2000 |
| 1 | 2 | 5000 |
| 1 | 3 | 9000 |
| 1 | 4 | 12000 |
Tags
HardASKED IN INTERVIEWWindow FunctionsRolling SumFinanceAnalytics
25-35 min
35%