[빠짝스터디 1주차 과제] Array, Struct, Pivot, Funnel
미해결
BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
1. ARRAY, STRUCT 1) array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요. SELECT title , genre FROM advanced.array_exercises CROSS JOIN UNNEST(genres) AS genre ; 2) array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. (배우와 배역은 별도의 컬럼으로 나와야 합니다) -- 동일한 단어에 대해 선택할 수 있는 함수 : cmd+d SELECT title , actor.actor , actor.character FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor ; 3) array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르(genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다. SELECT title , actor.actor , actor.character , genre FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor CROSS JOIN UNNEST(genres) AS genre ; 4) 앱 로그 데이터(app_logs)의 배열을 풀어주세요. SELECT user_id , event_date , event_name , user_pseudo_id , event_param.key , event_param.value.string_value , event_param.value.int_value , platform FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS event_param ; 2. PIVOT 1) orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT해주세요. 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다. SELECT order_date , SUM(IF(user_id = 1, amount, NULL)) AS `user_id_1` , SUM(IF(user_id = 2, amount, NULL)) AS `user_id_2` , SUM(IF(user_id = 3, amount, NULL)) AS `user_id_3` FROM advanced.orders GROUP BY 1 ORDER BY 1 ; 2) orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다. SELECT user_id , SUM(IF(order_date = '2023-05-01', amount, 0)) AS `2023-05-01` , SUM(IF(order_date = '2023-05-02', amount, 0)) AS `2023-05-02` , SUM(IF(order_date = '2023-05-03', amount, 0)) AS `2023-05-03` , SUM(IF(order_date = '2023-05-04', amount, 0)) AS `2023-05-04` , SUM(IF(order_date = '2023-05-05', amount, 0)) AS `2023-05-05` FROM advanced.orders GROUP BY 1 ; 3) orders 테이블에서 사용자(user_id)별, 날짜(order_date)별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다 SELECT user_id , MAX(IF(order_date = '2023-05-01', 1, 0)) AS `2023-05-01` , MAX(IF(order_date = '2023-05-02', 1, 0)) AS `2023-05-02` , MAX(IF(order_date = '2023-05-03', 1, 0)) AS `2023-05-03` , MAX(IF(order_date = '2023-05-04', 1, 0)) AS `2023-05-04` , MAX(IF(order_date = '2023-05-05', 1, 0)) AS `2023-05-05` FROM advanced.orders GROUP BY 1 ; 4) user_id = 32888이 카트 추가하기(click_cart)를 누를 때 어떤 음식(food_id)을 담았나요? WITH app_logs_info AS ( SELECT user_id , event_name , 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 FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS event_param GROUP BY 1, 2 ) SELECT food_id FROM app_logs_info WHERE user_id = 32888 AND event_name = 'click_cart' ; 3. Funnel 1) 일자별, 이벤트별 집계 WITH app_logs_info AS ( SELECT user_id , event_date , event_timestamp , event_name , user_pseudo_id , event_param.key , 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 FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS event_param WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18' AND event_name IN ('screen_view', 'click_payment') GROUP BY ALL ) , add_step_number AS ( SELECT event_date , DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_date_time , user_id , user_pseudo_id , CONCAT(event_name, '-', firebase_screen) AS event_name_with_screen , CASE CONCAT(event_name, '-', firebase_screen) WHEN 'screen_view-welcome' THEN 1 WHEN 'screen_view-home' THEN 2 WHEN 'screen_view-food_category' THEN 3 WHEN 'screen_view-restaurant' THEN 4 WHEN 'screen_view-cart' THEN 5 WHEN 'click_payment-cart' THEN 6 ELSE NULL END AS step_number FROM app_logs_info ) SELECT event_date , step_number , event_name_with_screen , COUNT(DISTINCT user_pseudo_id) AS user_cnt FROM add_step_number WHERE step_number IS NOT NULL GROUP BY 1, 2, 3 ORDER BY 1, 2 ; 2) 집계 데이터 PIVOT WITH app_logs_info AS ( SELECT user_id , event_date , event_timestamp , event_name , user_pseudo_id , event_param.key , 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 FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS event_param WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18' GROUP BY ALL ) , add_step_number AS ( SELECT event_date , DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_date_time , user_id , user_pseudo_id , CONCAT(event_name, '-', firebase_screen) AS event_name_with_screen , CASE CONCAT(event_name, '-', firebase_screen) WHEN 'screen_view-welcome' THEN 1 WHEN 'screen_view-home' THEN 2 WHEN 'screen_view-food_category' THEN 3 WHEN 'screen_view-restaurant' THEN 4 WHEN 'screen_view-cart' THEN 5 WHEN 'click_payment-cart' THEN 6 ELSE NULL END AS step_number FROM app_logs_info WHERE event_name IN ('screen_view', 'click_payment') ) , agg_user_cnt AS ( SELECT event_date , step_number , event_name_with_screen , COUNT(DISTINCT user_pseudo_id) AS user_cnt FROM add_step_number WHERE step_number IS NOT NULL GROUP BY 1, 2, 3 ORDER BY 1, 2 ) SELECT event_date , MAX(IF(step_number = 1, user_cnt, NULL)) AS `screen_view-welcome` , MAX(IF(step_number = 2, user_cnt, NULL)) AS `screen_view-home` , MAX(IF(step_number = 3, user_cnt, NULL)) AS `screen_view-food_category` , MAX(IF(step_number = 4, user_cnt, NULL)) AS `screen_view-restaurant` , MAX(IF(step_number = 5, user_cnt, NULL)) AS `screen_view-cart` , MAX(IF(step_number = 6, user_cnt, NULL)) AS `click_payment-cart` FROM agg_user_cnt GROUP BY 1 ORDER BY 1 ;
- sql
- Google-Analytics
- firebase
- google-sheets
- bigquery
- study