Your question is Quarterly Expense Forecast Rollup. Start with the requirements and the two tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
You are given raw expense transactions and a quarterly forecast table. Your task is to structure the expense data into a quarter-level report that a VP can review quickly. The output should compare posted actual spend against forecasted spend by quarter and department, and include the variance between them.
expense_id
INT
Primary key for each expense row
expense_date
DATE
Date the expense was incurred
department
VARCHAR(50)
Cost center or department name
amount
DECIMAL(12,2)
Expense amount; may be negative for credits
status
VARCHAR(20)
Posting status such as posted, pending, or void
forecast_quarter
DATE
Quarter start date used as the forecast key
department
VARCHAR(50)
Department name
forecast_amount
DECIMAL(12,2)
Planned quarterly spend
| 1 | 2024-01-05 | Marketing | 1200 | posted |
| 4 | 2024-04-02 | Sales | 900 | pending |
| 7 | 2024-06-30 | Finance | -50 | posted |
| 10 | 2024-08-01 | Engineering | 2000 | posted |
| 2024-01-01 | Marketing | 2500 |
| 2024-04-01 | Finance | 500 |
| 2024-07-01 | Engineering | 2200 |
| 2024-10-01 | Legal | 300 |