SQL#CHALLENGE4-1

codataffee·2024년 6월 4일
sql
0

SQL

목록 보기
15/19
post-thumbnail

개요

데이터 준비 (실습 및 과제)

  • solvesql 플레이그라운드 > 데이터 베이스 UK E-Commerce Orders

📌 실습

코호트 리텐션 (고객의 수) 구하기

월별 클래식 주문 - 클래식 리텐션(고객 수) 구하기

  • 데이터 살펴보기

    고객이 첫 주문을 한 월을 first_order_month 컬럼에 출력하고,
    첫 주문으로부터 1개월 후, 2개월 후, 11개월 후에도 주문하는 고객 수를
    각각 계산해 출력하는 쿼리 작성해보기

    • month0 - 해당 월에 처음 주문한 고객의 수
    • month1 - 해당 월에 처음 주문하고, 그 다음 달에도 주문한 고객의 수
    • month2 ~ month11 - 해당 월에 처음 주문하고, 2~11개월 뒤에도 주문한 고객의 수

  • 쿼리 구조 생각해보기

    1. 고객이 첫 주문을 한 데이터를 조회하는 테이블 생성
    2. 첫 주문 데이터들을 월별로 묶어서 조회하는 테이블 생성
    3. 각 월별 데이터들을 그룹화하여 1개월 단위로 해당하는 고유한 고객 수를 조회하기
  • CHECK
    미국 거래 데이터이다보니
    날짜를 나타내는 Date 컬럼의 데이터들이 월 / 일 / 년의 문자열로 작성되어 있었고,
    문자열로 작성된 데이터를 str_to_date() 함수를 통해 날짜 데이터로 변환했다.
# str_to_date(문자열, 날짜 형식) : 문자열 데이터 → 날짜 데이터 변환 
# 날짜 형식에는 문자열에 입력된 형식을 나타내줘야 한다.
# 아래 예에서 %m/%d/%Y는 입력 문자열의 형식이 "월/일/연도"임을 나타냄.

SELECT STR_TO_DATE('01/31/2024', '%m/%d/%Y');
결과: '2024-01-31'

1. 고객이 첫 주문을 한 데이터를 조회하는 테이블 생성 ( + 날짜 형식 변환)

with firsts as (
	select CustomerNo f_cn
		 , TransactionNo f_tn
		 , str_to_date(`Date`, '%m/%d/%Y') f_d
		 , min(str_to_date(`Date`, '%m/%d/%Y')) over (partition by CustomerNo order by str_to_date(`Date`, '%m/%d/%Y')) first_order_date
	from SALES_TRANSACTION st
	where CustomerNo is not NULL 
)
select *
from firsts

2. 첫 주문 데이터들을 월별로 묶어서 조회하는 테이블 생성

with firsts as (
	select CustomerNo f_cn
		 , TransactionNo f_tn
		 , str_to_date(`Date`, '%m/%d/%Y') f_d
		 , min(str_to_date(`Date`, '%m/%d/%Y')) over (partition by CustomerNo order by str_to_date(`Date`, '%m/%d/%Y')) first_order_date
	from SALES_TRANSACTION st
	where CustomerNo is not NULL 
),
monthly as (
	select f_cn
		 , f_tn
		 , f_d
		 , first_order_date
		 , date_format(f_d, '%Y-%m') order_month
		 , date_format(first_order_date, '%Y-%m') first_order_month
	from firsts
)
select *
from monthly

3. 각 월별 데이터들을 그룹화하여 1개월 단위로 해당하는 고유한 고객 수를 조회하기

with firsts as (
	select CustomerNo f_cn
		 , TransactionNo f_tn
		 , str_to_date(`Date`, '%m/%d/%Y') f_d
		 , min(str_to_date(`Date`, '%m/%d/%Y')) over (partition by CustomerNo order by str_to_date(`Date`, '%m/%d/%Y')) first_order_date
	from SALES_TRANSACTION st
	where CustomerNo is not NULL 
),
monthly as (
	select f_cn
		 , f_tn
		 , f_d
		 , first_order_date
		 , date_format(f_d, '%Y-%m') order_month
		 , date_format(first_order_date, '%Y-%m') first_order_month
	from firsts
)
select first_order_month
	 , count(distinct f_cn) month0
	 , count(distinct case when date_format(date_add(first_order_date, interval 1 month), '%Y-%m') = order_month then f_cn else null end) month1
	 , count(distinct case when date_format(date_add(first_order_date, interval 2 month), '%Y-%m') = order_month then f_cn else null end) month2
	 , count(distinct case when date_format(date_add(first_order_date, interval 3 month), '%Y-%m') = order_month then f_cn else null end) month3
	 , count(distinct case when date_format(date_add(first_order_date, interval 4 month), '%Y-%m') = order_month then f_cn else null end) month4
	 , count(distinct case when date_format(date_add(first_order_date, interval 5 month), '%Y-%m') = order_month then f_cn else null end) month5
	 , count(distinct case when date_format(date_add(first_order_date, interval 6 month), '%Y-%m') = order_month then f_cn else null end) month6
	 , count(distinct case when date_format(date_add(first_order_date, interval 7 month), '%Y-%m') = order_month then f_cn else null end) month7
	 , count(distinct case when date_format(date_add(first_order_date, interval 8 month), '%Y-%m') = order_month then f_cn else null end) month8
	 , count(distinct case when date_format(date_add(first_order_date, interval 9 month), '%Y-%m') = order_month then f_cn else null end) month9
	 , count(distinct case when date_format(date_add(first_order_date, interval 10 month), '%Y-%m') = order_month then f_cn else null end) month10
	 , count(distinct case when date_format(date_add(first_order_date, interval 11 month), '%Y-%m') = order_month then f_cn else null end) month11
from monthly
group by first_order_month
order by first_order_month

📌 과제

1. 활성 유저의 리텐션 계산

목표: Facebook SQL 인터뷰에 나온 활성 유저의 리텐션을 SQL로 계산

결과: 사이트에서 문제를 풀고 정답 SQL 코드를 제출

문제 :

제출 :

쿼리 :

# 6월과 7월 각각 존재하는 user_id 조회하는 CTE 생성
WITH june AS (
    SELECT DISTINCT user_id
    FROM user_actions
    WHERE event_date BETWEEN '2022-06-01' AND '2022-06-30'
),
july AS (
    SELECT DISTINCT user_id
    FROM user_actions
    WHERE event_date BETWEEN '2022-07-01' AND '2022-07-31'
)
# 두 테이블에 모두 존재하는 user_id의 수 (7월 활성유저) 조회
SELECT 
    7 AS mth,
    COUNT(DISTINCT jl.user_id) AS monthly_active_users
FROM 
    june jn INNER JOIN july jl
    ON jn.user_id = jl.user_id

2. 코호트 리텐션 계산

목표: 세션에서 코호트 리텐션 (고객의 수)를 구한 것을 기반으로 재구매 클래식 리텐션을 비율로 구하기

  • 학습 목표 : 코호트 리텐션의 필요성에 대해 이해하고 이를 SQL로 구현할 수 있다.
  1. solvesql에 구글 로그인으로 가입한다.
  2. 플레이그라운드로 들어간다.
  3. 데이터 베이스에서 UK E-Commerce Orders를 클릭한다.

결과: 월별 재구매 리텐션을 백분율 (소수점 두번째짜리까지)로 구해 SQL 코드를 제출

위 실습에서 진행한 데이터에 첫 달 구매 고객 수를 각 월별로 나누어 비율을 계산해볼 수 있다.

with firsts as (
	select CustomerNo f_cn
		 , TransactionNo f_tn
		 , str_to_date(`Date`, '%m/%d/%Y') f_d
		 , min(str_to_date(`Date`, '%m/%d/%Y')) over (partition by CustomerNo order by str_to_date(`Date`, '%m/%d/%Y')) first_order_date
	from SALES_TRANSACTION st
	where CustomerNo is not NULL 
),
monthly as (
	select f_cn
		 , f_tn
		 , f_d
		 , first_order_date
		 , date_format(f_d, '%Y-%m') order_month
		 , date_format(first_order_date, '%Y-%m') first_order_month
	from firsts
)
select first_order_month
	 , round((count(distinct f_cn) / count(distinct f_cn)) * 100, 2) month0
	 , round((count(distinct case when date_format(date_add(first_order_date, interval 1 month), '%Y-%m') = order_month then f_cn else null end) / count(distinct f_cn)) * 100, 2) month1
	 , round((count(distinct case when date_format(date_add(first_order_date, interval 2 month), '%Y-%m') = order_month then f_cn else null end) / count(distinct f_cn)) * 100, 2) month2
	 , round((count(distinct case when date_format(date_add(first_order_date, interval 3 month), '%Y-%m') = order_month then f_cn else null end) / count(distinct f_cn)) * 100, 2) month3
	 , round((count(distinct case when date_format(date_add(first_order_date, interval 4 month), '%Y-%m') = order_month then f_cn else null end) / count(distinct f_cn)) * 100, 2) month4
	 , round((count(distinct case when date_format(date_add(first_order_date, interval 5 month), '%Y-%m') = order_month then f_cn else null end) / count(distinct f_cn)) * 100, 2) month5
	 , round((count(distinct case when date_format(date_add(first_order_date, interval 6 month), '%Y-%m') = order_month then f_cn else null end) / count(distinct f_cn)) * 100, 2) month6
	 , round((count(distinct case when date_format(date_add(first_order_date, interval 7 month), '%Y-%m') = order_month then f_cn else null end) / count(distinct f_cn)) * 100, 2) month7
	 , round((count(distinct case when date_format(date_add(first_order_date, interval 8 month), '%Y-%m') = order_month then f_cn else null end) / count(distinct f_cn)) * 100, 2) month8
	 , round((count(distinct case when date_format(date_add(first_order_date, interval 9 month), '%Y-%m') = order_month then f_cn else null end) / count(distinct f_cn)) * 100, 2) month9
	 , round((count(distinct case when date_format(date_add(first_order_date, interval 10 month), '%Y-%m') = order_month then f_cn else null end) / count(distinct f_cn)) * 100, 2) month10
	 , round((count(distinct case when date_format(date_add(first_order_date, interval 11 month), '%Y-%m') = order_month then f_cn else null end) / count(distinct f_cn)) * 100, 2) month11	 	 
from monthly
group by first_order_month
order by first_order_month

3. 개별 서비스의 리텐션 추론하기

목표: 각자 관심있는 웹/앱 서비스를 3개 이상 선택하여 해당 서비스의 예상 리텐션 정의, 종류, 그리고 그렇게 생각한 이유를 서술형으로 작성

  • 학습 목표 : 리텐션의 기본 개념과 종류에 대해 이해하고 이를 설명할 수 있다.
  • 학습 목표 : 리텐션의 개념을 응용하여 개별 서비스의 리텐션을 추론할 수 있다.

결과:

예시 )

  • 서비스 : 배달의 민족
    • 리텐션 정의 :
      • 이유 :
    • 리텐션 종류 :
      • 이유 :

  • 서비스 : 슬랙 (Slack)

  • 리텐션 정의 :

    • 일간 활성 사용자 비율 (Daily Active Users, DAU) :
      하루 간 슬랙에 로그인하여 활동한 사용자 비율
    • 주간 활성 사용자 비율 (Weekly Active Users, WAU) :
      한 주간 슬랙에 로그인하여 활동한 사용자 비율
    • 월간 활성 사용자 비율 (Monthly Active Users, MAU) :
      한 달간 슬랙에 로그인하여 활동한 사용자 비율
      • 이유 :
        슬랙은 실시간 협업 도구로, 사용자들이 얼마나 자주 플랫폼에 접속하여 활동하는지가 중요하다. 높은 활성 사용자 비율은 서비스의 이용과 사용자들의 실시간 협업 등의 활동이 활발하게 이루어지고 있음을 나타낼 것이다.

  • 리텐션 종류 :

    • 일간 리텐션 :
      하루 동안 슬랙에 접속해 메시지 전송, 파일 공유 등의 활동을 한 사용자 비율
    • 주간 리텐션 :
      한 주 동안 슬랙에 접속하여 활동한 사용자 비율
    • 월간 리텐션 :
      한 달 동안 슬랙에 접속하여 활동한 사용자 비율
      • 이유 :
        일간 - 슬랙은 팀 커뮤니케이션 도구라 사용자들이 매일 접속해 협업하는 것이 중요하므로, 일간 리텐션을 통해 사용자들이 플랫폼을 일상적으로 사용하는지 확인할 수 있을 것이다.
        주간 - 주간 리텐션을 통해 사용자들이 정기적으로 협업을 하는지 파악할 수 있을 것이다.
        월간 - 월간 리텐션을 통해 사용자들이 장기간 지속적으로 사용하는지 파악하고, 슬랙의 전체적인 사용자 만족도와 플랫폼의 지속 사용 여부를 평가하는 데 도움이 될 것이다.

  • 리텐션 측정 지표 :

    • 일간 : DAU / 총 사용자 수 * 100
    • 주간 : WAU / 총 사용자 수 * 100
    • 월간 : MAU / 총 사용자 수 * 100

  • 서비스 : 뉴닉 (NEWNEEK)

  • 리텐션 정의 :

    • 뉴스레터 열람율
      • 이유 :
        뉴닉은 주기적으로 뉴스레터를 발송하여 사용자들에게 뉴스를 전달하는 서비스.
        뉴스레터를 꾸준히 열람하는 사용자는 뉴닉의 콘텐츠에 대한 관심이 높고,
        지속적으로 뉴닉의 서비스를 이용할 가능성이 크다.

  • 리텐션 종류 :

    • 주간 리텐션 (Weekly Retention)

      • 이유 :
        뉴닉은 주기적으로 뉴스레터를 발송하므로,
        매주 뉴스레터를 열람하는 사용자의 비율을 통해 리텐션을 측정
        주간 리텐션은 사용자가 정기적으로 뉴닉의 콘텐츠를 소비하는지 파악할 수 있을 것이다.

    • 월간 리텐션 (Monthly Retention)

      • 이유 :
        월간 리텐션을 통해 사용자가 한 달 동안 꾸준히 뉴스레터를 열람하는지를 확인하고,
        사용자가 장기적으로 서비스를 이용하는지를 파악할 수 있을 것이다.

  • 리텐션 측정 지표 :

    • 주간 : 주간 뉴스레터 열람 사용자 수 / 주간 뉴스레터 발송 사용자 수 * 100
    • 월간 : 월간 뉴스레터 열람 사용자 수 / 월간 뉴스레터 발송 사용자 수 * 100
profile
codataffee
커피 좋아하는 데이터 꿈나무
이전 포스트

SQL#CHALLENGE4

다음 포스트

SQL#CHALLENGE5

0개의 댓글

관련 채용 정보