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

강의 다 듣고, 마지막에 주신 계산하기 좋은 형태로 만드는 피벗테이블을 만들어 보았습니다. 코드에 대한 피드백이 있으시다면 알려주세요! 감사합니다.

WITH base AS(
  SELECT 
    event_date, event_timestamp, event_name, user_id,
    user_pseudo_id, platform,
    MAX(IF(event_param.key = 'firebase_screen', event_param.value.string_value, NULL)) AS firebase_screen,
    -- MAX(IF(event_param.key = 'food_id', event_param.value.int_value, NULL)) AS food_id,
    MAX(IF(event_param.key = 'session_id', event_param.value.string_value, NULL)) AS session_id
    FROM advanced.app_logs
  CROSS JOIN UNNEST(event_params) AS event_param
  WHERE event_date between "2022-08-01" and "2022-08-18"
  GROUP BY ALL
), filter_event_and_concat_event_and_screen AS (
  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")
), pivot1 AS(
  SELECT 
    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 filter_event_and_concat_event_and_screen
  GROUP BY ALL
  HAVING step_number IS NOT NULL
  ORDER BY event_date, step_number ASC
)

# 피벗 한번 더 돌려서 계산 쉽게 할 수 있는 테이블 형태 만들기
SELECT 
  event_date,
  MAX(IF(event_name_with_screen = 'screen_view-welcome', cnt, NULL)) AS `screen_view-welcome`,
  MAX(IF(event_name_with_screen = 'screen_view-home', cnt, NULL)) AS `screen_view-home`,
  MAX(IF(event_name_with_screen = 'screen_view-food_category', cnt, NULL)) AS `screen_view-food_category`,
  MAX(IF(event_name_with_screen = 'screen_view-restaurant', cnt, NULL)) AS `screen_view-restaurant`,
  MAX(IF(event_name_with_screen = 'screen_view-cart', cnt, NULL)) AS `screen_view-cart`,
  MAX(IF(event_name_with_screen = 'click_payment-cart', cnt, NULL)) AS `click_payment-cart`
FROM pivot1
GROUP BY event_date
ORDER BY 1;