14일차 SQL 스터디: 단골 고객님 찾기

Orders 테이블:

OrderID	CustomerID	OrderDate	TotalAmount
101	1	2024-01-01	150
102	2	2024-01-03	200
103	1	2024-01-04	300
104	3	2024-01-04	50
105	2	2024-01-05	80
106	4	2024-01-06	400

 

Customers 테이블:

CustomerID	CustomerName	Country
1	Alice	USA
2	Bob	UK
3	Charlie	USA
4	David	Canada

요구사항:

1. 고객별로 주문 건수와 총 주문 금액을 조회하는 SQL 쿼리를 작성해주세요

- 출력 결과에는 고객 이름, 주문 건수, 총 주문 금액이 포함되어야 함 (단, 주문을 한 적이 없는 고객도 결과에 포함되어야 함)

# 내가 작성한 쿼리
SELECT c.CustomerName,
	COUNT(o.OrderID) OrderCount,
    SUM(o.TotalAmount) TotalSpent
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
GROUP BY o.CustomerID;

# 정답
SELECT c.CustomerName,
    COUNT(o.OrderID) AS OrderCount,
    COALESCE(SUM(o.TotalAmount), 0) AS TotalSpent
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName;

# 주문을 한 적이 없는 고객도 결과에 포함되어야 하므로 Customers c LEFT JOIN 사용
# NULL값이 발생하는 경우 0으로 대체하기 위해 COALESCE 사용
# 이 데이터에서는 첫 번째 쿼리도 동일한 결과를 출력하지만 데이터가 달라질 경우 정답 쿼리를 써야함!

 

- 기대결과  

CustomerName	OrderCount	TotalSpent
Alice	2	450
Bob	2	280
Charlie	1	50
David	1	400

 

 

2. 나라별로 총 주문 금액이 가장 높은 고객의 이름과 그 고객의 총 주문 금액을 조회하는 SQL 쿼리를 작성해주세요

- 기대결과

Country	Top_Customer	Top_Spent
USA	Alice	450
UK	Bob	280
Canada	David	400

# 내가 작성한 쿼리
SELECT c.Country, 
	c.CustomerName Top_Customer, 
	SUM(o.TotalAmount) Top_Spent
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName
ORDER BY Top_Spent DESC
LIMIT 3;

# 정답
SELECT c.Country,
    c.CustomerName AS Top_Customer,
    SUM(o.TotalAmount) AS Top_Spent
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.Country, c.CustomerName
HAVING SUM(o.TotalAmount) = (
        SELECT 
            MAX(SumSpent)
        FROM 
            (SELECT 
                 SUM(o2.TotalAmount) AS SumSpent
             FROM 
                 Customers c2
             JOIN 
                 Orders o2 ON c2.CustomerID = o2.CustomerID
             WHERE 
                 c2.Country = c.Country
             GROUP BY 
                 c2.CustomerID) AS Subquery
    );
    
# 나라별 주문 금액이 가장 높은 고객! 문제를 잘 읽자...!
# 주문이 없는 고객도 계산에 포함하려면 LEFT JOIN을 써야함
# 이번 쿼리는 꽤 어렵...

제약사항:

• 두 쿼리 모두 서브쿼리, JOIN, GROUP BY, HAVING 등을 사용해 풀 수 있어야 한다
• 주문을 한 적이 없는 고객도 첫 번째 쿼리 결과에 포함되어야 한다