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

연습문제

(1) ARRAY, STRUCT

-- 1) array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요
# 쿼리를 작성하는 목표, 확인할 지표 : 평면화
# 쿼리 계산 방법 : UNNEST
# 데이터의 기간 :
# 사용할 테이블 : array_exercises
# Join KEY :
# 데이터 특징 :
SELECT
  title, 
  genre
FROM advanced.array_exercises 
CROSS JOIN UNNEST(genres) AS genre;


-- 2) array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야 합니다
# 쿼리를 작성하는 목표, 확인할 지표 : 영화별 배우와 배역 평면화
# 쿼리 계산 방법 : UNNEST
# 데이터의 기간 :
# 사용할 테이블 : array_exercises
# Join KEY :
# 데이터 특징 : 배우와 배역은 별도의 컬럼으로 나와야 합니다
SELECT
  title, 
  actor.actor, 
  actor.character 
FROM advanced.array_exercises
CROSS JOIN UNNEST(actors) AS actor;


-- 3) array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르 (genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다
# 쿼리를 작성하는 목표, 확인할 지표 :  배우, 배역, 장르
# 쿼리 계산 방법 : UNNEST
# 데이터의 기간 :
# 사용할 테이블 : array_exercises
# Join KEY :
# 데이터 특징 : 한 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)의 배열을 풀어주세요
# 쿼리를 작성하는 목표, 확인할 지표 : 앱 로그 데이터 평면화
# 쿼리 계산 방법 : UNNEST
# 데이터의 기간 :
# 사용할 테이블 : app_logs
# Join KEY :
# 데이터 특징 : event_params안에 ARRAY와 STRUCT 구조가 공존하고 있고 값이 string_value와 int_value 두 종류로 나뉨 확인 필요!!
SELECT
  user_id, 
  event_date, 
  event_name, 
  user_pseudo_id, 
  event_param.key, 
  event_param.value.string_value, 
  event_param.value.int_value
FROM advanced.app_logs
CROSS JOIN UNNEST(event_params) AS event_param
WHERE 
  event_date = "2022-08-01";

(2) PIVOT

-- 1) orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT해주세요. 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다
# 쿼리를 작성하는 목표, 확인할 지표 :  
# 쿼리 계산 방법 : MAX, IF, GROUP BY
# 데이터의 기간 :
# 사용할 테이블 : orders
# Join KEY :
# 데이터 특징 :
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)으로 만들어야 합니다
# 쿼리를 작성하는 목표, 확인할 지표 : 
# 쿼리 계산 방법 : SUM, IF, GROUP BY
# 데이터의 기간 :
# 사용할 테이블 : orders
# Join KEY : 
# 데이터 특징 :
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로 처리합니다
# 쿼리를 작성하는 목표, 확인할 지표 : 
# 쿼리 계산 방법 : MAX, IF, GROUP BY
# 데이터의 기간 :
# 사용할 테이블 : orders
# Join KEY :
# 데이터 특징 : 
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) app_log를 pivot하기
# 쿼리를 작성하는 목표, 확인할 지표 : app_log 데이터 PIVOT 테이블로 변경
# 쿼리 계산 방법 : UNNEST, MAX, IF, GROUP BY
# 데이터의 기간 : 2022-08-01
# 사용할 테이블 : app_logs
# Join KEY :
# 데이터 특징 : event_params안에 ARRAY와 STRUCT 구조가 공존하고 있고 값이 string_value와 int_value 두 종류로 나뉨 확인 필요!!

WITH base AS(
SELECT 
  user_id, 
  event_date,
  event_name,
  event_timestamp,
  user_pseudo_id, 
  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 avdanced.app_logs
CROSS JOIN UNNEST (event_params) AS event_param
WHERE
   event_date ='2022-08-01'
GROUP BY ALL
)

SELECT
  event_date, 
  COUNT(user_id) AS cnt
FROM base
WHERE
  event_name = 'click_cart' 
GROUP BY ALL;

(3) 퍼널 ( Funnel )

# 쿼리를 작성하는 목표, 확인할 지표 : screen_view-welcome, screen_view-home, screen_view-food_category, screen_view-restaurant, screen_view-cart, click_payment-cart 순서대로 step_number를 지정하고 퍼널 분석하기
# 쿼리 계산 방법 : MAX, IF, UNNEST, CASE WHEN
# 데이터의 기간 : 2022-08-01 ~ 2022-08-18
# 사용할 테이블 : app_logs
# Join KEY :
# 데이터 특징 : event_params안에 ARRAY와 STRUCT 구조가 공존하고 있고 값이 string_value와 int_value 두 종류로 나뉨 확인 필요!!
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 fire_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
), fiter_event_and_concat_event_and_screen AS (
  -- event_name + screen (필요한 이벤트만 WHERE 조건에 걸어서 사용)
  SELECT
    * EXCEPT(event_name, fire_screen, event_timestamp),
    CONCAT(event_name, "-", fire_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")
)

-- step_number + COUNT
-- step_number : CASE WHEN을 사용해 숫자 지정
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
  ELSE NULL
  END AS step_number,
  COUNT(DISTINCT user_pseudo_id) AS cnt
FROM fiter_event_and_concat_event_and_screen
GROUP BY ALL
HAVING step_number IS NOT NULL
WHERE
  user_pseudo_id = "1350836585.3421064109"

-- 일자별
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 fiter_event_and_concat_event_and_screen
GROUP BY ALL
HAVING step_number IS NOT NULL
ORDER BY event_date;

배운점

• ARRAY와 STRUCT 라는 조금은 생소할 수 있는 데이터 타입과 이를 어떻게 하면 평면화로 풀고 어떻게 데이터를 처리할 수 있을지를 조금은 알 수 있었고

• BigQuery로 생각보다 많은 부분을 할 수 있구나 생각이 들면서 이를 프로젝트에 연결시키면 어떨까 하는 재밌는 고민이 생긴거 같다

• BIgQuery를 배우고 싶은데 어떤 데이터로 어떻게 다뤄야 할지 막막했던 찰라에 많은 것을 배울 수 있던 1주차 였다