MySQL Query to Calculate the Percentile Rank of Salaries

Calculate the Percentile Rank of Each Employee's Salary

Write a MySQL query to calculate the percentile rank of each employee's salary using a window function.

Note:

The PERCENT_RANK() function in MySQL calculates the relative rank of a row within a group of rows as a percentage, ranging from 0 to 1. It is computed as (rank - 1) / (total rows - 1), where rank is the position of the row in the ordered set. This function helps compare the relative standing of values, such as sales or scores, within a dataset, showing how far a value is from the lowest value in the set.

Solution:

-- Begin the SELECT statement to define the columns to retrieve from the Employees table
SELECT 
    -- Retrieve the EmployeeID column to uniquely identify each employee
    EmployeeID, 
    -- Retrieve the Name column to display the employee's name
    Name, 
    -- Retrieve the Salary column to show the employee's current salary
    Salary,
    -- Calculate the percentile rank of each employee based on their salary:
    -- PERCENT_RANK() computes the relative rank of each salary in the dataset,
    -- It returns a value between 0 and 1, where:
    -- 0 represents the lowest salary and 1 represents the highest salary.
    -- ORDER BY Salary ensures that salaries are ranked in ascending order.
    PERCENT_RANK() OVER (ORDER BY Salary) AS PercentileRank
-- Specify the Employees table as the source of the data
FROM Employees;

Explanation:

• Purpose of the Query:
• The goal is to calculate the percentile rank of each employee's salary, which represents the relative standing of each salary within the entire dataset.
• This demonstrates the use of the PERCENT_RANK() window function.
• Key Components:
• PERCENT_RANK() OVER (ORDER BY Salary): Calculates the percentile rank of each salary.
• SELECT EmployeeID, Name, Salary: Retrieves the employee details along with their percentile rank.
• Why use Window Functions?:
• Window functions allow you to perform statistical calculations, such as percentile rank, without the need for complex subqueries.
• Real-World Application:
• For example, in a company, you might want to analyze the distribution of salaries to identify how each employee's compensation compares to their peers.

For more Practice: Solve these Related Problems:

• Write a MySQL query to determine the quartile rank of employees based on their salary.
• Write a MySQL query to categorize employees into salary bands using a CASE statement.
• Write a MySQL query to find employees whose salaries are in the top 10% of the company.
• Write a MySQL query to compute the difference between an employee’s salary and the salary at the 75th percentile.

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

Previous MySQL Exercise: Using Certificates for Database Authentication.
Next MySQL Exercise: Find the Lead and Lag Values for Sales.