전체 매출의 상위 20%를 차지하는 제품 구하기 (BDA학회 SQL 문법 기초 연습반)

전체 매출의 상위 20%를 차지하는 제품이 무엇인가?

---

1단계 : 제품별 각 주문 건에 대한 매출 합 

매출 = quantity(order_details) * price(products)

product_id로 join 해서 구해야 한다.

select 
    od.order_detail_id,
    od.product_id,
    od.quantity,
    p.price,
    (od.quantity * p.price) as order_detail_revenue
from 
    order_details od
join products p on od.product_id = p.product_id
order by product_id;

1단계 결과

+ 추가로 확인하면 도움 되는 글

[대외활동 및 인턴/빅데이터 분석 학회 BDA] - 테이블 JOIN 할 때, 기준이 되는 테이블은? (BDA학회 SQL 문법 기초 연습반)

테이블 JOIN 할 때, 기준이 되는 테이블은? (BDA학회 SQL 문법 기초 연습반)

---

2단계 : 제품별 총판매 매출

select
    od.product_id,
    p.name,
    sum(od.quantity * p.price) as total_product_revenue
from order_details od
join products p on od.product_id = p.product_id
group by od.product_id, p.name;

2단계 결과

+ 추가로 확인하면 도움 되는 글

[대외활동 및 인턴/빅데이터 분석 학회 BDA] - GROUP BY 사용법, 규칙 (BDA학회 SQL 문법 기초 연습반)

GROUP BY 사용법, 규칙 (BDA학회 SQL 문법 기초 연습반)

---

3단계 : 전체 매출의 합

select
    sum(total_product_revenue) as 총합
from (
    select
        od.product_id,
        p.name,
        sum(od.quantity * p.price) as total_product_revenue
    from order_details od
    join products p on od.product_id = p.product_id
    group by od.product_id, p.name
) as 3단게_테이블;

# 결과(전체 매출 합)
# => 2571936.00

---

4단계 : 전체 매출의 합 * 0.2  => 상위 20% 기준이 되는 값 구하기

select
	0.2 * sum(total_product_revenue) as top_20percent
from (
	select
		od.product_id,
		p.name,
		sum(od.quantity * p.price) as total_product_revenue
	from order_details od
	join products p on od.product_id = p.product_id
	group by od.product_id, p.name
) as 3단게_테이블;

# 결과(상위 20% 매출 값)
# => 514387.200

---

5단계 : 각 제품별 매출의 합에서 상위 20% 이상만 보이게

-- 각 제품별 매출 합계 계산
with 각제품별판매합계_테이블 as (
    select
        od.product_id,
        p.name,
        sum(od.quantity * p.price) as each_product_revenue
    from
        order_details od
    join 
        products p on od.product_id = p.product_id
    group by 
        od.product_id, p.name
),

-- 누적 매출 비율 계산
매출누적비율_테이블 as (
    select
        product_id,
        name,
        each_product_revenue,
        sum(each_product_revenue) over (order by each_product_revenue desc) as cumulative_revenue,
        sum(each_product_revenue) over () as total_revenue,
        sum(each_product_revenue) over (order by each_product_revenue desc) / sum(each_product_revenue) over () as cumulative_percentage
    from 
        각제품별판매합계_테이블
)

-- 상위 20% 매출에 해당하는 제품 선택
select
    product_id,
    name,
    each_product_revenue,
    round(cumulative_percentage * 100, 2) as cumulative_percentage
from 
    매출누적비율_테이블
where 
    cumulative_percentage <= 0.2;

5단계 결과