코호트별 D7 리텐션율

난이도 보통 · 권장 12분 · 출력 컬럼 first_trade_date, cohort_size, d7_active_users, d7_retention_rate

문제

first_trade_date 가 2026-02-01 ~ 2026-02-07 인 유저를 코호트로 봅니다. 첫 거래일자별로 아래를 구하세요. cohort_size 그 날 첫 거래한 유저 수 d7_active_users 그 유저 중 '첫 거래일 + 정확히 7일' 에 앱을 연 유저 수 d7_retention_rate d7_active_users / cohort_size x 100 · +7일 '이내' 가 아니라 '정확히 그 날' 입니다. · daily_app_visits 는 같은 유저가 같은 날 여러 행을 남길 수 있습니다. · 재방문자가 0 명인 날도 0 으로 한 행 나와야 합니다. · 리텐션율은 소수점 첫째 자리까지 반올림합니다. · first_trade_date 오름차순으로 정렬하세요.

정렬 first_trade_date ASC

쉽게 설명하면

첫 거래한 날로부터 딱 7일 뒤 다시 왔나. 첫 거래일마다 따로 센다.

첫 거래	확인할 날	왔으면
2월 1일	2월 8일	재방문 1명
2월 2일	2월 9일	아무도 안 옴 → 0

'7일 안에' 가 아니라 '딱 그 날'. 2월 7일에 왔어도 안 세고, 2월 9일에 왔어도 안 센다. 재방문 0명인 날도 0 으로 한 줄 나와야 한다.

이렇게 푼다

코호트 명단(first_trades)을 왼쪽에 둔다. 분모가 여기서 나온다.
방문 기록을 붙이는데, 조건을 ON 안에 쓴다 — ON 같은사람 AND 방문일 = 첫거래일 + 7일.
분모 = 명단 사람 수, 분자 = 붙은 사람 수. 둘 다 중복 지우고 센다.

이것만 피하면 된다

조건을 WHERE 에 쓰면 LEFT JOIN 이 그냥 JOIN 이 되어 버린다. 안 온 사람은 방문일이 빈칸인데, WHERE 가 그 빈칸을 걸러내서 분모에서까지 사라진다. → 오른쪽 테이블 조건은 반드시 ON 에. 이 한 줄이 리텐션 문제의 핵심이다.
분자에 중복 제거를 빼면 100% 를 넘는다. 같은 사람이 같은 날 여러 번 방문 기록을 남길 수 있다. 리텐션이 100 을 넘으면 거의 항상 이 실수다.
분모를 COUNT(*) 로 쓰면 안 된다. 붙이면서 늘어난 줄 수가 나온다.

자세히

묻는 것 · 코호트 테이블을 왼쪽에 두고 활동 로그를 LEFT JOIN 한다. 코호트 문제의 90% 가 이 모양이다.

풀이 순서

코호트 테이블(first_trades)을 왼쪽에 둔다. 분모가 여기서 나온다.
활동 로그를 LEFT JOIN 하고, 조인 조건에 visit_date = first_trade_date + 7일 을 넣는다.
분모는 COUNT(DISTINCT f.user_id), 분자는 COUNT(DISTINCT v.user_id). 둘 다 DISTINCT 다.
범위 조건은 WHERE 에 쓴다 (왼쪽 테이블 조건이므로 안전하다).

정답 (MySQL)

SELECT f.first_trade_date,
       COUNT(DISTINCT f.user_id) AS cohort_size,
       COUNT(DISTINCT v.user_id) AS d7_active_users,
       ROUND(100.0 * COUNT(DISTINCT v.user_id)
             / NULLIF(COUNT(DISTINCT f.user_id), 0), 1) AS d7_retention_rate
FROM first_trades f
LEFT JOIN daily_app_visits v
       ON v.user_id = f.user_id
      AND v.visit_date = DATE_ADD(f.first_trade_date, INTERVAL 7 DAY)
WHERE f.first_trade_date BETWEEN '2026-02-01' AND '2026-02-07'
GROUP BY f.first_trade_date
ORDER BY f.first_trade_date;

함정

JOIN 조건을 WHERE 로 옮기면 LEFT JOIN 이 INNER JOIN 이 된다. WHERE v.visit_date = ... 로 쓰면 재방문 안 한 유저의 NULL 행이 조건에 걸려 사라지고, 분모까지 같이 깎인다. 오른쪽 테이블 조건은 반드시 ON 에 — 이 한 줄이 리텐션 문제의 핵심이다.
'정확히 7일' 과 '7일 이내' 는 다른 문제다. = 로 쓸 자리에 <= 를 쓰면 숫자가 크게 부풀어 오른다. 문제를 다시 읽어라.
분자에 DISTINCT 를 빼면 100% 를 넘는다. 방문 로그는 같은 유저가 같은 날 여러 행을 남길 수 있다. 리텐션율이 100 을 넘으면 거의 항상 이 실수다.
분모를 COUNT(*) 로 쓰면 안 된다. LEFT JOIN 후의 COUNT(*) 는 조인으로 불어난 행 수다. 코호트 크기가 아니다.
재방문자 0 명인 날도 한 행 나와야 한다. LEFT JOIN 이면 자동으로 0 이 된다. COUNT 는 빈 입력에서 0 을 돌려주기 때문이다 (SUM 은 NULL 이 된다 — 다른 문제에서 자주 나오는 함정).

이렇게 변형된다

“D1/D7/D30 을 한 번에”면 조인 조건을 visit_date IN (+1일, +7일, +30일) 로 바꾸고 COUNT(DISTINCT CASE WHEN ... END) 를 세 개 만든다. “월 코호트 매트릭스”면 TIMESTAMPDIFF(MONTH, 코호트월, 활동월) 을 경과 개월로 쓰고 그걸로 피벗한다.