﻿ AdventureWorks: Calculate salary percentile for each employee for specific department

# 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
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|
```

## Practice Online

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿

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