바짝 스터디 3주차 과제 제출

연습문제 1번

select
*,
TRUNC(user_count / first_count, 3) AS ratio
from
(
  select
  *,
  first_value(user_count) over(order by week_diff asc) as first_count
  from
  (
    select
    week_diff,
    count(*) as user_count
    from
    (
      select
      *,
      date_diff(datetime_week, first_week, week) as week_diff
      from
      (
        select
        distinct
          event_date,
          user_pseudo_id,
          platform,
          datetime_week,
          -- event_name,
          first_value(datetime_week) over (partition by user_pseudo_id order by datetime_week asc) as first_week
        from
        (
          select 
          * except(firebase_screen,event_name),
          DATETIME_TRUNC(CAST(event_date AS DATE), WEEK) AS datetime_week,
          concat(firebase_screen,'_',event_name) as event_name 
          from
          (
            select 
            * except(event_param),
            max(if(event_param.key = 'firebase_screen',event_param.value.string_value , null)) as firebase_screen  
            from
            (
              select 
              event_date, event_timestamp, user_pseudo_id, platform, event_name, event_param 
              from `advanced.app_logs` 
              cross join unnest(event_params) as event_param 
              where event_date >= '2022-08-01' and event_date <= '2022-08-30'
            )
            group by all
          )
        )
      )
    )
    group by week_diff
  )
)
order by week_diff
;

연습문제2

• 현재 시간에서 마지막 활동이 2주 이상 경과면 이탈

• 마지막 활동이 현재 시간에서 1주 이내 인데 이전 활동과 갭이 2주이상이면 복귀

• 마지막 action이 현재시간에서 1주내이고 이전 액션이 없다면 new

• 나머진 active

select
user_pseudo_id,
case
  when DATETIME_DIFF(now, datetime_week, WEEK) >= 2 then 'dormant' 
  when DATETIME_DIFF(datetime_week, last_visit_week, WEEK) < 2 and DATETIME_DIFF(now, datetime_week, WEEK) < 2 then 'active'
  when DATETIME_DIFF(datetime_week, last_visit_week, WEEK) >= 2 and DATETIME_DIFF(now, datetime_week, WEEK) < 2 then 'resurrected'
  when DATETIME_DIFF(now, datetime_week, WEEK) < 2 and last_visit_week is null then 'new'
  else null
end as status
from
(
  select
  *,
  rank() over (partition by user_pseudo_id order by event_timestamp desc) as rank
  from
  (
    select
    distinct
      event_date,
      user_pseudo_id,
      platform,
      datetime_week,
      event_timestamp,
      -- event_name,
      -- first_value(datetime_week) over (partition by user_pseudo_id order by datetime_week asc) as first_week
      lag(datetime_week) over(partition by user_pseudo_id order by event_timestamp) as last_visit_week,
      CAST('2022-08-31 00:00:00' AS DATETIME) as now,
    from
    (
      select 
      * except(firebase_screen,event_name),
      DATETIME_TRUNC(CAST(event_date AS DATE), WEEK) AS datetime_week,
      concat(firebase_screen,'_',event_name) as event_name 
      from
      (
        select 
        * except(event_param),
        max(if(event_param.key = 'firebase_screen',event_param.value.string_value , null)) as firebase_screen  
        from
        (
          select 
          event_date, event_timestamp, user_pseudo_id, platform, event_name, event_param 
          from `advanced.app_logs` 
          cross join unnest(event_params) as event_param 
          where event_date >= '2022-08-01' and event_date <= '2022-08-30'
        )
        group by all
      )
    )
  )
  qualify rank=1
)
;

연습문제3, 연습문제4

• 쿼리는 동일하게 사용하고 event_name을 변경하면서 리텐션이 가장 높은 값을 추적

• click_restaurant의 리텐션이 가장 높아 이를 핵심 이벤트로 지정해야 하나 싶긴 한데 결과가 조금 이상해서 쿼리 점검 필요 해보임..

• 아래 쿼리에서 event_name만 click_payment로 변경하면 click_payment의 weekly retention

select
*,
TRUNC(user_count / first_count, 3) AS ratio
from
(
  select
  *,
  first_value(user_count) over(order by week_diff asc) as first_count
  from
  (
    select
    week_diff,
    count(*) as user_count
    from
    (
      select
      *,
      date_diff(datetime_week, first_week, week) as week_diff
      from
      (
        select
        distinct
          user_pseudo_id,
          datetime_week,
          -- event_name,
          first_value(datetime_week) over (partition by user_pseudo_id order by datetime_week asc) as first_week
        from
        (
          select 
          *,
          DATETIME_TRUNC(CAST(event_date AS DATE), WEEK) AS datetime_week
          from
          (
            select 
              event_date, event_timestamp, user_pseudo_id, platform, event_name 
              from `advanced.app_logs` 
              where event_date >= '2022-08-01' and event_date <= '2022-08-30' and event_name='click_restaurant'
          )
        )
      )
    )
    group by week_diff
  )
)
order by week_diff
;