연습문제

(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)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야 합니다
SELECT title,
     , actor.actor AS actor
     , actor.character AS character
FROM advanced.array_exercises
CROSS JOIN UNNEST(actors) AS actor

-- 3) array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르(genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다
SELECT title
     , actor.actor AS actor
     , actor.character AS 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
     , params.key AS key
     , params.value.string_value AS str_value
     , params.value.int_value AS int_value
FROM advanced.app_logs
CROSS JOIN UNNEST(event_params) AS params
WHERE event_date = '2022-08-01'

(2) PIVOT

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

     SELECT order_date
	  , user_id
          , sum(amount) AS sum_of_amount
     FROM advanced.orders
     GROUP BY ALL
    
)

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 step1
GROUP BY order_date
ORDER BY order_date

-- 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

-- 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 user_id
ORDER BY user_id

-- 4) 앱 로그 데이터 배열 PIVOT하기
SELECT user_id
     , event_date
     , event_name
     , user_pseudo_id
     , MAX(IF(params.key = 'firebase_screen', params.value.string_value, NULL)) AS firebase_screen
     , MAX(IF(params.key = 'food_id', params.value.int_value, NULL)) AS food_id
     , MAX(IF(params.key = 'session_id', params.value.string_value, NULL)) AS session_id
FROM advanced.app_logs
CROSS JOIN UNNEST(event_params) AS params
WHERE event_date = '2022-08-01'
GROUP BY ALL

(3) 퍼널 분석

WITH step1 AS (

  SELECT event_date
       , event_timestamp
       , event_name
       , user_id
       , user_pseudo_id
       , MAX(IF(params.key = 'firebase_screen', params.value.string_value, NULL)) AS firebase_screen
       , MAX(IF(params.key = 'session_id', params.value.string_value, NULL)) AS session_id
       , platform
  FROM advanced.app_logs
  CROSS JOIN UNNEST(event_params) AS params
  WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18'
  GROUP BY ALL

), step2 AS (

  SELECT * EXCEPT(event_timestamp)
       , CONCAT(event_name, '-', firebase_screen) AS event_name_with_screen
       , DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime
  FROM step1

), step3 AS (

  SELECT * 
       , 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
  FROM step2

), step3_1 AS ( -- 1) 각 퍼널별 유저 수 집계
  
  SELECT event_name_with_screen
       , step_number
       , COUNT(DISTINCT user_pseudo_id) AS cnt
  FROM step3
  GROUP BY ALL
  HAVING step_number IS NOT NULL
  ORDER BY step_number

)
, step3_2 AS ( -- 2) 일자별 각 퍼널별 유저 수 집계
 
  SELECT event_date
       , event_name_with_screen
       , step_number
       , COUNT(DISTINCT user_pseudo_id) AS cnt
  FROM step3
  GROUP BY ALL
  HAVING step_number IS NOT NULL
  ORDER BY event_date
         , step_number

)

-- 3) 2) 데이터를 PIVOT
SELECT event_date
     , MAX(IF(event_name_with_screen = 'screen_view-welcome', cnt, NULL)) AS `screen_view-welcome`
     , MAX(IF(event_name_with_screen = 'screen_view-home', cnt, NULL)) AS `screen_view-home`
     , MAX(IF(event_name_with_screen = 'screen_view-food_category', cnt, NULL)) AS `screen_view-food_category`
     , MAX(IF(event_name_with_screen = 'screen_view-restaurant', cnt, NULL)) AS `screen_view-restaurant`
     , MAX(IF(event_name_with_screen = 'screen_view-cart', cnt, NULL)) AS `screen_view-cart`
     , MAX(IF(event_name_with_screen = 'click_payment-cart', cnt, NULL)) AS `click_payment-cart`
FROM step3_2
GROUP BY event_date
ORDER BY event_date