Understanding Salary Distribution with PERCENT_RANK and CUME_DIST

Use PERCENT_RANK and CUME_DIST Functions

Write a SQL query to calculate the relative rank and cumulative distribution of employees based on their salary.

Solution:

-- Calculate PERCENT_RANK and CUME_DIST for employees based on salary.
SELECT 
    EmployeeID,
    Name,
    Salary,
    PERCENT_RANK() OVER (ORDER BY Salary) AS PercentRank,
    CUME_DIST() OVER (ORDER BY Salary) AS CumulativeDistribution
FROM Employees;

Explanation:

1. Purpose of the Query :

1. The goal is to demonstrate how to use PERCENT_RANK and CUME_DIST to analyze the relative position and cumulative distribution of employees' salaries.

2. Key Components :

1. PERCENT_RANK() : Calculates the relative rank of a row within a result set, ranging from 0 to 1.
2. CUME_DIST() : Calculates the cumulative distribution of a row, representing the proportion of rows with values less than or equal to the current row's value.
3. OVER (ORDER BY Salary) : Specifies the ordering for the calculations.

3. Why use PERCENT_RANK and CUME_DIST? :

1. These functions are useful for understanding data distribution and identifying outliers or trends.

4. Real-World Application :

1. For example, in HR systems, you might use this query to identify employees whose salaries fall below or above certain percentiles.

Additional Notes:

• PERCENT_RANK excludes the highest value, while CUME_DIST includes it.
• Use this exercise to teach how to analyze data distribution using advanced window functions.

For more Practice: Solve these Related Problems:

• Write a SQL query to calculate the relative rank of products based on their sales volume using PERCENT_RANK.
• Write a SQL query to determine the cumulative distribution of test scores among students using CUME_DIST.
• Write a SQL query to identify employees whose salary falls in the top 20% of the company using PERCENT_RANK.
• Write a SQL query to analyze the distribution of expenses across departments using CUME_DIST.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Transform XML Data Using XQuery.
Next SQL Exercise: Use STRING_AGG to Concatenate Strings.