[SQL] 세그먼트별 분석

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

select 	case when length(gender) < 1 then '기타'
		else gender end as gender
		,case when age is null then '나이를 적지 않았어요'
        when age <= 15 then '0세에서 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(*) as cnt
from fastcampus.tbl_customer
group by 1,2
order by 3 desc;

15. 성 + 연령을 "남성(25~29)"와 같이 통합해주시고 각 성+연령이 전체 고객에서 얼마나 차지하는지 분포(%)를 알려주세요(분포가 높은 순서)

select concat(case when gender = 'M' then '남성'
				when gender = "F" then '여성'
                when gender = 'Others' then '기타'
                when length(gender) < 1 then '기타'
                end 
			, '(', case when age is null then '나이를 적지 않았어요'
				when age <= 15 then '0세에서 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 gen_age, count(*) as cnt, round(count(*)
                / (select count(*) from fastcampus.tbl_customer)*100, 2) as percentage
FROM fastcampus.tbl_customer
group by 1
order by 2 desc;

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

select case when gender = 'M' then '남성'
		when gender = 'F' then '여성'
        when length(gender) < 1 then 'Others'
        else 'Others'
        end as gender
, COUNT(*) as cnt,SUM(price) as revenue
from fastcampus.tbl_purchase as t1
LEFT JOIN  fastcampus.tbl_customer as t2
ON t1.customer_id = t2.customer_id
where t1.purchased_at >= '2020-07-01'
	and t1.purchased_at < '2020-08-01'
    
GROUP BY 1;

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

SELECT case when t2.gender = 'M' then '남자'
			when t2.gender = 'F' then '여자'
            when length(t2.gender) < 1 then 'Others'
            else 'Others'
		end as gender,
        case when age is null then '나이를 적지 않았어요'
				when age <= 15 then '0세에서 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(*) as cnt, SUM(price) as total_price
FROM fastcampus.tbl_purchase as t1
LEFT JOIN fastcampus.tbl_customer as t2
ON t1.customer_id = t2.customer_id
WHERE t1.purchased_at >= '2020-07-01'
	and t1.purchased_at < '2020-08-01'
GROUP BY  1,2
ORDER BY 4 desc;

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

with 문 : 서브쿼리를 사용해서 임시테이블 처럼 사용할 수 있는 구문
-> 옵티마이저가 인라인 뷰나 임시테이블로 판단

window 함수 이해
-> lag : 이전 행을 가져옴
-> lead : 특정 위치의 행을 가져옴

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
order 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;

19. 일별로 많이 구매한 고객들한테 소정의 선물, 7월에 일별로 구매 금액 기준으로 가장 많이 지출한 고객 TOP3를 뽑아주세요

select *
from (select date_format(purchased_at - interval 9 hour, '%Y-%m-%d') as date,
			customer_id, SUM(price) as revenue,
            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) as foo

where rank_rev < 4;

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

with rt_tbl as (
	select A.*, B.customer_id as paying_user,
    B.purchased_at,
    TIME_TO_SEC(TIMEDIFF(B.purchased_at, A.created_at))/3600 as diff_hours
		from fastcampus.tbl_customer AS A
		left join(select customer_id, min(purchased_at) as purchased_at
			from fastcampus.tbl_purchase
            group by customer_id) AS  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 round(avg(diff_hours),2)
from rt_tbl;

21. 우리 서비스는 유저의 재방문율이 높은 서비스인가요? 이를 파악하기 위해 7월 기준 Day 1 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;