[달리기반 SQL] 4번 문제

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

---

 

 방법 1. max값을 조건문으로 연결하기 

1단계 : 개인별 총 구매 금액을 구하는 테이블 생성

SELECT c2.CustomerName,
	sum(o2.totalamount) AS totalspent
FROM Customers c2 LEFT JOIN Orders o2 ON o2.customerID = c2.customerID
GROUP BY 1

 

2단계 : 개인별 총 구매 금액의 MAX값 구하는 테이블 생성

SELECT max(totalspent)
FROM 
(
SELECT c2.CustomerName,
	 sum(o2.totalamount) AS totalspent
FROM Customers c2 LEFT JOIN Orders o2 ON o2.customerID = c2.customerID
GROUP BY 1
 ) a

 

3단계 : '개인별 총 구매금액 = MAX값' 조건을 가진 최종 테이블 생성

* Group by에 조건을 거는 Having 문 사용

SELECT c.Countrty AS Country,
	  c.CustomerName AS Top_Customer,
	  sum(o.TotalAmount) AS Top_Spent
FROM customers c INNER JOIN  orders o ON o.customerID = c.customerID
GROUP BY 1,2 
HAVING sum(o.TotalAmount) = (
						SELECT max(totalspent)
						FROM 
					      (
						SELECT c2.CustomerName,
						sum(o2.totalamount) AS totalspent
						FROM Customers c2
						LEFT JOIN Orders o2 ON o2.customerID = c2.customerID
                        			WHERE c2.Country = c.Country
						GROUP BY 1
					       )	 a
				           ) ;

 

 

🔶 궁금한 점 

서브쿼리 a의 [ WHERE c2.Country = c.Country ] 는 왜 필요함? 

답 : 메인쿼리가 각 국가별로 그룹핑 되어있기 때문에 조건도 국가별로 되어있어야 하나하나 집계할 수 있음. ⭐️⭐️⭐️⭐️⭐️

[ WHERE c2.Country = c.Country ]  가 빠지면 전체 데이터에서 최댓값을 가진 행만 추출됨.

 

 

 방법 2. max값을 window함수 (rank) 사용해서 나타내기  

1단계 : 개인별 총 구매 금액을 구하는 테이블 생성

SELECT c2.CustomerName,
       sum(o2.totalamount) AS totalspent
FROM Customers c2 LEFT JOIN Orders o2 ON o2.customerID = c2.customerID
GROUP BY 1

 

2단계 : 개인별 총 구매금액 테이블과 고객 테이블을 JOIN
           + 총 구매금액 기준으로 순위 매기는 테이블 생성

* WINDOW 함수 (RANK) 사용

SELECT c3.Country,
	  c3.CustomerName,
	  a.totalspent,
	  RANK() OVER (PARTITION BY c3.country ORDER BY totalspent DESC) AS rnk
FROM
 	( SELECT c2.CustomerName,
		sum(o2.totalamount) AS totalspent
	FROM Customers c2 LEFT JOIN Orders o2 ON o2.customerID = c2.customerID
	GROUP BY 1
         ) a INNER JOIN Customers c3 ON a.CustomerName = c3.CustomerName

 

3단계 : 1순위인 사람만 뽑는 조건을 가진 최종 테이블 생성

SELECT Country,	
	  CustomerName AS Top_Customer,
	  totalspent AS Top_Spent
FROM
(
SELECT c3.Country,
	  c3.CustomerName,
	  a.totalspent,
	  RANK() OVER (PARTITION BY c3.country ORDER BY totalspent desc) AS rnk
FROM
 	( SELECT c2.CustomerName,
		sum(o2.totalamount) AS totalspent
	FROM Customers c2 LEFT JOIN Orders o2 ON o2.customerID = c2.customerID
	GROUP BY 1
         ) a INNER JOIN Customers c3 ON a.CustomerName = c3.CustomerName
) b
WHERE rnk = 1