PERCENT_RANK Function - SQL
Overview
The PERCENT_RANK function in SQL calculates the relative rank of a row within a partition as a percentage of the total number of rows in the partition. It is useful for determining the percentile rank of a row.
Example:
SELECT department_id, first_name, salary,
PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)
FROM company.employees
Syntax:
SELECT column_name,
PERCENT_RANK() OVER (PARTITION BY partition_column[s] ORDER BY order_column[s])
FROM table_name
partition_column[s] is the column or columns that divide the result set into partitions.
order_column[s] is the column or columns that specify the order of rows within each partition.
PERCENT_RANK() calculates the relative rank of a row as a percentage of the total number of rows in the partition.
Sample Data:
Before
| department_id | first_name | salary |
|---|---|---|
| 3 | Frank | 123000 |
| 2 | Jane | 135000 |
| 3 | Ashley | 115000 |
| NULL | Glenn | 115000 |
| 2 | Kelly | 125000 |
| 1 | Richard | 120000 |
| 1 | George | 105000 |
| 5 | Kyle | 200000 |
| 2 | James | 107000 |
| 1 | Gustavo | 100000 |
After
| department_id | first_name | salary |
|---|---|---|
| NULL | Glenn | 115000 |
| 1 | Richard | 120000 |
| 1 | George | 105000 |
| 1 | Gustavo | 100000 |
| 2 | Jane | 135000 |
| 2 | Kelly | 125000 |
| 2 | James | 107000 |
| 3 | Ashley | 115000 |
| 3 | Frank | 123000 |
| 5 | Kyle | 200000 |
Example: Query without the WHERE Statement
In this example, we are returning the department_id, first_name, and salary column, along with a PERCENT_RANK column to calculate the relative rank of each employee’s salary within their department, ordering the salaries in descending order. The result is a value between 0 and 1 for each employee, indicating their salary’s percentile rank within their department.
Example: Query with the WHERE Statement
In this example, we are filtering for only rows in department_id 1. We are returning the department_id, first_name, and salary column, along with a PERCENT_RANK column to calculate the relative rank of each employee’s salary within their department, ordering the salaries in descending order. The result is a value between 0 and 1 for each employee, indicating their salary’s percentile rank within their department.
