PERCENT_RANK OVER

Calculates the relative rank of the current row within an ordered data set, as ( rank - 1 ) / ( rows - 1 ) where rank is the current row’s rank within the partition, and rows is the number of rows in the partition. PERCENT_RANK always returns values from 0 to 1 inclusive. The first row in any set has a PERCENT_RANK of 0.

​

Syntax

PERCENT_RANK() OVER ( [ PARTITION BY <partition_by> ] ORDER BY <order_by> [ASC|DESC] )

​

Parameters

​

Return Type

DOUBLE PRECISION

This function respects NULL values, and results will be ordered with default null ordering NULLS LAST unless otherwise specified in the ORDER BY clause.

​

Example

The example below calculates, for each student in grade nine, the percent rank of the student’s test score by their grade level.

SELECT
	nickname, current_score,
	PERCENT_RANK() OVER (PARTITION BY level ORDER BY current_score DESC) as percent_rank
FROM
	class_test
WHERE grade_level=9;

Returns: