SQL 연습 문제 - 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 |
문제:
- 각 고객이 구매한 모든 제품의 총 금액을 계산하고, 고객 이름, 총 구매 금액, 주문 수를 출력하는 SQL 쿼리를 작성해주세요.
select CustomerName, sum(Price) TotalAmount, count(a.OrderCount) OrderCount
from
(
select c.CustomerName, o.ProductID, count(o.OrderID) OrderCount
from customers c join orders o on c.CustomerID = o.CustomerID
group by 1,2
) a join products p on a.ProductID = p.ProductID
group by 1
첫번째 서브쿼리에서 customers테이블과 orders테이블을 join한 뒤, 필요한 columns들을 선택한다.
두번째 서브쿼리에서는 이미 join된 테이블과 product테이블을 join한 뒤, 집계함수를 사용하여 문제에서 요구하는 총구매금액, 주문수를 계산한다.
- 각 제품 카테고리별로 가장 많이 팔린 제품의 이름과 총 판매량을 조회하는 SQL 쿼리를 작성해주세요.
select Category , Top_Product, TotalSold
from
(
select p.Category , p.ProductName Top_Product, sum(o.Quantity) TotalSold,
rank () over(order by sum(o.Quantity) desc) ranking
from products p left join orders o on p.ProductID = o.ProductID
group by 1,2
) a
where ranking = 1
product테이블과 orders테이블을 join해서, 제품별 판매수량을 구하고,
판매수량이 많은 순으로 랭킹을 매겨,
1순위 제품만 결과로 보여주도록 한다.
