[SQL] 고객 잔존율을 구해보자 2

 

고객 잔존율을 구해보자

이전 글에서는 다소 거친 방법으로(?) 잔존율을 구해보았다.

그러면서 더 간단한 방법은 없을까? 생각하던 중 다른 방법을 알게되어

기록해놓기 위해서 글을 쓰게 되었다.

이전보다는 조금 더 간단하고, 짧은 쿼리가 되어 기분이 매우 좋다.(!!)

주차별 잔존율도 쉽게 뽑을 수 있게 되었다. 

 

뽑는 방법은 다음과 같다.

먼저 기준 주문일과 고객을 뽑는다. 이 때 중복이 없어야 한다. 

나는 주차별 잔존율을 뽑기 위해 주 시작일을 기준으로 잡았다.

 

기준일을 잡은 다음에는 전체 주문 테이블을 가져와서 inner join 으로 붙인다.

붙인 다음엔 case when을 사용해서 첫주문일이 주문일보다 작을 때 after 딱지를 붙여준다.

(첫주문일 이후의 잔존을 보기 위해서는, 첫주문일 이후의 주문만 가져와야 하니까 첫주문 이전의 주문인 before는 뺴야 한다.)

이렇게 구분자를 만들었으면 거의 다 됐다.

 

count와 sum을 이용하여 고객수와 주문수를 날짜에 맞춰 세주면 된다.

그리고 엑셀로 가져가서 피봇테이블을 돌리면 잔존율을 뽑을 수 있다. 

 

with first as (
select mem_no,min(weekstart_dt) as fisrt_weekstart_dt
from ord b
left join date c
on date(part_Date) = date(c.date_cd)
where 1=1
and b.part_date >= '2021-06-01'
and b.part_date <= '2021-12-31'
group by 1
order by 1 asc
), --기준 주문일

ord as (
select fisrt_weekstart_dt, a.mem_no, weekstart_dt,count(ord_no) as ord_cnt
,case when date(fisrt_weekstart_dt) <= date(weekstart_dt) then 'after' else 'before' end as gubun
from  ord a
inner join first b
on a.mem_no = b.mem_no
left join date c
on date(part_Date) = date(c.date_cd)
where 1=1
and a.part_date >= '2021-06-01'
and a.part_date <= '2021-12-31'
group by 1,2,3
)-- 전체 주문

select date(fisrt_weekstart_dt) as first_ord_dt
        ,gubun
        ,weekstart_dt
        ,count(distinct mem_no) as mem_cnt
        , sum(ord_cnt)
from ord
group by 1,2,3