빠짝스터디 1주차 과제

1. ARRAY, STRUCT 연습문제

• 문제 1) array_exercise테이블에서 각 영화(title)별로 장르(genres)를 UNNEST 해서 보여주세요

SELECT  
  title,
  genres
FROM `analystic-project.advanced.array_exercises` , UNNEST(genres) AS genres
;

• 문제 2) array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야합니다

SELECT
  title,
  actors.actor,
  actors.character
FROM `analystic-project.advanced.array_exercises` , UNNEST(actors) AS actors
;

• 문제 3) array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르 (genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다

   

SELECT
  title,
  actors.actor,
  actors.character,
  genres
FROM `analystic-project.advanced.array_exercises` , UNNEST(actors) AS actors, UNNEST(genres) genres
;

• 문제 4) 앱 로그 데이터(app_logs) 배열 풀기

SELECT  
  user_id,
  event_date,
  event_name,
  user_pseudo_id,
  pr.key,
  pr.value.string_value,
  pr.value.int_value
FROM `analystic-project.advanced.app_logs` , UNNEST(event_params) AS pr
WHERE event_date = "2022-08-01" 
LIMIT 1000
;

2. PIVOT 연습문제 풀이

• 문제 1) orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT해주세요. 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다

SELECT  
  order_date,
  COALESCE(SUM(IF(user_id = 1, amount, null)),0) AS user_1,
  COALESCE(SUM(IF(user_id = 2, amount, null)),0) AS user_2,
  COALESCE(SUM(IF(user_id = 3, amount, null)),0) AS user_3
FROM advanced.orders
GROUP BY order_date
ORDER BY order_date
;

• 문제 2) orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다

SELECT
  user_id,
  COALESCE(SUM(IF(order_date = '2023-05-01', amount, null)),0) AS `2023-05-01`,
  COALESCE(SUM(IF(order_date = '2023-05-02', amount, null)),0) AS `2023-05-02`,
  COALESCE(SUM(IF(order_date = '2023-05-03', amount, null)),0) AS `2023-05-03`,
  COALESCE(SUM(IF(order_date = '2023-05-04', amount, null)),0) AS `2023-05-04`,
  COALESCE(SUM(IF(order_date = '2023-05-05', amount, null)),0) AS `2023-05-05`,
FROM advanced.orders
GROUP BY user_id
ORDER BY user_id
;

• 문제 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' AND order_id is not null, 1, 0)) AS `2023-05-01`,
  MAX(IF(order_date = '2023-05-02' AND order_id is not null, 1, 0)) AS `2023-05-02`,
  MAX(IF(order_date = '2023-05-03' AND order_id is not null, 1, 0)) AS `2023-05-03`,
  MAX(IF(order_date = '2023-05-04' AND order_id is not null, 1, 0)) AS `2023-05-04`,
  MAX(IF(order_date = '2023-05-05' AND order_id is not null, 1, 0)) AS `2023-05-05`,
FROM advanced.orders
GROUP BY user_id
ORDER BY user_id
;

• 문제 4)

  user_id = 32888이 카트 추가하기(click_cart)를 누를때 어떤 음식(food_id)을 담았나요?

WITH app_order_raw AS (
SELECT
  user_id,
  event_date,
  event_name,
  user_pseudo_id,
  pr.key,
  pr.value.string_value,
  pr.value.int_value
FROM advanced.app_logs, UNNEST(event_params) AS pr
WHERE event_date = '2022-08-01'
)
SELECT
  user_id,
  event_date,
  event_name,
  user_pseudo_id,
  MAX(IF(key = 'firebase_screen', string_value, null)) AS firebase_screen,
  MAX(IF(key = 'food_id', int_value, null)) AS food_id,
  MAX(IF(key = 'session_id', string_value, null)) AS session_id,
FROM app_order_raw
GROUP BY user_id, event_date, event_name, user_pseudo_id
;

3. 퍼널분석

• 문제 1) 각 퍼널의 유저 수를 집계 / 데이터 기준: 2022-08-01 ~ 2022-08-18

WITH funnel_data_raw AS (
SELECT  
  event_date,
  event_timestamp,
  event_name,
  user_id,
  user_pseudo_id,
  MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null)) AS screen_name,
  CONCAT(event_name, '-', MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null))) AS event_name_with_screen
FROM advanced.app_logs, UNNEST(event_params) AS pr
WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18'
GROUP BY 1,2,3,4,5
)
SELECT
  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 END AS step_number,
  COUNT(DISTINCT user_pseudo_id) AS cnt
FROM funnel_data_raw
WHERE event_name IN ('screen_view', 'click_payment')
  AND screen_name IN ('welcome', 'home', 'food_category', 'restaurant', 'cart')
GROUP BY 1,2
ORDER BY 2
;

• 문제 2) 일자별 퍼널 유저 수 집계

   

WITH funnel_data_raw AS (
SELECT  
  event_date,
  event_timestamp,
  event_name,
  user_id,
  user_pseudo_id,
  MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null)) AS screen_name,
  CONCAT(event_name, '-', MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null))) AS event_name_with_screen
FROM advanced.app_logs, UNNEST(event_params) AS pr
WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18'
GROUP BY 1,2,3,4,5
)
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 END AS step_number,
  COUNT(DISTINCT user_pseudo_id) AS cnt
FROM funnel_data_raw
WHERE event_name IN ('screen_view', 'click_payment')
  AND screen_name IN ('welcome', 'home', 'food_category', 'restaurant', 'cart')
GROUP BY 1,2,3
ORDER BY 1,3
;

• 문제 3) 일자별 퍼널 유저 수 집계

  형태를 PIVOT형태로 전환하기

WITH funnel_data_raw AS (
SELECT  
  event_date,
  event_timestamp,
  event_name,
  user_id,
  user_pseudo_id,
  MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null)) AS screen_name,
  CONCAT(event_name, '-', MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null))) AS event_name_with_screen
FROM advanced.app_logs, UNNEST(event_params) AS pr
WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18'
GROUP BY 1,2,3,4,5
), daily_funnel_user_count 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 END AS step_number,
  COUNT(DISTINCT user_pseudo_id) AS cnt
FROM funnel_data_raw
WHERE event_name IN ('screen_view', 'click_payment')
  AND screen_name IN ('welcome', 'home', 'food_category', 'restaurant', 'cart')
GROUP BY 1,2,3
ORDER BY 1,3
)
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`,
FROM daily_funnel_user_count
GROUP BY ALL
ORDER BY 1
;