Headcount Trends & Attrition Analysis

Headcount analysis tracks how many people work here and how many leave. Attrition rate = terminations / average headcount × 100. Voluntary vs involuntary attrition have very different organizational meanings.

-- Monthly attrition rate by department (last 12 months)
WITH monthly_terms AS (
    SELECT
        dept_id,
        DATE_TRUNC('month', termination_date)          AS term_month,
        COUNT(*)                                        AS terminations,
        COUNT(*) FILTER (WHERE termination_reason = 'voluntary') AS voluntary,
        COUNT(*) FILTER (WHERE termination_reason != 'voluntary'
                           AND termination_reason IS NOT NULL)   AS involuntary
    FROM hr_employees
    WHERE termination_date >= NOW() - INTERVAL '12 months'
    GROUP BY dept_id, DATE_TRUNC('month', termination_date)
),
monthly_hc AS (
    SELECT dept_id, snapshot_month, headcount
    FROM hr_headcount_snapshots
    WHERE snapshot_month >= DATE_TRUNC('month', NOW() - INTERVAL '12 months')
)
SELECT
    d.name                                              AS department,
    TO_CHAR(hc.snapshot_month, 'YYYY-MM')               AS month,
    hc.headcount,
    COALESCE(mt.terminations, 0)                        AS terminations,
    COALESCE(mt.voluntary, 0)                           AS voluntary,
    COALESCE(mt.involuntary, 0)                         AS involuntary,
    ROUND(100.0 * COALESCE(mt.terminations, 0)
          / NULLIF(hc.headcount, 0), 2)                 AS attrition_rate_pct
FROM monthly_hc hc
JOIN hr_departments d ON d.id = hc.dept_id
LEFT JOIN monthly_terms mt
    ON mt.dept_id = hc.dept_id AND mt.term_month = hc.snapshot_month
ORDER BY d.name, hc.snapshot_month;