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

안녕하세요.

수업 잘 듣고 있습니다.

말씀 해 주신대로 풀이 보기 전에 풀어보고 다시 영상보고하니 재미도 있고 실력도 빨리 느는 것 같아요. 감사합니다!

1-9 강의에서 주신 피벗 쿼리 작성해 봤습니다!

WITH funnel_base AS (
  SELECT
    event_date,
    event_timestamp,
    event_name,
    -- params.key AS key,
    -- params.value.string_value AS string_value,
    -- params.value.int_value AS int_value,
    user_id,
    user_pseudo_id,
    platform,
    MAX(IF(params.key="firebase_screen",params.value.string_value,NULL))AS firebase_screen,
    MAX(IF(params.key="session_id",params.value.string_value,NULL))AS session_id,
    -- MAX(IF(params.key="food_id",params.value.int_value,NULL))AS food_id

    FROM 
  `advance.app_logs`
  CROSS JOIN 
  UNNEST (event_params) AS params
  WHERE 
    event_date BETWEEN "2022-08-01" AND "2022-08-18"
  GROUP BY ALL
), filter_event AS (

-- event_name+screen 
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_timestamp
FROM 
funnel_base
WHERE 
  event_name IN ('screen_view', 'click_payment')
), funnel_step AS (

SELECT
  event_date,
  filter_event.event_name_with_screen,
  CASE
    WHEN filter_event.event_name_with_screen = 'screen_view-welcome' THEN 1
    WHEN filter_event.event_name_with_screen = 'screen_view-home' THEN 2
    WHEN filter_event.event_name_with_screen = 'screen_view-food_category' THEN 3
    WHEN filter_event.event_name_with_screen = 'screen_view-restaurant' THEN 4
    WHEN filter_event.event_name_with_screen = 'screen_view-cart' THEN 5
    WHEN filter_event.event_name_with_screen = 'click_payment-cart' THEN 6
  ELSE NULL
  END AS step_number,
  COUNT(user_pseudo_id) AS cnt
FROM filter_event
WHERE 
  event_date BETWEEN "2022-08-01" AND "2022-08-15"
GROUP BY ALL
HAVING
step_number IS NOT NULL)

--3. event_name_with_screen 컬럼으로, 일자별로 count
SELECT
  event_date,
  SUM(IF(funnel_step.event_name_with_screen = "screen_view-welcome", cnt, null)) AS `screen_view-welcome`,
  SUM(IF(funnel_step.event_name_with_screen = "screen_view-home", cnt, null)) AS `screen_view-home`,
  SUM(IF(funnel_step.event_name_with_screen = "screen_view-food_category", cnt, null)) AS `screen_view-food_category`,
  SUM(IF(funnel_step.event_name_with_screen = "screen_view-restaurant", cnt, null)) AS `screen_view-restaurant`,
  SUM(IF(funnel_step.event_name_with_screen = "screen_view-cart", cnt, null)) AS `screen_view-cart`,
  SUM(IF(funnel_step.event_name_with_screen = "click_payment-cart", cnt, null)) AS `click_payment-cart`

FROM 
funnel_step
GROUP BY ALL
ORDER BY 
  event_date