Compensation & Pay Equity Analysis

Pay equity analysis detects systematic salary differences across gender and ethnicity groups after controlling for job level. The key is to compare within the same job level, not across all employees.

-- Salary distribution by job level and gender
WITH current_salary AS (
    SELECT DISTINCT ON (employee_id)
        employee_id,
        salary_usd
    FROM hr_salaries
    ORDER BY employee_id, effective_date DESC
)
SELECT
    jl.level_code,
    jl.title,
    e.gender,
    COUNT(*)                                                AS headcount,
    ROUND(AVG(cs.salary_usd))                               AS avg_salary,
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP
        (ORDER BY cs.salary_usd))                           AS median_salary,
    ROUND(MIN(cs.salary_usd))                               AS min_salary,
    ROUND(MAX(cs.salary_usd))                               AS max_salary
FROM current_salary cs
JOIN hr_employees e ON e.id = cs.employee_id
JOIN hr_job_levels jl ON jl.id = e.job_level_id
WHERE e.is_active
GROUP BY jl.level_code, jl.title, e.gender
ORDER BY jl.level_code, e.gender;