SQL데이터 분석 KPI 지표 수립 실습 (유저 세그먼트별 문제 모음)

이번 실습은 각 기업에서 실제로 통계자료로써 각종 KPI 지표들을 mysql을 통해  추출할 것이다.

유저들을 나이 또는 성별로 구분짓고 파티션을 나눠서 나타낼 것이다.

 

실습1

전체  유저의 demographic(인구 통계학적 변동/경향/요인)을 알고 싶다.

성, 연령별로 유저 숫자를 알려주세요. 
성별은 하나로 묶고 연령은 5세 단위로 묶고 유저수가 높은 순서로 보여주세요

 

case when 문을 이용하여 성별과 나이를 나눈다.

성별을 먼저 알아보니

보시다 시피 빈칸이 존재한다. 

빈칸을 is null 문으로 구분해봤지만 나오지 않았고 이럴때 사용하는 방법이

length(gender) <1 을 사용하는 것이다. 길이가 1보다 작다는 건 null이나 다름없으니 말이다.

 

이렇듯 성별을 구분해주고

나머지 나이도 구분해준다.

유저가 많은 순서대로 보여주기 위해 유저 수 기준으로 order by 내림차순을 진행한다.

 

실습2

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

 

실습1에서 진행한 성별과 연령을 concat으로 연결한 후

분포도를 구하는 실습이다.

분포도는 성별과 연령이 구분된 고객수/전체유저 수 *100 이다. 이는 round 함수를 사용하고

스칼라 서브쿼리로 전체유저수를 추출한다.

 

 

실습3

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

 

성별과 revenue는 서로 다른 테이블에서 추출할 수 있기 때문에 join을 사용하여 2테이블을 병합한다.

성별 묶는 건 위의 실습과 동일하다.

 

 

실습4

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

 

실습3과 거의 동일한 쿼리로 연령만 추가해주면 된다.