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

ARRAY, STRUCT, UNNEST 연습 문제

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

SELECT
 title,
 genre
FROM `plucky-catfish-394207.advanced.array_exercises`
   , unnest(genres) as genre
ORDER BY 1

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

SELECT
 title,
 actor.actor,
 actor.character,
FROM `plucky-catfish-394207.advanced.array_exercises`
   , unnest(genres) as genre
ORDER BY 1

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

   

  SELECT
   title,
   actor.actor,
   genre,
  FROM `plucky-catfish-394207.advanced.array_exercises`
     , unnest(genres) as genre
     , unnest(actors) as actors
  ORDER BY 1

• 앱 로그 데이터(app_logs)의 배열을 풀어주세요

  SELECT
   user_id,
   event_date,
   event_name,
   user_pseudo_id,
   ep.key,
   ep.value.string_value,
   ep.value.int_value
  FROM `plucky-catfish-394207.advanced.array_exercises`
     , unnest(event_params) as ep
  ORDER BY 2

PIVOT

• 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 `plucky-catfish-394207.advanced.array_exercises`
     , unnest(event_params) as ep
  GROUP BY 1
  ORDER BY 1

• 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 `plucky-catfish-394207.advanced.array_exercises`
     , unnest(event_params) as ep
  GROUP BY 1
  ORDER BY 1

• orders 테이블에서 사용자(user_id)별, 날짜(order_date)별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다

   

  SELECT
   user_id,
   SUM(IF(order_date = '2023-05-01', amount > 0, 1, 0) as `2023-05-01`,
   SUM(IF(order_date = '2023-05-02', amount > 0, 1, 0) as `2023-05-02`,
   SUM(IF(order_date = '2023-05-03', amount > 0, 1, 0) as `2023-05-03`,
   SUM(IF(order_date = '2023-05-04', amount > 0, 1, 0) as `2023-05-04`,
   SUM(IF(order_date = '2023-05-05', amount > 0, 1, 0) as `2023-05-05`,
  FROM `plucky-catfish-394207.advanced.array_exercises`
     , unnest(event_params) as ep
  GROUP BY 1
  ORDER BY 1

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

   

  SELECT
   event_date,
   event_name, 
   event_timestamp,
   user_id,
   user_pseudo_id,
   MAX(IF(ep.key = 'firebase_screen', ep.value.string_value, NULL)) as firebase_screen,
   MAX(IF(ep.key = 'food_id', ep.value.int_value, NULL)) as food_id,
   MAX(IF(ep.key = 'session_id', ep.value.int_value, NULL)) as session_id,
  FROM `plucky-catfish-394207.advanced.array_exercises`
     , unnest(event_params) as ep
  WHERE event_date = '2022-08-01'
   AND user_id = 32888
  GROUP BY 1, 2, 3, 4, 5

퍼널 분석

WITH base AS (
  SELECT
    event_date
    event_timestamp,
    event_name,
    user_id,
    user_pseudo_id,
    platform,
    MAX(IF(event_param.key = "firebase_screen", event_param.value.string_value, NULL)) AS firebase_screen,
    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")
), add_step_number 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