2025.01.16(목) 1일 1쿼리

Quiz 1) 프린트를 구매한 회원들이 산 제품들을 출력하시오. (프린트 제외)

 

SELECT 
    c.customer_id, 
    c.name, 
    p.name AS most_purchased_product, 
    COUNT(od.product_id) AS product_count
FROM customers AS c
JOIN orders AS o ON c.customer_id = o.customer_id
JOIN orderdetails AS od ON o.order_id = od.order_id
JOIN products AS p ON p.product_id = od.product_id
WHERE c.customer_id IN (
    SELECT DISTINCT o.customer_id
    FROM orders AS o
    JOIN orderdetails AS od ON o.order_id = od.order_id
    JOIN products AS p ON p.product_id = od.product_id
    WHERE p.name = 'Printer'
)
AND p.name != 'Printer'
GROUP BY c.customer_id, c.name, p.name
ORDER BY product_count DESC;

 

결과화면