내일배움캠프 데이터분석 2주차 SQL 개인과제 [2]

문제 4: "여러 주문을 한 고객들의 총 구매액 계산하기"

두 테이블 **list_of_orders**와 **order_details**가 있습니다. list_of_orders 테이블은 고객 이름과 주문 ID를, order_details 테이블은 각 주문의 상세 금액을 포함하고 있습니다. 이 테이블들을 사용하여, 3개 이상의 주문을 한 고객들의 이름, 총 주문 횟수, 그리고 그들의 총 구매액을 계산하세요.

select l.CustomerName, count(l.CustomerName), sum(amount) 
# CustomerName, count(l.CustomerName), sum(amount) 컬럼 출력

from list_of_orders l
inner join order_details d
on l.order_id = d.order_id
# list_of_orders 테이블과 order_details 테이블 조인해서 사용!

group by l.CustomerName 
# CustomerName 컬럼 데이터 기반으로 그룹화! 

having count(l.CustomerName) >=3
# count(l.CustomerName) >=3 조건에 충족한 값만 출력!

 

출력 결과

---

문제 5: "평균 수량을 초과하는 주문 찾기"

order_details 테이블에는 다양한 주문들의 상세 정보가 저장되어 있습니다. 이 테이블의 각 행은 주문 ID(order_id), 카테고리(Category), 그리고 주문 수량(Quantity)을 포함합니다. 이 테이블을 사용하여 각 카테고리별 평균 주문 수량을 초과하는 모든 주문들을 찾아보세요.

SELECT o.order_id, o.Category, o.Quantity
# o.order_id, o.Category, o.Quantity 컬럼 출력!

FROM order_details o
JOIN (
    SELECT Category, AVG(Quantity) AverageQuantity
    FROM order_details
    GROUP BY Category
) avg_q 
ON o.Category = avg_q.Category
# order_details 테이블과 카테고리 별 평균 Quantity 값을 구한 order_details 테이블의 셀프 조인!


WHERE o.Quantity > avg_q.AverageQuantity 
# o.Quantity > avg_q.AverageQuantity 조건이 충족한 값만 출력!

 

출력 결과

 

TMI) 셀프 조인한 전체 테이블 확인

SELECT*
FROM order_details o
JOIN (
    SELECT Category, AVG(Quantity) AverageQuantity
    FROM order_details
    GROUP BY Category
) avg_q 
ON o.Category = avg_q.Category

 

출력 결과

---

문제 6: "카테고리별 매출액 순위 및 누적합계 계산하기"

order_details 테이블에는 주문의 상세 정보가 포함되어 있습니다. 이 테이블은 주문 ID(order_id), 카테고리(Category), 그리고 주문의 금액(Amount)을 포함합니다. 이 테이블을 사용하여 각 카테고리 내에서 각 주문의 매출액 순위와 그 카테고리 내의 누적 매출액을 계산하세요.

 

select order_Id, category, amount,
# order_Id, category, amount 컬럼 출력!

rank() over(partition by Category order by amount desc),
# Category 항목 별로 amount 내림차순 정렬하여 순위 매기기!

sum(amount) over(partition by category order by amount desc )
# category 항목 별로 amount 내림차순 정렬하여 amount 누적 합계 구하기!

from order_details;
# order_details 테이블 사용하기!

 

출력 결과

---