T사 SQL 쿼리테스트 대비4: 리텐션 분석 (Retention Analysis)

1. 리텐션 분석 (Retention Analysis)

[가상 문제] 클래식 리텐션 (Day 1 Retention) 산출

특정 일자에 처음 방문한 유저가 **정확히 다음 날(D+1)**에 다시 방문했는지 확인하여, 일자별 Day 1 리텐션을 구하십시오.

• 테이블: UserVisits (컬럼: user_id, visit_date)

 

리텐션은 **'기준일(Day 0)의 유저 리스트'**를 확보하고, 그들이 **'비교일(Day N)'**에도 명단에 있는지 대조하는 작업입니다.

전체 방문자 수만 세는 것이 아니라 리텐션은 **'특정 날짜에 들어온 그 놈이 다시 왔는가'**를 묻는 겁니다. Self Join을 통해 기준일 유저와 기준일+1일 유저를 1:1로 매칭해야 합니다.

 

WITH FirstVisit AS (
    -- 유저별 최초 방문일 (Day 0)
    SELECT user_id, MIN(visit_date) AS first_date
    FROM UserVisits
    GROUP BY user_id
)
SELECT 
    f.first_date,
    COUNT(DISTINCT f.user_id) AS cohort_size,
    COUNT(DISTINCT v.user_id) AS day1_retained_users,
    ROUND(COUNT(DISTINCT v.user_id) / COUNT(DISTINCT f.user_id) * 100, 2) AS day1_retention_rate
FROM FirstVisit f
LEFT JOIN UserVisits v 
       ON f.user_id = v.user_id 
      AND v.visit_date = DATE_ADD(f.first_date, INTERVAL 1 DAY) -- 정확히 다음날 방문
GROUP BY f.first_date
ORDER BY f.first_date;

 

> 리텐션을 모르면 밑 빠진 독에 물 붓는 마케팅만 하게 된다.

 

 

 

2. 주차별 리텐션 (Weekly Retention)

특정 주(Week)에 처음 온 유저가, 그 다음 N주차에 다시 방문했는지 측정합니다.

주차 분석의 핵심은 날짜를 **'주 단위(Year-Week)'**로 그룹화하는 것입니다. FLOOR(DATEDIFF(...) / 7)를 써서 기준일로부터 몇 주가 지났는지 계산하는 논리가 훨씬 견고합니다. * DATE_ADD(..., INTERVAL 7 DAY) 지양

 

WITH FirstWeek AS (
    -- 1단계: 유저별 최초 방문 주 산출
    SELECT user_id, 
           MIN(visit_date) AS first_date,
           STR_TO_DATE(DATE_FORMAT(MIN(visit_date), '%X%V1'), '%X%V%w') AS first_week
    FROM UserVisits
    GROUP BY user_id
)
SELECT 
    f.first_week,
    FLOOR(DATEDIFF(v.visit_date, f.first_date) / 7) AS week_diff, -- 몇 주 뒤에 왔나?
    COUNT(DISTINCT v.user_id) AS retained_users
FROM FirstWeek f
LEFT JOIN UserVisits v ON f.user_id = v.user_id
GROUP BY 1, 2
HAVING week_diff >= 0
ORDER BY 1, 2;

 

 

3. 롤링 리텐션 (Rolling Retention / Unbounded Retention)

"Day N에 방문했는가?"가 아니라, **"Day N 이후에 단 한 번이라도 방문했는가?"**를 묻습니다. 이탈(Churn)을 측정할 때 매우 냉혹하고 정확한 지표입니다.

롤링 리텐션은 클래식 리텐션보다 훨씬 수치가 높게 나옵니다. "어쨌든 나중에라도 왔으면 리텐션"으로 치니까요.  롤링 리텐션은 **'이 날 이후로 영영 돌아오지 않은 유저'**를 찾기 위한 도구입니다.

 

WITH LastVisit AS (
    -- 1단계: 유저별 첫 방문일과 '마지막' 방문일 산출
    SELECT user_id, 
           MIN(visit_date) AS first_date,
           MAX(visit_date) AS last_date
    FROM UserVisits
    GROUP BY user_id
)
SELECT 
    first_date,
    COUNT(DISTINCT user_id) AS cohort_size,
    -- Day 7 롤링 리텐션: 첫 방문 후 7일이 지난 시점에도 여전히 '마지막 방문일'이 남아있는 유저
    COUNT(DISTINCT CASE WHEN DATEDIFF(last_date, first_date) >= 7 THEN user_id END) AS rolling_7d_users,
    ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(last_date, first_date) >= 7 THEN user_id END) 
          / COUNT(DISTINCT user_id) * 100, 2) AS rolling_7d_rate
FROM LastVisit
GROUP BY first_date
ORDER BY first_date;

 

 

풀이 방법

• DATEDIFF를 쓸 때 순서가 바뀌면 음수가 나옵니다.  쿼리에서도 방향성(last - first)을 명확히 하십시오.
• 롤링 리텐션 쿼리에서 MAX(visit_date)를 활용하는 방식은 굉장히 세련된 접근, 최소한의 연산으로 최대한의 통찰(Insight)

인사이트 

• 클래식 리텐션은 매일 쓰는 '카카오톡' 같은 서비스에 적합하고, 롤링 리텐션은 가끔 쓰는 '여행/부동산' 앱에 적합합니다. 문제의 맥락에 따라 어떤 리텐션을 쓸지 먼저 정의해야 합니다.