PERCENT_RANK()
SQL · Window Functions
Theory
PERCENT_RANK() shows where a row stands within its partition as a number from 0 to 1. The formula is (rank - 1) / (rows in partition - 1), where rank is the same value RANK() would give. The first row in the ORDER BY always gets 0 and the last distinct value gets 1 (unless it is tied).
With five salaries 45000, 52000, 60000, 60000, 75000 ordered ascending, the ranks are 1, 2, 3, 3, 5, so PERCENT_RANK is 0, 0.25, 0.5, 0.5 and 1. Read 0.5 as 'half of the other rows are below me'. Tied rows get the same value because they share a RANK. With only one row in a partition, the result is 0. Multiply by 100 for a percentile-style score, and remember the direction: ORDER BY salary DESC would give the top earner 0.
Why we use it
Raw ranks are hard to compare across groups of different sizes: rank 5 out of 6 and rank 5 out of 500 mean very different things. PERCENT_RANK scales every rank to 0-1 so you can compare positions fairly.
When to use it
Use PERCENT_RANK to express relative standing, such as 'in the top 10% of sellers' (value >= 0.9 with ascending order). Use CUME_DIST if you want 'share of rows at or below me'; use NTILE if you want fixed buckets.
How it works
- Rows sorted by the window ORDER BY
- Compute RANK() for the current row
- Count rows in the partition (N)
- Is N equal to 1? Yes: Return 0. No: Return (rank - 1) / (N - 1).
- Value between 0 and 1 for each row
Syntax
PERCENT_RANK() OVER (ORDER BY col)Example
SELECT name, salary,
PERCENT_RANK() OVER (ORDER BY salary) AS pct_rank
FROM employees;Key points
- Formula: (rank - 1) / (total_rows - 1)
- The lowest value in the window always returns 0
- Ties share a value because they share a RANK
- Returns 0 for a single-row partition
- Direction matters: ORDER BY DESC gives the top row 0