Categories:

Window Functions (Rank-related)

# PERCENT_RANK¶

Returns the relative rank of a value within a group of values.

## Syntax¶

```PERCENT_RANK() OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ { ASC | DESC } ] )
```

## Usage Notes¶

• PERCENT_RANK is calculated as:

If n is 1:

`PERCENT_RANK = 0`

If n is greater than 1:

`PERCENT_RANK = (r - 1) / (n - 1)`

where `r` is the RANK of the row and `n` is the number of rows in the window partition.

• This function does not support:

• ORDER BY sub-clause in the OVER() clause.

• Window frames.

## Examples¶

```SELECT
exchange,
symbol,
PERCENT_RANK() OVER (PARTITION BY exchange ORDER BY price) AS percent_rank

+--------+------+------------+
|exchange|symbol|PERCENT_RANK|
+--------+------+------------+
|C       |SPY   |         0.0|
|C       |AAPL  |         0.5|
|C       |AAPL  |         1.0|
|N       |YHOO  |         0.0|
|N       |QQQ   |         0.2|
|N       |QQQ   |         0.4|
|N       |SPY   |         0.6|
|N       |SPY   |         0.6|
|N       |AAPL  |         1.0|
|Q       |YHOO  |         0.0|
|Q       |YHOO  |         0.2|
|Q       |MSFT  |         0.4|
|Q       |MSFT  |         0.6|
|Q       |QQQ   |         0.8|
|Q       |QQQ   |         1.0|
|P       |YHOO  |         0.0|
|P       |MSFT  |        0.25|
|P       |MSFT  |         0.5|
|P       |SPY   |        0.75|
|P       |AAPL  |         1.0|
+--------+------+------------+
``` 