15일차 SQL 스터디: 가장 많이 팔린 품목은?

문제:

Products 테이블:

ProductID	ProductName	Category	Price
1	Laptop	Electronics	1000
2	Smartphone	Electronics	800
3	Headphones	Electronics	150
4	Coffee Maker	Home	200
5	Blender	Home	100

 

Orders 테이블:

OrderID	ProductID	OrderDate	Quantity	CustomerID
101	1	2024-02-01	2	1
102	3	2024-02-02	1	2
103	2	2024-02-03	1	1
104	4	2024-02-04	3	3
105	1	2024-02-05	1	2
106	5	2024-02-06	2	3

 

Customers 테이블:

CustomerID	CustomerName	Country
1	Alice	USA
2	Bob	UK
3	Charlie	USA

요구사항:

1. 각 고객이 구매한 모든 제품의 총 금액을 계산하고, 고객 이름, 총 구매 금액, 주문 수를 출력하는 SQL 쿼리를 작성해주세요. 

CustomerName	Total Amount	OrderCount
Alice	2600	3
Bob	950	2
Charlie	800	2

// 내가 작성한 쿼리
SELECT 
	c.CustomerName,
	SUM(p.Price*o.Quantity) TotalAmount,
   	COUNT(o.OrderID) OrderCount
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName;
// 3개의 테이블을 어떻게 조인하는가?

// 정답: 그냥 이어서 하면 됨...!
SELECT 
    c.CustomerName,
    SUM(p.Price * o.Quantity) AS TotalAmount,
    COUNT(o.OrderID) AS OrderCount
FROM 
    Customers c
JOIN 
    Orders o ON c.CustomerID = o.CustomerID
JOIN 
    Products p ON o.ProductID = p.ProductID
GROUP BY 
    c.CustomerName;

 

2. 각 제품 카테고리별로 가장 많이 팔린 제품의 이름과 총 판매량을 조회하는 SQL 쿼리를 작성해주세요.  

Category	Top_Product	TotalSold
Electronics	Laptop	3
Home	Coffee Maker	3

// 내가 작성한 쿼리
SELECT 
	p.Category, 
	p.ProductName Top_Product, 
    O2.TotalSold
FROM Products p
JOIN Orders o ON p.ProductID = o.ProductID
JOIN Orders o2 ON O2.ProductID = o.ProductID
WHERE 
	O2.TotalSold = (
    	SELECT MAX(Quantity)
        FROM Orders o2
        GROUP BY ProductID
    );
    
// 정답
SELECT // 3. SUM(o.Quantity) 값 중 MAX(SumQuantity) 값을 가진 값만 출력
    p.Category,
    p.ProductName AS Top_Product,
    SUM(o.Quantity) AS TotalSold
FROM Products p
JOIN Orders o ON p.ProductID = o.ProductID
GROUP BY p.Category, p.ProductName
HAVING 
    SUM(o.Quantity) = (
        SELECT // 2. 카테고리별 수량 합계 중 최댓값 구하기
            MAX(SumQuantity)
        FROM 
            (SELECT // 1. 카테고리별 수량 합계 구하기
                 p2.Category,
                 SUM(o2.Quantity) AS SumQuantity
             FROM Products p2
             JOIN Orders o2 ON p2.ProductID = o2.ProductID
             GROUP BY p2.Category, p2.ProductID) AS Subquery
        WHERE Subquery.Category = p.Category
    );

제약사항:

• 두 쿼리 모두 서브쿼리, JOIN, GROUP BY, HAVING 등의 SQL 기능을 사용해 문제를 풀어야 합니다.
• 여러 테이블 간의 관계를 명확히 이해하고, 데이터를 효율적으로 결합하는 것이 중요해요!