SQL Query to Rank Customers Based on Diversity of Cuisines Tried

Question

I need help with a SQL query that ranks each customer based on the diversity of cuisines they have tried. The rank should be unique (not repeated) and should not skip any values. The goal is to determine which customers have tried the most distinct cuisines and rank them accordingly. Here are the details:

1. Expected Output:

consumer_id

distinct_cuisines_tried

diversity_rank

2. Requirements:

Rank 1 should be assigned to the customer who has tried the most diverse cuisines.

The rank should be unique (use ROW_NUMBER()).

The results should be sorted by distinct_cuisines_tried in descending order, then by diversity_rank in ascending order, and finally by consumer_id in ascending order.

  WITH CuisineDiversity AS (
    SELECT 
        consumer_id,
        COUNT(DISTINCT cuisine_id) AS distinct_cuisines_tried
    FROM 
        orders
    GROUP BY 
        consumer_id
),
RankedCustomers AS (
    SELECT 
        consumer_id,
        distinct_cuisines_tried,
        ROW_NUMBER() OVER (ORDER BY distinct_cuisines_tried DESC) AS diversity_rank
    FROM 
        CuisineDiversity
)
SELECT 
    consumer_id,
    distinct_cuisines_tried,
    diversity_rank
FROM 
    RankedCustomers
ORDER BY 
    distinct_cuisines_tried DESC,
    diversity_rank ASC,
    consumer_id ASC;

3. Expected Output:

consumer_id: ID of the consumer

distinct_cuisines_tried: Number of distinct cuisines tried by the consumer

diversity_rank: Rank based on the number of distinct cuisines tried

Answer 1

You can use consumer_id in order for row_number().
This is how you will meet the requirements.
The results should be

• sorted by distinct_cuisines_tried in descending order
• then by diversity_rank in ascending order
• and finally by consumer_id in ascending order.

I've shortened one CTE.

WITH CuisineDiversity AS (
    SELECT consumer_id,
        COUNT(DISTINCT cuisine_id) AS distinct_cuisines_tried,
        row_number() OVER (ORDER BY COUNT(DISTINCT cuisine_id) DESC,consumer_id ASC) AS diversity_rank
    FROM orders
    GROUP BY consumer_id
)
SELECT consumer_id,
    distinct_cuisines_tried,
    diversity_rank
FROM CuisineDiversity
ORDER BY 
    distinct_cuisines_tried DESC,
    diversity_rank ASC,
    consumer_id ASC;

fiddle

Actually, the ORDER BY clause is executed last in the request and all the above-mentioned names are available to it. Also, ordering by the row-number is the same as ordering by its constituent PARTITION BY and ORDER BY clauses, so it's redundant.

Then the query becomes compact and beautiful.

SELECT consumer_id,
    COUNT(DISTINCT cuisine_id) AS distinct_cuisines_tried,
    row_number() 
         OVER (ORDER BY COUNT(DISTINCT cuisine_id) DESC,consumer_id) AS diversity_rank
FROM orders
GROUP BY consumer_id
ORDER BY 
    distinct_cuisines_tried DESC,
    consumer_id ASC;

Fiddle