Interview › SQL Joins › Orders With Full Product Line Details
⌨ Programming Hard SQL Joins

Orders With Full Product Line Details

Question

For each order, list the order ID, customer name, order date, and a count of distinct products in that order. Return only orders with 3 or more distinct products.

💡 Join customers, orders, and order_items.
SQL Editor
Solution
SELECT
  o.order_id,
  c.first_name || ' ' || c.last_name AS customer_name,
  o.order_date,
  COUNT(DISTINCT oi.product_id) AS distinct_products
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id, c.first_name, c.last_name, o.order_date
HAVING COUNT(DISTINCT oi.product_id) >= 3
ORDER BY distinct_products DESC;
Explanation: Three-table JOIN, GROUP BY order, use COUNT(DISTINCT product_id) to count unique products per order, filter with HAVING.