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
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 table: HumanResources.EmployeePayHistory
businessentityid|ratechangedate |rate |payfrequency|modifieddate |
----------------+-----------------------+-------+------------+-----------------------+
1|2009-01-14 00:00:00.000| 125.5| 2|2014-06-30 00:00:00.000|
2|2008-01-31 00:00:00.000|63.4615| 2|2014-06-30 00:00:00.000|
3|2007-11-11 00:00:00.000|43.2692| 2|2014-06-30 00:00:00.000|
4|2007-12-05 00:00:00.000| 8.62| 2|2007-11-21 00:00:00.000|
4|2010-05-31 00:00:00.000| 23.72| 2|2010-05-16 00:00:00.000|
4|2011-12-15 00:00:00.000|29.8462| 2|2011-12-01 00:00:00.000|
5|2008-01-06 00:00:00.000|32.6923| 2|2014-06-30 00:00:00.000|
6|2008-01-24 00:00:00.000|32.6923| 2|2014-06-30 00:00:00.000|
7|2009-02-08 00:00:00.000|50.4808| 2|2014-06-30 00:00:00.000|
8|2008-12-29 00:00:00.000|40.8654| 2|2014-06-30 00:00:00.000|
-- more --
Sample Solution:
-- Selecting Department, LastName, Rate, Cumulative Distribution (CUME_DIST), and Percent Rank (PERCENT_RANK)
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 the vEmployeeDepartmentHistory view in the HumanResources schema, joining with EmployeePayHistory table
FROM HumanResources.vEmployeeDepartmentHistory AS edh
INNER JOIN HumanResources.EmployeePayHistory AS e
ON e.BusinessEntityID = edh.BusinessEntityID
-- Filtering the results for specific departments
WHERE Department IN ('Information Services','Document Control')
-- Ordering the results by Department and Rate in descending order
ORDER BY Department, Rate DESC;
Explanation:
- This SQL query retrieves data related to employee departments, including department name, employee last name, rate of pay, cumulative distribution (CUME_DIST), and percent rank (PERCENT_RANK) based on the rate of pay.
- The SELECT clause specifies the columns to be retrieved along with additional calculations for cumulative distribution and percent rank.
- The FROM clause indicates the source tables/views (vEmployeeDepartmentHistory and EmployeePayHistory) and their alias names (edh and e).
- The INNER JOIN statement joins the two tables/views based on the BusinessEntityID column.
- The WHERE clause filters the results to include only the specified departments ('Information Services' and 'Document Control').
- The PARTITION BY clause partitions the result set by department, allowing window functions to operate independently within each department.
- The ORDER BY clause orders the results first by department and then by rate of pay 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 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.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics