[문제풀이] SQL 개인과제 (고급 1)

스파르타코딩클럽에서 제공하는 문제의 풀이를 기반으로 작성하였습니다.

 

이전내용(초급/중급): https://kime2pan.tistory.com/41

고급

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

 

list_of_orders, order_details, 그리고 sales_target 세 개의 테이블이 있습니다.

list_of_orders 테이블은 주문 ID(order_id)와 주문이 이루어진 주(State)를,

order_details 테이블은 각 주문의 금액(Amount)과 이익(Profit)을,

sales_target 테이블은 각 카테고리별 매출 목표(Target)를 포함합니다.

 

이 테이블들을 사용하여 각 주별로 (각)주문의 총 금액과 이익을 계산하고,

각 주 내에서 (각) 주문의 매출 순위를 결정하세요.

또한, 각 주문의 총 금액이 해당 카테고리의 평균 매출 목표의 50%를 달성했는지 여부도 판단하세요.

 

문제해석:

1. list_of_orders, order_details을 조인-> order_id, State, Amount, Profit 조회

2. 주(state) 그룹화 -> 각 주별 금액, 이익 계산(sum)

3. 윈도우함수 -> 카테고리: 주, 순위: 금액

4. 카테고리의 평균 매출 목표의 50% 기준설정 -> with절을 통한 임시테이블

5. 각 주문의 총 금액과 카테고리별 평균 매출 비교-> case 문

 

--> 임시테이블 형태

with target as( -- 4.카테고리의 평균 매출 목표의 50%을 알 수 있는 임시테이블 SELECT category, (avg(target)/2) as avg_target from sales_target group by category )

A. 작성답안

with target as( -- 4.카테고리의 평균 매출 목표의 50%을 알 수 있는 임시테이블 
	SELECT 
		category, 
		(avg(target)/2) as avg_target
	from
		sales_target 
	group by 
		category
)
SELECT 
	li.order_id, 
	li.state, 
	sum(de.amount), -- 2. 각 주별 각 주문의총 금액과 총이익 집계
	sum(de.Profit), -- 2. 각 주별 각 주문의총 금액과 총이익 집계
	rank()over(partition by li.state order by sum(de.amount) desc) as ranking, 
    -- 3.각 카테고리 내에 각 주문의 총 매출액의 큰순으로의 순위
	case when sum(de.amount) >= t.avg_target then 'Met 50% target' 
    -- 5. 각 주문별 합계인 sum(de.amount)과 같은 카테고리인 avg_target와 비
		else 'Below 50% target' 
		end as target_comparision
FROM 
	list_of_orders li 
join 
	order_details de on li.order_id = de.order_id 
    -- 1. 조인을 통한 데이터 조회(아이디, 주, 매출액, 이)
join 
	target t on de.category = t.category -- 4. targe 비교를 위한 조인
group by li.order_id, li.state, t.avg_target;

 

 

A. 답안쿼리

WITH Average_Targets AS (
    SELECT 
        Category, 
        AVG(Target) AS Avg_Target
    FROM 
        sales_target
    GROUP BY 
        Category
)
SELECT 
    lo.order_id, 
    lo.State, 
    SUM(od.Amount) AS Total_Amount, 
    SUM(od.Profit) AS Total_Profit,
    RANK() OVER (PARTITION BY lo.State ORDER BY SUM(od.Amount) DESC) AS State_Rank,
    CASE 
        WHEN SUM(od.Amount) >= at.Avg_Target / 2 THEN 'Met 50% Target'
        ELSE 'Below 50% Target'
    END AS Target_Comparison
FROM 
    list_of_orders AS lo
JOIN 
    order_details AS od ON lo.order_id = od.order_id
JOIN 
    Average_Targets AS at ON od.Category = at.Category
GROUP BY 
    lo.order_id, lo.State, at.Avg_Target;

 

 

A. 답안비교

작성답안	답안쿼리

 

 

궁금점

• 왜? group by t.avg_target이 필요할까? ->  카테고리별!비교하기 위해서는 카테고리가 섞인 주문을 카테고리별 구분해야 하기때문
• 각 주문의 총 금액과 카테고리별 평균 매출 비교 모두 50% 이하이다 -> 왜? sales_target테이블은 총 12달의 데이터로 평균을 구할 시 월 평균 목표매출액을 구할 수 있다. 그러나 각 주별 주문의 총 매출은 1회 구입 주문건의 합이므로 기준이 일치하지 않다
• -> 즉, 비교를 위해서는 월 카테고리 총 매출액과 avg_target을 구하는 것 또는 일 카테고리별 총 매출액과 일별 목표액avg_target/30을 비교하는 방법이 있다