[패스트캠퍼스] SQL로 시작하는 데이터 분석 첫걸음 / 4주차

4주차 SQL로 시작하는 데이터 분석 첫걸음

4주차부턴 새로운 강사님이 강의를 진행한다. 데이터를 쿼리로 만드는 것이 아니라 실무에서 자주 쓰이는 데이터를 활용하였다. 매출 관련 데이터라 매출액을 찾거나 구매 유저 수 등을 찾는 것이 주요 문제였다. 해당 데이터를 다뤄본 적이 거의 없어서 데이터가 낯설었다. 그래도 실무에 자주 쓰이는 부분을 알려줘서 유용하게 쓰일 것 같다. 시간데이터를 잘 다루면 충분히 모든 서비스에 적용할 수 있을 것만 같았다. 아직 미숙하지만 완강하고도 연습해서 SQL를 잘 쓸 수 있도록 해야겠다.

CH 03. 서비스 이해

01. Revenue

-- 2020년 7월의 Revenue를 구해주세요

select sum(price)
from fastcampus.tbl_purchase
where purchased_at >= '2020-07-01'
and purchased_at <'2020-08-01'

02. MAU(Monthly Active Users)

-- 2020년 7월의 MAU를 구해주세요

select count(distinct customer_id)
from fastcampus.tbl_visit
where visited_at >= '2020-07-01'
and visited_at <'2020-08-01'

03. Paying Rate

-- 2020년 7월의 우리 Active 유저의 구매율(Paying Rate)은 어떻게 되나요?
-- 구매유저 수 / 전체 활성유저

select count(distinct customer_id)
from fastcampus.tbl_purchase
where purchased_at >= '2020-07-01'
and purchased_at <'2020-08-01';

select count(distinct customer_id)
from fastcampus.tbl_visit
where visited_at >= '2020-07-01'
and visited_at <'2020-08-01';

select round(11174/16414 * 100,2)

04. ARPPU

-- 2020년 7월의 구매 유저의 월 평균 구매액은 어떻게 되나요?
-- ARPPU = Average Revenue per Paying User

select avg(revenue)
from(select customer_id, sum(price) as revenue
	from fastcampus.tbl_purchase
	where purchased_at >= '2020-07-01'
	and purchased_at <'2020-08-01'
	group by customer_id) sub

05. 고과금 유저

-- 2020년 7월에 가장 많이 구매한 Top 3 고객과 Top10~15 고객을 뽑아주세요

select customer_id, sum(price) as revenue
from fastcampus.tbl_purchase
where purchased_at >= '2020-07-01'
and purchased_at <'2020-08-01'
group by 1
order by 2 desc
limit 3 -- offset 10

CH 04. 날짜 시간별 데이터 분석

01. Data Functions

SELECT DATE_ADD() : 날짜 더하기

SELECT DATE_SUB() : 날짜 빼기

SELECT DATEDIFF() : 날짜 차이

SELECT TIMEDIFF() : 시간 차이

SELECT DATE_FORMAT(NOW(), "포맷") : 시간날짜 형식 지정

 

02. DAU(Daily Active Users)

* 시간 포맷팅 주의 ! 

-- 2020년 7월의 평균 DAU를 구해주세요, Active User 수가 추세 증가하는 추세인가요?

select avg(users)
from (
select date_format(visited_at-interval 9 hour, '%Y-%m-%d') as date_at
	, count(distinct customer_id) as users
from fastcampus.tbl_visit
where visited_at >= '2020-07-01'
and visited_at <'2020-08-01'
group by 1
order by 1) sub

03. WAU(weekly Active Users)

-- 2020년 7월의 평균 MAU를 구해주세요

select avg(users)
from (
select date_format(visited_at-interval 9 hour, '%Y-%m-%U') as date_at
	, count(distinct customer_id) as users
from fastcampus.tbl_visit
where visited_at >= '2020-07-05'
and visited_at <'2020-07-26'
group by 1
order by 1) sub

04. Daily Weekly Revenue

-- 2020년 7월의 Daily Revenue는 증가하는 추세인가요? 평균 Daily Revenue도 구해주세요.
-- 2020년 7월의 평균 Weekly Revenue를 구해세요

select avg(revenue)
from(select date_format(purchased_at - interval 9 hour, '%Y-%m-%d') as date_at
		, sum(price) as revenue
	from fastcampus.tbl_purchase
	where purchased_at >= '2020-07-01'
	and purchased_at < '2020-08-01'
	group by 1
	order by 1) sub;
    
select avg(revenue)
from(select date_format(purchased_at - interval 9 hour, '%Y-%m-%U') as date_at
		, sum(price) as revenue
	from fastcampus.tbl_purchase
	where purchased_at >= '2020-07-05'
	and purchased_at < '2020-07-26'
	group by 1
	order by 1) sub

05. 요일별 Revenue

-- 2020년 7월의 요일별 Daily Revenue를 구해주세요. 어느 요일이 Revenue가 가장 높고 낮나요?

select date_format(date_at, '%w') as day_order
	,date_format(date_at, '%W') as day_name
	,avg(revenue)
from(select date_format(purchased_at - interval 9 hour, '%Y-%m-%d') as date_at
		, sum(price) as revenue
	from fastcampus.tbl_purchase
	where purchased_at >= '2020-07-01'
	and purchased_at < '2020-08-01'
	group by 1) sub
group by 1, 2
order by 1

06. 시간대별 Revenue

-- 2020년 7월 시간대별 시간당 총 Revenue를 구해주세요. 어느 시간대가 Revenue가 가장 높고 낮나요?

select hour_at, avg(revenue)
from (select date_format(purchased_at - interval 9 hour, '%Y-%m-%d') as date_at
		, date_format(purchased_at - interval 9 hour, '%H') as hour_at
		, sum(price) as revenue
	from fastcampus.tbl_purchase
	where purchased_at >= '2020-07-01'
	and purchased_at < '2020-08-01'
	group by 1,2) sub
group by 1
order by 2 desc

07. 요일 시간대별 Revenue

-- 2020년 7월의 요일 및 시간대별 Revenue를 구해주세요

select dayofweek_at
	, hour_at
    , avg(revenue)
from(select date_format(purchased_at - interval 9 hour, '%Y-%m-%d') as date_at
		, date_format(purchased_at - interval 9 hour, '%W') as dayofweek_at
		, date_format(purchased_at - interval 9 hour, '%H') as hour_at
		, sum(price) as revenue
	from fastcampus.tbl_purchase
	where purchased_at >= '2020-07-01'
	and purchased_at < '2020-08-01'
	group by 1,2,3) sub
group by 1,2
order by 3 desc

CH 05. 유저 세그먼트별 분석

01. 그룹핑하여 집계

-- 전체 유저의 Demographic을 알고 싶어요. 성 연령별로 유저 숫자를 알려주세요.
-- 어느 세그먼트가 가장 숫자가 많나요? 참고로 기타 성별은 하나로, 연령은 5세 단위로 적당히 묶어주시고 유저 수가 높은 순서대로 보여주세요

select case when length(gender) < 1 then 'Others'
			else gender end as gender
	, case when age <= 15 then '15세 이하'
		   when age <= 20 then '16-20세'
		   when age <= 25 then '21-25세'
		   when age <= 30 then '26-30세'
		   when age <= 35 then '31-35세'
           when age <= 40 then '36-40세'
		   when age <= 45 then '41-45세'
		   when age >= 46 then '46세 이상'
		   end as age
	, count(*)
from fastcampus.tbl_customer
group by 1,2
order by 3 desc

02. 세그먼트별 분포

-- 앞의 결과의 성 연령을 "남성(25-29세)"와 같이 통합해주시고, 각 성 연령이 전체 고객에서 얼마나 차지하는지 분포(%)를 알려주세요, 역시 분포가 높은 순서대로 알려주세요

select concat(case when length(gender) < 1 then '기타'
			when gender = 'Others' then '기타'
			when gender = 'M' then '남성'
			when gender = 'F' then '여성'
			else gender
            end
	, "("
	, case when age <= 15 then '15세 이하'
		   when age <= 20 then '16-20세'
		   when age <= 25 then '21-25세'
		   when age <= 30 then '26-30세'
		   when age <= 35 then '31-35세'
           when age <= 40 then '36-40세'
		   when age <= 45 then '41-45세'
		   when age >= 46 then '46세 이상'
		   end
    , ")") as segement
	, round(count(*)/(select count(*) from fastcampus.tbl_customer) * 100,2) as per
from fastcampus.tbl_customer
group by 1
order by 2 desc

03. 세그먼트별 Revenue(1)

-- 2022년 7월, 성별에 따라 총 구매 건수와, 총 Revenue를 구해주세요. 이전 처럼 남녀 이외의 성별은 하나로 묶어주세요

select case when length(B.gender) < 1 then '기타'
			when B.gender = 'Others' then '기타'
			when B.gender = 'M' then '남성'
			when B.gender = 'F' then '여성'
            end as gender
            , count(*) as cnt
            , sum(price) as revenue
from fastcampus.tbl_purchase A
left join fastcampus.tbl_customer B
on A.customer_id = B.customer_id
where A.purchased_at >= '2020-07-01'
and A.purchased_at < '2020-08-01'
group by 1

04. 세그먼트별 Revenue(2)

 

-- 2022년 7월, 성별 연령대에 따라 총 구매 건수와, 총 Revenue를 구해주세요.

select case when length(B.gender) < 1 then '기타'
			when B.gender = 'Others' then '기타'
			when B.gender = 'M' then '남성'
			when B.gender = 'F' then '여성'
            end as gender
			, case when age <= 15 then '15세 이하'
				when age <= 20 then '16-20세'
				when age <= 25 then '21-25세'
				when age <= 30 then '26-30세'
				when age <= 35 then '31-35세'
				when age <= 40 then '36-40세'
				when age <= 45 then '41-45세'
				when age >= 46 then '46세 이상'
		   end as age_group
            , count(*) as cnt
            , sum(price) as revenue
from fastcampus.tbl_purchase A
left join fastcampus.tbl_customer B
on A.customer_id = B.customer_id
where A.purchased_at >= '2020-07-01'
and A.purchased_at < '2020-08-01'
group by 1,2
order by 3 desc

CH 06. 매출 관련 추가 분석

01. 일별 매출 증감률

-- 2022년 7월 일별 매출의 전일 대비 증감폭, 증감률을 구해주세요

with tbl_revenue as (
					select date_format(purchased_at - interval 9 hour, '%Y-%m-%d') as d_date
						 , sum(price) as revenue
					from fastcampus.tbl_purchase
					where purchased_at >= '2020-07-01'
					  and purchased_at < '2020-08-01'
					group by 1
)

select  * 
	, revenue - lag(revenue) over (order by d_date asc) as diff_revenue
    , round((revenue - lag(revenue) over (order by d_date asc))/lag(revenue) over (order by d_date asc)*100,2) as chg_revenue
from tbl_revenue

02. 일별 고과금 유저

select *
from (
select date_format(purchased_at - interval 9 hour, '%Y-%m-%d') as d_date
	, customer_id
	, sum(price)
    , dense_rank() over (partition by date_format(purchased_at - interval 9 hour, '%Y-%m-%d') order by sum(price) desc) as rank_rev
from fastcampus.tbl_purchase
where purchased_at >= '2020-07-01'
  and purchased_at < '2020-08-01'
group by 1,2) sub
where rank_rev<4

CH 07. 프로덕트 분석 심화

01. 신규유저 Paying Conversion

-- 2020년 7월에 우리 신규유저가 하루 안에 결제로 넘어가는 비율이 어떻게 되나요? 그 비율이 어떤지 알고싶고, 결제까지 보통 몇 분정도가 소요되는지 알고싶어요.

with rt_tbl as(
select A.customer_id
, B.customer_id as paying_user
, time_to_sec(timediff(B.purchased_at, A.created_at)) / 3600 as diff_hour
from fastcampus.tbl_customer A
left join(
	select customer_id
		, min(purchased_at) as purchased_at
	from fastcampus.tbl_purchase
	group by 1) B
on A.customer_id = B.customer_id
and B.purchased_at < A.created_at + interval 1 day

where A.created_at >= '2020-07-01'
  and A.created_at < '2020-08-01') 
  
select round(count(paying_user) / count(customer_id)*100,2)
from rt_tbl
union all
select avg(diff_hour)
from rt_tbl

02. Retention

-- 우리 서비스는 유저의 재방문율이 높은 서비스인가요? 이를 파악하기 위해 7월 기준 Day1 Retention이 어떤지 구해주시고 추세를 보기 위해 Daily로 추출해주세요.

select date_format(A.visited_at - interval 9 hour, '%Y-%m-%d') as d_date
	, count(distinct A.customer_id) as active_user
	, count(distinct B.customer_id) as retained_user
    , count(distinct B.customer_id) / count(distinct A.customer_id) as retention
from fastcampus.tbl_visit A
left join fastcampus.tbl_visit B
on A.customer_id = B.customer_id
and date_format(A.visited_at - interval 9 hour, '%Y-%m-%d') = date_format(B.visited_at - interval 9 hour - interval 1 day, '%Y-%m-%d')
where A.visited_at >= '2020-07-01'
  and A.visited_at < '2020-08-01'
group by 1

03. 가입기간별 DAU

-- 2020sus 7월 우리 서비스는 신규유저가 많나요? 기존유저가 많나요? 가입기간별로 고객 분포가 어떤지 알려주세요. DAU 기준으로 부탁합니다.
-- tbl_visit 일자별로 고객의 last_visit crated_at = service age

with tbl_visit_by_joined as (
select date_format(A.visited_at - interval 9 hour, '%Y-%m-%d') as d_date
	, A.customer_id
    , B.created_at as d_joined
	, max(A.visited_at) as last_visit
    , datediff(max(A.visited_at),B.created_at) as date_diff
from fastcampus.tbl_visit A
left join fastcampus.tbl_customer B
on A.customer_id = B.customer_id

where A.visited_at >= '2020-07-01'
  and A.visited_at < '2020-08-01'

group by 1,2,3)

select A.d_date
	, case when A.date_diff >= 730 then "2년 이상"
		   when A.date_diff >= 365 then "1년 이상"
           when A.date_diff >= 183 then "6개월 이상"
           when A.date_diff >= 91 then "3개월 이상"
           when A.date_diff >= 30 then "1개월 이상"
           else "1개월 미만"
           end as segment
	, B.all_users
	, count(A.customer_id) as users
    , round(count(A.customer_id) / B.all_users*100,2) as per
from tbl_visit_by_joined A
left join (select d_date
				, count(customer_id) as all_users
			from tbl_visit_by_joined
            group by 1) B
on A.d_date = B.d_date
            
group by 1,2,3
order by 1,2

 

본 강의는 국비지원교육입니다.