SQL 달리기반 Lv4. 단골 고객님 찾기

문제:

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 쿼리를 작성해주세요.

   1. 출력 결과에는 고객 이름, 주문 건수, 총 주문 금액이 포함되어야 합니다. 단, 주문을 한 적이 없는 고객도 결과에 포함되어야 합니다.

   2. 기대결과

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

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

   1. 기대결과

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

---

1번 문제 풀이
Orders 테이블에서
CustomerID로 그룹화하고 총 주문 건수와 총 주문 금액을 구한 후,
Customers 테이블과 CustomerID로 조인하여 이름을 출력한다.

select c.CustomerName, count(1) as OrderCount, sum(o.TotalAmount) as TotalSpent
from orders o join customer c on o.CustomerID =c.CustomerID 
group by c.CustomerName

2번 문제 풀이

먼저 1번 정답 테이블에서 Country 칼럼도 출력하게 했다.

select c.CustomerName, count(1) as OrderCount, sum(o.TotalAmount) as TotalSpent, c.Country
from orders o join customer c on o.CustomerID =c.CustomerID 
group by c.Country, c.CustomerName

그 다음 특정 나라('USA')의 Max(TotalAmount)를 추출하는 쿼리를 작성했다.

select max(temp.TotalSpent)
	from (
		select c.Country, c.CustomerName, sum(o.TotalAmount) as TotalSpent, count(1) as OrderCount
		from orders o join customer c on o.CustomerID =c.CustomerID 
		group by c.Country, c.CustomerName
	) as temp
	where temp.Country = 'USA'

마지막으로 다시 원래의 테이블과 비교하여 where 절에서
sum(TotalAmount) 값이 추출한 Max값과 같게 필터링하려고 했는데 안 됐고
having 절에서 적용하니 잘 작동했다. having을 쓰기까지 오래 걸렸다.

select c.Country, c.CustomerName, sum(o.TotalAmount) as Top_Spent
from orders o join customer c on o.CustomerID =c.CustomerID
group by c.Country, c.CustomerName
having sum(o.TotalAmount) = (
	select max(temp.TotalSpent)
	from (
		select c.Country, c.CustomerName, sum(o.TotalAmount) as TotalSpent, count(1) as OrderCount
		from orders o join customer c on o.CustomerID =c.CustomerID 
		group by c.Country, c.CustomerName
	) as temp
	where temp.Country = c.Country 
	group by temp.Country
)

그룹화를 할 때, where 필터링과 having 필터링의 작동 방식의 차이를 좀 더 잘 이해해야겠다.

where

• 그룹화 전에 데이터에 대해 조건 검사
• 특정 조건을 만족하는 행들을 그룹에 포함하고자 할 때

having

• 그룹화가 된 데이터에 대해 조건 검사
• 그룹화된 결과에 조건을 걸 때 사용

특히, 집계 함수를 사용한 조건을 걸 때는 무조건 HAVING을 사용한다