[바짝스터디 1주차 과제] ARRAY, STRUCT 연습 문제 / PIVOT 연습 문제 / 퍼널 쿼리 연습 문제
미해결
BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
# ARRAY, STRUCT 연습 문제 1. array_exercises 테이블에서 각 영화(title) 별로 장르(genres)를 UNNEST 해서 보여주세요 SELECT title, genre FROM advanced.array_exercises CROSS JOIN UNNEST(genres) AS genre # ARRAY, STRUCT 연습 문제 2. array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야 합니다. SELECT title, actor.actor, actor.character FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor # ARRAY, STRUCT 연습 문제 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, UNNEST(genres) AS genre # ARRAY, STRUCT 연습 문제 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 FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS event_param # PIVOT 연습 문제 1. orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT 해주세요. 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어아 합니다 -- SELECT -- order_date, -- MAX(IF(user_id = 1, amount, 0)) AS user_1, -- MAX(IF(user_id = 2, amount, 0)) AS user_2, -- MAX(IF(user_id = 3, amount, 0)) AS user_3 -- FROM advanced.orders -- GROUP BY -- order_date -- ORDER BY -- order_date SELECT order_date, MAX(IF(user_id = 1, sum_of_amount, 0)) AS user_1, MAX(IF(user_id = 2, sum_of_amount, 0)) AS user_2, MAX(IF(user_id = 3, sum_of_amount, 0)) AS user_3 FROM ( SELECT order_date, user_id, MAX(amount) AS sum_of_amount FROM advanced.orders GROUP BY order_date, user_id ) GROUP BY order_date ORDER BY order_date # PIVOT 연습 문제 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 user_id ORDER BY user_id # PIVOT 연습 문제 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', amount, 0)) AS `2023-05-01`, MAX(IF(order_date = '2023-05-02', amount, 0)) AS `2023-05-02`, MAX(IF(order_date = '2023-05-03', amount, 0)) AS `2023-05-03`, MAX(IF(order_date = '2023-05-04', amount, 0)) AS `2023-05-04`, MAX(IF(order_date = '2023-05-05', amount, 0)) AS `2023-05-05` FROM ( SELECT user_id, order_date, CASE WHEN amount IS NULL THEN 0 ELSE 1 END AS amount FROM advanced.orders ) GROUP BY user_id ORDER BY user_id # PIVOT 연습 문제 version 1 -- WITH sub AS ( -- SELECT -- user_id, -- event_date, -- event_name, -- event_timestamp, -- user_pseudo_id, -- event_param.key AS key, -- event_param.value.string_value AS string_value, -- event_param.value.int_value AS int_value -- FROM ( -- SELECT -- * -- FROM advanced.app_logs -- WHERE -- event_date = '2022-08-01') -- CROSS JOIN UNNEST(event_params) AS event_param -- ) -- SELECT -- event_date, -- COUNT(user_id) AS user_cnt -- FROM ( -- SELECT -- user_id, -- event_date, -- event_name, -- event_timestamp, -- user_pseudo_id, -- MAX(IF(sub.key = 'firebase_screen', sub.string_value, NULL)) AS firebase_screen, -- MAX(IF(sub.key = 'food_id', sub.int_value, NULL)) AS food_id, -- MAX(IF(sub.key = 'session_id', sub.string_value, NULL)) AS session_id -- FROM sub -- GROUP BY -- user_id, -- event_date, -- event_name, -- event_timestamp, -- user_pseudo_id -- ) -- WHERE -- event_name = 'click_cart' # AND food_id = 1544 -- GROUP BY -- event_date # PIVOT 연습 문제 version 2 WITH base AS ( SELECT -- * EXCEPT(event_params), # * EXCEPT(컬럼) : 컬럼을 제외하고 모두 다 보여줘 event_date, event_timestamp, event_name, user_id, user_pseudo_id, 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, FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS param WHERE event_date = "2022-08-01" GROUP BY ALL ) SELECT event_date, COUNT(user_id) AS user_cnt FROM base WHERE event_name = 'click_cart' # AND food_id = 1544 GROUP BY event_date # 퍼널 쿼리 연습 문제 퍼널 별 전환율 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 = '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 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, 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') ), before_pivot AS ( SELECT event_date, event_name_with_screen, 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_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 HAVING step_number IS NOT NULL ORDER BY event_date ) SELECT event_date, MAX(IF(step_number = 1, cnt, NULL)) AS `screen_view-welcome`, MAX(IF(step_number = 2, cnt, NULL)) AS `screen_view-home`, MAX(IF(step_number = 3, cnt, NULL)) AS `screen_view-food_category`, MAX(IF(step_number = 4, cnt, NULL)) AS `screen_view-restaurant`, MAX(IF(step_number = 5, cnt, NULL)) AS `screen_view-cart`, MAX(IF(step_number = 6, cnt, NULL)) AS `click_payment-cart`, CAST(MAX(IF(step_number = 2, cnt, NULL)) AS FLOAT64) / CAST(MAX(IF(step_number = 1, cnt, NULL)) AS FLOAT64) AS CVR1, CAST(MAX(IF(step_number = 3, cnt, NULL)) AS FLOAT64) / CAST(MAX(IF(step_number = 2, cnt, NULL)) AS FLOAT64) AS CVR2, CAST(MAX(IF(step_number = 4, cnt, NULL)) AS FLOAT64) / CAST(MAX(IF(step_number = 3, cnt, NULL)) AS FLOAT64) AS CVR3, CAST(MAX(IF(step_number = 5, cnt, NULL)) AS FLOAT64) / CAST(MAX(IF(step_number = 4, cnt, NULL)) AS FLOAT64) AS CVR4, CAST(MAX(IF(step_number = 6, cnt, NULL)) AS FLOAT64) / CAST(MAX(IF(step_number = 5, cnt, NULL)) AS FLOAT64) AS CVR5 FROM before_pivot GROUP BY event_date ORDER BY event_date
- sql
- bigquery
- 카일스쿨