안녕하세요 카일스쿨님!

1-9 PIVOT문제를 풀어봤습니다.

먼저 PIVOT전에 제방식대로 푼코드를 드리겠습니다. 제가 WITH문을 자주 쓰지않고 그냥 한코드에 다쓰는 습관이 있어서 이런식으로 코드를 짜면 안좋은점이 있는지 피드백받고싶습니다. 또한 PIVOT문제를 풀긴 했는데 맞는 답인지 피드백받고싶습니다.

-- PIVOT하기전에 제가 푼 것

-- 내가 쓴 답
-- SELECT
--   event_date,
--   concat(event_name,'-',  param.value.string_value) AS event_name_with_screen,
--   CASE 
--   WHEN (event_name='screen_view' AND param.value.string_value='welcome') THEN 1
--   WHEN (event_name='screen_view' AND param.value.string_value='home') THEN 2
--   WHEN (event_name='screen_view' AND param.value.string_value='food_category') THEN 3
--   WHEN (event_name='screen_view' AND param.value.string_value='restaurant') THEN 4
--   WHEN (event_name='screen_view' AND param.value.string_value='cart') THEN 5
--   WHEN (event_name = 'click_payment' AND param.value.string_value='home') THEN 6
--   ELSE 0
--   END AS step_number,
--   COUNT(DISTINCT user_id) AS cnt
-- FROM `expanded-idiom-446706-d4.advanced.app_logs`
-- CROSS JOIN UNNEST(event_params) AS param
-- WHERE 
-- (event_date BETWEEN "2022-08-01"AND '2022-08-18') AND key='firebase_screen'  
-- GROUP BY event_date,event_name,param.value.string_value,step_number
-- HAVING step_number !=0
-- ORDER BY step_number ,event_date

-- PIVOT문제 푼것

WITH base AS (
SELECT
  event_date,
  event_timestamp,
  event_name,
  user_id,
  user_pseudo_id,
  platform,
  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
WHERE 
  -- event_date = '2022-08-01' # 적은 데이터로 쿼리를 작성하기 위해 만들어둔 조건
  event_date BETWEEN '2022-08-01' AND '2022-08-18'
GROUP BY ALL
) , fliter_event_and_concat_evnet_and_screen AS(
-- screen과 event_name을 합치자(필요한 이벤트만 where 조건에걸어서 사용)
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_datetime
  FROM base
  WHERE 
    event_name IN ('screen_view','click_payment')
)



SELECT 
  event_date,
  SUM(IF(event_name_with_screen='screen_view-welcome'),cnt,0) AS `screen_view-welcome`,
  SUM(IF(event_name_with_screen='screen_view-home'),cnt,0) AS `screen_view-home`,
  SUM(IF(event_name_with_screen='	screen_view-food_category'),cnt,0) AS 	`screen_view-food_category`,
  SUM(IF(event_name_with_screen='screen_view-restaurant'),cnt,0) AS `screen_view-restaurant`,
  SUM(IF(event_name_with_screen='screen_view-cart'),cnt,0) AS `screen_view-cart`,
  SUM(IF(event_name_with_screen='click_payment-cart'),cnt,0) AS `click_payment-cart`
FROM fliter_event_and_concat_evnet_and_screen
GROUP BY ALL
ORDER BY event_date