[과제] 퍼널 쿼리(피벗테이블 적용) 작성

# base 쿼리 작성 전 생각 흐름


----------------------------------------------------
## 1. 테이블 내 데이터 구조 파악

-- select
--   *
-- from advanced.app_logs
-- where
--   event_date = '2022-08-01'

-- 목적에 맞는 쿼리를 기존 데이터로 구현할 수 있는지 여부 확인
-- 	연습문제 결과물 컬럼인 'event_name_with_screen', 'step_number', 'cnt'는 기존 컬럼에 없음.
-- 		기존 컬럼에 없기 때문에 해당 컬럼을 어떻게 구현해내야 할 지 생각해보기



## 2. event_name_with_screen 컬럼 구현 순서 및 방법

-- event_name_with_screen는 다음과 같은 value를 가짐
-- 	1. screen_view-welcome
-- 	2. screen_view-home
-- 	3. screen_view-food_vategory
-- 	4. screen_view-restaurant
-- 	5. screen_view-cart
-- 	6. click_payment-cart
-- 		위 6가지 value가 기존 컬럼에 있는지 확인해보기
-- 				있는 경우
-- 					기존 컬럼 활용
-- 				없는 경우
-- 					concat 등으로 새롭게 구현
-- 						연습문제에서는 event_name과 event_params.key에 있는 value를 concat 했음.
-- 						event_params.key는 데이터타입이 struct이기 때문에 unnest 필요.

-- select
--   event_name,
--   unnest_event_params
-- from advanced.app_logs
-- cross join unnest(event_params) as unnest_event_params
-- where
--   event_date = '2022-08-01'


-- unnest 결과물에 피봇테이블 적용하기
-- 	피봇테이블로 만들면 직관적으로 event_name과 concat 하기가 용이해서.
-- select
--     event_name,
--     event_date,
--     event_timestamp,
--     user_pseudo_id,
--     max(if(unnest_event_params.key = 'firebase_screen', unnest_event_params.value.string_value, null)) as firebase_screen,
--     max(if(unnest_event_params.key = 'food_id', unnest_event_params.value.int_value, null)) as food_id,
--     max(if(unnest_event_params.key = 'session_id', unnest_event_params.value.string_value, null)) as session_id
-- from advanced.app_logs
-- cross join unnest(event_params) as unnest_event_params
-- where event_date between '2022-08-01' and '2022-08-18'
-- group by 1, 2, 3, 4

----------------------------------------------------



with base as (
  select
    event_name,
    event_date,
    event_timestamp,
    user_pseudo_id,
    max(if(unnest_event_params.key = 'firebase_screen', unnest_event_params.value.string_value, null)) as firebase_screen,
    max(if(unnest_event_params.key = 'food_id', unnest_event_params.value.int_value, null)) as food_id,
    max(if(unnest_event_params.key = 'session_id', unnest_event_params.value.string_value, null)) as session_id
  from advanced.app_logs
  cross join unnest(event_params) as unnest_event_params
  where event_date between '2022-08-01' and '2022-08-18'
  group by 1, 2, 3, 4
), base2 as (
  select
    *,
    concat(event_name, '-', firebase_screen) as event_name_with_screen
  from base
)


## 일자별 퍼널 쿼리
, base3 as (select 
  event_date,
  event_name_with_screen,
  # 2개 이상 조건을 한 개 컬럼에 적용해야 하기 때문에 case when 사용
  case
    when event_name_with_screen = 'screen_view-welcome' then 1
    when event_name_with_screen = 'screen_view-home' then 2
    when event_name_with_screen = 'screen_view-food_category' then 3
    when event_name_with_screen = 'screen_view-restaurant' then 4
    when event_name_with_screen = 'screen_view-cart' then 5
    when event_name_with_screen = 'click_payment-cart' then 6
  else null
  end as step_number,
  # 중복되지 않은 사용자수(비로그인 이용자까지)를 파악하기 위해서 count(distinct 컬럼명) 사용
  count(distinct user_pseudo_id) as cnt
from base2
group by all
# null 값 제외하기 위해서 그룹에 필터링 거는 having 사용
having
  step_number is not null
# 1일부터 보고 싶기 때문에 오름차순(asc) 적용
order by 
  1 asc)


## 일자별 쿼리 (피벗테이블 적용)
select
  event_date,
  max(if(event_name_with_screen = 'screen_view-welcome', cnt, 0)) as screen_view_welcome,
  max(if(event_name_with_screen = 'screen_view-home', cnt, 0)) as screen_view_home,
  max(if(event_name_with_screen = 'screen_view-food_category', cnt, 0)) as screen_view_food_category,
  max(if(event_name_with_screen = 'screen_view-restaurant', cnt, 0)) as screen_view_restaurant,
from base3
group by all
order by
  1 asc