Monthly Salary Expense — MoM Growth & Rolling Average

WITH monthly AS (SELECT e.department_id, d.department_name, substr(sh.effective_date, 1, 7) AS expense_month, SUM(sh.salary) AS monthly_expense FROM salaries_history sh INNER JOIN employees e ON sh.employee_id = e.employee_id INNER JOIN departments d ON e.department_id = d.department_id GROUP BY e.department_id, d.department_name, substr(sh.effective_date, 1, 7)) SELECT department_name, expense_month, monthly_expense, LAG(monthly_expense) OVER (PARTITION BY department_id ORDER BY expense_month) AS prev_month_expense, ROUND((monthly_expense - LAG(monthly_expense) OVER (PARTITION BY department_id ORDER BY expense_month)) * 100.0 / NULLIF(LAG(monthly_expense) OVER (PARTITION BY department_id ORDER BY expense_month), 0), 2) AS mom_growth_pct, ROUND(AVG(monthly_expense) OVER (PARTITION BY department_id ORDER BY expense_month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS rolling_3month_avg, CASE WHEN monthly_expense > LAG(monthly_expense) OVER (PARTITION BY department_id ORDER BY expense_month) THEN 'YES' ELSE 'NO' END AS has_growth FROM monthly ORDER BY department_name, expense_month;

Why this works

Month-over-month analysis is a BI staple. Bucketing the month with substr keeps it portable; LAG provides the prior period; an explicit ROWS frame gives the rolling average. NULLIF prevents the classic divide-by-zero on the first month.

Success check

The result should read like a multi-stage financial growth analysis, not a simple roster.