HR Analytics: Attrition & Compensation Equity

WITH pay_quartiles AS (
  SELECT
    employee_number,
    job_role,
    monthly_income,
    attrition,
    NTILE(4) OVER (
      PARTITION BY job_role
      ORDER BY monthly_income
    ) AS pay_quartile_in_role
  FROM employees
)
SELECT
  job_role,
  COUNT(*)                                          AS headcount,
  ROUND(100.0 * AVG(CASE WHEN attrition = 'Yes'
        THEN 1 ELSE 0 END), 1)                      AS attrition_pct,
  ROUND(100.0 * AVG(CASE WHEN attrition = 'Yes'
        AND pay_quartile_in_role = 1
        THEN 1 ELSE 0 END) /
    NULLIF(AVG(CASE WHEN pay_quartile_in_role = 1
        THEN 1 ELSE 0 END), 0), 1)                  AS attrition_pct_bottom_pay_q
FROM pay_quartiles
GROUP BY job_role
ORDER BY attrition_pct DESC;

WITH role_level_median AS (
  SELECT
    job_role,
    job_level,
    PERCENTILE_CONT(0.5) WITHIN GROUP (
      ORDER BY monthly_income
    ) AS median_income
  FROM employees
  GROUP BY job_role, job_level
)
SELECT
  e.employee_number,
  e.job_role,
  e.total_working_years,
  e.monthly_income,
  m.median_income                       AS peer_median,
  ROUND(100.0 * (e.monthly_income - m.median_income)
        / m.median_income, 1)           AS pct_vs_peer_median
FROM employees e
JOIN role_level_median m
  ON  m.job_role  = e.job_role
  AND m.job_level = e.job_level
WHERE e.monthly_income < m.median_income * 0.85
  AND e.total_working_years >
      (SELECT AVG(total_working_years) FROM employees e2
       WHERE e2.job_role = e.job_role
         AND e2.job_level = e.job_level)
ORDER BY pct_vs_peer_median;