rank.md

Ranking Data

Description

A common requirement is the need to rank a particular row in a group by some quantity - for example, 'rank stores by total sales in each region'. Fortunately, Postgres natively has a rank window function to do this. The syntax looks like this:

SELECT
  RANK() OVER(PARTITION BY <fields> ORDER BY <rank_by>) AS RANK
FROM <schema.tablename>

where

• fields - zero or more columns to split the results by - a rank will be calculated for each unique combination of values in these columns
• rank_by - a column to decide the ranking
• schema.table - where to pull these columns from

Example

We want to find out, for our random data sample of fruits, which fruit is most popular.

WITH data AS (
    SELECT CASE
               WHEN RANDOM() <= 0.25 THEN 'apple'
               WHEN RANDOM() <= 0.5 THEN 'banana'
               WHEN RANDOM() <= 0.75 THEN 'pear'
               ELSE 'orange'
               END AS fruit,
           RANDOM() + (series^RANDOM()) AS popularity
    FROM generate_series(1, 10) AS series
    )
    
SELECT
  fruit,
  popularity,
  RANK() OVER(ORDER BY popularity DESC) AS popularity_rank
FROM data

fruit	popularity	popularity_rank
banana	3.994866211350613	1
banana	3.2802621307398208	2
banana	2.626913466142052	3
pear	2.59071184223806	4
banana	2.137745293125355	5
apple	2.0741654350518637	6
apple	2.0401770856253933	7
pear	1.771223675755631	8
banana	1.743055659082957	9
pear	1.7158950640518675	10