PERCENT_RANK

The PERCENT_RANK function returns the relative position of the rank of the value to the number of values in an ordered set. Similar in definition to Percentile, it evaluates individual values relative to values in the ordered set and returns a position in percentage.

The PERCENT_RANK can be useful to extend the rank function normalized between 0 and 1 thus proving a good benchmark to understand the distribution of the data.

USING PERCENT_RANK()

The PERCENT_RANK function is used as any other window function but does require an ordered set. Therefore, without an ORDER BY in the OVER() clause, it is likely not going to work. The syntax looks like this:

PERCENT_RANK() OVER( ORDER BY some_column DESC ) 

PERCENT_RANK() by Example

In the example below, I use the orders table to return the percent rank of freight loads. The query looks like this:

SELECT
    order_id,
    total_amount,
    PERCENT_RANK() OVER(ORDER BY total_amount DESC) AS per_rank_raw,
    ROUND(CAST(percent_rank() OVER(ORDER BY total_amount DESC) AS DECIMAL), 2) as per_rank_raw
FROM orders
LIMIT 20;

 order_id | total_amount |     per_rank_raw     | per_rank_raw
----------+--------------+----------------------+--------------
       16 |      3899.99 |                    0 |         0.00
       11 |      2999.98 | 0.010101010101010102 |         0.01
        2 |      2499.99 | 0.020202020202020204 |         0.02
        4 |      1799.99 | 0.030303030303030304 |         0.03
       50 |      1549.98 |  0.04040404040404041 |         0.04
        9 |      1299.99 | 0.050505050505050504 |         0.05
        1 |      1249.98 |  0.06060606060606061 |         0.06
        6 |       999.99 |   0.0707070707070707 |         0.07
       54 |       999.99 |   0.0707070707070707 |         0.07
       80 |       999.99 |   0.0707070707070707 |         0.07
        8 |       799.99 |  0.10101010101010101 |         0.10
       91 |       699.99 |   0.1111111111111111 |         0.11
       14 |       669.99 |  0.12121212121212122 |         0.12
        3 |       599.99 |  0.13131313131313133 |         0.13
       26 |       599.99 |  0.13131313131313133 |         0.13
       56 |       599.99 |  0.13131313131313133 |         0.13
       87 |       499.99 |  0.16161616161616163 |         0.16
       71 |       499.99 |  0.16161616161616163 |         0.16
       99 |       499.99 |  0.16161616161616163 |         0.16
       10 |       499.99 |  0.16161616161616163 |         0.16
(20 rows)

In the above exmaple, I provide two versions of the percent_rank outcomes, one rounded and the other raw. What is noticeable here is that we now can look at the percentile position of each value. Note that this is descending order so the interpretation of the rank needs to be adjusted appropriately.