Table Schema
Inspect
Table
| Column | Type | Description |
|---|---|---|
| order_id | integer | Order identifier. |
| product_id | integer | Product identifier. |
| is_returned | integer | 1 if the product was returned, otherwise 0. |
Most
Returned
Product
Find the product with the highest return rate.
Inspect
Sample Data
Input
Output
Sample Input: orders
| order_id | product_id | is_returned |
|---|---|---|
| 1 | 10 | 1 |
| 2 | 10 | 0 |
| 3 | 20 | 1 |
| 4 | 20 | 1 |
| 5 | 30 | 0 |
| 6 | 30 | 0 |
Expected Output
| product_id | return_rate |
|---|---|
| 20 | 100 |
SQL Editor
Run
Query
Waiting for query
| product_id | return_rate |
|---|---|
| 20 | 100 |
Hints
Unlock
Clues
Hint 01: Group rows by product_id.
Hint 02: Calculate returned rows divided by total rows for each product.
Hint 03: Keep the product with the largest return percentage.
Solution
Locked
Answer
Solution is locked until you decide to reveal it. Try the editor first, then open this when you want the reference answer.
WITH product_returns AS (
SELECT
product_id,
ROUND(AVG(is_returned) * 100, 0) AS return_rate
FROM orders
GROUP BY product_id
),
ranked_products AS (
SELECT
product_id,
return_rate,
DENSE_RANK() OVER (ORDER BY return_rate DESC) AS return_rank
FROM product_returns
)
SELECT
product_id,
return_rate
FROM ranked_products
WHERE return_rank = 1
ORDER BY product_id;
Explanation
Step By
Step
01
Calculate the return percentage for each product.
02
Rank products by that percentage from highest to lowest.
03
Keep the top-ranked product or products if there is a tie.
Related Questions