Promotion Velocity & Career Progression Analysis

Promotion velocity measures how quickly employees move up job levels. Slow promotion in competitive tech roles drives attrition. We identify fast-trackers, career plateaus, and level-skipping patterns.

-- Promotion history: identify job level changes via salary change_reason
-- Using salary events as a proxy for promotions (change_reason = 'promotion')
WITH promotion_events AS (
    SELECT
        s.employee_id,
        s.effective_date                               AS promoted_date,
        s.salary_usd                                   AS new_salary,
        LAG(s.salary_usd) OVER (
            PARTITION BY s.employee_id ORDER BY s.effective_date
        )                                              AS prev_salary,
        s.change_reason
    FROM hr_salaries s
    WHERE s.change_reason IN ('promotion', 'merit', 'hire')
),
promotions AS (
    SELECT
        employee_id,
        promoted_date,
        new_salary,
        prev_salary,
        ROUND(100.0 * (new_salary - prev_salary) / NULLIF(prev_salary, 0), 1) AS raise_pct
    FROM promotion_events
    WHERE change_reason = 'merit' AND prev_salary IS NOT NULL
      AND (new_salary - prev_salary) > prev_salary * 0.10  -- >10% = likely promotion
)
SELECT
    e.emp_id,
    e.name,
    d.name                                             AS department,
    jl.level_code,
    e.hire_date,
    COUNT(p.promoted_date)                             AS promotion_count,
    MAX(p.promoted_date)                               AS last_promotion,
    ROUND((AVG(p.raise_pct))::NUMERIC, 1)                         AS avg_raise_pct,
    EXTRACT(YEAR FROM AGE(CURRENT_DATE, e.hire_date))  AS tenure_years
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
LEFT JOIN promotions p                  ON p.employee_id = e.id
WHERE e.is_active
GROUP BY e.emp_id, e.name, d.name, jl.level_code, e.hire_date
ORDER BY promotion_count DESC, tenure_years DESC
LIMIT 20;