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

Parameter	Description	Supported input types
Parameter	Description
`<partition_by>`	An expression used for the partition by clause.	Any
`<order_by>`	An expression used for the order by clause.	Any

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 the relative rank of each player’s score within their level. The highest score has a PERCENT_RANK of 0, and the lowest has 1.

SELECT
    nickname,
    current_score,
    PERCENT_RANK() OVER (PARTITION BY level ORDER BY current_score DESC) AS percent_rank
FROM
    (VALUES
        ('kennethpark', 9, 90),
        ('sabrina21', 9, 85),
        ('rileyjon', 9, 80),
        ('ymatthews', 9, 79)
    ) AS t(nickname, level, current_score)
ORDER BY level, nickname;

nickname text	current_score int	percent_rank double
kennethpark	90	0
rileyjon	80	0.6666666666666666
sabrina21	85	0.3333333333333333
ymatthews	79	1

Rows: 4Execution time: 3.04ms

NTILE OVER

RANK OVER

⌘I

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

Syntax

Parameters

Return Type

Example

​Syntax

​Parameters

​Return Type

​Example

Syntax

Parameters

Return Type

Example