[👩‍💻TIL 53일차 ] 유데미 스타터스 취업 부트캠프 4기

 

오늘은 어제에 이어서 팀플을 진행했다.

우리 조에서 최종적으로 정리한 코드들은 다음과 같다.

 

/********************************
 * 2. 이탈률 
 *********************************/
WITH 
cte_customers AS( --1. 필요 컬럼 추출 
SELECT DISTINCT customer_unique_id
		 , order_id
		 , date(order_purchase_timestamp) AS order_date
		 , payment_value AS sales
		 , max(date(order_purchase_timestamp)) over() AS std
		 , max(date(order_purchase_timestamp)) over() - date(order_purchase_timestamp) AS date_diff
FROM olist.final_table
) ,cte_rfm AS( --2. 고객 RFM 집계 
SELECT customer_unique_id
		 , COALESCE(min(date_diff), 0) AS recency
		 , COALESCE(count(DISTINCT order_id), 0) AS frequency
		 , COALESCE(sum(sales), 0) AS monetary
FROM cte_customers
GROUP BY 1
ORDER BY 1
), cte_score AS( --3. 고객 RFM 등급생성 
SELECT *
	 , CASE WHEN recency <= 180 THEN 3
	 		WHEN recency <= 360 THEN 2
	 		ELSE 1
	 		END AS r_score
	 , CASE WHEN frequency = 1 THEN 1
	 		WHEN frequency = 2 THEN 2
		 	ELSE 3
			END AS f_score
	 , CASE when monetary <= 63 then 1
			when monetary <= 108 then 2
			when monetary <= 183 then 3
			when monetary <= 624 then 4
			else 5
			end as m_score
FROM cte_rfm
) --4. 이탈률 확인 
SELECT round(((SELECT count(DISTINCT customer_unique_id) FROM cte_score WHERE r_score = 3) / count(*)::NUMERIC * 100), 2)::varchar(10)||'%' AS "잔존고객"
	 , round(((SELECT count(DISTINCT customer_unique_id) FROM cte_score WHERE r_score = 2) / count(*)::NUMERIC * 100), 2)::varchar(10)||'%' AS "이탈위험고객"
	 , round(((SELECT count(DISTINCT customer_unique_id) FROM cte_score WHERE r_score = 1) / count(*)::NUMERIC * 100), 2)::varchar(10)||'%' AS "이탈고객"
FROM cte_score;

➡️ 어제 정했던 RFM 등급 기준을 기반으로 이탈률을 확인해보았다.

 

/********************************
 * 4. 등급 별 고객 수 + 비율
 *********************************/
with 
cte_grade_cnt AS (
	SELECT grade
		 , count(DISTINCT customer_unique_id) AS cnt
	FROM olist.table_rfm 
	GROUP BY grade)
SELECT *
	 , round((cnt *100/ sum(cnt) over())::NUMERIC, 3) AS percent
FROM cte_grade_cnt
ORDER BY 1;

 

 

/********************************
 * 4-1. 계절성 확인 
 *********************************/
with
cte_table as ( --1. 필요 컬럼만 추출 
select 	distinct to_char(order_purchase_timestamp, 'MM') as month
    	, to_char(order_purchase_timestamp, 'q') as quarter
    	, order_id, customer_unique_id, payment_value, freight_value 
from olist.table_rfm 
)  --2. 월, 분기별 주문건수, 고객수, 총 매출 확인 
select month, quarter
		, count(distinct order_id) as order_cnt
    	, count(distinct customer_unique_id) as cust_cnt
    	, round(sum(payment_value)) as total_sales
    	, round(sum(freight_value)) as total_freight
    	, count(*) as cnt_grade
from cte_table
group by 1,2
;

 

 

📍 state 고객 수 top 3, city 고객 수 top 3 구하기

with
cte_table as(
	select *
	from final_table ft
)
, cte_region as(
	select distinct customer_state, customer_city ,  count(distinct customer_unique_id) 고객수
	from cte_table
	group by 1,2
	order by 3 desc
)

 

, cte_state as(
	select customer_state , 고객수
	from cte_region
	limit 3
)

 

, cte_rank as(
select	cs.customer_state, cr.customer_city, cr.고객수
	, row_number () over(partition by cs.customer_state order by cr.고객수 desc) rank
from cte_state cs left join cte_region cr 
on cs.customer_state = cr.customer_state
order by 1, 3 desc
)
-- rank top 3
select *
from cte_rank
where rank <= 3;

rank <= 3

 

 

✍️ 마무리하며

 

다양한 코드를 작성해보며 그동안 SQL 쿼리들과 익숙해지는 시간을 가져보았다. 잘 모르는 기능들도 다른 조원 언니오빠들이 하는 것을 보며 많이 배울 수 있는 시간이었다. 다음주 해커톤에도 잘 활용해서 쿼리를 짜보고 싶다.

 

---

* 유데미 큐레이션 바로가기 : https://bit.ly/3HRWeVL

* STARTERS 취업 부트캠프 공식 블로그 : https://blog.naver.com/udemy-wjtb

📌 본 후기는 유데미-웅진씽크빅 취업 부트캠프 4기 데이터분석/시각화 학습 일지 리뷰로 작성되었습니다.