0Pricing
SQL Interview Prep · 강의

이탈 및 복귀 쿼리

이탈한 사용자와 일정 기간이 지난 뒤 돌아온 사용자를 식별합니다.

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

잔존율의 반대편

잔존율이 남아 있는 사용자를 측정한다면, 이탈은 떠난 사용자를 측정하고 재활성화는 돌아온 사용자를 측정합니다. 면접관은 이 지표들을 잔존율과 함께 묻습니다. 활동의 부재를 추론할 수 있는지 보여 주기 때문이며, 이는 활동의 존재를 세는 것보다 어렵습니다.

반복해서 등장하는 핵심 함정은 존재하지 않는 행을 필터링할 수 없다는 점입니다. 이탈 쿼리는 근본적으로 사용자의 마지막 활동과 현재 시점(또는 다음 활동) 사이의 공백을 찾는 문제입니다.

이탈을 정확하게 정의하기

기간이 없으면 "이탈"은 의미가 없습니다. 흔히 사용하는 정의는 사용자가 최근 30일 동안 활동하지 않았다면 이탈한 사용자로 보는 것입니다. 30일의 비활동 임계값은 명확히 정해야 하는 비즈니스 선택입니다.

구독 제품에서는 이탈이 취소되거나 만료된 구독을 의미할 수도 있습니다. 이는 활동 공백이 아니라 상태 변경입니다. SQL을 작성하기 전에 어떤 모델을 적용할지 명확히 하시기 바랍니다.

사용자별 마지막 활동

활동 공백 기반 이탈 분석의 토대는 각 사용자의 가장 최근 이벤트입니다. 사용자별로 GROUP하고 이벤트 날짜의 MAX를 구합니다.

이 하나의 값을 오늘 날짜와 비교하면 사용자가 얼마나 오래 침묵했는지 알 수 있습니다. 이후의 모든 작업은 이 마지막 확인 날짜와의 비교입니다.

SELECT
  user_id,
  MAX(event_at::date) AS last_active
FROM events
GROUP BY user_id;

이탈 사용자 쿼리

마지막 활동이 30일보다 이전이면 사용자는 이탈한 것입니다. last_active를 CURRENT_DATE - 30과 비교합니다. 가장 최근 이벤트가 그 기준일보다 이전인 사용자는 활동을 멈춘 상태입니다.

실제 작업은 집계 이후에 이루어진다는 점에 유의하시기 바랍니다. 사용자별 한 행으로 줄인 다음 공백을 검사합니다. 원시 이벤트를 날짜로 필터링하면 해당 기간에 활동하지 않은 사용자만 알 수 있을 뿐, 전체적으로 이탈한 사용자는 알 수 없습니다.

WITH last_seen AS (
  SELECT user_id, MAX(event_at::date) AS last_active
  FROM events
  GROUP BY user_id
)
SELECT user_id, last_active
FROM last_seen
WHERE last_active < CURRENT_DATE - INTERVAL '30 days';

이탈률 계산하기

이탈률은 관련 기준 집합에서 이탈한 사용자가 차지하는 비율이며, 보통 기간의 시작 시점에 활동한 사용자를 기준으로 합니다. 조건부 집계로 이탈 사용자와 전체 사용자를 한 번에 센 다음, 100.0과 NULLIF를 사용하여 신중하게 나눕니다.

면접에서는 분모를 명확히 말씀하시기 바랍니다. 전체 기간의 사용자 대비 이탈률과 이전에 활동한 사용자 대비 이탈률은 서로 다른 지표입니다.

WITH last_seen AS (
  SELECT user_id, MAX(event_at::date) AS last_active
  FROM events GROUP BY user_id
)
SELECT
  COUNT(*) FILTER (
    WHERE last_active < CURRENT_DATE - INTERVAL '30 days'
  ) AS churned,
  COUNT(*) AS total_users,
  ROUND(100.0 * COUNT(*) FILTER (
    WHERE last_active < CURRENT_DATE - INTERVAL '30 days')
    / NULLIF(COUNT(*), 0), 1) AS churn_pct
FROM last_seen;

집합 논리를 이용한 기간별 이탈

다른 관점으로는 "지난달에는 활동했지만 이번 달에는 활동하지 않은 사용자는 누구인가?"가 있습니다. 이는 집합 차이입니다. 지난달 활동 사용자 집합과 이번 달 활동 사용자 집합을 만든 다음, 첫 번째 집합에는 있고 두 번째 집합에는 없는 구성원을 찾습니다.

EXCEPT, LEFT JOIN / IS NULL 안티 조인 또는 NOT EXISTS로 표현할 수 있습니다. 안티 조인이 가장 이식성이 높고 면접관이 가장 자주 보고 싶어 하는 방식입니다.

WITH last_month AS (
  SELECT DISTINCT user_id FROM events
  WHERE event_at >= DATE '2024-04-01' AND event_at < DATE '2024-05-01'
),
this_month AS (
  SELECT DISTINCT user_id FROM events
  WHERE event_at >= DATE '2024-05-01' AND event_at < DATE '2024-06-01'
)
SELECT user_id FROM last_month
EXCEPT
SELECT user_id FROM this_month;

안티 조인 형식

이번 기간 이탈 쿼리를 안티 조인으로 작성한 예입니다. 이번 달의 활성 사용자를 지난달의 활성 사용자에 LEFT JOIN한 다음, 일치하는 행이 NULL인 행만 남깁니다. 즉, 지난달에는 있었지만 이번 달에는 없는 사용자, 곧 이탈 사용자입니다.

NOT EXISTS를 사용해도 똑같이 올바른 답을 얻을 수 있으며 NULL도 안전하게 처리합니다. 내부 집합에 NULL이 포함될 수 있다면 NOT IN은 위험할 수 있다는 점도 언급하십시오. 이는 고전적인 함정입니다.

SELECT lm.user_id
FROM last_month lm
LEFT JOIN this_month tm ON tm.user_id = lm.user_id
WHERE tm.user_id IS NULL;

재활성화 정의

재활성화(reactivation이라고도 함)란 이탈한 사용자가 다시 활성 상태가 된 경우입니다. 대표적인 특징은 타임라인의 공백입니다. 활성 상태였다가 이탈 임계값보다 긴 침묵 기간이 이어지고, 다시 활성 상태가 됩니다.

따라서 이번 달의 재활성화 사용자는 현재 활성 상태이고 지난 기간에는 비활성 상태였지만, 그보다 이전 기간에는 활동 기록이 있었던 사용자입니다. 이탈의 반대되는 개념입니다.

LAG로 공백 감지

재활성화를 찾는 우아한 방법은 LAG 윈도 함수입니다. 사용자별 각 활동 기간에서 이전 활성 기간을 확인합니다. 두 기간 사이의 공백이 임계값을 초과하면 현재 기간은 재활성화입니다.

LAG를 사용하면 자체 조인이 필요 없고 쿼리도 깔끔하게 읽힙니다. 사용자별로 나눈 뒤 활성 기간을 기준으로 정렬하고, 각 기간을 바로 앞 기간과 비교하십시오.

WITH monthly AS (
  SELECT DISTINCT user_id,
    DATE_TRUNC('month', event_at) AS active_month
  FROM events
),
gaps AS (
  SELECT user_id, active_month,
    LAG(active_month) OVER (
      PARTITION BY user_id ORDER BY active_month
    ) AS prev_month
  FROM monthly
)
SELECT user_id, active_month AS resurrected_month
FROM gaps
WHERE prev_month IS NOT NULL
  AND active_month > prev_month + INTERVAL '1 month';

신규, 재활성화, 유지 사용자

완전한 활동 분류 쿼리는 이번 기간에 활성 상태인 모든 사용자를 다음 중 하나로 표시합니다. 신규(이전 활동 없음), 유지(지난 기간에도 활성 상태), 재활성화(이전 활동은 있지만 공백이 있음)입니다. LAG에서 얻은 prev_month가 세 가지 분류를 모두 결정합니다.

  • prev_month IS NULL → 신규
  • prev_month = active_month - 1 → 유지
  • 그 외(공백) → 재활성화

이렇게 분류 결과를 제시하면 강력하고 완전한 답이 됩니다.

SELECT user_id, active_month,
  CASE
    WHEN prev_month IS NULL THEN 'new'
    WHEN active_month = prev_month + INTERVAL '1 month' THEN 'retained'
    ELSE 'resurrected'
  END AS user_state
FROM gaps;

NULL이 있는 NOT IN의 함정

마지막으로 주의할 함정입니다. 이탈을 WHERE user_id NOT IN (SELECT user_id FROM this_month)으로 작성했는데 하위 쿼리가 NULL을 하나라도 반환하면, 전체 결과가 빈 결과가 됩니다. NOT IN은 NULL과 비교할 때 UNKNOWN으로 평가되기 때문입니다.

NULL이 있어도 올바르게 동작하는 NOT EXISTS 또는 LEFT JOIN / IS NULL 안티 조인을 사용하십시오. 이 차이를 별도의 질문을 받지 않아도 짚어 내면, 리텐션 면접에서 확실한 시니어 역량을 보여 줄 수 있습니다.

-- safe anti-join instead of NOT IN
SELECT lm.user_id
FROM last_month lm
WHERE NOT EXISTS (
  SELECT 1 FROM this_month tm
  WHERE tm.user_id = lm.user_id
);

빠른 확인

지난달에는 활성 상태였지만 이번 달에는 아닌 사용자를 찾으려고 합니다. 팀원이 WHERE user_id NOT IN (SELECT user_id FROM this_month)을 작성했는데, 분명히 이탈한 사용자가 있는데도 행을 하나도 반환하지 않습니다. 가장 안전한 해결 방법은 무엇입니까?

복습: 이탈과 재활성화

이탈과 재활성화의 핵심:

  • 비활성 임계값(예: 30일 동안 활동 없음) 또는 구독 상태 변경으로 이탈을 정의하십시오. 어느 기준인지 명확히 하십시오.
  • 각 사용자의 MAX(마지막 활동)을 계산한 뒤 CURRENT_DATE - threshold와 비교하십시오.
  • 기간별 이탈은 집합 차집합입니다. EXCEPT, NOT EXISTS 또는 LEFT JOIN / IS NULL 안티 조인을 사용하십시오.
  • 재활성화는 타임라인의 공백입니다. LAG로 이를 감지하여 사용자를 신규 / 유지 / 재활성화로 분류하십시오.
  • NULL이 발생할 수 있을 때는 NOT IN을 피하십시오. 결과가 조용히 빈 결과가 될 수 있습니다.

자주 묻는 질문

“이탈 및 복귀 쿼리” 강의는 무료인가요?

네 — “이탈 및 복귀 쿼리” 전체 내용을 이 웹사이트에서 무료로 읽을 수 있습니다. 인터랙티브하게 실습하려면(내장 코드 에디터와 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개 중 4번째 강의입니다.

“이탈 및 복귀 쿼리” 강의는 얼마나 걸리나요?

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

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

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

이 강의의 모든 강의

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