0Pricing
SQL Interview Prep · 강의

리텐션 매트릭스 만들기

코호트와 기간 오프셋별 활성 사용자를 집계해 리텐션 테이블을 만듭니다.

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

유지율 매트릭스란 무엇인가

코호트를 정의한 다음 단계는 유명한 유지율 매트릭스입니다. 행은 코호트이고 열은 기간 오프셋(0개월, 1개월, 2개월 등)이며, 각 셀에는 해당 오프셋 시점에도 여전히 활성 상태인 코호트 사용자의 수가 들어갑니다.

면접관이 이 문제를 좋아하는 이유는 코호트 배정, 활동 데이터로의 재조인, 기간 차이 계산, 피벗을 모두 결합해야 하기 때문입니다. 제품 분석을 대표하는 가장 전형적인 쿼리입니다.

두 가지 입력

두 가지가 필요합니다. 이전 레슨에서 만든 각 사용자의 코호트 기간과, 사용자별 모든 활성 기간의 기록입니다. 활동 데이터는 같은 이벤트 테이블에서 가져와 기간 단위로 축약합니다.

따라서 쿼리는 다음과 같이 계획하십시오. 코호트 CTE를 만든 다음, 각 사용자가 어느 달에 활성 상태였는지 나열하는 활동 CTE를 만들고, 두 결과를 조인합니다.

WITH user_cohort AS (
  SELECT user_id,
    DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events
  GROUP BY user_id
)
SELECT * FROM user_cohort;

활성 기간 나열하기

활동 CTE는 "각 사용자가 어느 달에 활성 상태였는가?"라는 질문에 답합니다. 모든 이벤트를 월 단위로 자르고 DISTINCT 또는 GROUP BY로 중복을 제거해야 합니다. 그래야 3월에 40번 활동한 사용자도 3월 행 하나로 기록됩니다.

이 사용자별·월별 목록을 코호트와 조인하여 오프셋에 따른 잔존을 측정합니다.

WITH activity AS (
  SELECT DISTINCT
    user_id,
    DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT * FROM activity;

기간 오프셋 계산하기

매트릭스의 핵심은 기간 번호입니다. 특정 활동이 코호트 시작 후 몇 개월째에 발생했는지를 나타냅니다. 활성 월에서 코호트 월을 뺍니다.

Postgres에서는 두 날짜 사이의 전체 월 수를 세는 방식이 깔끔합니다. 이식 가능한 공식은 연도 차이에 12를 곱한 뒤 월 차이를 더하는 것입니다. 많은 데이터베이스 엔진에는 이를 위한 보조 함수도 있습니다. 오프셋 0은 코호트가 시작된 바로 그 달을 의미합니다.

-- months between two month-truncated dates (Postgres)
SELECT
  (EXTRACT(YEAR  FROM active_month) - EXTRACT(YEAR  FROM cohort_month)) * 12
+ (EXTRACT(MONTH FROM active_month) - EXTRACT(MONTH FROM cohort_month))
  AS period_number;

코호트를 활동에 결합하기

코호트 CTE를 user_id를 기준으로 활동 CTE에 JOIN합니다. 각 결과 행은 코호트 X에 속한 이 사용자가 오프셋 N에서 활동했다는 뜻입니다. (코호트, 오프셋)별 고유 사용자 수를 세면 행 형태의 행렬이 됩니다.

모든 코호트 구성원은 자신의 시작 월에 활동하므로 오프셋 0은 코호트 규모와 같아야 합니다. 이는 내장된 검증 수단입니다.

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events GROUP BY user_id
),
activity AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT c.cohort_month, a.active_month, c.user_id
FROM user_cohort c
JOIN activity a ON a.user_id = c.user_id;

세로형 잔존율 표

오프셋 계산을 추가하고 집계합니다. 이제 깔끔한 세로형 결과를 얻었습니다. 오프셋별 코호트마다 잔존 사용자 수가 담긴 한 행이 있습니다. 피벗은 꾸미기 작업에 불과하므로 많은 면접관이 이 결과를 그대로 인정합니다.

오프셋 표현식은 저장된 열이 아니라 계산되는 값이므로 SELECT와 GROUP BY 양쪽에 모두 나타난다는 점에 유의하시기 바랍니다.

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events GROUP BY user_id
),
activity AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT
  c.cohort_month,
  (EXTRACT(YEAR FROM a.active_month)-EXTRACT(YEAR FROM c.cohort_month))*12
  +(EXTRACT(MONTH FROM a.active_month)-EXTRACT(MONTH FROM c.cohort_month)) AS period_number,
  COUNT(DISTINCT c.user_id) AS retained_users
FROM user_cohort c
JOIN activity a ON a.user_id = c.user_id
GROUP BY c.cohort_month, period_number
ORDER BY c.cohort_month, period_number;

넓은 열로 피벗하기

고전적인 격자를 얻으려면 조건부 집계를 사용하여 오프셋을 열로 피벗합니다. 오프셋마다 CASE를 적용한 SUM을 사용하는 방식입니다. 이식성이 높은 이 패턴은 특수한 PIVOT 구문 없이 모든 SQL 방언에서 작동합니다.

각 CASE는 행의 기간 번호가 해당 열과 일치할 때 1을 내보내므로 SUM은 해당 오프셋의 잔존 사용자 수를 셉니다.

SELECT
  cohort_month,
  COUNT(DISTINCT CASE WHEN period_number = 0 THEN user_id END) AS m0,
  COUNT(DISTINCT CASE WHEN period_number = 1 THEN user_id END) AS m1,
  COUNT(DISTINCT CASE WHEN period_number = 2 THEN user_id END) AS m2,
  COUNT(DISTINCT CASE WHEN period_number = 3 THEN user_id END) AS m3
FROM retention_long
GROUP BY cohort_month
ORDER BY cohort_month;

개수에서 잔존율로

면접관은 보통 원시 개수가 아니라 백분율을 원합니다. 각 오프셋의 잔존 사용자 수를 코호트 규모(오프셋 0)로 나눕니다. 정수 나눗셈을 피하려면 값을 부동 소수점으로 CAST하거나 1.0을 곱하시기 바랍니다. 이는 여기서 가장 흔히 발생하는 조용한 오류입니다.

결과는 잔존율 곡선입니다. 0개월 차의 100%에서 시작하여 일정한 수준을 향해 감소합니다. 그 일정한 수준이 실제로 이해관계자들이 중요하게 보는 지표입니다.

SELECT
  cohort_month,
  period_number,
  retained_users,
  ROUND(
    100.0 * retained_users
    / MAX(retained_users) OVER (PARTITION BY cohort_month),
    1
  ) AS retention_pct
FROM retention_long
ORDER BY cohort_month, period_number;

정수 나눗셈의 함정

면접에서 반드시 나오는 함정이 있습니다. 대부분의 데이터베이스 엔진에서 120 / 500은 0.24가 아니라 0입니다. 두 피연산자가 모두 정수이기 때문입니다. 잔존율 백분율이 조용히 전부 0으로 계산됩니다.

한쪽을 숫자형으로 만들면 해결할 수 있습니다. 100.0을 곱하거나, 한 피연산자를 NUMERIC으로 CAST하거나, NULLIF(size, 0)으로 나누어 빈 코호트도 처리하시기 바랍니다. "NULLIF는 0으로 나누는 문제도 막습니다"라고 말하면 추가 점수를 얻을 수 있습니다.

SELECT
  retained_users,
  cohort_size,
  100.0 * retained_users / NULLIF(cohort_size, 0) AS pct
FROM retention_long;

누락된 오프셋을 0으로 채우기

코호트의 오프셋 2에서 잔존 사용자가 0명이면 JOIN 결과에 행이 생기지 않아 행렬에 빈칸이 남습니다. 명시적인 0을 표시하려면 (코호트, 오프셋) 조합의 전체 격자를 생성한 다음 집계 결과를 LEFT JOIN하시기 바랍니다.

코호트와 숫자/오프셋 목록을 CROSS JOIN하여 격자를 만든 다음, 누락된 개수를 0으로 대체합니다. 이 빈틈을 알아챘다는 점을 면접관은 높이 평가합니다.

WITH offsets AS (SELECT generate_series(0, 6) AS period_number),
cohorts AS (SELECT DISTINCT cohort_month FROM retention_long)
SELECT
  c.cohort_month, o.period_number,
  COALESCE(r.retained_users, 0) AS retained_users
FROM cohorts c
CROSS JOIN offsets o
LEFT JOIN retention_long r
  ON r.cohort_month = c.cohort_month
 AND r.period_number = o.period_number
ORDER BY c.cohort_month, o.period_number;

삼각형 구조와 최신성 편향

한 가지 더 이야기할 내용은 행렬이 삼각형 구조라는 점입니다. 지난달에 시작한 코호트는 아직 3개월 차 값이 있을 수 없으므로 뒤쪽 오프셋에는 기여하는 코호트가 더 적습니다.

따라서 코호트 전체에서 한 열의 평균을 비교하면 오래된 코호트 쪽으로 편향됩니다. 삼각형 구조를 있는 그대로 보여 주거나, 모든 코호트가 도달한 오프셋으로 비교를 제한하겠다고 말씀하시기 바랍니다. 이런 인식이 분석가와 쿼리 작성자를 구분합니다.

간단한 확인

잔존율 쿼리에서 잔존 사용자 수를 코호트 규모로 나누는데, 0개월 차를 제외한 모든 백분율이 0으로 출력됩니다. 가장 가능성 높은 원인은 무엇입니까?

복습: 잔존율 행렬

면접에서 잔존율 행렬을 만드는 방법은 다음과 같습니다.

  • 각 사용자에게 코호트 기간을 지정한 다음, 각 사용자의 중복 제거된 활동 기간을 나열합니다.
  • 두 목록을 결합하고 코호트와 활동 사이의 개월 수인 기간 오프셋을 계산합니다.
  • COUNT(DISTINCT user_id)로 세로형 결과를 집계하고, 격자가 필요하면 CASE로 피벗합니다.
  • 정수 나눗셈을 피하고 100.0과 NULLIF로 0으로 나누는 문제를 막으면서 개수를 신중하게 비율로 변환합니다.
  • 생성한 격자에 LEFT JOIN하여 0인 칸을 채우고, 행렬이 삼각형 구조라는 점을 기억합니다.

자주 묻는 질문

“리텐션 매트릭스 만들기” 강의는 무료인가요?

네 — “리텐션 매트릭스 만들기” 전체 내용을 이 웹사이트에서 무료로 읽을 수 있습니다. 인터랙티브하게 실습하려면(내장 코드 에디터와 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개 중 2번째 강의입니다.

“리텐션 매트릭스 만들기” 강의는 얼마나 걸리나요?

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

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

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

이 강의의 모든 강의

  1. 첫 행동으로 코호트 정의하기
  2. 리텐션 매트릭스 만들기
  3. N일차 및 롤링 리텐션
  4. 이탈 및 복귀 쿼리
← SQL Interview Prep(으)로 돌아가기