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

강의 보면서 '퍼널별 유저 수 집계' 쿼리 작성했는데 결과값이 올바르게 나오지 않아요... 어디서 잘못 되었을까요?

WITH base AS( SELECT event_date, event_name, DATETIME(TIMESTAMP_MICROS(event_timestamp),'Asia/Seoul') as event_timestamp, event_param, user_id, user_pseudo_id, platform, -- event_parma.key PIVOT 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 GROUP BY ALL) -- event_name + screen (필요한 이벤트만 where 조건에 걸어서 사용) ,filter_event as( SELECT * except(event_name,firebase_screen), concat(event_name,'-',firebase_screen) AS event_name_with_screen FROM base WHERE event_name in ('screen_view','click_payment')) SELECT event_name_with_screen, -- step_number 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_pavement-cart' then 6 else null end AS Step_number, count(DISTINCT user_pseudo_id) as cnt FROM filter_event GROUP BY ALL HAVING step_number IS NOT NULL