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

WITH base as (
SELECT
  event_date,
  event_timestamp,
  event_name,
  user_id,
  user_pseudo_id,
  platform,
  MAX(IF(params.key='firebase_screen', params.value.string_value, NULL)) as firebase_screen,
  -- MAX(IF(params.key='food_id', params.value.int_value, NULL)) as food_id,
  MAX(IF(params.key='session_id', params.value.string_value, NULL)) as session_id,
FROM advanced.app_logs
CROSS JOIN UNNEST(event_params) as params
WHERE
  -- event_date = "2022-08-01" # 적은 데이터로 쿼리를 작성하기 위해 만든 조건
  event_date BETWEEN "2022-08-01" AND "2022-08-18"
GROUP BY ALL
), fileter_event_and_concat_event_and_screen AS(
-- event_name + screen 하면서(필요한 이벤트만 WHERE 조건에 걸어서 사용)
  SELECT
    * EXCEPT(event_name, firebase_screen, event_timestamp),
    CONCAT(event_name, "-", firebase_screen) AS event_name_with_screen,
    DATETIME(TIMESTAMP_MICROS(event_timestamp), "Asia/Seoul") AS event_datetime
  FROM base
  WHERE
    event_name IN("screen_view", "click_payment")
)
-- step_number + cnt 만들기
-- step_number : CASE WHEN을 사용해 숫자 지정
, pivot_base as 
(
SELECT
  event_date, # 일자별로 퍼널별 유저 수 쿼리, 일자별로 처리하기 위해 event_date 추가
  event_name_with_screen,
  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 user_pseudo_id) as cnt
FROM fileter_event_and_concat_event_and_screen
GROUP BY ALL
HAVING step_number IS NOT NULL
ORDER BY event_date
-- WHERE 
  -- user_pseudo_id = "2173465695.8694960923"
  -- user_pseudo_id = "2173465695.8694960923" :event_name_with_screen =  screen_view-welcome의 user_id는 NULL -> home에서 로그인
-- food_detal, search 등 event_name_with_scree의 여러 요소들을 확인해보기
)

# [자습] 위 테이블을 screen_view 타입별로 피벗 테이블 만들기 쿼리 (위 테이블을 with 절로 만들고 아래 쿼리 실행)
SELECT
  event_date,
  MAX(IF(event_name_with_screen="screen_view-welcome", cnt, NULL)) as `scren_view-welcome`,
  MAX(IF(event_name_with_screen="screen_view-home", cnt, NULL)) as `scren_view-home`,
  MAX(IF(event_name_with_screen="screen_view-food_category", cnt, NULL)) as `scren_view-category`,
  MAX(IF(event_name_with_screen="screen_view-restaurant", cnt, NULL)) as `scren_view-restaurant`
FROM pivot_base
GROUP BY event_date
ORDER BY event_date

과제 올립니다 감사합니다