Interview › SQL Joins › Products Ordered More Than 50 Times
⌨ Programming Medium SQL Joins

Products Ordered More Than 50 Times

Question

Find products that have appeared in more than 50 order line items. Return product name and the count of times ordered.

💡 Use products and order_items tables.
SQL Editor
Solution
SELECT
  p.product_name,
  COUNT(oi.item_id) AS times_ordered
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name
HAVING COUNT(oi.item_id) > 50
ORDER BY times_ordered DESC;
Explanation: JOIN products to order_items, GROUP BY product, COUNT the items, filter with HAVING > 50.