SQL Reference - PERCENT

PERCENT_RANK() OVER ([PARTITION BY partition_expression] ORDER BY sort_expression [ASC | DESC])

Example

CREATE TABLE Students (
  student_name varchar(30),
  score int
);
INSERT INTO Students
VALUES('Alex', 90), ('Ben', 85), ('Catherine', 92), ('Daniel', 80), ('Emma', 89);

SELECT student_name, score,
       PERCENT_RANK() OVER(ORDER BY score)
FROM Students;

Output

| student_name | score | percent_rank |
|--------------|-------|--------------|
| Daniel       | 80    | 0            |
| Ben          | 85    | 0.25         |
| Emma         | 89    | 0.5          |
| Alex         | 90    | 0.75         |
| Catherine    | 92    | 1            |

Explanation

In the given example, PERCENT_RANK function is applied on the score column in the Students table. The function calculates the percentile rank for each student’s score. The percentile rank is a percentile score that ranges from 0 to 1. It represents the proportion of scores in a distribution that a specific score is greater than. For example, a percent rank of 0.75 means that 75% of the data is below this point.

PERCENT_RANK() OVER ( [ PARTITION BY value_expression , … [ n ] ] ORDER BY sort_expression [ COLLATE collation_name ] [ ASC | DESC ] , … [ n ] )

Example

CREATE TABLE TestRank (ID INT, Value INT);
INSERT INTO TestRank VALUES (1, 10);
INSERT INTO TestRank VALUES (2, 20);
INSERT INTO TestRank VALUES (3, 30);
INSERT INTO TestRank VALUES (4, 40);
INSERT INTO TestRank VALUES (5, 50);

SELECT ID,
       Value,
       PERCENT_RANK() OVER (ORDER BY Value) AS PercentRank
FROM TestRank
ORDER BY Value;

Output

ID	Value	PercentRank
1	10	0.000
2	20	0.250
3	30	0.500
4	40	0.750
5	50	1.000

Explanation

The PERCENT_RANK() function is used to calculate the relative rank of each row in the “TestRank” table based on the ‘Value’. The ranking values are distributed over the interval 0 to 1. This function gives the percentage of values that are less than the current value in the total dataset. For example, the PercentRank for the row with Value ‘30’ is 0.500, meaning that 50% of the total Values are less than 30.

PERCENT_RANK() OVER ([PARTITION BY column_name] ORDER BY column_name)

Example

SELECT
    employee_id,
    salary,
    PERCENT_RANK() OVER(ORDER BY salary) as percent_rank
FROM employees;

Output

| EMPLOYEE_ID | SALARY | PERCENT_RANK |
|-------------|--------|--------------|
|     105     |  2500  |     0.00     |
|     106     |  2700  |     0.20     |
|     107     |  2800  |     0.40     |
|     108     |  3000  |     0.60     |
|     109     |  3200  |     0.80     |
|     110     |  3400  |     1.00     |

Explanation

The PERCENT_RANK function is used to calculate the percentage rank of a specified value in a group of values. In the above example, PERCENT_RANK is used to compute the percentile rank of employees’ salaries within the employees table. The lowest salary has a rank of 0 and the highest has a rank of 1. The PERCENT_RANK is then displayed in the third column of the output.