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

문제 1) Weekly Retention을 구하는 쿼리를 바닥부터 스스로 작성해보세요

WITH base AS (
  SELECT
    DISTINCT
      user_pseudo_id,
      DATE_TRUNC(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul'), WEEK(MONDAY)) AS event_week,
      DATE_TRUNC(MIN(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) OVER(PARTITION BY user_pseudo_id), WEEK(MONDAY)) AS first_week
  FROM `advanced.app_logs`
),
first_week_diff AS (
  SELECT
    user_pseudo_id,
    DATE_DIFF(event_week, first_week, WEEK) AS diff_of_week
  FROM base
),
user_counts AS (
  SELECT
    diff_of_week,
    COUNT(DISTINCT user_pseudo_id) AS user_count
  FROM first_week_diff
  GROUP BY diff_of_week
)
SELECT
  diff_of_week,
  user_count,
  ROUND(SAFE_DIVIDE(user_count, FIRST_VALUE(user_count) OVER (ORDER BY diff_of_week ASC)), 2) AS retention_rate
FROM user_counts
ORDER BY diff_of_week;

문제 2) Retain User를 New + Current + Resurrected + Dormant User로 나누는 쿼리를
작성해보세요.
WITH event_data AS (
    SELECT DISTINCT 
        DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date,
        user_pseudo_id
    FROM advanced.app_logs
),

user_visits AS (
    SELECT 
        user_pseudo_id,
        MIN(event_date) AS first_visit,
        MAX(event_date) AS last_visit -- 최근 방믄
    FROM event_data
    GROUP BY user_pseudo_id
),

-- 최신일자 
current_date AS (
    SELECT MAX(event_date) AS max_date 
    FROM event_data
),

user_types AS (
    SELECT 
        u.user_pseudo_id,
        u.first_visit,
        u.last_visit,
        c.max_date as current_date,
        DATE_DIFF(c.max_date, u.last_visit, DAY) as days_since_last_visit,
        CASE 
            WHEN DATE_DIFF(c.max_date, u.last_visit, DAY) <= 14 
            THEN 'current' -- 최근 2주 내 방문
            
            WHEN DATE_DIFF(c.max_date, u.last_visit, DAY) > 60 
            THEN 'dormant' -- 60일 이상 미접속
            
            WHEN DATE_DIFF(c.max_date, u.last_visit, DAY) > 30 
                 AND DATE_DIFF(c.max_date, u.last_visit, DAY) <= 44
                 AND EXISTS (
                     SELECT 1 
                     FROM event_data e 
                     WHERE e.user_pseudo_id = u.user_pseudo_id
                     AND DATE_DIFF(c.max_date, e.event_date, DAY) <= 14
                 )
            THEN 'resurrected' -- 30일 이상 비활동 후 최근 14일 내 재접속
            
            WHEN DATE_DIFF(c.max_date, u.last_visit, DAY) > 60 
            THEN 'inactive' -- 60일 이상 비활동
            
            ELSE 'none'
        END AS user_type
    FROM user_visits u
    CROSS JOIN current_date c
),

weekly_retention AS (
    SELECT 
        ut.user_type,
        DATE_DIFF(DATE_TRUNC(ed.event_date, WEEK), DATE_TRUNC(ut.first_visit, WEEK), WEEK) as week_number,
        COUNT(DISTINCT ed.user_pseudo_id) as user_count
    FROM event_data ed
    JOIN user_types ut ON ed.user_pseudo_id = ut.user_pseudo_id
    GROUP BY 1, 2
),

retention_rates AS (
    SELECT 
        user_type,
        week_number,
        user_count,
        FIRST_VALUE(user_count) OVER (
            PARTITION BY user_type 
            ORDER BY week_number
        ) as initial_users
    FROM weekly_retention
)

SELECT 
    user_type,
    week_number,
    user_count as active_users,
    initial_users as cohort_size,
    ROUND(user_count * 100.0 / initial_users, 2) as retention_rate
FROM retention_rates
ORDER BY user_type, week_number;

문제 3) 주어진 데이터에서 어떤 사람들이 리텐션이 그나마 높을까요? 찾아보세요

- current의 경우 15주까지는 리텐션 유지 및 증가하며 큰 변동 없다가 그 후부터 지속적으로 하락하였다.
- current는 그래도 리텐션율이 9.X로 시작했으나 dormant는 7.X로 시작하였고 더 빠르게 유입율이 감소하며 리텐션이 좋지 않았다.

문제 4) Core Event를 “click_payment”라고 설정하고 Weekly Retention을 구해주세요
WITH base AS (
  SELECT 
    user_pseudo_id,
    DATE_TRUNC(DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')), WEEK(MONDAY)) as week
  FROM advanced.app_logs 
  WHERE event_name = "click_payment"
),
cal_week AS (
  SELECT 
    user_pseudo_id,
    week as visit_week,
    MIN(week) OVER (PARTITION BY user_pseudo_id) as first_week
  FROM base
),
weekly_retention AS (
  SELECT 
    first_week 
    DATE_DIFF(visit_week, first_week, WEEK) as week_number,
    COUNT(DISTINCT user_pseudo_id) as user_count
  FROM cal_week
  GROUP BY first_week, DATE_DIFF(visit_week, first_week, WEEK)
),
cohort_sizes AS (
  SELECT 
    first_week,
    MAX(CASE WHEN week_number = 0 THEN user_count END) as initial_users
  FROM weekly_retention
  GROUP BY cohort_week
)
SELECT 
  wr.first_week,
  cs.initial_users as total_users,
  wr.week_number,
  wr.user_count as active_users,
  ROUND(wr.user_count * 100.0 / cs.initial_users, 2) as retention_rate
FROM weekly_retention wr
JOIN cohort_sizes cs ON wr.cohort_week = cs.cohort_week
WHERE wr.week_number >= 0
ORDER BY wr.cohort_week, wr.week_number;