PERCENT_RANK: which direction puts Alice at 0.0?

Question

Accepted Answer

PERCENT_RANK is calculated as (rank - 1) / (total_rows - 1). With ORDER BY score ASC, Carol is rank 1, Bob rank 2, Alice rank 3. But wait - Alice is the lowest rank in ASC order... actually Alice has the highest score so she is rank 3 in ASC. No: ASC means lowest first. Carol (60) = rank 1, Bob (80) = rank 2, Alice (100) = rank 3. Alice's PERCENT_RANK = (3-1)/(3-1) = 1.0. The bottom row always gets 0.0 (Carol here). Trick: ORDER BY score ASC means the highest score has PERCENT_RANK 1.0, not 0.0. To rank Alice at 0.0, use ORDER BY score DESC.