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

1번

with base as (
  select 
  user_pseudo_id,
  DATE_TRUNC(MIN(event_date) OVER (PARTITION BY user_pseudo_id), WEEK(MONDAY)) as event_first_week,   
  DATE_TRUNC(event_date,WEEK(monday)) as event_week, 
  DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')  as event_time
  from advanced.app_logs
) 

, diff_date_tbl as (
  select user_pseudo_id, 
  event_first_week, 
  event_week,
  DATE_DIFF(event_week, event_first_week, WEEK) as diff_date 
  from base 
  order by diff_date

) 
, user_counts as (
  select event_first_week, 
  diff_date, 
  count(distinct user_pseudo_id) as user_count
  from diff_date_tbl
  group by all 
  order by diff_date
) 

select * , 
ROUND(SAFE_DIVIDE(user_count, first_user_count), 2)  as retention_rate
from (
  select * , 
  FIRST_VALUE(user_count) OVER (PARTITION BY event_first_week ORDER BY diff_date) as first_user_count
  from user_counts 
) 

2번

with base as (
  select 
  user_pseudo_id,
  MIN(event_date) OVER (PARTITION BY user_pseudo_id) as event_first_day,   
  event_date, 
  DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')  as event_time
  from advanced.app_logs
) 

, period as (
  select user_pseudo_id, 
  event_date, 
  LAG(event_date) OVER (PARTITION BY user_pseudo_id ORDER BY event_date) as before_event_date
  from base 
  GROUP BY ALL 
  ORDER BY user_pseudo_id
) 

-- 평균주기 구하기 
SELECT AVG(diff_day) as avg_diff_period
FROM (
select *, 
DATE_DIFF(event_date, before_event_date, DAY ) as diff_day 
from period
) 

-- 접속 주기 분포도 확인 

SELECT diff_day, 
COUNT(distinct user_pseudo_id) as user_count
FROM (
  select *, 
  DATE_DIFF(event_date, before_event_date, DAY ) as diff_day 
  from period
)
GROUP BY ALL 
ORDER BY diff_day 

1) new 유저 =  (최근월 - 1)월 = 첫접속일자(event_first_month) 
2) current 유저 =  (최근월 -1)월 , (최근월 -2)월 모두 활동 o 
3) dormant 유저 =  (최근월 - 1)월 , (최근월 -2)월 모두 활동 x
4) resurrected  유저 = (최근월 -2)월 활동 x, (최근월 -1)월 에는 활동

with base as (
select user_pseudo_id, 
event_date, 
DATE_TRUNC(event_date, MONTH) as event_month
from advanced.app_logs
order by 1 
) 

, min_max_month as (
  SELECT *, 
  MIN(event_month) over (partition by user_pseudo_id order by event_month) as first_month,
  MAX(DATE_TRUNC(event_date, MONTH)) OVER () as lastest_month
  from base 
) 

, filter_month  as (
  SELECT user_pseudo_id, 
  first_month, 
  lastest_month, 
  COUNT(CASE WHEN event_month = DATE_SUB(lastest_month, INTERVAL 1 MONTH) THEN 1 END) AS previous_1_lastest_month,
  COUNT(CASE WHEN event_month = DATE_SUB(lastest_month, INTERVAL 2 MONTH) THEN 1  END)  AS previous_2_lastest_month  
  FROM min_max_month 
  group by ALL 
) 

, user_classification as (
  SELECT *, 
  CASE WHEN first_month = DATE_SUB(lastest_month, INTERVAL 1 MONTH ) THEN 'New'
       WHEN previous_1_lastest_month > 0 and previous_2_lastest_month >= 0 THEN 'Current'
       WHEN previous_1_lastest_month = 0 and previous_2_lastest_month = 0 THEN 'Dormant'
       WHEN previous_1_lastest_month = 0 and previous_2_lastest_month > 0 THEN 'Resurrected'
       ELSE 'Others' END AS user_category
  FROM filter_month 
) 

select user_category, 
count(distinct user_pseudo_id)
from user_classification 
group by all 

4번

WITH base AS (
SELECT
    user_pseudo_id
  , first_value(datetime(timestamp_micros(event_timestamp), 'Asia/Seoul')) over(partition by user_pseudo_id order by event_timestamp) as first_visit
  , last_value(datetime(timestamp_micros(event_timestamp), 'Asia/Seoul')) over(partition by user_pseudo_id order by event_timestamp) as last_visit
  , datetime(timestamp_micros(event_timestamp), 'Asia/Seoul') as event_datetime
  , date_trunc(datetime(timestamp_micros(event_timestamp), 'Asia/Seoul'), day) as event_date
  , date_trunc(datetime(timestamp_micros(event_timestamp), 'Asia/Seoul'), week(monday)) as event_week
  , date_trunc(datetime(timestamp_micros(event_timestamp), 'Asia/Seoul'), month) as event_month
  , event_name
FROM advanced.app_logs
), click_payment AS (
SELECT DISTINCT user_pseudo_id
              , min(event_week) over(partition by user_pseudo_id) as first_week
              , event_week as week_date
FROM base
WHERE event_name = 'click_payment'
), diff_week AS (
SELECT
  first_week, 
  date_diff(week_date, first_week, week) as week_diff,
  count(distinct user_pseudo_id) as users_cnt
FROM click_payment
GROUP BY ALL
)
SELECT first_week
    , week_diff
    , users_cnt
    , safe_divide(users_cnt, first_value(users_cnt) over(partition by first_week order by week_diff)) as retention_rate
FROM  diff_week
order by 1, 2
;