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.
Sample table: HumanResources.vEmployeeDepartmentHistory
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 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.
SQL: Tips of the Day
How to avoid the "divide by zero" error in SQL?
Select Case when divisor=0 then null Else dividend / divisor End ,,,
OR:
Select dividend / NULLIF(divisor, 0) ...
Ref: https://bit.ly/3dLj7gS
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook