Describe your experience using Microsoft SQL Server and Python to analyze data and produce insights for finance stakeholders.
For this practical exercise, write a PostgreSQL query that reconciles monthly actuals and budgets by cost center. Include cost centers appearing on only one side, calculate profitability variance, compare each cost center with its prior reported month, and rank results within each month.
Output
- One row per cost center and reported month, excluding rows with a NULL cost center ID or month.
- Return
cost_center_name, month_start, actual and budget revenue, actual and budget expense, profit_variance, prior_variance, and monthly_variance_rank.
- Sort by month, descending profit variance, then cost center name.
Output details
- Restrict reported months to
2025-01-01 inclusive through 2025-04-01 exclusive.
- Sort
month_start ascending and cost_center_name ascending after descending profit_variance.
- Represent monetary values as numeric values rounded to two decimal places.
- Treat missing-side amounts and NULL sums as
0.00.
- Compute
prior_variance by cost_center_name in ascending month order, returning NULL when no prior reported month exists.
- Assign tied variances the same rank, with subsequent ranks skipped.
