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

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

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

 

 

 

✅제약사항

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

 

 

✅문제 풀이

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

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

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.CustomerID

 

 

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

 

나라와 고객 이름 그룹화

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

 

1. 고객 ID 기준 총 금액

select sum(o2.TotalAmount) as subSum
from Customers c2
join Orders o2 on c2.CustomerID=o2.CustomerID
group by c2.CustomerID;

 

2. 고객 ID 기준 총 금액 결과에서 main query 에서의 국가와 매칭되는 max 값 계산

select max(subSum)
from
(
    select sum(o2.TotalAmount) as subSum
    from Customers c2
    join Orders o2 on c2.CustomerID=o2.CustomerID
    where c.Country=c2.Country
    group by c2.CustomerID
) as sub

 

최종 결과

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(subSum)
	from
	(
		select sum(o2.TotalAmount) as subSum
		from Customers c2
		join Orders o2 on c2.CustomerID=o2.CustomerID
        where c.Country=c2.Country
		group by c2.CustomerID
	) as sub
);