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

1. ARRAY, STRUCT 연습문제

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

SELECT  
  title,
  genres
FROM `analystic-project.advanced.array_exercises` , UNNEST(genres) AS genres
;

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

SELECT
  title,
  actors.actor,
  actors.character
FROM `analystic-project.advanced.array_exercises` , UNNEST(actors) AS actors
;

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

   

SELECT
  title,
  actors.actor,
  actors.character,
  genres
FROM `analystic-project.advanced.array_exercises` , UNNEST(actors) AS actors, UNNEST(genres) genres
;

• 문제 4) 앱 로그 데이터(app_logs) 배열 풀기

SELECT  
  user_id,
  event_date,
  event_name,
  user_pseudo_id,
  pr.key,
  pr.value.string_value,
  pr.value.int_value
FROM `analystic-project.advanced.app_logs` , UNNEST(event_params) AS pr
WHERE event_date = "2022-08-01" 
LIMIT 1000
;

2. PIVOT 연습문제 풀이

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

SELECT  
  order_date,
  COALESCE(SUM(IF(user_id = 1, amount, null)),0) AS user_1,
  COALESCE(SUM(IF(user_id = 2, amount, null)),0) AS user_2,
  COALESCE(SUM(IF(user_id = 3, amount, null)),0) AS user_3
FROM advanced.orders
GROUP BY order_date
ORDER BY order_date
;

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

SELECT
  user_id,
  COALESCE(SUM(IF(order_date = '2023-05-01', amount, null)),0) AS `2023-05-01`,
  COALESCE(SUM(IF(order_date = '2023-05-02', amount, null)),0) AS `2023-05-02`,
  COALESCE(SUM(IF(order_date = '2023-05-03', amount, null)),0) AS `2023-05-03`,
  COALESCE(SUM(IF(order_date = '2023-05-04', amount, null)),0) AS `2023-05-04`,
  COALESCE(SUM(IF(order_date = '2023-05-05', amount, null)),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' AND order_id is not null, 1, 0)) AS `2023-05-01`,
  MAX(IF(order_date = '2023-05-02' AND order_id is not null, 1, 0)) AS `2023-05-02`,
  MAX(IF(order_date = '2023-05-03' AND order_id is not null, 1, 0)) AS `2023-05-03`,
  MAX(IF(order_date = '2023-05-04' AND order_id is not null, 1, 0)) AS `2023-05-04`,
  MAX(IF(order_date = '2023-05-05' AND order_id is not null, 1, 0)) AS `2023-05-05`,
FROM advanced.orders
GROUP BY user_id
ORDER BY user_id
;

• 문제 4)

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

WITH app_order_raw AS (
SELECT
  user_id,
  event_date,
  event_name,
  user_pseudo_id,
  pr.key,
  pr.value.string_value,
  pr.value.int_value
FROM advanced.app_logs, UNNEST(event_params) AS pr
WHERE event_date = '2022-08-01'
)
SELECT
  user_id,
  event_date,
  event_name,
  user_pseudo_id,
  MAX(IF(key = 'firebase_screen', string_value, null)) AS firebase_screen,
  MAX(IF(key = 'food_id', int_value, null)) AS food_id,
  MAX(IF(key = 'session_id', string_value, null)) AS session_id,
FROM app_order_raw
GROUP BY user_id, event_date, event_name, user_pseudo_id
;

3. 퍼널분석

• 문제 1) 각 퍼널의 유저 수를 집계 / 데이터 기준: 2022-08-01 ~ 2022-08-18

WITH funnel_data_raw AS (
SELECT  
  event_date,
  event_timestamp,
  event_name,
  user_id,
  user_pseudo_id,
  MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null)) AS screen_name,
  CONCAT(event_name, '-', MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null))) AS event_name_with_screen
FROM advanced.app_logs, UNNEST(event_params) AS pr
WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18'
GROUP BY 1,2,3,4,5
)
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 END AS step_number,
  COUNT(DISTINCT user_pseudo_id) AS cnt
FROM funnel_data_raw
WHERE event_name IN ('screen_view', 'click_payment')
  AND screen_name IN ('welcome', 'home', 'food_category', 'restaurant', 'cart')
GROUP BY 1,2
ORDER BY 2
;

• 문제 2) 일자별 퍼널 유저 수 집계

   

WITH funnel_data_raw AS (
SELECT  
  event_date,
  event_timestamp,
  event_name,
  user_id,
  user_pseudo_id,
  MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null)) AS screen_name,
  CONCAT(event_name, '-', MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null))) AS event_name_with_screen
FROM advanced.app_logs, UNNEST(event_params) AS pr
WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18'
GROUP BY 1,2,3,4,5
)
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 END AS step_number,
  COUNT(DISTINCT user_pseudo_id) AS cnt
FROM funnel_data_raw
WHERE event_name IN ('screen_view', 'click_payment')
  AND screen_name IN ('welcome', 'home', 'food_category', 'restaurant', 'cart')
GROUP BY 1,2,3
ORDER BY 1,3
;

• 문제 3) 일자별 퍼널 유저 수 집계

  형태를 PIVOT형태로 전환하기

WITH funnel_data_raw AS (
SELECT  
  event_date,
  event_timestamp,
  event_name,
  user_id,
  user_pseudo_id,
  MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null)) AS screen_name,
  CONCAT(event_name, '-', MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null))) AS event_name_with_screen
FROM advanced.app_logs, UNNEST(event_params) AS pr
WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18'
GROUP BY 1,2,3,4,5
), daily_funnel_user_count 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 END AS step_number,
  COUNT(DISTINCT user_pseudo_id) AS cnt
FROM funnel_data_raw
WHERE event_name IN ('screen_view', 'click_payment')
  AND screen_name IN ('welcome', 'home', 'food_category', 'restaurant', 'cart')
GROUP BY 1,2,3
ORDER BY 1,3
)
SELECT
  event_date,
  MAX(IF(step_number = 1, cnt, null)) AS `screen_view-welcome`,
  MAX(IF(step_number = 2, cnt, null)) AS `screen_view-home`,
  MAX(IF(step_number = 3, cnt, null)) AS `screen_view-food_category`,
  MAX(IF(step_number = 4, cnt, null)) AS `screen_view-restaurant`,
  MAX(IF(step_number = 5, cnt, null)) AS `screen_view-cart`,
  MAX(IF(step_number = 6, cnt, null)) AS `click_payment-cart`,
FROM daily_funnel_user_count
GROUP BY ALL
ORDER BY 1
;

4. 새롭게 배웠던 점

1. 쿼리를 활용하여 PIVOT 형태로 데이터를 변환 할 수 있는 스킬을 습득함.

2. "GROUP BY ALL" 이라는 방식으로 전체 집계를 할 수 있다는 방법을 처음 알게 됨.

3. `` (백틱) 기호를 사용하면 특수문자 '-'와 한글까지 컬럼명으로 사용할 수 있다는 것을 처음 알게 됨.

4. 실무에서 FROM절에 UNNEST() 함수 앞에 ',' 를 왜(Why) 써야되는지에 대한 궁금했는데, CROSS JOIN의 약자로 사용하고 있다는 것을 처음 알게 됨.

5. ARRAY와 STRUCT 구조의 차이점과 해당 데이터타입 구조의 데이터를 추출하기 위한 방법을 명확하게 알게 됨.

5. 느낀점

사소하지만 스킬적인 부분으로 새롭게 배웠던 부분은 따로 잘 정리해두어 업무에 바로 사용해볼 예정. 또한 데이터 탐색 방법으로 Funnel 사용하는데 있어, Open 퍼널과 Closed 퍼널 방식의 종류를 배울 수 있어 뿌듯했음.