Weekly 및 Monthly Retention 과제 제출합니다.

--Weekly Retention

WITH base AS(
  SELECT
    user_pseudo_id,
    event_name,
    DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_date,
    DATE_TRUNC(DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul'), WEEK(MONDAY)) AS event_week,
  FROM advanced.app_logs
  WHERE
    event_date BETWEEN '2022-08-01' AND '2022-11-03'
), first_week_data AS(
#first_week 추출
SELECT
  DISTINCT
    user_pseudo_id,
    event_week,
    MIN(event_week) OVER(PARTITION BY user_pseudo_id) AS first_week,
FROM base
), week_diff_user_cnt_data AS(
  #week_diff 및 user_cnt 추출
  SELECT
    week_diff,
    COUNT(DISTINCT user_pseudo_id) AS user_cnt
  FROM(
    SELECT
      *,
      DATE_DIFF(event_week, first_week, WEEK(MONDAY)) AS week_diff
    FROM first_week_data
    )
  GROUP BY week_diff
)

#first_week_user_cnt 및 retention_ratio 추출
SELECT
  *,
  ROUND(SAFE_DIVIDE(user_cnt, first_week_user_cnt), 3) AS retention_rate
FROM(
  SELECT
    *,
    FIRST_VALUE(user_cnt) OVER(ORDER BY week_diff) AS first_week_user_cnt
  FROM week_diff_user_cnt_data
  )
ORDER BY week_diff

--Monthly Retention

WITH base AS(
  SELECT
    user_pseudo_id,
    event_name,
    DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_date,
    DATE_TRUNC(DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul'), MONTH) AS event_month,
  FROM advanced.app_logs
  WHERE
    event_date BETWEEN '2022-08-01' AND '2022-11-03'
), first_month_data AS(
#first_month 추출
SELECT
  DISTINCT
    user_pseudo_id,
    event_month,
    MIN(event_month) OVER(PARTITION BY user_pseudo_id) AS first_month,
FROM base
), month_diff_user_cnt_data AS(
  #month_diff 및 user_cnt 추출
  SELECT
    month_diff,
    COUNT(DISTINCT user_pseudo_id) AS user_cnt
  FROM(
    SELECT
      *,
      DATE_DIFF(event_month, first_month, MONTH) AS month_diff
    FROM first_month_data
    )
  GROUP BY month_diff
)

#first_month_user_cnt 및 retention_ratio 추출
SELECT
  *,
  ROUND(SAFE_DIVIDE(user_cnt, first_month_user_cnt), 3) AS retention_rate
FROM(
  SELECT
    *,
    FIRST_VALUE(user_cnt) OVER(ORDER BY month_diff) AS first_month_user_cnt
  FROM month_diff_user_cnt_data
  )
ORDER BY month_diff

더 보완할 점은 없는지 평가해 주시면 감사하겠습니다. 강의 항상 정말 잘 듣고있습니다.