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

      내가 작성한 쿼리 :

select CustomerName , 
       count(distinct OrderID) OrderCount, 
       SUM(TotalAmount) TotalSpent
from customers c 
     left join orders o 
     on c.CustomerID = o.CustomerID
group by 1
having count(OrderID) >=0

주문정보가 없는 고객의 데이터도 가져와야 하니, customers테이블을 기준으로 join해준다.
각 고객의 총 주문 건수와 총 주문 금액을 계산해야 하니, count()함수와 SUM()함수를 사용해준다. 각 고객별로 결과를 알고 싶으니, group by CustomerName 으로 그룹핑 해준다. (=group by 1)

중복되는 주문 없이 카운트 할 수 있도록 distinct키워드를 사용해준다.

where 절에 aggregate 함수 넣고 싶을 때, where 대신 having 을 쓰면 된다.

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

   1. 기대결과

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

내가 작성한 쿼리 :

select Country, CustomerName, sum_amount
from
(
select Country, CustomerName, sum_amount,
       rank () over (partition by Country order by sum_amount desc) ranking
from 
(
select Country , 
       CustomerName , 
       sum(TotalAmount) sum_amount
from customers c left join orders o on c.CustomerID = o.CustomerID
group by 1,2
) a )b 
where ranking = 1

고객 별로 총 주문 금액을 계산해주는 첫번째 서브쿼리,
나라별 주문 금액이 많은 순으로 랭킹을 매겨주는 두번째 서브쿼리,
나라별 주문 금액이 가장 많은 (랭킹이 1인) 고객만 결과값으로 보여주는 마지막 select문 순서로 쿼리를 작성했다.