SQL Quest 달리기반 - 5_1)

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

 

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

1. 먼저 고객별 총 구매액 계산하기.

Price(Products 테이블)와 Quantity(Orders 테이블) 컬럼이 둘 다 필요한데 서로 다른 테이블에 존재함.

두 테이블을 조인해야 비로소 group by로 구매자별 총 구매액과 주문수를 구할 수 있음

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

나머지 테이블과 조인을 위해서 CustomerID도 같이 가져옴

 

2. 남은 Customers 테이블과 조인해서 필요한 컬럼인 고객 이름을 출력하자.

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

 

서브 쿼리 형태로 다시 테이블을 조인하고 출력할 컬럼을 select 절에 작성하면 완성.

한데 문제에서 요하는 주문수가 주문 횟수인지, 주문한 상품 수량인지 좀 헷갈린다.

일단 주문 횟수로 출력시킴

Result

 

 

 

요구사항 2)

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

먼저 상품별 총 판매량을 구해보자

select ProductID,
       sum(Quantity) Total_Quantity
from orders
group by 1

아직 상품 이름은 모르지만 ID로 진행시켜

 

 

그리고 공통 컬림인 ProductID를 기준. 서브쿼리로 다시 묶어 Products 테이블과 조인하고 rank over 함수를 사용하면 카테고리별 판매량 1등 상품이 나올 것 같은 느낌

select Category,
       ProductName Top_Product,
       Total_Quantity TotalSold
from
(
select Category,
       ProductName,
       Total_Quantity,
       rank() over (partition by Category order by Total_Quantity desc) rn
from
(
select ProductID,
       sum(Quantity) Total_Quantity
from orders
group by 1
) a left join products p on a.ProductID=p.ProductID
) b
where rn=1

Result

---

 

오늘은 여기까지..

일단 서브 쿼리 여러개에 조인까지 해야하고, 메인 쿼리까지 작성하려니 길이가 길어지고 약간 뇌정지가 와버린다.

 

특히 rank over 함수를 사용하고 where 절에서 조건을 걸려고 했는데, 뭘 해도 무한 오류 창에 급 스트레스

알고 보니, SQL 작성하는 순서와 다르게 실행될 때는 select 절보다 where 절이 먼저 실행되기 때문에, rank over 인식을 못해버린다. 순서에 대한 개념을 좀 다잡고 다시 보니 다음에 작성할 때는 한결 수월할 것 같다. 정리해두기.

 

- SQL의 작성 순서

1. SELCET

2. FROM

3. WHERE

4. GROUP BY

5. HAVING

6. ORDER BY

7. LIMIT

 

- SQL의 실행 순서

5. SELCET

1. FROM

2. WHERE

3. GROUP BY

4. HAVING

6. ORDER BY

7. LIMIT