Attrition Risk Scoring — Multi-Factor Early Warning Model

We combine multiple signals into a single attrition risk score per employee. High-risk employees get surfaced for retention conversations before they resign.

Risk factors:

• Salary at or near band ceiling
• No promotion in 2+ years
• Declining performance ratings
• Long tenure without level change
• Low manager effectiveness score

-- Multi-factor attrition risk score
WITH current_salary AS (
    SELECT DISTINCT ON (employee_id)
        employee_id, salary_usd, effective_date
    FROM hr_salaries
    ORDER BY employee_id, effective_date DESC
),
rating_trend AS (
    SELECT
        employee_id,
        MAX(rating) FILTER (WHERE review_year = 2024)  AS rating_2024,
        MAX(rating) FILTER (WHERE review_year = 2023)  AS rating_2023,
        MAX(rating) FILTER (WHERE review_year = 2022)  AS rating_2022
    FROM hr_performance_reviews
    GROUP BY employee_id
),
manager_attrition AS (
    SELECT
        e.manager_id,
        ROUND(100.0 * COUNT(*) FILTER (WHERE NOT e.is_active)
              / NULLIF(COUNT(*), 0), 1)                AS mgr_attrition_pct
    FROM hr_employees e
    WHERE e.manager_id IS NOT NULL
    GROUP BY e.manager_id
),
risk_factors AS (
    SELECT
        e.id,
        e.emp_id,
        e.name,
        d.name                                                       AS department,
        jl.level_code,
        -- Factor 1: Salary band ceiling (0-30 points)
        ROUND(LEAST(30, 30 * cs.salary_usd::numeric / NULLIF(jl.max_salary, 0)))
                                                                     AS band_ceiling_score,
        -- Factor 2: Tenure without promotion (0-25 points based on years since hire)
        LEAST(25, ROUND(
            EXTRACT(YEAR FROM AGE(CURRENT_DATE, e.hire_date)) * 5
        ))                                                           AS tenure_score,
        -- Factor 3: Rating trend declining (0-25 points)
        CASE
            WHEN rt.rating_2024 < rt.rating_2023
             AND rt.rating_2023 < rt.rating_2022 THEN 25
            WHEN rt.rating_2024 < rt.rating_2023 THEN 15
            WHEN rt.rating_2024 IS NULL           THEN 10
            ELSE 0
        END                                                          AS rating_decline_score,
        -- Factor 4: High manager attrition (0-20 points)
        LEAST(20, COALESCE(ma.mgr_attrition_pct, 0))                AS mgr_risk_score
    FROM hr_employees e
    JOIN hr_departments d  ON d.id = e.dept_id
    JOIN hr_job_levels jl  ON jl.id = e.job_level_id
    JOIN current_salary cs                  ON cs.employee_id = e.id
    LEFT JOIN rating_trend rt               ON rt.employee_id = e.id
    LEFT JOIN manager_attrition ma          ON ma.manager_id = e.manager_id
    WHERE e.is_active
)
SELECT
    emp_id,
    name,
    department,
    level_code,
    band_ceiling_score,
    tenure_score,
    rating_decline_score,
    mgr_risk_score,
    band_ceiling_score + tenure_score + rating_decline_score + mgr_risk_score
                                                                     AS total_risk_score,
    CASE
        WHEN band_ceiling_score + tenure_score + rating_decline_score + mgr_risk_score >= 70
             THEN 'Critical'
        WHEN band_ceiling_score + tenure_score + rating_decline_score + mgr_risk_score >= 50
             THEN 'High'
        WHEN band_ceiling_score + tenure_score + rating_decline_score + mgr_risk_score >= 30
             THEN 'Medium'
        ELSE 'Low'
    END                                                              AS risk_tier
FROM risk_factors
ORDER BY total_risk_score DESC
LIMIT 25;