1. 고객-주문 분석: 최적의 고객 세그먼트 식별
목표:
고객을 3가지 세그먼트로 나누어 각 그룹의 특성을 분석합니다.
고객 세그먼트 정의:
A: 총 주문 금액 상위 10% 고객
B: 총 주문 금액 중간 40% 고객
C: 총 주문 금액 하위 50% 고객
각 그룹에서 가장 선호하는 제품 카테고리를 도출합니다.
난이도 포인트:
서브쿼리를 사용하여 고객별 총 주문 금액 집계.
RANK() 또는 NTILE()를 활용해 세그먼트를 나눔.
JOIN을 통해 고객과 제품 정보를 연결하고 카테고리별 집계를 수행.
### 조인 , 고객별 주문 총 금액(revenue) 컬럼 추가 생성
with cte_1 as (select a.customerNumber,customerName, orderDate,status, priceEach,quantityOrdered,d.productCode, productName, productLine,( priceEach * quantityOrdered) as revenue,productVendor
from customers a inner join orders b on a.customerNumber = b.customerNumber
inner join orderdetails c on b.orderNumber = c.orderNumber
inner join products d on c.productCode = d.productCode),
### 고객별 세그먼트 분류
cte_2 as (select customerNumber,
case when rank_revenue >=0.9 then 'A'
when rank_revenue >=0.5 then 'B'
else 'C' end as segment
from
(
### 고객 기준 group by, percent_rank 을 활용하여 매출 백분율 환산하기
select customerNumber, percent_rank() over (order by revenue) as rank_revenue
from (
select customerNumber, avg(revenue) as revenue
from cte_1
group by customerNumber) t1
) t2)
### 값 구하기 ( 고객별 가장 잘 나간 product category, 하위 카테고리 및 제품 이름도 구하기)
select segment,
max(productName) as 'High productName',
max(productLine) as 'High productLine',
max(productVendor) as 'High productVendor',
min(productName) as 'Low productName',
min(productLine) as 'Low productLine'
from cte_1 a inner join cte_2 b on a.customerNumber = b.customerNumber
group by segment
order by 1 ;
2. 재고 최적화: 효율적인 주문 예측
목표:
제품별 월별 판매량 변화를 기반으로 재고량이 부족해질 가능성이 높은 제품을 예측합니다.
월별 판매량의 3개월 평균값(moving average)을 계산.
평균 판매량 대비 현재 재고량이 부족한 제품을 식별.
난이도 포인트:
월별 데이터 집계를 위한 DATE_FORMAT() 또는 MONTH() 함수 사용.
3개월 이동 평균 계산을 위한 LAG() 및 윈도우 함수 활용.
현재 재고량(products.quantityInStock)을 평균 판매량과 비교.
step1.제품별 월별 판매량을 구하기
with cte_1 as
(
select productCode, date_month, total_revenue,
ifnull(lag(total_revenue,1) over(partition by productCode order by date_month),0) as total_revenue_1,
ifnull(lag(total_revenue,2) over(partition by productCode order by date_month),0) as total_revenue_2
from
(
select c.productCode, month(orderDate) as date_month,sum(quantityOrdered * priceEach ) as total_revenue
from customers a inner join orders b on a.customerNumber = b.customerNumber
inner join orderdetails c on b.orderNumber = c.orderNumber
inner join products d on c.productCode = d.productCode
group by c.productCode, month(orderDate)
order by 1,2
) t
)
select from cte_1
step2.제품별 월별 판매량을 구하기
3개월 평균 구하기 위해 lag함수 활용하여 이 전 2개월 판매량 데이터 가져오기
cte_2 as (select
productCode ,
date_month,
total_revenue as 현재재고량 , round((total_revenue+ total_revenue_1 + total_revenue_2)/3,2) as 평균재고량,
round(total_revenue *100/round((total_revenue+ total_revenue_1 + total_revenue_2)/3,2),1 ) as 평균대비현재재고비율
from cte_1)
select * from cte_2;
step3.평균 재고 비율보다 현재 비율이 낮을 경우, 위험하다고 판단
where 구문을 통해 따로 불러와 제품별 주의 필요 month 출력
select productCode, group_concat(distinct date_month) as '월별 주의 필요 현황'
from cte_2
where 평균대비현재재고비율 <= 100.0
group by productCode
