PERCENT_RANK [analytic]

Calculates the relative rank of a row for a given row in a group within a by dividing that row’s rank less 1 by the number of rows in the partition, also less 1.

Calculates the relative rank of a row for a given row in a group within a window by dividing that row’s rank less 1 by the number of rows in the partition, also less 1. PERCENT_RANK always returns values from 0 to 1 inclusive. The first row in any set has a PERCENT_RANK of 0. The return value is NUMBER.

( rank - 1 ) / ( [ rows ] - 1 )

In the preceding formula, rank is the rank position of a row in the group and rows is the total number of rows in the partition defined by the OVER() clause.

Behavior type

Immutable

Syntax

PERCENT_RANK ( ) OVER (
    [ window-partition-clause ]
    window-order-clause  )

Parameters

OVER()
See Analytic Functions

Examples

The following example finds the percent rank of gross profit for different states within each month of the first quarter:

=> SELECT calendar_month_name AS MONTH, store_state,
      SUM(gross_profit_dollar_amount),
      PERCENT_RANK() OVER (PARTITION BY calendar_month_name
      ORDER BY SUM(gross_profit_dollar_amount)) AS PERCENT_RANK
   FROM store.store_sales_fact JOIN date_dimension
   USING(date_key)
   JOIN store.store_dimension
   USING (store_key)
   WHERE calendar_month_name IN ('January','February','March')
   AND store_state IN ('OR','IA','DC','NV','WI')
   GROUP BY calendar_month_name, store_state
   ORDER BY calendar_month_name, PERCENT_RANK;
  MONTH   | store_state |  SUM   | PERCENT_RANK
----------+-------------+--------+--------------
 February | IA          | 418490 |            0
 February | OR          | 460588 |         0.25
 February | DC          | 616553 |          0.5
 February | WI          | 619204 |         0.75
 February | NV          | 838039 |            1
 January  | OR          | 446528 |            0
 January  | IA          | 474501 |         0.25
 January  | DC          | 628496 |          0.5
 January  | WI          | 679382 |         0.75
 January  | NV          | 871824 |            1
 March    | IA          | 460282 |            0
 March    | OR          | 481935 |         0.25
 March    | DC          | 716063 |          0.5
 March    | WI          | 771575 |         0.75
 March    | NV          | 970878 |            1
(15 rows)

The following example calculates, for each employee, the percent rank of the employee's salary by their job title:

=> SELECT job_title, employee_last_name, annual_salary,
       PERCENT_RANK()
      OVER (PARTITION BY job_title ORDER BY annual_salary DESC) AS percent_rank
   FROM employee_dimension
   ORDER BY percent_rank, annual_salary;
     job_title      | employee_last_name | annual_salary |    percent_rank
--------------------+--------------------+---------------+---------------------
 Cashier            | Fortin             |          3196 |                   0
 Delivery Person    | Garnett            |          3196 |                   0
 Cashier            | Vogel              |          3196 |                   0
 Customer Service   | Sanchez            |          3198 |                   0
 Shelf Stocker      | Jones              |          3198 |                   0
 Custodian          | Li                 |          3198 |                   0
 Customer Service   | Kramer             |          3198 |                   0
 Greeter            | McNulty            |          3198 |                   0
 Greeter            | Greenwood          |          3198 |                   0
 Shift Manager      | Miller             |         99817 |                   0
 Advertising        | Vu                 |         99853 |                   0
 Branch Manager     | Jackson            |         99858 |                   0
 Marketing          | Taylor             |         99928 |                   0
 Assistant Director | King               |         99973 |                   0
 Sales              | Kramer             |         99973 |                   0
 Head of PR         | Goldberg           |        199067 |                   0
 Regional Manager   | Gauthier           |        199744 |                   0
 Director of HR     | Moore              |        199896 |                   0
 Head of Marketing  | Overstreet         |        199955 |                   0
 VP of Advertising  | Meyer              |        199975 |                   0
 VP of Sales        | Sanchez            |        199992 |                   0
 Founder            | Gauthier           |        927335 |                   0
 CEO                | Taylor             |        953373 |                   0
 Investor           | Garnett            |        963104 |                   0
 Co-Founder         | Vu                 |        977716 |                   0
 CFO                | Vogel              |        983634 |                   0
 President          | Sanchez            |        992363 |                   0
 Delivery Person    | Li                 |          3194 | 0.00114155251141553
 Delivery Person    | Robinson           |          3194 | 0.00114155251141553
 Custodian          | McCabe             |          3192 | 0.00126582278481013
 Shelf Stocker      | Moore              |          3196 | 0.00128040973111396
 Branch Manager     | Moore              |         99716 | 0.00186567164179104
...

See also