mysql 실무 적용 코딩 (고객별 매출 요약)

상황 : oo님 우리 회사 최근 6개월 데이터 기준으로 고객별 신규,기존 구분해서 매출 요약 분석 좀 해주세요. 

---

1단계 설계

• 신규,기존 고객 구분 : 해당 기간에 대해서 고객별 최초 주문 월 가지고 신규 구분하기
• 매출 요약 지표
  • 고객별 매출, 비중
  • 고객별 주문량, 비중
  • 주문당 평균 매출 금액

 

sql 쿼리 작성

-- 기존 버전
with customer_min_month as (
    select customer_id,
           min(date_format(order_date,'%Y-%m')) as min_month
    from orders
    group by customer_id
)
select c.ym, 
       case when c.ym = cm.min_month then '신규' else '기존' end as cust_ny,
	   count(distinct c.customer_id) user_cnt, 
       sum(c.sale_sum) as total_sale,
       sum(c.sale_sum)/m.tot_sale as sale_ratio,
       sum(c.order_cnt) as total_order_cnt,
       sum(c.order_cnt)/m.tot_cnt as order_ratio,
       sum(c.sale_sum)/sum(c.order_cnt) as avg_sale_per_order,
       sum(c.sale_sum)/count(distinct c.customer_id) as avg_sale_per_customer
from (
    select date_format(order_date,'%Y-%m') as ym, customer_id,
           sum(amount) as sale_sum,
           count(*) as order_cnt
    from orders
    group by date_format(order_date,'%Y-%m'), customer_id
) c
inner join (
    select date_format(order_date, '%Y-%m') as ym,
           sum(amount) as tot_sale,
           count(*) as tot_cnt
    from orders
    group by date_format(order_date, '%Y-%m')
) m on c.ym = m.ym
inner join customer_min_month cm on c.customer_id = cm.customer_id
group by c.ym, 
       case when c.ym = cm.min_month then '신규' else '기존' end
;


-- 윈도운 함수 사용
with customer_min_month as (
    select customer_id,
           min(date_format(order_date,'%Y-%m')) as min_month
    from orders
    group by customer_id
)
select date_format(o.order_date, '%Y-%m') as ym,
       case when date_format(o.order_date, '%Y-%m') = cm.min_month 
            then '신규' else '기존' end as cust_ny,
       count(distinct o.customer_id) cust_cnt,
       sum(o.amount) as sale_sum,
       sum(o.amount) * 100.0 / sum(sum(o.amount)) over() as sale_rate,
       count(*) as order_cnt,
       count(*) * 100.0 / sum(count(*)) over() as order_rate,
       sum(o.amount) / count(*) as avg_sale_per_order,                    -- 주문당
       sum(o.amount) / count(distinct o.customer_id) as avg_sale_per_customer -- 고객당
from orders o
inner join customer_min_month cm on o.customer_id = cm.customer_id
group by date_format(o.order_date, '%Y-%m'), 
         case when date_format(order_date, '%Y-%m') = cm.min_month 
              then '신규' else '기존' end;

 

보고서 작성 미션 끝!