사전캠프 달리기반 퀘스트 06

문제 6-1

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

문제 6-1 기대결과

 

[풀이]

조인을 두 번 해야겠는디...? 아닌가 아무튼 해보자구...

어 근데 뭔가 이상해서 확인해본 총 주문수가 6인데 기대 결과가 어떻게 3 2 2가 나오는 겨.....?

뭐다냐.......... 그럼 기대결과는 무시하고 일단 문제 하라는대로 풀어보자....

총 구매 금액은 Products 테이블의 Price 컬럼과 Orders테이블의 Quantity를 곱한 값을 담은 컬럼을,

CustomerID 또는 CustomerName을 기준으로 그룹화하여 sum()으로 감싸면 되겄다.

주문 수는  같은 기준에서 Order 테이블 count() 해주면 될 듯! 

 

[첫 시도]

select c.CustomerName 
	, TotalAmount
	, OrderCount
from 
	(
	select o.CustomerID
		, sum(p.Price*o.Quantity) as TotalAmount
		, count(o.OrderID) as OrderCount
	from orders o left join products p on o.ProductID = p.ProductID 
	group by 1
    /* 고객별 총구매금액과 총주문수를 담은 서브쿼리에 기존 고객 테이블을 조인! */
	) SubQ left join customers c on SubQ.CustomerID = c.CustomerID

 바로 성공은 했는데 역시나 결과값은 다르게 나온다~~~ 잘못된 건 이 세상일 거야 내가 아녀...

고객명 컬럼은 고객 테이블에만 있기 때문에 조인 두번이 불가피했다~!

 

[결과]

문제 6-1 결과

 

문제 6-2

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

문제 6-2 기대결과

 

[풀이]

이번엔 조인 한 번만 해도 되겄다. 그룹화 기준은 Category . 

총 판매량을 먼저 구하면, 제품 별 Quantity에 sum() 감싸면 되겄고.

제품명을 조회하는데, 그 제품의 총 판매량이 카테고리 내에서 최대값인 녀석을 데려오도록 조건을 주면 되겠당

 

[첫 시도]

select Category 
	, ProductName as Top_Product
	, qSum as TotalSold
from
	(
	select p.ProductID as pID
		, sum(o.Quantity) as qSum
	from orders o 
	left join products p on o.ProductID = p.ProductID 
	group by 1
	) SubQ 
left join products p on SubQ.pID = p.ProductID 
/* 카테고리가 Electronics이면, 총판매량이 Electronics의 최대판매제품 판매량과 같아야 조회한다! */
where case when Category = 'Electronics' then qSum = 
		(
		select sum(o.Quantity)
		from orders o 
		left join products p on o.ProductID = p.ProductID 
		where Category = 'Electronics'
		group by p.ProductID
		order by 1 desc limit 1
		)
/* 카테고리가 Home이면, 총판매량이 Home의 최대판매제품 판매량과 같아야 조회한다! */
	when Category = 'Home' then qSum = 
		(
		select sum(o.Quantity)
		from orders o 
		left join products p on o.ProductID = p.ProductID 
		where Category = 'Home'
		group by p.ProductID
		order by 1 desc limit 1
		) end

"??? : 이번엔 조인 한 번만 해도 되겠다~~" ㅋㅋㅋㅋㅋㅋ 어림도 없지! 전설적인 코드가 탄생하고야 말았다... 이게 최선일까... 억지로 결과 조회엔 성공했지만, 이 코드대로면 우선 공동 판매 1등 제품을 판별하지 못 하는 문제가 있다. 실제로 문제에서 주어진 테이블에 공동 판매 1등 제품들이 있다.... 그래도 이번 퀘스트를 통해 배운 점 둘.

• 서브쿼리를 조인에 활용할 수 있음
• 여러 조건의 적용이 필요할 때 where절에 case문을 활용할 수 있음

[결과]

문제 6-2 결과