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

안녕하세요,

강의 수강하면서 Weekly, Monthly Retention 쿼리를 작성해보았습니다!

그리고 질문이 하나 있는데,

첨부해드린 코드는 현재 첨부해드린 사진처럼 오류가 발생합니다. 그런데 Weekly Retention 쿼리를 전부 주석처리하면 Monthly Retention 쿼리는 정상 작동합니다.

반대로 Monthly Retention 쿼리를 주석처리하면 Weekly Retention 쿼리는 정상 작동합니다.

이 현상의 원리에 대한 감이 잡히지 않아서 질문드립니다!

그 외에 쿼리 관련해서 고칠 점이 있다면 편하게 말씀해주시면 감사하겠습니다!

WITH base AS (
  SELECT
    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_date, first_date, DAY) diff_of_day
    DATE_DIFF(event_week, first_week, WEEK) diff_of_week
  FROM (
    SELECT
      DISTINCT -- 일자별로 중복 제거
        user_pseudo_id,
        -- DATE_TRUNC
        DATE_TRUNC(MIN(event_date) OVER (PARTITION BY user_pseudo_id), WEEK(MONDAY)) AS first_week,
        MIN(event_date) OVER (PARTITION BY user_pseudo_id) AS first_date,
        DATE_TRUNC(event_date, WEEK(MONDAY)) AS event_week,
        event_date
    FROM base
  )
), first_month_and_diff AS (
  SELECT
    *,
    -- DATE_DIFF(event_date, first_date, DAY) diff_of_day
    DATE_DIFF(event_month, first_month, MONTH) AS diff_of_month
  FROM (
    SELECT
      DISTINCT
        user_pseudo_id,
        --DATE_TRUNC
        DATE_TRUNC(MIN(event_date) OVER (PARTITION BY user_pseudo_id), MONTH) AS first_month,
        DATE_TRUNC(event_date, MONTH) AS event_month,
        MIN(event_date) OVER (PARTITION BY user_pseudo_id) AS first_date,
        event_date
    FROM base
  )
)

-- Weekly Retention

SELECT
  *,
  ROUND(SAFE_DIVIDE(user_cnt, first_cnt), 3) AS week_retention_rate
FROM (
  SELECT
    *,
    FIRST_VALUE(user_cnt) OVER (ORDER BY diff_of_week) AS first_cnt
  FROM (
    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;


-- 추가. Monthly Retention

SELECT
  *,
  ROUND(SAFE_DIVIDE(user_cnt, first_cnt), 3) AS month_retention_rate
FROM (
  SELECT
    *,
    FIRST_VALUE(user_cnt) OVER (ORDER BY diff_of_month) AS first_cnt
  FROM (
    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;