퍼널 단계별 유저 수와 단계 전환율

난이도 쉬움 · 권장 8분 · 출력 컬럼

app_open_users, item_view_users, cart_users, purchase_users, view_conversion_rate, cart_conversion_rate, purchase_conversion_rate

이 문제 직접 풀어보기 →

문제

user_event_logs 로 아래를 한 행으로 구하세요. app_open_users app_open 을 남긴 순 유저 수 item_view_users item_view 를 남긴 순 유저 수 cart_users add_to_cart 를 남긴 순 유저 수 purchase_users purchase 를 남긴 순 유저 수 view_conversion_rate item_view_users / app_open_users x 100 cart_conversion_rate cart_users / item_view_users x 100 purchase_conversion_rate purchase_users / cart_users x 100 · 유저 수는 전부 중복을 지운 수입니다 (한 유저가 같은 행동을 여러 번 남깁니다). · 단계 순서를 따지지 않습니다. 그 행동을 남겼으면 그 단계에 셉니다. · 전환율은 소수점 첫째 자리까지 반올림합니다. · 분모가 0 이면 전환율은 NULL 이어야 합니다. MySQL·SQLite 는 0 으로 나누면 그냥 NULL 을 주지만, 다른 DB 는 오류가 납니다. NULLIF(분모, 0) 를 쓰는 습관을 들이세요. · 결과는 한 행입니다.

정렬 (한 행)

쉽게 설명하면

들어온 사람 → 구경한 사람 → 장바구니에 담은 사람 → 산 사람. 각 칸에 몇 명.

단계	사람 수	앞 단계 대비
app_open	2,834
item_view	1,872	66.1%
add_to_cart	837	44.7%
purchase	320	38.2%

표 하나에 한 줄로 나와야 한다. 테이블은 한 번만 읽으면 된다.

이렇게 푼다

한 번 읽으면서 네 숫자를 동시에 만든다 — COUNT(DISTINCT CASE WHEN 행동='app_open' THEN user_id END) 식으로 네 개.
그 네 개를 서브쿼리로 감싸서 바깥에서 이름으로 다시 쓴다. 안 감싸면 전환율 식에 같은 걸 또 써야 해서 쿼리가 네 배로 길어진다.
ROUND(100.0 * 뒤 / NULLIF(앞, 0), 1).

이것만 피하면 된다

DISTINCT 를 빼면 안 된다. 한 사람이 app_open 을 네 번 남기면 4 로 세어진다. 묻는 건 사람 수다.
CASE 에 ELSE 0 을 쓰지 마라. 0 도 값이라서 '0 번 사람' 한 명으로 세어진다. ELSE 를 안 쓰면 NULL 이 되고, COUNT 는 NULL 을 안 센다.

자세히

묻는 것 · 한 테이블을 한 번만 읽어 네 숫자를 뽑고, 그 숫자끼리 나눈다. JOIN 도 GROUP BY 도 필요 없다. 여기서 시간을 쓰면 안 되는 문제다.

풀이 순서

COUNT(DISTINCT CASE WHEN ... THEN user_id END) 로 단계별 순 유저 수를 한 줄씩 만든다. 테이블을 네 번 읽을 필요가 없다.
그 네 숫자를 서브쿼리로 감싸서 바깥에서 이름으로 다시 쓴다. 그러지 않으면 전환율 식에 COUNT(DISTINCT CASE...) 를 또 써야 해서 길이가 네 배가 된다.
전환율은 100.0 * 을 앞에 붙이고 ROUND(..., 1).
분모에 NULLIF(분모, 0) 을 씌운다.

정답 (MySQL)

SELECT a AS app_open_users,
       v AS item_view_users,
       k AS cart_users,
       p AS purchase_users,
       ROUND(100.0 * v / NULLIF(a, 0), 1) AS view_conversion_rate,
       ROUND(100.0 * k / NULLIF(v, 0), 1) AS cart_conversion_rate,
       ROUND(100.0 * p / NULLIF(k, 0), 1) AS purchase_conversion_rate
FROM (
  SELECT COUNT(DISTINCT CASE WHEN event_name = 'app_open'    THEN user_id END) AS a,
         COUNT(DISTINCT CASE WHEN event_name = 'item_view'   THEN user_id END) AS v,
         COUNT(DISTINCT CASE WHEN event_name = 'add_to_cart' THEN user_id END) AS k,
         COUNT(DISTINCT CASE WHEN event_name = 'purchase'    THEN user_id END) AS p
  FROM user_event_logs
) c;

함정

DISTINCT 를 빼면 틀린다. 한 유저가 app_open 을 네 번 남기면 COUNT(CASE ...) 는 4 를 센다. 묻는 건 유저 수이지 이벤트 수가 아니다.
CASE 에 ELSE 를 쓰지 마라. ELSE 0 을 붙이면 0 도 값이라서 COUNT(DISTINCT ...) 가 0 을 하나의 유저로 센다. ELSE 를 안 쓰면 NULL 이 되고 COUNT 는 NULL 을 세지 않는다.
100.0 의 소수점이 핵심이다. 100 * v / a 는 정수 나눗셈이 되는 엔진에서 0 이 나온다. 토스 테스트돔에서 실제로 이걸로 틀린 적이 있다.
이 문제의 NULLIF 는 채점으로 확인할 수 없다. MySQL·SQLite 는 0 으로 나누면 오류 없이 NULL 을 준다. PostgreSQL·MS SQL 은 오류가 난다. 즉 여기서는 있어도 없어도 같은 답이 나오지만, 습관을 안 들이면 다른 엔진에서 터진다.
이 문제의 '퍼널' 은 진짜 퍼널이 아니다. 단계 순서를 안 따지므로, 장바구니를 안 거치고 구매한 유저가 있으면 뒤 단계가 앞 단계보다 많아져 전환율이 100% 를 넘을 수 있다. 면접에서 이걸 물으면 '순서를 강제하려면 단계별 첫 시각을 비교해야 한다' 고 답하면 된다.

이렇게 변형된다

“단계별 이탈률”은 100 − 전환율. “채널별 퍼널”이면 GROUP BY channel 만 추가되고 구조는 그대로다. 순서를 강제하는 진짜 퍼널은 단계별 MIN(created_at) 을 구해 MIN(cart) > MIN(view) 처럼 비교한다.