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

안녕하세요 카일님!

강의 너무 잘 듣고 있습니다! 온오프라인에서 SQL실무와 관련된 강의가 없어 많이

고민했는데 카일님 강의 덕분에 입문 강의 부터 정말 많이 배웠습니다 ㅜㅜ!

아래 일자별 퍼널을 PIVOT 해보는 쿼리를 짜봤는데 피드백 주시면 너무 감사하겠습니다!

더불어서 PIVOT을 할 때 제가 아래 주석처리한 것들도 디버깅 했을때는 동일하게 나왔는데

혹시나 실무단에서 어떻게 하는건 조심해야한다! 라는 부분이 있을지도 궁금합니다..!

항상 감사합니다 :)

#일자별 퍼널 PIVOT 
WITH base AS (
SELECT 
  event_date,
  event_timestamp,
  event_name,
  user_id,
  user_pseudo_id,
  platform,
  MAX(IF(param.key = "firebase_screen", param.value.string_value, NULL )) AS firebase_screen,
  -- MAX(IF(param.key = "food_id", param.value.int_value, NULL )) AS food_id,
  MAX(IF(param.key = "session_id", param.value.string_value, NULL )) AS session_id,
  -- param.key AS screen,
  -- param.value.string_value AS string_value,
  -- param.value.int_value AS int_value,
FROM `advanced.app_logs`
CROSS JOIN UNNEST(event_params) AS param
WHERE 
  -- event_date = "2022-08-01" #적은 데이터로 쿼리를 작성하기 위해 만들어둔 조건 
  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),
    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")
), event_by_user_cnt_by_date AS (
--event_name + screen (필요한 이벤트만 WHERE 조건에 걸어서 사용)
--step_number + COUNT 
--step_number : CASE WHEN 사용해 숫자 지정 1~6 (welcome, home, food_category, restaurant, cart )
SELECT 
  -- DISTINCT event_name_with_screen, #해당 열 Unique값 확인 가능 
  event_date, #일자별로 퍼널별 유저수 쿼리
  event_name_with_screen,
  -- event_datetime, user_pseudo_id,
  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-restaurarnt" 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
--screen_view-restaurant/screen_view-food_detail/screen_view-search/screen_view-search_result 도 퍼널 정의에 어떻게 넣을지 생각해보고 단계에 넣어보기 > 고객이 어떤 흐름으로 움직이는지 확인해보기 
HAVING step_number IS NOT NULL 
ORDER BY event_date 
)
SELECT 
  event_date,
  SUM(IF(event_name_with_screen = 'screen_view-welcome', cnt, NULL)) AS screen_view_welcome,
	SUM(IF(event_name_with_screen = 'screen_view-home', cnt, NULL)) AS screen_view_home,
	SUM(IF(event_name_with_screen = 'screen_view-food_category', cnt, NULL )) AS screen_view_food_category,
	SUM(IF(event_name_with_screen = 'screen_view-restaurarnt', cnt, NULL)) AS screen_view_restaurarnt,
	SUM(IF(event_name_with_screen = 'screen_view-cart', cnt, NULL)) AS screen_view_cart,
	SUM(IF(event_name_with_screen = 'click_payment-cart',  cnt, NULL)) AS click_payment_cart,
  -- 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-restaurarnt', cnt, NULL)) AS screen_view_restaurarnt,
	-- 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,
	-- MAX(CASE WHEN event_name_with_screen = 'screen_view-welcome' THEN cnt END) AS screen_view_welcome,
	-- MAX(CASE WHEN event_name_with_screen = 'screen_view-home' THEN cnt END) AS screen_view_home,
	-- MAX(CASE WHEN event_name_with_screen = 'screen_view-food_category'THEN cnt END) AS screen_view_food_category,
	-- MAX(CASE WHEN event_name_with_screen = 'screen_view-restaurarnt'THEN cnt END) AS screen_view_restaurarnt,
	-- MAX(CASE WHEN event_name_with_screen = 'screen_view-cart'THEN cnt END) AS screen_view_cart,
	-- MAX(CASE WHEN event_name_with_screen = 'click_payment-cart'THEN cnt END) AS click_payment_cart
FROM  event_by_user_cnt_by_date 
GROUP BY event_date 
ORDER BY event_date ;