[SQL]Lv4. 단골 고객님 찾기

아래에 나오는 데이터는 서버에 없는 데이터 이기때문에 따로 CSV 파일을 만들어 LOCALHOST(MY SQL) 서버에 데이터를 넣어주어야한다.

방법:https://computerreport.tistory.com/16

문제

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

 

주의사항

- 모두 서브쿼리 join,group by,Having 등을 사용하여야한다.

- 주문을 한적이 없는 고객도 첫번째 쿼리 결과에 포함되어야한다.

 

요구사항

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

 

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

 

ex)

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

select c.CustomerName,
       Count(o.OrderID ) as OrderCount,
       SUM(o.TotalAmount) as TotalSpent
from orders o left join Customers c on o.CustomerID  = c.CustomerID 
group by c.CustomerName

 

 group by 를 사용했을땐 반드시 집계합수(COUNT,SUM)등이 있어야 실행이 가능하다.

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

 

ex)

 

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

 

 

select a.country ,a.cn as CustomerName, a.Top_rank as Top_Spent
from 
      (select  c.Country,c.CustomerName as cn,SUM(o.TotalAmount ) as Top_rank,rank() over(partition by c.Country order by sum(o.TotalAmount ) desc) ranknum
       from orders o left join customers c on o.CustomerID = c.CustomerID 
       group by 1,2
       order by c.Country desc)a
where a.ranknum=1

 

 우선 첫 from 절안에서 이름별로 총 주문 금액을 구하고 나라별로 가장 높은 고객에게 rank() 함수로 순위를 매겨준다.

다음, rank() 의 값이 1인것만 뽑아 쿼리에 보여준다.

 

처음에 나라별로 가장 높은 주문금액을 구하는것을 한참 고민했다. 여러문제를 풀어보면서 상황에 맞는 함수를 잘 사용할수 있도록 연습이 필요해보인다.

이전에 했던 단계보다는 어려워진거 같아 한번더 복습이 필요할것 같다.