연습과제

Core Event를 "click_payment"라고 설정하고 Weekly Retention을 구해주세요.

SQL

WITH base AS(
  SELECT
    event_name,
    user_pseudo_id,
    DATE_TRUNC(DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul'), WEEK(MONDAY)) AS event_week,
  FROM advanced.app_logs
  WHERE
    event_name = 'click_payment'
), week_data AS(
  #first_week, week_diff 추출
  SELECT
    DISTINCT *,
    DATE_DIFF(event_week, first_week, WEEK(MONDAY)) AS week_diff
  FROM(
    SELECT
      *,
      MIN(event_week) OVER(PARTITION BY user_pseudo_id) AS first_week
    FROM base
  )
), cnt_data AS(
  #active_users, cohort_users 추출
  SELECT
    *,
    FIRST_VALUE(active_users) OVER(PARTITION BY first_week ORDER BY week_diff) AS cohort_users
  FROM(
    SELECT
      event_name,
      first_week,
      week_diff,
      COUNT(DISTINCT user_pseudo_id) AS active_users
    FROM week_data
    GROUP BY ALL
  )
)

#retention_rate 추출
SELECT
  *,
  ROUND(SAFE_DIVIDE(active_users, cohort_users), 3) AS retention_rate
FROM cnt_data
ORDER BY
  first_week, week_diff

https://docs.google.com/spreadsheets/d/1t_sZ2FbnTB_xWXYvMvh6dlnEfK1Al6oGUb0Pjn5MIkE/edit?usp=sharing