AdventureWorks Database: Obtain the salary percentile of each employee for a department
112. From the following table write a query in SQL to compute the salary percentile for each employee for departments 'Information Services' and 'Document Control'. 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.
Sample table: HumanResources.vEmployeeDepartmentHistory
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 --
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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.