Performance Calibration & Rating Distribution Analysis

Performance review calibration ensures ratings are fair and consistent across managers and departments. We analyze rating distributions, manager leniency/harshness, and correlation between ratings and outcomes.

-- Rating distribution by department and year
SELECT
    d.name                                              AS department,
    pr.review_year,
    COUNT(*)                                            AS total_reviews,
    ROUND(100.0 * COUNT(*) FILTER (WHERE pr.rating = 5) / COUNT(*), 1) AS pct_5,
    ROUND(100.0 * COUNT(*) FILTER (WHERE pr.rating = 4) / COUNT(*), 1) AS pct_4,
    ROUND(100.0 * COUNT(*) FILTER (WHERE pr.rating = 3) / COUNT(*), 1) AS pct_3,
    ROUND(100.0 * COUNT(*) FILTER (WHERE pr.rating <= 2) / COUNT(*), 1) AS pct_low,
    ROUND((AVG(pr.rating::numeric))::NUMERIC, 2)                   AS avg_rating
FROM hr_performance_reviews pr
JOIN hr_employees e ON e.id = pr.employee_id
JOIN hr_departments d ON d.id = e.dept_id
GROUP BY d.name, pr.review_year
ORDER BY d.name, pr.review_year;