[바짝스터디 1주차 과제] ARRAY, STRUCT 연습 문제 / PIVOT 연습 문제 / 퍼널 쿼리 연습 문제

1. ARRAY, STRUCT 연습 문제

-- 1.array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요
SELECT
  movie_id,
  title,
  genre
FROM `advanced.array_exercises`,
CROSS JOIN UNNEST(genres) AS genre

-- 2.array_exercises 테이블에서 각 영화(title) 별로 배우(actor)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야 합니다.
SELECT
  title,
  actors.actor,
  actors.character
FROM `advanced.array_exercises`
CROSS JOIN UNNEST(actors) AS actors

-- 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
  event_date,
  event_timestamp,
  event_name,
  event_param.key AS key,
  event_param.value.string_value AS string_value,
  event_param.value.int_value AS int_value,
  user_id,
  user_pseudo_id,
  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, 0)) AS user_1,
  SUM(IF(user_id = 2, amount, 0)) AS user_2,
  SUM(IF(user_id = 3, amount, 0)) AS user_3
FROM advanced.orders
GROUP BY ALL
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 ALL
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 ALL
ORDER BY
  user_id

-- 4.앱 로그 PIVOT
WITH base AS (
SELECT
  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
GROUP BY ALL
)

SELECT
  event_date,
  COUNT(user_id) AS user_cnt
FROM base
WHERE
  event_name = "click_cart"
GROUP BY ALL

 3. 퍼널 쿼리 연습 문제

WITH base AS (
select
  event_date,
  event_timestamp,
  event_name,
  user_id,
  user_pseudo_id,
  platform,
  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
  1=1
  AND 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
  1=1
  AND event_name IN ("screen_view", "click_payment")
),

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

-- 피벗 쿼리 만들기
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 main
GROUP BY ALL
ORDER BY
  event_date