AdventureWorks Database: Calculate salary percentile for each employee for specific department Determine each employee's salary percentile for a particular department

SQL Query - AdventureWorks: Exercise-101 with Solution

101. From the following table write a query in SQL to calculate the salary percentile for each employee within a given department. Return department, last name, rate, cumulative distribution and percent rank of rate. Order the result set by ascending on department and descending on rate.

Sample table: HumanResources.vemployeedepartmenthistory

Click to view Full table

Sample table: HumanResources.EmployeePayHistory

Click to view Full table

Sample Solution:

SELECT Department, LastName, Rate,   
       CUME_DIST () OVER (PARTITION BY Department ORDER BY Rate) AS CumeDist,   
       PERCENT_RANK() OVER (PARTITION BY Department ORDER BY Rate ) AS PctRank  
FROM HumanResources.vEmployeeDepartmentHistory AS edh  
    INNER JOIN HumanResources.EmployeePayHistory AS e    
    ON e.BusinessEntityID = edh.BusinessEntityID  
WHERE Department IN ('Information Services','Document Control')   
ORDER BY Department, Rate DESC;

Sample Output:

department          |lastname     |rate   |cumedist|pctrank           |
Document Control    |Arifin       |17.7885|     1.0|               1.0|
Document Control    |Kharatishvili|16.8269|     0.8|               0.5|
Document Control    |Norred       |16.8269|     0.8|               0.5|
Document Control    |Chai         |  10.25|     0.4|               0.0|
Document Control    |Berge        |  10.25|     0.4|               0.0|
Information Services|Trenary      |50.4808|     1.0|               1.0|
Information Services|Conroy       |39.6635|     0.9|0.8888888888888888|
Information Services|Ajenstat     |38.4615|     0.8|0.6666666666666666|
Information Services|Wilson       |38.4615|     0.8|0.6666666666666666|
Information Services|Sharma       |32.4519|     0.6|0.4444444444444444|
Information Services|Connelly     |32.4519|     0.6|0.4444444444444444|
Information Services|Bueno        |27.4038|     0.4|               0.0|
Information Services|Meyyappan    |27.4038|     0.4|               0.0|
Information Services|Bacon        |27.4038|     0.4|               0.0|
Information Services|Berg         |27.4038|     0.4|               0.0|

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Find the sum of the ListPrice and StandardCost for each color.
Next: Return the name of the product that is the least expensive.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

Share this Tutorial / Exercise on : Facebook and Twitter

SQL: Tips of the Day

Convert Timestamp to date in MySQL Query:

DATE_FORMAT(FROM_UNIXTIME(`user.registration`), '%e %b %Y') AS 'date_formatted'

Database: MySQL

Ref : https://bit.ly/3EJPnMQ