SQL Practice Problem #021 - Rank orders within each customer

Problem

Task

Return each order with a rank number based on total_amount from highest to lowest within the same customer.

Schema

Table Schema

orders(id, customer_id, total_amount)

Input

Sample Data

id	customer_id	total_amount
701	1	50
702	1	120
703	2	90

Output

Expected Output

id	customer_id	total_amount	customer_order_rank
702	1	120	1
701	1	50	2
703	2	90	1

Answer

Check Your Solution

Show Answer and Explanation

Correct Answer

SELECT id, customer_id, total_amount,
  ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY total_amount DESC
  ) AS customer_order_rank
FROM orders
ORDER BY customer_id, customer_order_rank;

Explanation

PARTITION BY restarts the numbering for each customer. ORDER BY total_amount DESC makes the largest order rank first inside each partition.

Common Mistakes

Using GROUP BY, which collapses rows instead of ranking each row.
Leaving out PARTITION BY and ranking all customers together.
Using ASC when the highest amount should be first.

Concepts

Related Concepts

Window Functions ROW_NUMBER PARTITION BY Window Function

Next practice