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

1번

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_date, first_date, DAY) AS diff_of_day
    DATE_DIFF(event_week, first_week, WEEK) AS 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,
        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
)

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

2번

WITH base AS ( 
    SELECT
    DISTINCT
      user_id,
      user_pseudo_id,
      event_name,
      DATETIME(TIMESTAMP_MICROS(event_timestamp),'Asia/Seoul') AS event_datetime,
      DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp),'Asia/Seoul')) AS event_date,
    FROM advanced.app_logs)
, weekly_user_active AS (
    SELECT 
      user_pseudo_id,
      DATE_TRUNC(event_date, WEEK) AS event_week,
      MIN(DATE_TRUNC(event_date, WEEK)) OVER (PARTITION BY user_pseudo_id) AS first_active_week,
      LAG(DATE_TRUNC(event_date, WEEK)) OVER (PARTITION BY user_pseudo_id ORDER BY DATE_TRUNC(event_date, WEEK)) AS pre_active_week
      FROM base
)

, user_group AS (
  SELECT
    user_pseudo_id,
    event_week,
    DATE_DIFF(event_week, pre_active_week, WEEK(MONDAY)) AS diff_prior_week, 
    DATE_DIFF(event_week, first_active_week, WEEK(MONDAY)) AS diff_first_week,
    CASE 
      WHEN event_week = first_active_week THEN 'NEW' 
      WHEN DATE_DIFF(event_week, pre_active_week, WEEK) = 1 THEN 'Current' 
      WHEN DATE_DIFF(event_week, pre_active_week, WEEK) > 1 THEN 'Resurrected' 
      ELSE 'Dormant '
    END AS user_seg 
    FROM weekly_user_active
)

SELECT  
  event_week,
  user_seg,
  COUNT(DISTINCT user_pseudo_id) AS user_cnt,
FROM user_group
GROUP BY ALL
ORDER BY 1 ; 

4번

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"
    AND event_name = 'click_payment'  
), 

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
)

SELECT
  diff_of_week AS week,
  user_cnt,
  FIRST_VALUE(user_cnt) OVER(ORDER BY diff_of_week ASC) AS first_week_user_cnt,
  ROUND(SAFE_DIVIDE(user_cnt, FIRST_VALUE(user_cnt) OVER(ORDER BY diff_of_week ASC)) * 100, 2) AS retention_rate
FROM user_counts
ORDER BY diff_of_week