Find the best-selling product in each category, using rating to break ties.
Table Schema
| Column | Type | Description |
|---|---|---|
| product_id | integer | Unique product identifier. |
| product_name | string | Name of the product. |
| category_name | string | Product category. |
| Column | Type | Description |
|---|---|---|
| product_id | integer | Product identifier. |
| sales_quantity | integer | Total units sold. |
| rating | decimal | Product rating. |
Sample Data
| product_id | product_name | category_name |
|---|---|---|
| 1 | Laptop | Electronics |
| 2 | Phone | Electronics |
| 3 | Book A | Books |
| 4 | Book B | Books |
| 5 | TV | Electronics |
| 6 | Book C | Books |
| product_id | sales_quantity | rating |
|---|---|---|
| 1 | 200 | 4.5 |
| 2 | 250 | 4.2 |
| 3 | 300 | 4.8 |
| 4 | 300 | 4.6 |
| 5 | 250 | 4.7 |
| 6 | 100 | 4.9 |
| category_name | product_name |
|---|---|
| Books | Book A |
| Electronics | Phone |
SQL Editor
| category_name | product_name |
|---|---|
| Books | Book A |
| Electronics | Phone |
Hints
Solution
Solution is locked until you decide to reveal it. Try the editor first, then open this when you want the reference answer.
WITH ranked_products AS (
SELECT
p.category_name,
p.product_name,
ROW_NUMBER() OVER (
PARTITION BY p.category_name
ORDER BY ps.sales_quantity DESC, ps.rating DESC
) AS product_rank
FROM products AS p
JOIN product_sales AS ps
ON p.product_id = ps.product_id
)
SELECT
category_name,
product_name
FROM ranked_products
WHERE product_rank = 1;
Explanation
Join product details to sales data using product_id.
Rank products within each category by sales_quantity descending.
Use rating descending as the tie-breaker, then keep the top-ranked product.
Related Questions