PostgreSQL PERCENT_RANK Window Function

Summary: In this tutorial, you’ll learn how to use the PostgreSQL PERCENT_RANK() function to calculate a relative rank of a value in a set of values.

Getting Started with the PostgreSQL PERCENT_RANK() Window Function #

The PERCENT_RANK() function assigns a relative rank value to each row within a partition based on the values of one or more columns.

Syntax #

Here’s the syntax of the PERCENT_RANK() function:

PERCENT_RANK() OVER (
    [PARTITION BY expression_list]
    [ORDER BY expression_list]
)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

PARTITION BY: Divides the result set into partitions where the PERCENT_RANK() function applies. This clause is optional. If omitted, the PERCENT_RANK() function treats the whole result set as a single partition.
ORDER BY: Sorts rows within each partition. The values of the columns listed in this clause will determine rank values.

Return Value #

The PERCENT_RANK() function returns a number in the range of (0, 1). The first value in the set always receives a rank of zero.

If multiple rows have the same value in the columns specified by the ORDER BY clause, the PERCENT_RANK() function will assign them the same rank.

Calculation Formula #

PostgreSQL calculates the percent rank based on the following formula:

percent_rank = (rank - 1) / (total_rows - 1)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Where:

rank: The rank of the current row within the partition.
total_rows: The total number of rows in the partition.

The PERCENT_RANK() indicates the percentage of rows that fall below the current row’s value in the specified ORDER BY field.

This function can be helpful when you want to understand the distribution of data points and compare individual values within a dataset.

Examples of PostgreSQL PERCENT_RANK() Window Function #

Let’s take some examples of using the PERCENT_RANK() function with the products table:

Ranking Products by Safety Stock #

The following SELECT statement uses the PERCENT_RANK() window function to rank the products by their safety stocks from high to low:

SELECT
  product_name,
  safety_stock,
  PERCENT_RANK() OVER (
    ORDER BY
      safety_stock DESC
  ) AS safety_stock_rank
FROM
  products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

        product_name        | safety_stock |  safety_stock_rank
----------------------------+--------------+----------------------
 Samsung Galaxy Tab S9      |           60 |                    0
 Apple iPhone 15 Pro Max    |           50 | 0.041666666666666664
 Apple AirPods Pro 3        |           45 |  0.08333333333333333
 Samsung QN900C Neo QLED    |           40 |                0.125
 Sony Bravia XR A95K        |           30 |  0.16666666666666666
 Huawei Mate 60             |           30 |  0.16666666666666666
 Samsung Galaxy Buds Pro 2  |           30 |  0.16666666666666666
 LG OLED TV C3              |           20 |   0.2916666666666667
 Apple iPhone 15            |           20 |   0.2916666666666667
 Samsung Galaxy Z Fold 5    |           20 |   0.2916666666666667
 Apple Watch Series 9       |           20 |   0.2916666666666667
 Samsung Galaxy Watch 6     |           15 |   0.4583333333333333
 Apple iPad Pro 12.9        |           15 |   0.4583333333333333
 Samsung Galaxy S24         |           10 |   0.5416666666666666
 Sony Xperia 1 VI           |           10 |   0.5416666666666666
 LG G3 OLED                 |           10 |   0.5416666666666666
 Dell Inspiron 27           |            0 |   0.6666666666666666
 Sony HT-A7000 Soundbar     |            0 |   0.6666666666666666
 Bose SoundLink Max         |            0 |   0.6666666666666666
 Dell XPS 15                |            0 |   0.6666666666666666
 HP Spectre x360            |            0 |   0.6666666666666666
 Microsoft Surface Laptop 5 |            0 |   0.6666666666666666
 Lenovo ThinkPad X1 Carbon  |            0 |   0.6666666666666666
 Apple iMac 24"             |            0 |   0.6666666666666666
 Xiaomi Mi 14               |            0 |   0.6666666666666666Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example:

The ORDER BY safety_stock DESC clause orders the rows by safety stock in descending order.
The PERCENT_RANK() function assigns a percent rank value to each row.

To make the result more readable, you can cast the result into decimal numbers using the cast operator (::):

SELECT
  product_name,
  safety_stock,
  PERCENT_RANK() OVER (
    ORDER BY
      safety_stock DESC
  )::DEC(3,2) AS safety_stock_rank
FROM
  products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

        product_name        | safety_stock | safety_stock_rank
----------------------------+--------------+-------------------
 Samsung Galaxy Tab S9      |           60 |              0.00
 Apple iPhone 15 Pro Max    |           50 |              0.04
 Apple AirPods Pro 3        |           45 |              0.08
 Samsung QN900C Neo QLED    |           40 |              0.13
 Sony Bravia XR A95K        |           30 |              0.17
 Huawei Mate 60             |           30 |              0.17
 Samsung Galaxy Buds Pro 2  |           30 |              0.17
 LG OLED TV C3              |           20 |              0.29
 Apple iPhone 15            |           20 |              0.29
 Samsung Galaxy Z Fold 5    |           20 |              0.29
 Apple Watch Series 9       |           20 |              0.29
 Samsung Galaxy Watch 6     |           15 |              0.46
 Apple iPad Pro 12.9        |           15 |              0.46
 Samsung Galaxy S24         |           10 |              0.54
 Sony Xperia 1 VI           |           10 |              0.54
 LG G3 OLED                 |           10 |              0.54
 Dell Inspiron 27           |            0 |              0.67
 Sony HT-A7000 Soundbar     |            0 |              0.67
 Bose SoundLink Max         |            0 |              0.67
 Dell XPS 15                |            0 |              0.67
 HP Spectre x360            |            0 |              0.67
 Microsoft Surface Laptop 5 |            0 |              0.67
 Lenovo ThinkPad X1 Carbon  |            0 |              0.67
 Apple iMac 24"             |            0 |              0.67
 Xiaomi Mi 14               |            0 |              0.67Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Ranking Products Within Each Brand by Safety Stock #

The following SELECT statement uses the PERCENT_RANK() function to rank products within each brand by their safety stock in descending order:

SELECT
  brand_id,
  product_name,
  safety_stock,
  PERCENT_RANK() OVER (
    PARTITION BY
      brand_id
    ORDER BY
      safety_stock DESC
  )::DEC(3,2) AS safety_stock_rank
FROM
  products p;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 brand_id |        product_name        | safety_stock | safety_stock_rank
----------+----------------------------+--------------+-------------------
        1 | Samsung Galaxy Tab S9      |           60 |              0.00
        1 | Samsung QN900C Neo QLED    |           40 |              0.20
        1 | Samsung Galaxy Buds Pro 2  |           30 |              0.40
        1 | Samsung Galaxy Z Fold 5    |           20 |              0.60
        1 | Samsung Galaxy Watch 6     |           15 |              0.80
        1 | Samsung Galaxy S24         |           10 |              1.00
        2 | Apple iPhone 15 Pro Max    |           50 |              0.00
        2 | Apple AirPods Pro 3        |           45 |              0.20
        2 | Apple iPhone 15            |           20 |              0.40
        2 | Apple Watch Series 9       |           20 |              0.40
        2 | Apple iPad Pro 12.9        |           15 |              0.80
        2 | Apple iMac 24"             |            0 |              1.00
        3 | Huawei Mate 60             |           30 |              0.00
        4 | Xiaomi Mi 14               |            0 |              0.00
        5 | Sony Bravia XR A95K        |           30 |              0.00
        5 | Sony Xperia 1 VI           |           10 |              0.50
        5 | Sony HT-A7000 Soundbar     |            0 |              1.00
        6 | LG G3 OLED                 |           10 |              0.00
        7 | LG OLED TV C3              |           20 |              0.00
        7 | Dell Inspiron 27           |            0 |              0.33
        7 | Bose SoundLink Max         |            0 |              0.33
        7 | Dell XPS 15                |            0 |              0.33
        8 | HP Spectre x360            |            0 |              0.00
        9 | Microsoft Surface Laptop 5 |            0 |              0.00
       10 | Lenovo ThinkPad X1 Carbon  |            0 |              0.00Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this query:

The PARTITION BY brand_id clause divides the result set into partitions by brand_id.
The ORDER BY safety_stock DESC clause orders the rows within each partition by safety stock in descending order.

To retrieve the brand name, you can join the products table with the brands table:

SELECT
  brand_name,
  product_name,
  safety_stock,
  PERCENT_RANK() OVER (
    PARTITION BY
      brand_name
    ORDER BY
      safety_stock DESC
  )::DEC(3,2) AS safety_stock_rank
FROM
  products
  JOIN brands USING (brand_id);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 brand_name |        product_name        | safety_stock | safety_stock_rank
------------+----------------------------+--------------+-------------------
 Apple      | Apple iPhone 15 Pro Max    |           50 |              0.00
 Apple      | Apple AirPods Pro 3        |           45 |              0.20
 Apple      | Apple iPhone 15            |           20 |              0.40
 Apple      | Apple Watch Series 9       |           20 |              0.40
 Apple      | Apple iPad Pro 12.9        |           15 |              0.80
 Apple      | Apple iMac 24"             |            0 |              1.00
 Dell       | LG OLED TV C3              |           20 |              0.00
 Dell       | Bose SoundLink Max         |            0 |              0.33
 Dell       | Dell Inspiron 27           |            0 |              0.33
 Dell       | Dell XPS 15                |            0 |              0.33
 HP         | HP Spectre x360            |            0 |              0.00
 Huawei     | Huawei Mate 60             |           30 |              0.00
 Lenovo     | Lenovo ThinkPad X1 Carbon  |            0 |              0.00
 LG         | LG G3 OLED                 |           10 |              0.00
 Microsoft  | Microsoft Surface Laptop 5 |            0 |              0.00
 Samsung    | Samsung Galaxy Tab S9      |           60 |              0.00
 Samsung    | Samsung QN900C Neo QLED    |           40 |              0.20
 Samsung    | Samsung Galaxy Buds Pro 2  |           30 |              0.40
 Samsung    | Samsung Galaxy Z Fold 5    |           20 |              0.60
 Samsung    | Samsung Galaxy Watch 6     |           15 |              0.80
 Samsung    | Samsung Galaxy S24         |           10 |              1.00
 Sony       | Sony Bravia XR A95K        |           30 |              0.00
 Sony       | Sony Xperia 1 VI           |           10 |              0.50
 Sony       | Sony HT-A7000 Soundbar     |            0 |              1.00
 Xiaomi     | Xiaomi Mi 14               |            0 |              0.00Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example:

The PARTITION BY brand_name clause divides the result set into partitions by brand name.
The ORDER BY safety_stock DESC clause sorts the rows within each partition by safety stock in descending order.

Summary #

Use the PostgreSQL PERCENT_RANK() function to assign a relative rank value to each row within a partition of a result set.
The first value always has a rank of 0. Except for the first row, rows with the same values will receive the same percent rank.

Quiz #

Was this tutorial helpful ?