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

문제 7: "주별 매출 순위 및 평균 매출 목표 달성 여부 확인"

list_of_orders, order_details, 그리고 sales_target 세 개의 테이블이 있습니다. list_of_orders 테이블은 주문 ID(order_id)와 주문이 이루어진 주(State)를, order_details 테이블은 각 주문의 금액(Amount)과 이익(Profit)을, sales_target 테이블은 각 카테고리별 매출 목표(Target)를 포함합니다. 이 테이블들을 사용하여 각 주별로 주문의 총 금액과 이익을 계산하고, 각 주 내에서 주문의 매출 순위를 결정하세요. 또한, 각 주문의 총 금액이 해당 카테고리의 평균 매출 목표의 50%를 달성했는지 여부도 판단하세요.

 

select
loo.order_id "주문id",
odd.amount "매출",
odd.category "카테고리",
loo.state "주",
a.sum_a"주별 총 매출",
a.sum_p"주별 총 이익",
#각 컬럼 출력

rank() over(partition by loo.state order by odd.amount desc)"주별 매출 순위",
# 주 별 매출 순위 구하기

if(a.sum_a >= avg_am*0.5, "목표 달성", "목표 미달성")"매출 목표",
# 평균 매출의 50% 달성 여부 구하기

avg_am*0.5 "카테고리 별 평균 매출 50%"
#각 컬럼 출력

from list_of_orders loo
join order_details odd
on loo.order_id = odd.order_id
# list_of_orders과 order_details 테이블 조인

join
(
select 
lo.state,
sum(od.amount)"sum_a", 
sum(od.profit)"sum_p"
from list_of_orders lo
join order_details od 
on lo.order_id  = od.order_id
group by 1
)a
on loo.state = a.state
#주별 총 매출, 총 이익 테이블과 조인

JOIN 
(
SELECT Category, AVG(target) avg_am
FROM sales_target
GROUP BY Category
) avg_a 
ON odd.Category = avg_a.Category;
# 카테고리 별 평균 매출 테이블 join

 

출력 결과