Ranking Salary Deviations
Workday, a workforce management platform, wants to measure employee compensation intricacies. Every employee is anchored within a department, and the company want to identify those who exhibit the most significant deviations from the departmental norm.
You are given a table employees:
Craft a SQL query that orchestrates the ranking of employees based on their divergence from the department's average salary. You should provide the employee's name, their department, individual salary, the department's average salary, and an assigned rank. This ranking corresponds to the magnitude of difference between the employee's salary and the department's average (rank 1 = most noteworthy deviation).
If two employees from the same department have salaries that deviate to the same extent, let them share the same rank.
Desired output:
To address this challenge, our initial step involves aggregating the data from the employee records. This aggregation, performed through a special construct called a Common Table Expression (CTE) named "DepartmentAverages," allows us to establish the average salary for each department. This approach enhances the clarity of our code.
Subsequently, we proceed by merging this aggregated data with the main employee dataset, thereby gaining insights into the difference between individual employee salaries and their respective department's average salary. This nuanced information becomes integral for our ranking process.
We then employ the DENSE_RANK() function, which intuitively assigns ranks based on the salary discrepancies within each department. Our meticulous ordering ensures that the employee with the most substantial salary difference secures the top rank.
To present a coherent solution, we organize the results in a manner that corresponds to departmental divisions and ranks within each department. The output provides valuable insights into how employees fare against their departmental benchmarks. This systematic approach ensures clarity in understanding and presentation.
WITH DepartmentAverages AS (
SELECT
department_id,
AVG(salary) AS avg_salary
FROM
employees
GROUP BY
department_id
)
SELECT
e.employee_name,
e.department_id,
e.salary,
da.avg_salary,
DENSE_RANK() OVER (PARTITION BY e.department_id ORDER BY ABS(e.salary - da.avg_salary) DESC) AS deviation_rank
FROM
employees e
JOIN
DepartmentAverages da ON e.department_id = da.department_id
ORDER BY
e.department_id, deviation_rank;
with t1 as (select employee_name, department_id, salary, avg(salary) over (partition by department_id) as avg_salary, abs(salary - avg(salary) over (partition by department_id)) as diff from employees ) select employee_name, department_id, salary, avg_salary, dense_rank() over (partition by department_id order by diff desc) as deviation_rank from t1 order by department_id asc, deviation_rank asc, employee_name