AdventureWorks Database: Obtain the salary percentile of each employee for a department

SQL Query - AdventureWorks: Exercise-112 with Solution

112. From the following table write a query in SQL to compute the salary percentile for each employee within a given department. Return Department, LastName, Rate, CumeDist, and percentile rank. Sort the result set in ascending order on department and descending order on rate.

N.B.: The cumulative distribution calculates the relative position of a specified value in a group of values.

businessentityid|title|firstname  |middlename      |lastname         |suffix|shift  |department                |groupname                           |startdate |enddate   |
----------------+-----+-----------+----------------+-----------------+------+-------+--------------------------+------------------------------------+----------+----------+
               1|     |Ken        |J               |Sánchez          |      |Day    |Executive                 |Executive General and Administration|2009-01-14|          |
               2|     |Terri      |Lee             |Duffy            |      |Day    |Engineering               |Research and Development            |2008-01-31|          |
               3|     |Roberto    |                |Tamburello       |      |Day    |Engineering               |Research and Development            |2007-11-11|          |
               4|     |Rob        |                |Walters          |      |Day    |Engineering               |Research and Development            |2007-12-05|2010-05-30|
               4|     |Rob        |                |Walters          |      |Day    |Tool Design               |Research and Development            |2010-05-31|          |
               5|Ms.  |Gail       |A               |Erickson         |      |Day    |Engineering               |Research and Development            |2008-01-06|          |
               6|Mr.  |Jossef     |H               |Goldberg         |      |Day    |Engineering               |Research and Development            |2008-01-24|          |
               7|     |Dylan      |A               |Miller           |      |Day    |Research and Development  |Research and Development            |2009-02-08|          |
               8|     |Diane      |L               |Margheim         |      |Day    |Research and Development  |Research and Development            |2008-12-29|          |
               9|     |Gigi       |N               |Matthew          |      |Day    |Research and Development  |Research and Development            |2009-01-16|          |
              10|     |Michael    |                |Raheem           |      |Day    |Research and Development  |Research and Development            |2009-05-03|          |
			-- more --

Click to view Full table

Sample Solution:

-- Selecting specific columns from the vEmployeeDepartmentHistory view and joining it with the EmployeePayHistory table
SELECT 
    -- Selecting the Department column
    Department, 

    -- Selecting the LastName column
    LastName, 

    -- Selecting the Rate column
    Rate,   

    -- Calculating the cumulative distribution of Rate within each Department partition
    CUME_DIST() OVER (
        -- Partitioning the data by Department and ordering by Rate
        PARTITION BY Department 
        ORDER BY Rate
    ) AS CumeDist,   

    -- Calculating the percent rank of Rate within each Department partition
    PERCENT_RANK() OVER (
        -- Partitioning the data by Department and ordering by Rate
        PARTITION BY Department 
        ORDER BY Rate
    ) AS PctRank  

-- Joining vEmployeeDepartmentHistory with EmployeePayHistory on BusinessEntityID
FROM 
    HumanResources.vEmployeeDepartmentHistory AS edh  
    INNER JOIN HumanResources.EmployeePayHistory AS e    
    ON e.BusinessEntityID = edh.BusinessEntityID  

-- Filtering records for departments 'Information Services' and 'Document Control'
WHERE 
    Department IN ('Information Services', 'Document Control')   

-- Ordering the result set by Department and Rate in descending order
ORDER BY 
    Department, Rate DESC;

Explanation:

• This SQL code joins data from two tables/views: vEmployeeDepartmentHistory and EmployeePayHistory.
• It retrieves data related to employee departments, last names, and pay rates.
• The CUME_DIST function calculates the cumulative distribution of the Rate within each department partition. This function returns the fraction of rows with values less than or equal to the current row's value.
• The PERCENT_RANK function calculates the percent rank of the Rate within each department partition. This function returns the rank of a value in a group of values as a percentage.
• The data is partitioned by Department and ordered by Rate within each partition.
• Records are filtered for departments 'Information Services' and 'Document Control'.
• The result set is ordered by Department and Rate in descending order.

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 differences in sales quota for the next quarters.
Next: Add two days to each value in the OrderDate column.