3-7 Weekly, Monthly Retention 쿼리 작성

# 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_counts 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
)

SELECT
  *,
  ROUND(SAFE_DIVIDE(user_cnt, first_week_user_cnt),3) AS retention_rate
FROM(
  SELECT
    diff_of_week,
    user_cnt,
    FIRST_VALUE(user_cnt) OVER(ORDER BY diff_of_week) AS first_week_user_cnt
  FROM user_counts
)

# Monthly 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_month_and_diff AS (

  SELECT
    *,
    DATE_DIFF(event_month, first_month, MONTH) AS diff_of_month
  FROM (
    SELECT
      DISTINCT
        user_pseudo_id,
        DATE_TRUNC(MIN(event_date) OVER(PARTITION BY user_pseudo_id), MONTH) AS first_month,
        DATE_TRUNC(event_date, MONTH) AS event_month
    FROM base
  )
), user_counts AS (
  SELECT
    diff_of_month,
    COUNT(DISTINCT user_pseudo_id) AS user_cnt
  FROM first_month_and_diff
  GROUP BY diff_of_month
  ORDER BY diff_of_month
)

SELECT
  *,
  ROUND(SAFE_DIVIDE(user_cnt, first_month_user_cnt),3) AS retention_rate
FROM(
  SELECT
    diff_of_month,
    user_cnt,
    FIRST_VALUE(user_cnt) OVER(ORDER BY diff_of_month) AS first_month_user_cnt
  FROM user_counts
)