SQL PERCENT_RANK

Summary: in this tutorial, you will learn how to use the SQL PERCENT_RANK() function to calculate the percentile rankings of rows in a result set.

The PERCENT_RANK() is a window function that calculates the percentile ranking of rows in a result set.

The syntax of the PERCENT_RANK() function is as follows:

PERCENT_RANK()  
    OVER ( 
        PARTITION BY expr1, expr2,...
        ORDER BY expr1 [ASC|DESC], expr2 ...
    )
Code language: SQL (Structured Query Language) (sql)

The PERCENT_RANK() function returns a percentile ranking number which ranges from zero to one.

For a specific row, PERCENT_RANK() uses the following formula to calculate the percentile rank:

(rank - 1) / (total_rows - 1)
Code language: SQL (Structured Query Language) (sql)

In this formula, rank is the rank of the row. total_rows is the number of rows that are being evaluated.

Based on this formula, the PERCENT_RANK() function always returns zero for the first row the result set.

The PARTITION BY clause divides the rows into partitions and the ORDER BY clause specifies the logical order of rows for each partition. The PERCENT_RANK() function is calculated for each ordered partition independently.

The PARTITION BY clause is optional. If you omit the PARTITION BY clause, the function treats the whole result set as a single partition.

SQL PERCENT_RANK() function examples

We will use the employees and departments tables from the sample database for the demonstration.

Using SQL PERCENT_RANK() over the query result set example

The following query finds the percentile ranks of employees by their salaries:

SELECT
    first_name,
    last_name,
    salary,
    ROUND(
        PERCENT_RANK() OVER (
            ORDER BY salary
        ) 
    ,2) percentile_rank
FROM
    employees;
Code language: SQL (Structured Query Language) (sql)

In this example, we omitted the PARTITION BY clause so the function treated the whole employees in the result set as a single partition. Notice that we used the ROUND() function to round the percentile rankings to two decimal places.

The following picture shows the output:

The ORDER BY clause sorted the salaries of employees and the PERCENT_RANK() function calculated the percentile ranking of employees by salaries in ascending order.

Let’s analyze some rows in the output.

• Karen has the lowest salary which is not greater than anyone so her percentile ranking is zero. On the other hand, Steven has the highest salary which is higher than anyone, therefore, his percentile ranking is 1 or 100%.
• Nancy and Shelley have the percentile ranking of 82% which means their salary is higher than 82% all other employees.

Using SQL PERCENT_RANK() over partition example

The following statement returns the percentile ranking of employees by their salaries per department:

SELECT
    first_name,
    last_name,
    salary,
    department_name,
    ROUND(
        PERCENT_RANK() OVER (
            PARTITION BY e.department_id
            ORDER BY salary
        ) 
    ,2) percentile_rank
FROM 
    employees e
    INNER JOIN departments d 
        ON d.department_id = e.department_id;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

In this example, we divided the employees by department names. The PERCENT_RANK() then applied to each partition.

As clearly shown in the output, the percentile ranking was reset whenever the department changed.

In this tutorial, you have learned how to use the SQL PERCENT_RANK() function to calculate the percentile rankings of rows in a result set.