Weekly Retention 구하기 완성하였습니다.

WITH base AS(
  SELECT
    DISTINCT
      user_id,
      user_pseudo_id,
      event_name,
      DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date,
      DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime
  FROM advanced.app_logs
  WHERE event_date BETWEEN "2022-08-01" AND "2022-11-03"
), first_week_and_diff AS(
  SELECT
    *,
    DATE_DIFF(event_week, first_week, WEEK) AS diff_of_week
  FROM (
    SELECT
      DISTINCT
      user_pseudo_id,
      DATE_TRUNC(MIN(event_date) OVER(PARTITION BY user_pseudo_id), WEEK(MONDAY)) AS first_week,
      DATE_TRUNC(event_date, WEEK(MONDAY)) AS event_week
    FROM base
  )
), user_count AS(
  SELECT 
    diff_of_week,
    COUNT(DISTINCT user_pseudo_id) AS user_cnt
  FROM first_week_and_diff
  GROUP BY diff_of_week
  ORDER BY diff_of_week
), division_before AS(
  SELECT 
    *,
    FIRST_VALUE(user_cnt) OVER(ORDER BY diff_of_week) AS first
  FROM user_count
)

SELECT 
  *, 
  ROUND(SAFE_DIVIDE(user_cnt, first),2) AS retention_rate
FROM division_before

안녕하세요, 인프런 AI 인턴입니다.

강효주님, 작성하신 리텐션 SQL 쿼리에 대해서 검토해 보겠습니다. 현재 쿼리는 주간 리텐션(Weekly Retention)을 계산하기 위한 쿼리로, WITH 절을 통해 여러 단계의 중간 테이블을 생성하고 최종적으로 리텐션 비율을 계산하고 있습니다. 몇 가지 주의해야 할 요소와 설명을 덧붙이면 유용할 것 같아 관련 자료를 준비했습니다.

쿼리 구조 설명

기본 데이터 추출 (base CTE):
- 고유한 user_pseudo_id별로 이벤트 날짜 및 시간을 가져옵니다.
- event_date 필드는 한국 시간대에 맞춰 변환되어 있습니다.
첫 번째 주와 주차 간 차이 계산 (first_week_and_diff CTE):
- 각 사용자별 첫 번째 활동 주(first_week)와 각 이벤트의 주(event_week)를 계산한 후, 이 둘의 차이를 계산하여 diff_of_week 컬럼에 저장합니다.
사용자 수 계산 (user_count CTE):
- 주차 차이(diff_of_week)를 기준으로 사용자 수를 집계합니다.
리텐션 비율 계산 준비 (division_before CTE):
- 각 주차의 사용자 수와 첫 주의 사용자 수(first)를 준비합니다.
최종 리텐션 비율 계산:
- SAFE_DIVIDE 함수로 각 주차의 사용자 수를 첫 주의 사용자 수로 나누어 리텐션 비율을 계산합니다.

인프런 커뮤니티 질문&답변

쿼리 구조 설명

관련 질문 및 해결 링크