Skip to main content

PERCENT_RANK

Introduced: v1.1.50

Returns the relative rank of a given value within a set of values. The resulting value falls between 0 and 1, inclusive. Please note that the first row in any set has a PERCENT_RANK of 0.

See also: CUME_DIST

Syntax

PERCENT_RANK() OVER (
PARTITION BY expr, ...
ORDER BY expr [ASC | DESC], ...
)

Examples

This example retrieves the students' names, scores, grades, and the percentile ranks (percent_rank) within each grade using the PERCENT_RANK() window function.

CREATE TABLE students (
name VARCHAR(20),
score INT NOT NULL,
grade CHAR(1) NOT NULL
);

INSERT INTO students (name, score, grade)
VALUES
('Smith', 81, 'A'),
('Jones', 55, 'C'),
('Williams', 55, 'C'),
('Taylor', 62, 'B'),
('Brown', 62, 'B'),
('Davies', 84, 'A'),
('Evans', 87, 'A'),
('Wilson', 72, 'B'),
('Thomas', 72, 'B'),
('Johnson', 100, 'A');

SELECT
name,
score,
grade,
PERCENT_RANK() OVER (PARTITION BY grade ORDER BY score) AS percent_rank
FROM
students;

name |score|grade|percent_rank |
--------+-----+-----+------------------+
Smith | 81|A | 0.0|
Davies | 84|A |0.3333333333333333|
Evans | 87|A |0.6666666666666666|
Johnson | 100|A | 1.0|
Taylor | 62|B | 0.0|
Brown | 62|B | 0.0|
Wilson | 72|B |0.6666666666666666|
Thomas | 72|B |0.6666666666666666|
Jones | 55|C | 0.0|
Williams| 55|C | 0.0|
Explore Databend Cloud for FREE
Low-cost
Fast Analytics
Easy Data Ingestion
Elastic Scaling
Try it today