[SQL]Lv5. 가장 많이 팔린 품목은?

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

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

CSV 파일을 이용해 데이터 가져오기(MY SQL)

문제

Products 테이블:

ProductID	ProductName	Category	Price
1	Laptop	Electronics	1000
2	Smartphone	Electronics	800
3	Headphones	Electronics	150
4	Coffee Maker	Home	200
5	Blender	Home	100

Orders 테이블:

OrderID	ProductID	OrderDate	Quantity	CustomerID
101	1	2024-02-01	2	1
102	3	2024-02-02	1	2
103	2	2024-02-03	1	1
104	4	2024-02-04	3	3
105	1	2024-02-05	1	2
106	5	2024-02-06	2	3

Customers 테이블:

CustomerID	CustomerName	Country
1	Alice	USA
2	Bob	UK
3	Charlie	USA

 

 

 

요구사항

1.각 고객이 구매한 모든 제품의 총 금액을 계산하고, 고객 이름, 총 구매 금액, 주문 수를 출력하는 SQL 쿼리를 작성해주세요.

ex)

CustomerName	TotalAmount	OrderCount
Alice	2600	3
Bob	950	2
Charlie	800	2

 

select c.CustomerName,sum(p.Price*o.Quantity ) TotalAmount,Count(o.OrderID) OrderCount
from customers c 
join orders o on c.CustomerID = o.CustomerID
join products p on o.ProductID = p.ProductID 
group by 1

 

join on 절을 두번 사용하여 3개의 테이블을 inner join 하였다.

 

 

2.각 제품 카테고리별로 가장 많이 팔린 제품의 이름과 총 판매량을 조회하는 SQL 쿼리를 작성해주세요.

ex)

Category	Top_Product	TotalSold
Electronics	Laptop	3
Home	Coffee Maker	3

 

select t1.category,t1.ProductName Top_Product,total TotalSold
from(select P.ProductName,p.Category ,o.order_sum total,rank() over(order by order_sum desc) rank_sum
from Products p join (select ProductID,Sum(Quantity) order_sum
from orders
group by ProductID) o on p.ProductID = o.ProductID) t1
where t1.rank_sum = 1

 

코드 해석

• 우선, orders 테이블에서 Prodcut ID 별로 총 주문수량을 구한다 -> Product 와 orders 테이블을 조인하여 총 주문수량이 가장 많은 제품순으로 rank 함수를 사용하여 정렬한다 -> rank num가 1인것만 추출한다.