[과제] 연습문제 쿼리 작성했습니다!

안녕하세요 카일님,

강의 잘 듣고 있습니다.

연습문제 쿼리 작성해 보았는데 잘못된 부분이 있는지, 더 나은 쿼리 방식이 있다면 피드백 부탁드립니다

감사합니다.

WITH basic AS

(SELECT
  event_date,
  event_name,
  user_pseudo_id,
  event_params,
  if(event_params.key="firebase_screen", event_params.value.string_value, null) as screen
FROM advanced.app_logs
CROSS JOIN UNNEST(event_params) as event_params
WHERE event_date between '2022-08-01' and '2022-08-18'
),

basic2 as (
SELECT
event_date,
concat(event_name, '-', screen) as event_name_with_screen,
COUNT(user_pseudo_id) as cnt
FROM basic
WHERE event_name in ('screen_view', 'click_payment')
GROUP BY ALL
HAVING event_name_with_screen is not null
ORDER BY event_date)

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-restaurant', cnt, null)) as screen_view_restaurant,
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-food_detail', cnt, null)) as screen_view_food_detail,
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(if(event_name_with_screen='screen_view-search', cnt, null)) as screen_view_search,
MAX(if(event_name_with_screen='screen_view-search_result', cnt, null)) as screen_view_search_result
FROM basic2
GROUP BY all
ORDER BY event_date