Workforce Planning — Headcount Forecasting & Gap Analysis

Workforce planning answers: where will we be short-staffed in 6 months? We use historical headcount trends and attrition rates to project future gaps.

-- Headcount trend: month-over-month growth by department
WITH monthly_trend AS (
    SELECT
        dept_id,
        snapshot_month,
        headcount,
        LAG(headcount) OVER (
            PARTITION BY dept_id ORDER BY snapshot_month
        )                                               AS prev_headcount
    FROM hr_headcount_snapshots
    WHERE snapshot_month >= CURRENT_DATE - INTERVAL '12 months'
)
SELECT
    d.name,
    TO_CHAR(mt.snapshot_month, 'YYYY-MM')              AS month,
    mt.headcount,
    mt.prev_headcount,
    mt.headcount - mt.prev_headcount                   AS mom_change,
    ROUND(100.0 * (mt.headcount - mt.prev_headcount)
          / NULLIF(mt.prev_headcount, 0), 1)           AS mom_growth_pct
FROM monthly_trend mt
JOIN hr_departments d ON d.id = mt.dept_id
WHERE mt.prev_headcount IS NOT NULL
ORDER BY d.name, mt.snapshot_month;