SQL Interview Prep · 강의

다단계 퍼널 만들기

순서가 정해진 각 단계에 도달한 사용자를 집계하고 단계별 전환율을 계산합니다.

다단계 퍼널 만들기은(는) CoddyKit의 무료 SQL Interview Prep 강의입니다. 이것은 4개 중 1번째 강의입니다. 아래에서 전체 강의를 무료로 읽을 수 있으며, 내장 코드 에디터와 24/7 AI 튜터와 함께 브라우저에서 직접 실습할 수 있습니다. 이 강의는 SQL Interview Prep 학습 경로의 일부이며, 진행 상황이 웹과 CoddyKit 앱에 동기화됩니다. SQL Interview Prep 강의에는 총 4개의 강의가 포함되어 있습니다.

퍼널 질문이 실제로 평가하는 것

면접관이 “가입 퍼널을 만들어 주세요”라고 말하면, 각 순서가 정해진 단계에 도달한 고유 사용자 수를 세고 단계 사이의 이탈을 표현할 수 있는지 평가하는 것입니다.

퍼널에는 visit -> signup -> activate -> purchase와 같은 단계가 있습니다. 일반적으로 결과는 단계마다 한 행을 두고 사용자 수와 전환율을 표시하는 형태입니다.

이벤트가 아니라 사용자를 세십시오. 한 사용자가 이벤트를 두 번 발생시켜도 한 명의 사용자입니다.
단계에는 순서가 있습니다. 3단계에 도달했다는 것은 1단계와 2단계를 통과했다는 뜻입니다.

제공되는 이벤트 테이블

거의 모든 퍼널 질문에서는 긴 형식의 단일 이벤트 테이블을 제공합니다. 다음과 같은 구조라고 생각해 보십시오.

user_id 작업을 수행한 사용자
event_name 'visit', 'signup', 'purchase'와 같은 이벤트 이름
event_time 시각 정보

각 작업마다 한 행이 있습니다. 여러분의 과제는 이를 단계별 집계 결과로 변환하는 것입니다. SQL을 작성하기 전에 면접관에게 정확한 이벤트 이름을 항상 확인하십시오.

CREATE TABLE events (
  user_id    INT,
  event_name VARCHAR(50),
  event_time TIMESTAMP
);

한 단계의 사용자 수 세기

간단한 것부터 시작해 보겠습니다. 하나의 단계에 도달한 고유 사용자는 몇 명일까요? 이벤트 이름을 조건으로 걸고 COUNT(DISTINCT user_id)를 사용하십시오.

이것이 모든 퍼널의 기본 구성 요소입니다. 한 단계를 깔끔하게 셀 수 있다면 모든 단계도 셀 수 있습니다.

SELECT COUNT(DISTINCT user_id) AS users_who_signed_up
FROM events
WHERE event_name = 'signup';

모든 단계에 대한 조건부 집계

면접에서 깔끔한 답은 조건부 집계를 사용하여 한 번의 처리로 모든 단계를 세는 것입니다. COUNT(DISTINCT ...) 안에 CASE를 넣으십시오.

각 단계마다 해당 단계와 일치하는 이벤트를 가진 고유 사용자를 셉니다. 한 번의 스캔으로 단계별 합계를 한 행에 얻을 수 있습니다.

SELECT
  COUNT(DISTINCT CASE WHEN event_name = 'visit'    THEN user_id END) AS step1_visit,
  COUNT(DISTINCT CASE WHEN event_name = 'signup'   THEN user_id END) AS step2_signup,
  COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_id END) AS step3_purchase
FROM events;

숨은 버그: 단계에는 순서가 없습니다

방금 본 쿼리에는 면접관들이 좋아하는 함정이 있습니다. 데이터에서 방문하거나 가입한 적이 전혀 없는 사용자라도 '구매' 이벤트를 발생시켰다면 구매 사용자로 집계합니다.

실제 퍼널에서는 각 이후 단계가 이전 단계의 부분집합이어야 합니다. 이벤트를 독립적으로 세면 3단계가 2단계보다 커질 수 있는데, 퍼널에서는 논리적으로 불가능한 결과입니다.

해결 방법은 각 사용자의 단계를 서로 연결하는 것입니다. 보통 먼저 사용자마다 한 행만 남도록 집계합니다.

플래그를 사용한 사용자별 한 행

견고한 방식은 이벤트 기록을 사용자별 한 행으로 축약하고, 각 단계를 수행한 적이 있는지를 0/1 참·거짓 플래그로 표시하는 것입니다. MAX(CASE ...)를 사용하면 긴 형식의 기록을 사용자별 가로 요약으로 바꿀 수 있습니다.

WITH user_steps AS (
  SELECT
    user_id,
    MAX(CASE WHEN event_name = 'visit'    THEN 1 ELSE 0 END) AS did_visit,
    MAX(CASE WHEN event_name = 'signup'   THEN 1 ELSE 0 END) AS did_signup,
    MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS did_purchase
  FROM events
  GROUP BY user_id
)
SELECT * FROM user_steps;

단계 순서 적용

이제 퍼널 규칙을 적용하십시오. 사용자가 그보다 앞선 모든 단계도 수행한 경우에만 N단계의 사용자로 집계합니다. '구매'에 도달한 것은 방문과 가입도 수행한 경우에만 의미가 있습니다.

선행 조건을 모두 충족하도록 플래그를 합산하여 각 단계가 바로 앞 단계의 진정한 부분집합이 되게 하십시오.

WITH user_steps AS (
  SELECT
    user_id,
    MAX(CASE WHEN event_name = 'visit'    THEN 1 ELSE 0 END) AS did_visit,
    MAX(CASE WHEN event_name = 'signup'   THEN 1 ELSE 0 END) AS did_signup,
    MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS did_purchase
  FROM events
  GROUP BY user_id
)
SELECT
  SUM(did_visit)                                       AS step1_visit,
  SUM(CASE WHEN did_visit = 1 AND did_signup = 1 THEN 1 ELSE 0 END)                       AS step2_signup,
  SUM(CASE WHEN did_visit = 1 AND did_signup = 1 AND did_purchase = 1 THEN 1 ELSE 0 END)  AS step3_purchase
FROM user_steps;

집계 결과를 정돈된 세로 형식으로 변환하기

면접관들은 가로로 넓은 한 행보다 단계별 한 행으로 이루어진 결과를 선호하는 경우가 많습니다. 작은 UNION ALL을 사용하여 가로 합계 결과를 세로 형식으로 피벗하고, 정렬을 위한 단계 번호를 붙이십시오.

이렇게 하면 다음 단계에서 전환율을 계산하고 차트로 표현하기가 훨씬 쉬워집니다.

WITH funnel AS (
  SELECT 1 AS step_no, 'visit'    AS step_name, 1000 AS users UNION ALL
  SELECT 2,           'signup',                   420  UNION ALL
  SELECT 3,           'purchase',                 95
)
SELECT step_no, step_name, users
FROM funnel
ORDER BY step_no;

단계 간 전환율

중요한 비율은 두 가지이며, 면접관은 어떤 비율을 의미하는지 물어볼 것입니다.

단계 전환율: 현재 단계의 사용자 수를 이전 단계의 사용자 수로 나눈 값입니다.
전체 전환율: 현재 단계의 사용자 수를 퍼널 최상위 단계의 사용자 수로 나눈 값입니다.

단계 간 전환율을 계산하려면 LAG로 이전 단계의 사용자 수를 가져오십시오. 정수 나눗셈이 발생하지 않도록 소수로 변환하십시오.

WITH funnel AS (
  SELECT 1 AS step_no, 'visit'    AS step_name, 1000 AS users UNION ALL
  SELECT 2,           'signup',                   420  UNION ALL
  SELECT 3,           'purchase',                 95
)
SELECT
  step_name,
  users,
  ROUND(100.0 * users / LAG(users) OVER (ORDER BY step_no), 1) AS step_conv_pct
FROM funnel
ORDER BY step_no;

최상위 단계부터 계산하는 전체 전환율

전체 전환율을 계산할 때는 각 단계의 사용자 수를 첫 단계의 사용자 수로 나눕니다. 순서가 정해진 퍼널에 FIRST_VALUE를 적용하면 모든 행에서 최상위 단계의 수를 고정해 사용할 수 있습니다.

100.0을 곱하여 정수 나눗셈을 방지했다는 점을 면접관에게 항상 언급하십시오.

WITH funnel AS (
  SELECT 1 AS step_no, 'visit'    AS step_name, 1000 AS users UNION ALL
  SELECT 2,           'signup',                   420  UNION ALL
  SELECT 3,           'purchase',                 95
)
SELECT
  step_name,
  users,
  ROUND(100.0 * users / FIRST_VALUE(users) OVER (ORDER BY step_no), 1) AS overall_pct
FROM funnel
ORDER BY step_no;

전체 퍼널 구성

다음은 면접관들이 원하는 처음부터 끝까지의 답입니다. 사용자별 플래그로 축약하고, 순서를 적용하고, 세로 형식으로 변환한 다음 두 전환율을 계산합니다. 각 CTE의 목적을 말하면서 풀이 과정을 소리 내어 설명하십시오.

이 구조는 확장하기도 쉽습니다. 플래그 하나와 UNION ALL 행 하나를 추가하면 단계를 늘릴 수 있습니다.

WITH user_steps AS (
  SELECT user_id,
    MAX(CASE WHEN event_name = 'visit'    THEN 1 ELSE 0 END) AS s1,
    MAX(CASE WHEN event_name = 'signup'   THEN 1 ELSE 0 END) AS s2,
    MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS s3
  FROM events GROUP BY user_id
),
totals AS (
  SELECT 1 AS step_no, 'visit'    AS step_name, SUM(s1) AS users FROM user_steps UNION ALL
  SELECT 2, 'signup',   SUM(CASE WHEN s1=1 AND s2=1 THEN 1 ELSE 0 END) FROM user_steps UNION ALL
  SELECT 3, 'purchase', SUM(CASE WHEN s1=1 AND s2=1 AND s3=1 THEN 1 ELSE 0 END) FROM user_steps
)
SELECT step_name, users,
  ROUND(100.0 * users / LAG(users) OVER (ORDER BY step_no), 1) AS step_pct
FROM totals ORDER BY step_no;

빠른 확인

면접관이 여러분의 퍼널에서 '구매' 사용자는 95명인데 '가입' 사용자는 80명뿐인 것을 발견했습니다. 가장 가능성 높은 원인은 무엇입니까?

복습: 다단계 퍼널

이제 면접에서 기대하는 방식으로 퍼널을 만들 수 있습니다.

원시 이벤트가 아니라 순서가 정해진 각 단계의 고유 사용자를 셉니다.
이벤트 기록을 MAX(CASE ...) 플래그를 사용해 사용자별 한 행으로 축약합니다.
각 단계가 이전 단계의 부분집합이 되도록 순서를 적용합니다.
정수 나눗셈을 방지하면서 단계 간(LAG) 전환율과 전체(FIRST_VALUE) 전환율을 계산합니다.

다음에는 단계가 실제로 올바른 순서로, 그리고 정해진 시간 범위 안에서 발생했는지 확인하는 방법을 알아봅니다.

자주 묻는 질문

“다단계 퍼널 만들기” 강의는 무료인가요?

네 — “다단계 퍼널 만들기” 전체 내용을 이 웹사이트에서 무료로 읽을 수 있습니다. 인터랙티브하게 실습하려면(내장 코드 에디터와 24/7 AI 튜터), CoddyKit PRO로 업그레이드하면 SQL Interview Prep 강의 전체를 잠금 해제할 수 있습니다. SQL Interview Prep 강의에는 총 4개의 강의가 포함되어 있습니다.

“다단계 퍼널 만들기”에서 뭘 배우나요?

순서가 정해진 각 단계에 도달한 사용자를 집계하고 단계별 전환율을 계산합니다. 브라우저에서 직접 실행하는 실습 코드로 SQL Interview Prep을(를) 배우며, 24/7 AI 튜터가 강의를 진행하면서 질문에 답변해줍니다.

SQL Interview Prep을(를) 시작하는 데 경험이 필요한가요?

사전 경험은 필요하지 않습니다. CoddyKit의 SQL Interview Prep은(는) 초급자부터 고급 학습자까지를 위해 구성되어 있으므로, 여기서 시작하거나 처음부터 시작할 수 있으며 자신의 속도대로 진행할 수 있습니다. 이것은 4개 중 1번째 강의입니다.

“다단계 퍼널 만들기” 강의는 얼마나 걸리나요?

대부분의 CoddyKit 강의는 약 5~10분이 소요됩니다. 각 강의는 간결하고 인터랙티브하여 꾸준한 진행이 가능하며, 웹과 앱에서 중단한 부분부터 바로 시작할 수 있습니다.

이 SQL Interview Prep 강의에서 코드를 작성하고 실행할 수 있나요?

네. 모든 SQL Interview Prep 강의에는 내장 코드 에디터가 포함되어 있으므로, 브라우저에서 바로 실제 코드를 작성하고 실행한 후 즉시 AI 피드백을 받을 수 있습니다 — 로컬 설정이 필요 없습니다.

이 강의의 모든 강의

← SQL Interview Prep(으)로 돌아가기