Shows a row's relative position from 0 to 1: what share of the other rows comes before it.
PERCENT_RANK() OVER (ORDER BY <sort key>)
PERCENT_RANK() OVER (PARTITION BY <group> ORDER BY <sort key>)
Calculates (rank − 1) / (rows − 1): the first in order gets 0, the last gets 1. Handy for saying “earns more than 75% of colleagues”. The similar CUME_DIST calculates the share including the current row and never gives 0.
employees
| name | department | salary | bonus | phone |
|---|---|---|---|---|
| Emma | backend | 1200 | 300 | +44 7700 900123 |
| Liam | backend | 1000 | NULL | NULL |
| Olivia | frontend | 1350 | 150 | +44 7700 900456 |
| Noah | frontend | 950 | NULL | +44 7700 900789 |
| Sophia | design | 1200 | 200 | NULL |
These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.
| name | salary | percent_rank |
|---|---|---|
| Noah | 950 | 0 |
| Liam | 1000 | 0.25 |
| Emma | 1200 | 0.5 |
| Sophia | 1200 | 0.5 |
| Olivia | 1350 | 1 |
| name | salary | higher_than_percent |
|---|---|---|
| Noah | 950 | 0 |
| Liam | 1000 | 25 |
| Emma | 1200 | 50 |
| Sophia | 1200 | 50 |
| Olivia | 1350 | 100 |
Emma and Sophia earn more than 50% of their colleagues, and Olivia — more than everyone else.
| name | department | salary | rank_in_department |
|---|---|---|---|
| Liam | backend | 1000 | 0 |
| Emma | backend | 1200 | 1 |
| Sophia | design | 1200 | 0 |
| Noah | frontend | 950 | 0 |
| Olivia | frontend | 1350 | 1 |
In a department of one — design — the result is 0: there's no one to compare with.
The result is a fraction from 0 to 1, not a percentage. For percent, multiply by 100.
Equal values get the same result, so several people can have 0.5 at once.
For a single row the result is 0: there's nothing to divide by.