월별 신규 가입자와 결제 전환율

난이도 쉬움 · 권장 6분 · 출력 컬럼 signup_month, signups, converted, conversion_rate

문제

가입 월별로 아래 세 가지를 구하세요. signups 그 달의 신규 가입자 수 converted 그중 가입 후 30일 이내에 첫 결제를 한 사람 수 conversion_rate converted / signups × 100 · 분모는 그 달의 전체 가입자입니다. 결제 이력이 없는 유저도 포함합니다. · 가입 후 31일째 이후에 첫 결제한 사람은 converted 에서 제외합니다. · 전환율은 소수점 둘째 자리까지 반올림합니다. · 월 오름차순으로 정렬하세요.

정렬 signup_month ASC

쉽게 설명하면

반 인원 중에 몇 명이 학원에 등록했나 — 를 가입한 달마다 따로 센다. 등록 안 한 애도 반 인원에는 들어가야 한다.

가입 월	가입자 (분모)	30일 안에 결제 (분자)	전환율
1월	100명	23명	23.00
2월	120명	31명	25.83

결제를 아예 안 한 사람도 가입자 100명에는 포함된다. 이게 핵심.

이렇게 푼다

먼저 사람마다 첫 결제일이 언제인지 따로 구해 둔다.
유저 명단 왼쪽에 두고 그 첫 결제일을 옆에 붙인다 (LEFT JOIN).
가입한 달로 묶어서, 전체 인원을 세고 / 30일 안에 결제한 사람만 따로 센다.
나누고 ×100, 소수점 둘째 자리까지.

이것만 피하면 된다

그냥 JOIN 으로 붙이면 안 된다. 결제 안 한 사람이 통째로 사라져서 분모가 줄어들고, 전환율이 실제보다 훨씬 높게 나온다. LEFT JOIN — '왼쪽 명단은 무조건 다 남긴다' 는 뜻이다.
100 이 아니라 100.0. 정수끼리 나누면 소수점이 날아가서 0 이 나오는 환경이 있다.

자세히

묻는 것 · ‘전체’가 분모인 비율을 구할 줄 아는가. 그리고 조건부 집계.

풀이 순서

유저별 첫 결제일을 먼저 만든다 (MIN). payments 를 바로 조인하면 한 유저가 여러 행이 된다.
users 를 기준으로 LEFT JOIN. 결제 없는 유저가 분모에 남아야 한다.
converted 는 조건부 집계로 센다.
비율은 converted / signups × 100.

정답 (MySQL)

WITH first_pay AS (
  SELECT user_id, MIN(paid_date) AS fp FROM payments GROUP BY user_id
)
SELECT DATE_FORMAT(u.signup_date, '%Y-%m') AS signup_month,
       COUNT(*) AS signups,
       SUM(CASE WHEN f.fp <= DATE_ADD(u.signup_date, INTERVAL 30 DAY)
                THEN 1 ELSE 0 END) AS converted,
       ROUND(100.0 * SUM(CASE WHEN f.fp <= DATE_ADD(u.signup_date, INTERVAL 30 DAY)
                THEN 1 ELSE 0 END) / COUNT(*), 2) AS conversion_rate
FROM users u
LEFT JOIN first_pay f ON f.user_id = u.user_id
GROUP BY signup_month
ORDER BY signup_month;

함정

INNER JOIN 을 쓰면 결제 없는 유저가 사라져 전환율이 부풀려진다. 이 문제의 핵심 함정.
분자를 전체 결제자로 쓰기 쉽다. 30일 조건이 붙은 쪽이어야 한다.
×100 누락. *1.0 만으로는 비율(0.23)이지 퍼센트가 아니다.
f.fp IS NOT NULL 은 안 써도 된다. NULL 과의 비교는 NULL 이라 CASE 가 거짓으로 떨어진다.

이렇게 변형된다

“가입 후 7일 이내 재방문율”, “첫 구매까지 N일 이내 비율” — 날짜 기준만 바뀐다.