SQL 실전!(4)

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 쿼리를 작성해주세요.
   • 출력 결과에는 고객 이름, 주문 건수, 총 주문 금액이 포함되어야 합니다. 단, 주문을 한 적이 없는 고객도 결과에 포함되어야 합니다.
   • 기대결과

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

 

문제

1번

고객별로 주문 건수와 총 주문 금액을 조회하는 SQL 쿼리를 작성해주세요.
출력 결과에는 고객 이름, 주문 건수, 총 주문 금액이 포함되어야 합니다. 단, 주문을 한 적이 없는 고객도 결과에 포함되어야 합니다.

 

문제풀이

어떤 테이블? : Customers를 기준으로 LEFT JOIN(주문한 적 없는 고객도 결과에 포함시켜야 하니까)
어떤 컬럼? : CustomerID, CustomerName, TotalAmount
조건 : x
어떤 함수? : COUNT(CustomerID), SUM(TotalAmount) … GROUP BY CustomerName

 

정답:

SELECT co.CustomerName AS CustomerName
    , COUNT(co.CustomerID) AS OrderCount
    , SUM(co.TotalAmount) AS TotalSpent
FROM
(
SELECT c.CustomerID
    , c.CustomerName
    , o.TotalAmount
FROM Customers AS c
LEFT JOIN Orders AS o
ON c.CustomerID = o.CustomerID
) co
GROUP BY co.CustomerID, co.CustomerName
;

 

결과 확인:

 

문제

2번

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

 

 

문제풀이

어떤 테이블? : Orders를 기준으로 Customers를 LEFT JOIN
필요한 컬럼 : Country, CustomerName, TotalAmount
조건 : WHERE ranking = 1
컬럼에 필요한 함수 : SUM(TotalAmount) … GROUP BY Country, RANK() OVER (PARTITION BY Country ORDER BY Top_Spent DESC), Orders를 기준으로 Customers를 LEFT JOIN → Country, Top_Customer(CustomerName), Top_Spent, RANK() 컬럼을 조회 → RANK() 함수

 

정답:

SELECT oc.Country
    , oc.Top_Customer
    , oc.Top_Spent
FROM
(
SELECT c.Country
    , c.CustomerName AS Top_Customer
    , SUM(o.TotalAmount) as Top_Spent
    , RANK() OVER (PARTITION BY c.Country ORDER BY SUM(o.TotalAmount) DESC) AS ranking
FROM Orders AS o
LEFT JOIN Customers AS c
ON o.CustomerID = c.CustomerID
GROUP BY c.COUNTRY, c.CustomerName
) oc
WHERE ranking = 1
ORDER BY Top_Customer
;

결과 확인:

다른 방식으로 풀어보기

문제의 힌트를 보았더니 HAVING 함수에 대한 언급이 있었다.

 

W3Schools에서 해당 함수에 대해 살펴보았다.

 

HAVING은 WHERE 절 대신에 집계함수를 사용하여 쓰일 수 있다고 한다. 앞선 문제 풀이에서 조건식을 WHERE ranking = 1로 작성하였지만, HAVING Top_Spent = (총 주문금액의 최댓값을 구하는 서브쿼리) 사용한다면 나라별 총 주문 금액이 가장 많은 고객을 찾아낼 수 있지 않을까?

 

해당 문법에 대해 잘 알진 못해서 챗GPT의 도움을 받았다.

SELECT c.Country
    , c.CustomerName AS Top_Customer
    , SUM(o.TotalAmount) AS Top_Spent
FROM Orders AS o
LEFT JOIN Customers AS c
ON o.CustomerID = c.CustomerID
GROUP BY c.COUNTRY, c.CustomerName
HAVING
Top_Spent =
(
SELECT MAX(Top_Spent)
FROM
(
SELECT SUM(o2.TotalAmount) Top_Spent
FROM Orders AS o2
LEFT JOIN Customers AS c2
ON o2.CustomerID = c2.CustomerID
WHERE c2.Country = c.Country
GROUP BY c2.CustomerName
) oc
)
ORDER BY Top_Customer
;

결과 확인:

결과는 같게 나온다.