Ranking Variations in Salaries

This SQL question involves an operation called 'Ranking Salary Deviations.' Here's what it entails: You have a table with employee salary information. Your task is to rank these salaries according to how much they deviate from the median salary. A smaller deviation from the median salary results in a higher rank. To effectively tackle this question, consider the following important points: You need to calculate the median salary from the provided data. Next, you must determine the deviation of each salary from the median salary. This deviation can be found by subtracting the median salary from each individual salary. Lastly, you will employ a ranking function to rank these deviations.

Let's assume we have an "Employee" table containing "ID", "Name", "Department", and "Salary". First, we need to find the median salary using the following SQL query: SELECT AVG(salary) AS Median FROM( SELECT salary, COUNT(salary) OVER() AS cnt, ROW_NUMBER() OVER(ORDER BY salary) AS rownum FROM Employee) x WHERE rownum IN ((cnt + 1) / 2, (cnt + 2) / 2) Next, we will compute the deviation of each salary from the median: SELECT ID, Name, Salary, ABS(Salary - Median) AS Deviation FROM Employee, (<the previous table>) AS MedianTable Lastly, we will rank these deviations: SELECT ID, Name, Salary, RANK() OVER (ORDER BY Deviation) AS Rank FROM (<the previous table>)

Imagine you have a "Person" table containing the columns "PersonId" and "Salary". To compute the median of "Salary", you can use the following query: SELECT (CASE WHEN MOD((SELECT COUNT(Salary) FROM Person), 2) = 0 THEN (SELECT AVG(Salary) FROM (SELECT Salary FROM (SELECT Salary, ROW_NUMBER() OVER (ORDER BY Salary) AS Row FROM Person) AS B WHERE Row IN ((SELECT COUNT(Salary) FROM Person) / 2, ((SELECT COUNT(Salary) FROM Person) / 2) + 1))) ELSE (SELECT Salary FROM (SELECT Salary, ROW_NUMBER() OVER (ORDER BY Salary) AS Row FROM Person) AS B WHERE Row = ((SELECT COUNT(Salary) FROM Person) + 1) / 2) END) AS Median FROM Person LIMIT 1; Next, to calculate the deviation, use the following query: SELECT PersonId, Salary, ABS(Median - Salary) AS Deviation FROM Person, (<the previous query result>) AS MedianTable Finally, to rank the deviations, execute this query: SELECT PersonID, Salary, RANK() OVER (ORDER BY Deviation) AS Rank FROM (<the previous query result>) The output will consist of a table displaying the "PersonId", "Salary", and the "Rank" of individuals based on their salary deviations.