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

1. ARRAY, STRUCT

-- array_exercises 테이블
-- 영화(title)별로 장르(genres) UNNEST

SELECT
  movie_id,
  title,
  genre
FROM `advanced.array_exercises`,
CROSS JOIN UNNEST(genres) AS genre

-- array_exercises 테이블
-- 영화(title) 별로 배우(actor), 배역(character) UNNEST

SELECT
  title,
  actors.actor,
  actors.character
FROM `advanced.array_exercises`
CROSS JOIN UNNEST(actors) AS actors

-- array_exercises 테이블
-- 영화(title) 별로 배우(actor), 배역(character), 장르(genre) UNNEST

SELECT
  title,
  actor.actor,
  actor.character,
  genre
FROM `advanced.array_exercises`
CROSS JOIN UNNEST(actors) AS actor
CROSS JOIN UNNEST(genres) AS genre

-- 앱 로그 데이터(app_logs) UNNEST
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. FUNNEL

with base as (
  select
    event_date,
    event_timestamp,
    event_name,
    user_id,
    user_pseudo_id,
    platform,
    -- event_param
    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,
    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' )
)

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 1, 3


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
  all