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

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

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

   | Category | Top_Product | TotalSold |
   | --- | --- | --- |
   | Electronics | Laptop | 3 |
   | Home | Coffee Maker | 3 |

   ---

1번 문제 풀이

1. 조회할 칼럼 : 고객 이름, 총 구매 금액, 주문 수

select CustomerName, sum(Price*Quantity), count(1)	

2. 필요한 모든 데이터를 포함한 조인 테이블 생성

customers c 
	join orders o on c.CustomerID = o.CustomerID
	join products p on o.ProductID = p.productID

3. 고객 단위로 그룹화

group by CustomerName

4. 정답

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

2번 문제 풀이

1. 조회할 칼럼 : 제품 카테고리, 제품 이름, 총 판매량

select Category, ProductName, sum(Quantity)

2. 필요한 모든 데이터를 포함한 조인 테이블 생성

products p join orders o on p.productID = o.ProductID

3. 그룹화 : 특정 카테고리 중 특정(가장 많이 팔린) 상품
   (카테고리, 상품명) 단위로 그룹화

group by Category, ProductName

4. 조건 : (카테고리, 상품명) 그룹화 후 sum(Quantity)가 최대 sum(Quantity)와 동일.
   그룹화 후 이퀄 조건 -> having

having sum(Quantity) = 최대 sum(Quantity)

5. 최대 sum(Quantity) 구하기

select sum(o2.Quantity)
from products p2 join orders o2 on p2.productID = o2.ProductID
where p2.Category = p.Category
group by p2.Category, p2.ProductName
order by sum(o2.Quantity) desc
limit 1

5. 정답

select p.Category, p.ProductName, sum(o.Quantity) as TotalSold
from products p join orders o on p.productID = o.ProductID
group by p.Category, p.ProductName
having sum(o.Quantity) = (
	select sum(o2.Quantity)
	from products p2 join orders o2 on p2.productID = o2.ProductID
	where p2.Category = p.Category
	group by p2.Category, p2.ProductName
	order by sum(o2.Quantity) desc
	limit 1
)