[SQL/Classicmodels Database] 문제 12,문제 13 (직원별 담당 고객 매출 상위 비율 분석,제품 공급자별 매출 기여도 분석)

문제 12: 직원별 담당 고객 매출 상위 비율 분석

각 직원이 담당하는 고객의 매출 중 상위 20% 고객의 매출이 차지하는 비율을 분석하세요.

• employees, customers, orders, orderdetails 테이블을 사용하세요.
• 각 직원별로 담당 고객의 매출을 합산한 후, 상위 20% 고객이 차지하는 매출 비율을 계산하세요.
• 출력: 직원번호, 직원이 담당하는 고객의 전체 매출, 상위 20% 고객 매출, 상위 20% 매출 비율 (%)

step1.상위 20% 매출을 보유한 고객 구하기

이때 합계가 아니라 평균을 구하고, percent_rank 윈도우 함수를 통해 상위 20%에 속한 고객(customerNumber)을 구하였음

with cte_1 as (select b.customerNumber,avg( priceEach * quantityOrdered) as avg_revenue
from employees a 
inner join customers b on a.employeeNumber = b.SalesRepEmployeeNumber 
inner join orders c on b.customerNumber = c.customerNumber 
inner join orderdetails d on c.orderNumber = d.orderNumber
group by b.customerNumber),
cte_2 as (select customerNumber
from
(select customerNumber, avg_revenue,percent_rank() over (order by avg_revenue) as percent_revenue  
from cte_1) t
where percent_revenue >= 0.8),

select * from cte_2

step2.직원 별 담당 고객 매출 합산 구하기

cte_3 as (
select a.employeeNumber, sum(priceEach * quantityOrdered) as sum_revenue
from employees a 
inner join customers b on a.employeeNumber = b.SalesRepEmployeeNumber 
inner join orders c on b.customerNumber = c.customerNumber 
inner join orderdetails d on c.orderNumber = d.orderNumber
group by a.employeeNumber
)


select * from cte_3;

step3.상위 20% 고객, 담당 직원 내 고객으로 join 하기

cte_4 as (
select a.employeeNumber , sum(priceEach * quantityOrdered) as sum_20per_revenue
from employees a 
inner join customers b on a.employeeNumber = b.SalesRepEmployeeNumber 
inner join orders c on b.customerNumber = c.customerNumber 
inner join orderdetails d on c.orderNumber = d.orderNumber
inner join cte_2 e on b.customerNumber= e.customerNumber
group by a.employeeNumber 
)
select cte_3.employeeNumber, sum_revenue, sum_20per_revenue, concat(round(sum_20per_revenue*100/sum_revenue,2),'%') as rate_revenue  
from cte_3 inner join cte_4 on cte_3.employeeNumber  = cte_4.employeeNumber

문제 13: 제품 공급자별 매출 기여도 분석

제품 공급자별 총 매출 계산 & 총 매출 계산 한 뒤, 전체 매출에서 차지하는 비율을 계산하고 내림차순으로 정렬
제품 공급자(productVendor)별로 총 매출을 계산하고, 전체 매출에서 차지하는 비율을 구하세요.

• products, orderdetails, orders 테이블을 사용하세요.
• 각 공급자의 매출을 구하고, 공급자별로 전체 매출에서 차지하는 비율을 계산하여 내림차순으로 정렬하세요.
• 출력: productVendor, 총 매출, 전체 매출 대비 비율 (%)

select productVendor,vendor_revenue,concat(round(vendor_revenue*100/total_revenue,2),'%') as rate_revenue
from
(select 
-- vendor별 총 매출 계산
productVendor,
sum(priceEach*quantityOrdered) as vendor_revenue ,
-- 총 매출 계산
(select sum(priceEach*quantityOrdered) as total_revenue 
from products a inner join orderdetails b on a.productCode = b.productCode
inner join orders c on b.orderNumber = c.orderNumber) as total_revenue

from products a inner join orderdetails b on a.productCode = b.productCode
inner join orders c on b.orderNumber = c.orderNumber
group by productVendor) t
-- 내림차순 정렬
order by 2 desc;