Retail: Most Expensive Product in Each Category

SQL coding challenge · Difficulty: medium · +100 XP

Problem

The retail team wants to feature the most expensive product in each category on the homepage. If two products share the top price in a category, return both.

Tables

Table: Customers

| customer_id | name | email | city |
| --- | --- | --- | --- |
| 1 | Rahul | rahul@gmail.com | Delhi |
| 2 | Prince | prince@gmail.com | Mumbai |
| 3 | Mohit | mohit@gmail.com | Bangalore |
| 4 | Sudheer | sudheer@gmail.com | Hyderabad |

Table: Products

| product_id | product_name | category | price |
| --- | --- | --- | --- |
| 101 | T-Shirt | Apparel | 599.00 |
| 102 | Jeans | Apparel | 1299.00 |
| 103 | Sneakers | Footwear | 2499.00 |
| 104 | Backpack | Accessories | 899.00 |
| 105 | Watch | Accessories | 1499.00 |

Table: Orders

| order_id | customer_id | order_date | total_amount | payment_mode |
| --- | --- | --- | --- | --- |
| 201 | 1 | 2024-05-01 | 1898.00 | UPI |
| 202 | 2 | 2024-05-02 | 2499.00 | Credit Card |
| 203 | 3 | 2024-05-03 | 1399.00 | Debit Card |
| 204 | 1 | 2024-05-04 | 599.00 | UPI |
| 205 | 4 | 2024-05-05 | 3898.00 | Credit Card |
| 206 | 2 | 2024-05-06 | 899.00 | UPI |

Expected Output

| category | product_name | price |
| --- | --- | --- |
| Accessories | Watch | 1499.00 |
| Apparel | Jeans | 1299.00 |
| Footwear | Sneakers | 2499.00 |

Return: category, product_name, price
If there is a price tie, return all tied products
Sort by category ascending, then price descending