-- 1)전체 고객의 30% 이상이 구매한 제품의 제품카테고리와 고객이름을 출력하시오 SELECT DISTINCT b.productid , b.productname , c.categoryid , c.categoryname , f.customerid , f.customername FROM (SELECT a.productid FROM orderdetails a INNER JOIN orders b ON a.orderid = b.orderid GROUP BY a.productid HAVING COUNT(DISTINCT b.customerid) >= (SELECT COUNT(*) * 0.3 FROM customers) ) a INNER JOIN products b ON a.productid = b.productid INNER JOIN categories c ON b.categoryid = c.categoryid INNER JOIN orderdetails d ON a.productid = d.productid INNER JOIN orders e ON d.orderid = e.orderid INNER JOIN customers f ON e.customerid = f.customerid ;
-- 2)2가지 이상의 카테고리에 제품을 구매한 고객의 이름을 출력하시오 SELECT a.customername FROM customers a INNER JOIN orders b ON a.customerid = b.customerid INNER JOIN orderdetails c ON b.orderid = c.orderid INNER JOIN products d ON c.productid = d.productid GROUP BY a.customerid, a.customername HAVING COUNT(DISTINCT d.categoryid) >= 2 ;