PERCENT_RANK
intermediatewindow-functions
2 min read
What is PERCENT_RANK?
PERCENT_RANK() computes the relative rank of the current row within its partition as a value between 0 and 1. The formula is: (rank - 1) / (total_rows - 1).
The first row always gets 0 and the last row gets 1 (or close to 1).
Syntax
SELECT column,
PERCENT_RANK() OVER (ORDER BY column) AS pct_rank
FROM table_name;When to Use
- Calculating percentile positions
- Normalizing ranks across differently sized groups
- Statistical analysis and distribution checks
Key Points
- Range 0–1 — Values range from 0.0 (first) to 1.0 (last).
- Formula — (rank - 1) / (total_rows - 1). For a single row, the result is 0.
- Ties — Rows with equal values get the same PERCENT_RANK.
- PARTITION BY — Compute percentile ranks within groups.
- Differs from CUME_DIST — CUME_DIST uses total_rows in the denominator, not total_rows - 1.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
PERCENT_RANK Challenge
Write a query that solve this task: see where each student falls in the age distribution.
Expected result
Each student with their age percentile rank (0.00 for youngest, 1.00 for oldest).
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is PERCENT_RANK?
Initializing database...Each run starts from fresh sample data.
More Examples
Percent rank within grade groups
Rank students by age within their grade.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
What is the difference between PERCENT_RANK and CUME_DIST?
PERCENT_RANK = (rank-1)/(N-1), ranging 0 to 1. CUME_DIST = rank/N, ranging 1/N to 1. CUME_DIST answers 'what fraction of rows are <= this value?'
What is the PERCENT_RANK of a single row?
It is 0, because (1-1)/(1-1) is defined as 0 in SQL.