[과제]1-9 피벗 테이블 만들어보기

안녕하세요.

강의 마지막 부분의 피벗 과제 올려봅니다.

감사합니다.!

WITH base AS(
  SELECT
    user_id,
    event_date,
    event_name,
    event_timestamp,
    user_pseudo_id,
    MAX(IF(ep.key = "firebase_screen",ep.value.string_value,NULL)) AS firebase_screen,
    MAX(IF(ep.key = "food_id",ep.value.int_value,NULL)) AS food_id,
    MAX(IF(ep.key = "session_id",ep.value.string_value,NULL)) AS session_id
  FROM advanced.app_logs
  CROSS JOIN UNNEST(event_params) AS ep
  WHERE event_date BETWEEN "2022-08-01" AND "2022-08-18"
  GROUP BY ALL
), filter_event_name_screen AS(
  SELECT
    * EXCEPT(event_name,firebase_screen),
    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")
)

SELECT
  * EXCEPT (step_number, event_name_with_screen,cnt),
  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
(
  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(user_pseudo_id) AS cnt
FROM filter_event_name_screen
GROUP BY ALL
HAVING step_number IS NOT NULL
--ORDER BY step_number
)
GROUP BY ALL
ORDER BY event_date

COUNT(user_pseudo_id) AS cnt

COUNT(DISTINCT user_pseudo_id) AS cnt