Salary vs Department Median & Percentile

WITH dept_median AS (SELECT department_id, AVG(salary) AS median_salary FROM (SELECT department_id, salary, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary) AS rn, COUNT(*) OVER (PARTITION BY department_id) AS cnt FROM employees) t WHERE rn IN ((cnt + 1) / 2, (cnt + 2) / 2) GROUP BY department_id) SELECT d.department_name, e.first_name, e.last_name, e.salary, m.median_salary AS dept_median, e.salary - m.median_salary AS diff_from_median, ROUND(PERCENT_RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary) * 100, 2) AS percentile_rank FROM employees e INNER JOIN departments d ON e.department_id = d.department_id INNER JOIN dept_median m ON e.department_id = m.department_id ORDER BY d.department_name, e.salary DESC, e.employee_id;

Why this works

Median is awkward in SQL because most engines lack a portable MEDIAN(). The ROW_NUMBER + middle-row trick is the classic workaround. PERCENT_RANK gives each row's relative standing (0 = lowest, 1 = highest) within its partition.