Manager Effectiveness — Team Metrics Rollup

A manager's effectiveness shows in their team's metrics: retention rate, average performance rating, salary growth, and team size stability. This query aggregates all of those into a manager scorecard.

-- Manager scorecard: retention, ratings, compensation growth
WITH direct_reports AS (
    SELECT
        manager_id,
        COUNT(*)                                                    AS team_size,
        COUNT(*) FILTER (WHERE NOT is_active)                       AS terminated,
        AVG(EXTRACT(EPOCH FROM AGE(
            COALESCE(termination_date, CURRENT_DATE), hire_date
        )) / 2592000)                                               AS avg_tenure_months
    FROM hr_employees
    WHERE manager_id IS NOT NULL
    GROUP BY manager_id
),
team_ratings AS (
    SELECT
        e.manager_id,
        ROUND((AVG(pr.rating::numeric))::NUMERIC, 2)                           AS avg_team_rating,
        COUNT(DISTINCT pr.employee_id)                              AS rated_employees
    FROM hr_performance_reviews pr
    JOIN hr_employees e ON e.id = pr.employee_id
    WHERE pr.review_year = 2024
      AND e.manager_id IS NOT NULL
    GROUP BY e.manager_id
),
team_comp AS (
    SELECT manager_id, AVG(salary_usd) AS avg_team_salary
    FROM (
        SELECT e.manager_id, s.salary_usd,
            ROW_NUMBER() OVER (PARTITION BY e.id ORDER BY s.effective_date DESC) AS rn
        FROM hr_employees e
        JOIN hr_salaries s ON s.employee_id = e.id
        WHERE e.is_active
    ) latest
    WHERE rn = 1
    GROUP BY manager_id
)
SELECT
    m.emp_id,
    m.name                                                         AS manager,
    d.name                                                         AS department,
    jl.level_code,
    dr.team_size,
    dr.terminated                                                  AS team_terminations,
    ROUND(100.0 * dr.terminated / NULLIF(dr.team_size, 0), 1)     AS team_attrition_pct,
    ROUND(dr.avg_tenure_months, 1)                                 AS avg_tenure_months,
    tr.avg_team_rating,
    ROUND(tc.avg_team_salary)                                      AS avg_team_salary_usd
FROM direct_reports dr
JOIN hr_employees m    ON m.id = dr.manager_id
JOIN hr_departments d  ON d.id = m.dept_id
JOIN hr_job_levels jl  ON jl.id = m.job_level_id
LEFT JOIN team_ratings tr               ON tr.manager_id = dr.manager_id
LEFT JOIN team_comp tc                  ON tc.manager_id = dr.manager_id
WHERE m.is_active
ORDER BY team_attrition_pct DESC
LIMIT 20;