3-7 리텐션 SQL 쿼리 작성하기 연습 (Weekly, Monthly)

리텐션 SQL 쿼리 작성하기, Weekly와 Monthly 쿼리를 작성한 부분을 게시판에 올립니다.

-- Weekly 리텐션 작성하기. 마지막 부분

   , analysis AS
    (SELECT diff_of_week,
            user_cnt,
            FIRST_VALUE(user_cnt) OVER (ORDER BY diff_of_week) AS first_user_cnt
     FROM (SELECT diff_of_week,
                  COUNT(DISTINCT user_pseudo_id) AS user_cnt
           FROM first_date_and_diff
           GROUP BY 1))
SELECT diff_of_week,
       user_cnt,
       ROUND(SAFE_DIVIDE(user_cnt, first_user_cnt), 3) AS retention_week_rate
FROM analysis
ORDER BY 1;

-- 월별 리텐션 계산

WITH base AS (
    SELECT
        DISTINCT
            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_date_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
         )
), analysis AS (
    SELECT
        diff_of_month,
        user_cnt,
        FIRST_VALUE(user_cnt) OVER(ORDER BY diff_of_month) AS first_user_cnt
    FROM (
        SELECT
            diff_of_month,
            COUNT(DISTINCT user_pseudo_id) AS user_cnt
        FROM first_date_and_diff
        GROUP BY 1
         )
)
SELECT
    diff_of_month,
    user_cnt,
    ROUND(SAFE_DIVIDE(user_cnt, first_user_cnt), 3) AS retention_month_rate
FROM analysis

   , analysis AS
    (SELECT diff_of_week,
            user_cnt,
            FIRST_VALUE(user_cnt) OVER (ORDER BY diff_of_week) AS first_user_cnt
     FROM (SELECT diff_of_week,
                  COUNT(DISTINCT user_pseudo_id) AS user_cnt
           FROM first_date_and_diff
           GROUP BY 1))
SELECT diff_of_week,

, analysis AS (
    SELECT 
      diff_of_week,
      user_cnt,
      FIRST_VALUE(user_cnt) OVER (ORDER BY diff_of_week) AS first_user_cnt
    FROM (
      SELECT 
        diff_of_week,
        COUNT(DISTINCT user_pseudo_id) AS user_cnt
      FROM first_date_and_diff
      GROUP BY 1
    )
)

SELECT 
  diff_of_week,
  user_cnt,
  ROUND(SAFE_DIVIDE(user_cnt, first_user_cnt), 3) AS retention_week_rate
FROM analysis
ORDER BY 1;