Top 3 Best-Selling Products by Units Sold
Which 3 products have sold the most total units across all orders?
- Joins
- Aggregation
- Sorting
- Top-N
Challenge brief
Understand the request
Catalog Team wants to highlight top sellers on the homepage and needs the 3 products with the most units moved.
Find the top 3 best-selling products by total units sold.
Return
- product_name
- category
- total_units_sold
Constraints
- Total units sold = SUM of quantity across all order_items rows for that product
- Return exactly 3 products
- Order by total_units_sold descending, then product_name alphabetically as a tiebreaker
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
products
product_idINTEGERproduct_nameVARCHAR(200)categoryVARCHAR(100)
order_items
product_idINTEGERquantityINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Product names are in products. The quantity sold per order line is in order_items. You need to join them on product_id, then sum up quantities per product.
Hint 2
JOIN products to order_items on product_id. Use SUM(oi.quantity) AS total_units_sold with GROUP BY product. Add ORDER BY total_units_sold DESC, product_name and cap with LIMIT 3.
Hint 3
Scaffold: aggregate quantity at product grain, sort units descending with product name as the tie-breaker, and retain three rows.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT p.product_name, p.category, SUM(oi.quantity) AS total_units_sold FROM products p INNER JOIN order_items oi ON p.product_id = oi.product_id GROUP BY p.product_id, p.product_name, p.category ORDER BY total_units_sold DESC, p.product_name LIMIT 3;Why this works
SUM(quantity) aggregates all units sold across every order line for each product. LIMIT 3 after ORDER BY returns the top 3. Three products tie at 3 units each — the alphabetical tiebreaker determines the final order.
Success check
3 products — Bluetooth Speaker, USB-C Cable, Wireless Mouse (each with 3 units sold)
Expected result
Use this output to verify values, aliases, ordering, and row count.
| product_name | category | total_units_sold |
|---|---|---|
| Bluetooth Speaker | Electronics | 3 |
| USB-C Cable | Electronics | 3 |
| Wireless Mouse | Electronics | 3 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Explore related company challenges
Airbnb
Independent Airbnb-style marketplace, booking, listing, payment, review, and guest analytics SQL practice.
Uber
Independent Uber-style mobility marketplace SQL practice covering trips, drivers, riders, pricing, payments, and promotions.
Microsoft
Independent Microsoft-style cloud, productivity, subscription, usage, support, and customer analytics SQL practice.
Return to the complete interview preparation experience.