미해결
BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
[빠짝스터디 1주차 과제] Array, Struct, Pivot, Funnel
1. ARRAY, STRUCT1) 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. PIVOT1) 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. Funnel1) 일자별, 이벤트별 집계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) 집계 데이터 PIVOTWITH 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
;
