6주차 과제

- 1) Weekly Retention을 구하는 쿼리를 바닥부터 스스로 작성해보세요
with base as (
select
    distinct
    user_pseudo_id
    , event_name
    , date(datetime(timestamp_micros(event_timestamp),'Asia/Seoul')) AS event_date  
from advanced.app_logs
where event_date between '2022-08-01' and '2022-11-01'
)
, retention_base as (
select 
    distinct
    date_trunc(event_date, week(monday)) as event_week
    , min(date_trunc(event_date , week(monday)) ) over(partition by user_pseudo_id order by event_date)as first_week

    , user_pseudo_id
from base
)
, middle as (
select 
    event_week
    , first_week
    , date_diff(event_week, first_week, day) as diff
    , user_pseudo_id
from retention_base
)
, final as (
select 
    diff
    , count(distinct user_pseudo_id) as user_cnt
from middle
group by all
)
select 
    diff
    , first_value(user_cnt) over(order by diff asc) as first_visit_users
    , user_cnt
    , round((user_cnt/first_value(user_cnt) over(order by diff asc)), 2) 
from final




- 2) Retain User를 New + Current + Resurrected + Dormant User로 나누는 쿼리를 작성해보세요. 
--한달내 유저가 얼마나 많은 이벤트를 발생시키는지?
--지속적으로 사용한다의 정의를 이벤트를 고려할 것인지 아니면 방문만 볼것인지
신규 유저(New) : 제품을 처음 사용하는 유저 --각 달에 첫 로그를 남긴 유저
기존 유저(Current) : 제품을 지속적으로 사용하는 유저 --한달내에 
복귀 유저(Resurrected) : 과거에 사용 -> 비활성 -> 다시 제품을 사용한 유저 
휴면 유저(Dormant) : 일정 기간 제품을 사용하지 않은 비활성화 사용자 --해당 주 이외에 



- 3) 주어진 데이터에서 어떤 사람들이 리텐션이 그나마 높을까요? 찾아보세요
--함께 고민해보면 좋을 가이드라인
- 휴면 유저를 어떻게 복귀 유저로 부활시킬 수 있을까? 
- 어떻게 신규 유저를 계속 늘릴 수 있을까?
- 기존 유저가 감소하지 않으려면 어떻게 해야할까?


- 4) Core Event를 “click_payment”라고 설정하고 Weekly Retention을 구해주세요
with base as (
select
    distinct
    user_pseudo_id
    , event_name
    , date(datetime(timestamp_micros(event_timestamp),'Asia/Seoul')) AS event_date  
    , min(date_trunc(datetime(timestamp_micros(event_timestamp),'Asia/Seoul') , week(monday)) ) over(partition by user_pseudo_id order by event_week)as first_all_week
from advanced.app_logs
where event_date between '2022-08-01' and '2022-11-01'
)
, retention_base as (
select 
    distinct
    -- extract(event_date from week) as event_week
    , min(date_trunc(event_date , week(monday)) ) over(partition by user_pseudo_id order by event_week)as first_week
        -- , 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
    , user_pseudo_id
from base
where event_name = “click_payment”
)
, middle as (
select 
    event_week
    , first_week
    , date_diff(event_week, first_week, day) as diff
    , user_pseudo_id
from retention_base
)
, weeks as (
    select 
        distinct
            first_week
    from base

)
, final as (
select 
    diff
    , min(first_week) as first_Week
    , count(distinct user_pseudo_id) as user_cnt
from middle
group by all
)
select 
    diff
    , first_value(user_cnt) over(order by diff asc) as first_visit_users
    , user_cnt
    , round((user_cnt/first_value(user_cnt) over(order by diff asc)). , 2) --safe_divide() 사용하면 좋음
from final

1번 문제(15분)

• 주차별 각 카테고리별 평균 할인율이 가장 높았던 기간과 할인율을 구하는 쿼리를 작성해주세요

  • 단, 날짜 데이터를 YYYY-MM-DD 23:59:39 이런 형태로 변경해주세요

with raw as (select 
	a.user_id
	, a.item_id
	, a.actual_price 90
	, b.category
	, b.list_price 100
	, (b.list_price-a.actual_price)/ b.list_price as discount
	, a.transaction_date
from transaction_data a left join item_info b
	on a.item_id=b.item_id
)
select 
 FORMAT_TIMESTAMP('%Y-%m-%d 23:59:59', TIMESTAMP(DATE_TRUNC(DATE(transaction_date), WEEK(MONDAY)))) AS week_start_date, -- 주차의 월요일 날짜를 “YYYY-MM-DD 23:59:59” 형식으로 변환
 , category
 , avg(discount) as avg_discount
from raw
group by 1,2
order 3 desc
limit 1

2번 문제(10분)

• 2024년 1월에 가장 많은 매출을 기록한 카테고리를 구하는 쿼리를 작성해주세요

  with raw as (select 
  	a.user_id
  	, a.item_id
  	, a.actual_price 90
  	, b.category
  	, b.list_price 100
  	, (b.list_price-a.actual_price)/ b.list_price as discount
  	, a.transaction_date
  from transaction_data a left join item_info b
  	on a.item_id=b.item_id
  where extract(month from a.transaction_date) = 1
  	and extract(year from a.transaction_date) = 2024
  )
  select 
  	category
  	, sum(actual_price) as gmv
  from raw
  group by 1
  order gmv desc
  limit 1
  
  

3번 문제(10분)

• 유저별 총 구매 금액이 200만원 이상인 유저들이 가장 많이 구매한 카테고리를 찾는 쿼리를 작성해주세요

with raw as (select 
	a.user_id
	, a.item_id
	, a.actual_price
	, b.category
	, b.list_price
	, a.transaction_date
	, sum(a.actual_price) over(partition by user_id) as user_gmv
from transaction_data a left join item_info b
	on a.item_id=b.item_id
where 
)
select 
	category
	, count(item_id) as cnt_item
	, count(distinct item_id) as cnt_item
	, sum(actual_price) as gmv
from raw
where user_gmv
group by 1
order 2 desc --가장많이의 정의가 구매건수라면
order 3 desc --가장많이의 정의가 유니크한 상품수라면
order 4 desc --가장많이의 정의가 gmv 매출이라면
limit 1