유저 세그먼트별 분석
1. 전체 유저의 Demographic 알고 싶어요. 성/연령별 유저 숫자를 알려주세요. 어느 세그먼트가 가장 숫자가 많나요? 참고로 기타 성별은 하나로, 연령은 5세 단위로 적당히 묶어주시고 유저 수가 높은 순서대로 보여주세요.
*성별 유저 숫자 쿼리
select gender, count(*)
from fastcampus.tbl_customer
group by 1
*성별에 null 값이 있을 때 null 값은 others 로 표현하는 쿼리
select case when gender is null then 'Others'
else gender end as gender
, count(*)
from fastcampus.tbl_customer
group by 1
*이렇게 했음에도 data가 정제가 되어있지 않아서 ohters로 표현이 안될때 length사용(length [컬럼이름] <1 작다는 것은 null 값이라는 뜻)
select case when length(gender) < 1 then 'Others'
else gender end as gender
, count(*)
from fastcampus.tbl_customer
group by 1
*성/연령별 유저 숫자
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;
2. Q1의 결과의 성/연령을 "남성(25~29세)"와 같이 통합해주시고, 각 성/연령이 전체 고객에서 얼마나 차지하는지 분포(%)를 알려주세요, 역시 분포가 높은 순서대로 알려주세요.
*성/연령을 "남성(25~29세)"와 같이 통합하는 쿼리
select concat(case when length(gender) < 1 then '기타'
when gender = 'Others' then '기타'
when gender = 'F' then '여성'
when gender = 'M' then '남성'
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 segment
, count(*)
from fastcampus.tbl_customer
group by 1;
*전체 고객 수를 측정하는 쿼리
select count(*)
from fastcampus.tbl_customer
*각 성/연령이 전체 고객에서 얼마나 차지하는지 분포(%) -> 전체 고객 수를 측정하는 쿼리를 통해 나온 값을 나눠 줌
select concat(case when length(gender) < 1 then '기타'
when gender = 'Others' then '기타'
when gender = 'F' then '여성'
when gender = 'M' then '남성'
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 segment
, count(*)/14225
from fastcampus.tbl_customer
group by 1;
*각 성/연령이 전체 고객에서 얼마나 차지하는지 분포(%) -> select 문에 쿼리를 하나 집어 넣어줌(스컬라서브쿼리), 값이 하나가 나올 때는 스컬라 서브 쿼리를 사용 가능
select count(*)
from fastcampus.tbl_customer ;
select concat(case when length(gender) < 1 then '기타'
when gender = 'Others' then '기타'
when gender = 'F' then '여성'
when gender = 'M' then '남성'
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 segment
, round(count(*)/(select count(*) from fastcampus.tbl_customer)*100,2) as per
from fastcampus.tbl_customer
group by 1
order by 2 desc;
3. 2020년 7월 셩별에 따라 총 구매 건수와 총 Revenue를 구해주세요. 이전 처럼 남녀 이외의 성별은 하나로 묶어주세요.
*left join으로 테이블 합치기(구매의 모든 테이블+고객 gerder 컬럼)
select A.*, B.gender
from fastcampus.tbl_purchase A
left join fastcampus.tbl_customer B
on A.customer_id = B.customer_id
limit 10
*테이블 합친 후, -> where 사용하여 기간에 대한 조건주기 -> gender case, when문 사용, gender에 따라 그 외 필요컬럼추가
select case when B.gender = 'M' then '남성'
when B.gender = 'F' then '여성'
when B.gender = 'Others' then '기타'
when length(B.gender)<1 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;
4. 2020년 7월의 성별/연령대에 따라 구매 건수와 총 revenue 구하기
select count(*)
from fastcampus.tbl_customer ;
select concat(case when length(gender) < 1 then '기타'
when gender = 'Others' then '기타'
when gender = 'F' then '여성'
when gender = 'M' then '남성'
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 segment
, round(count(*)/(select count(*) from fastcampus.tbl_customer)*100,2) as per
from fastcampus.tbl_customer
group by 1
order by 2 desc;
select case when B.gender = 'M' then '남성'
when B.gender = 'F' then '여성'
when B.gender = 'Others' then '기타'
when length(B.gender)<1 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;
매출 관련 추가 분석
1. 2020년 7월 일별 매출의 전일 대비 증감폭, 증감률을 구해주세요
*일별 매출 쿼리
select date_format(purchased_at - interval 9 hour, '%Y-%m-%d') as d_date, sum(price)
from fastcampus.tbl_purchase
where purchased_at >='2020-07-01'
and purchased_at < '2020-08-01'
group by 1
*똑같은 쿼리를 여러번 작성해야한다면 임시 테이블처럼 테이블 정의 가능(with문)
-with 문 : with 테이블이름 as (쿼리)
-행순서 관련 함수, 윈도우함수, 레그함수 : 전일대비 증감폭 구하려면 컬럼 하나를 추가, 전일 revenue 를 가져와야함
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 *
, lag(revenue) over(order by d_date asc)
from tbl_revenue
*전일대비 증감폭 구하기 revenue에서 전일 revenue 빼기
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)
from tbl_revenue
*전일대비 증감률 구하기
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;
2. 7월 일별로 구매 금액 기준으로 가장 많이 지출한 고객 top 3
*7월 구매를 가장 많이한 고객 top 3 쿼리
select customer_id, sum(price)
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
*일별로 순위 구하기 -> rank 함수의 파티션을 사용하여 rank 내에서 그룹을 지어 줌
select date_format(purchased_at - interval 9 hour, '%Y-%m-%d') 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)) as rank_rev
from fastcampus.tbl_purchase
where purchased_at >='2020-07-01'
and purchased_at < '2020-08-01'
group by 1, 2
*일별로 top3 구하기 -> 서브쿼리를 사용하여 조건절 적용시키기
select *
from(
select date_format(purchased_at - interval 9 hour, '%Y-%m-%d') 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)) as rank_rev
from fastcampus.tbl_purchase
where purchased_at >='2020-07-01'
and purchased_at < '2020-08-01'
group by 1, 2) foo
where rank_rev < 4;
