percent_rank

The relative rank is (rank - 1) / (partition_size - 1). Peers share the same result. A partition containing one row returns 0.0.

Syntax

text

percent_rank() OVER ([PARTITION BY expression, ...] ORDER BY expression, ...)

Arguments The function takes no arguments. PARTITION BY starts an independent ranking for each group; without it, all selected rows form one partition. ORDER BY inside OVER determines the ranking order. A frame clause does not restrict the rows used for ranking.

Returned value A REAL fraction from 0 through 1. The window order does not determine the final output order; use an outer ORDER BY to sort the result.

Example Ranking employee salaries

Ada and Grace have the same salary and therefore belong to the same peer group.

sql

CREATE TABLE salaries (player TEXT, salary INTEGER);
INSERT INTO salaries VALUES ('Ada', 100), ('Grace', 100), ('Linus', 80), ('Sam', 60);

sql

SELECT player, salary,
       round(percent_rank() OVER (ORDER BY salary DESC), 6) AS result
FROM salaries
ORDER BY salary DESC, player
FORMAT csv;

text

"Ada",100,0.0
"Grace",100,0.0
"Linus",80,0.666667
"Sam",60,1.0

See rank, dense_rank and row_number to compare tie handling, and window functions for partition and frame syntax.

percent_rank ​

percent_rank