[SQL/Classicmodels Database] 문제 8,문제 9 ( 특정 제품군의 주간 판매량 변화 분석,매출 상위 10% 제품의 매출 기여도 분석)

문제 8: 특정 제품군의 주간 판매량 변화 분석

특정 제품군(productLine)의 주별 판매량 변화를 분석하려고 합니다. 제품군 이름을 매개변수로 받아, 각 주마다 해당 제품군의 총 판매량과 주별 변화율을 계산하는 쿼리를 작성하세요.

• products, orderdetails, orders 테이블을 사용하세요.
• 주별 판매량은 (quantityOrdered * priceEach)로 계산하고, 주별 판매량 변화율은 전주 대비 판매량 변화율로 계산하세요.
• 출력: 연도, 주, 주간 판매량, 주간 판매량 변화율 (%)

step1.products, orderdetails, orders 테이블 조인하기

with cte_1 as (select orderLineNumber,productCode,b.customerNumber, b.orderNumber, orderDate,status,quantityOrdered, priceEach
from customers a inner join orders b on a.customerNumber = b.customerNumber 
inner join orderdetails c on b.orderNumber = c.orderNumber),

select * from cte_1;

step2.주간별 매출 확인하기

cte_2 as (select date_format(orderDate, '%Y-%m') as year_of_month, week(orderDate,1) as week,
sum(quantityOrdered * priceEach) as week_revenue

from cte_1
group by date_format(orderDate, '%Y-%m'),week(orderDate,1)
order by 1,2 )

select * from cte_2;

step3.lag함수 활용하여 전주 데이터 column 출력 & 전주 대비 변화율 컬럼 추가하기

select 
year_of_month,
week, 
concat(round(((week_revenue-week_revenue_ago)* 100/(week_revenue_ago) ),1),'%') as rate_of_change
from (select *,lag(week_revenue) over (order by year_of_month, week) as week_revenue_ago
from cte_2) t;

문제 9: 매출 상위 10% 제품의 매출 기여도 분석

전체 제품 중 매출 상위 10%가 전체 매출에서 차지하는 비율을 분석하는 쿼리를 작성하세요.

• orderdetails, products 테이블을 사용하세요.
• 각 제품의 매출을 계산하여 매출 상위 10% 제품군의 매출 기여도를 출력하세요.
• 출력: 전체 매출, 상위 10% 제품군 매출, 상위 10% 제품군 매출 비율 (%)

step1.제품 기준 수익 구하기

• group by을 통해 제품별 매출 합계 계산
• 이후 percentage_rank 윈도우 함수를 통해서 각 수익의 백분율 환산

with cte_1 as (select productCode, productName, revenue,
round(percent_rank() over (order by revenue),2) as percentile_rank
from (
select a.productCode, productName,sum(quantityOrdered*priceEach) as revenue 
from orderdetails a inner join products b on a.productCode = b.productCode
group by a.productCode, productName) t )

select * from cte_1;

2.상위 10% 매출 구하고 총 매출의 percentage 계산하기

select *, concat(round(10_per_revenue*100/total_revenue,2),'%') as percentage
from (select sum(revenue) as total_revenue , sum(case when percentile_rank>=0.9 then revenue end) as 10_per_revenue
from cte_1) t