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

1. ARRAY, STRUCT

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

-- 2. array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요.
-- arrary 안의 struct 영화/배우 꺼내기
SELECT
  title
  , ac.actor
  , ac.character
FROM `advanced.array_exercises`
  CROSS JOIN UNNEST(actors) AS ac -- actor.actor도 가능

-- 3. array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character), 장르(genre) 출력
-- actors, genres 각각 꺼내기 (2번 조인)
SELECT
-- title, actor, character, genre
  title
  , ac.actor
  , ac.character
  , genre
FROM `advanced.array_exercises`
  CROSS JOIN UNNEST(actors) AS ac
  CROSS JOIN UNNEST(genres) AS genre

-- 4. 앱로그 데이터(app_logs)의 배열을 풀어주세요.
-- event_params 꺼내기
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' -- test

2. PIVOT

-- 1. orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIOVT해주세요.
-- 날짜(order_date)를 행, user_id를 열, sum(amount)
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 1
ORDER BY 1

-- 2. orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount) 합계를 PIVOT
-- user_id 행, order_date 열, sum(주문 금액), '-' 포함 날짜 별칭은 backtick(``) 활용
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 user_id
ORDER BY user_id

-- 3. orders 테이블에서 사용자별 날짜별로 주문이 있다면 1, 없으면 0으로 PIOVT
-- user_id 행, order_date 열, if(날짜, 1, 0)
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 user_id
ORDER BY user_id

3. 퍼널

3-1. 퍼널별 유저 수

-- 1. 퍼널별 유저 수 : 2022-08-01 ~ 2022-08-18, 오픈 퍼널, COUNT(DISTINCT user_pseudo_id)

WITH funnels AS (
-- 조건/컬럼 필터링, event_params UNNEST, event name + screen 문자열 컬럼 병합
SELECT
  CONCAT(event_name, '-', event_param.value.string_value) AS event_name_with_screen
  , COUNT(DISTINCT user_pseudo_id) AS cnt
FROM `advanced.app_logs`
  CROSS JOIN UNNEST(event_params) AS event_param
WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18'
AND event_name IN ('screen_view', 'click_payment')
AND event_param.key = 'firebase_screen'
AND event_param.value.string_value NOT IN ('food_detail', 'search', 'search_result')
GROUP BY ALL
)

SELECT 
  event_name_with_screen
  -- step_number 생성
  , (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
  , cnt
FROM funnels
ORDER BY step_number

3-2. 퍼널별 유저 수(일자별)

-- 2. 퍼널별 유저 수(일자별) : 2022-08-01 ~ 2022-08-18, 오픈 퍼널, COUNT(DISTINCT user_pseudo_id)

WITH funnels AS (
-- 조건/컬럼 필터링, event_params UNNEST, event name + screen 문자열 컬럼 병합
SELECT
  -- 일자별
  event_date
  , CONCAT(event_name, '-', event_param.value.string_value) AS event_name_with_screen
  , COUNT(DISTINCT user_pseudo_id) AS cnt
FROM `advanced.app_logs`
  CROSS JOIN UNNEST(event_params) AS event_param
WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18'
AND event_name IN ('screen_view', 'click_payment')
AND event_param.key = 'firebase_screen'
AND event_param.value.string_value NOT IN ('food_detail', 'search', 'search_result')
GROUP BY ALL
)

SELECT 
  event_date
  , event_name_with_screen
  -- step_number 생성
  , (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
  , cnt
FROM funnels
ORDER BY event_date, step_number -- 정렬 변경

3-3. 퍼널별 유저 수(일자별 PIVOT)

-- 3. 퍼널별 유저 수(일자별) 집계 PIVOT

WITH funnels AS (
-- 조건/컬럼 필터링, event_params UNNEST, event name + screen 문자열 컬럼 병합
SELECT
  -- 일자별
  event_date
  , CONCAT(event_name, '-', event_param.value.string_value) AS event_name_with_screen
  , COUNT(DISTINCT user_pseudo_id) AS cnt
FROM `advanced.app_logs`
  CROSS JOIN UNNEST(event_params) AS event_param
WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18'
AND event_name IN ('screen_view', 'click_payment')
AND event_param.key = 'firebase_screen'
AND event_param.value.string_value NOT IN ('food_detail', 'search', 'search_result')
GROUP BY ALL
)

SELECT 
  event_date
  -- event PIVOT
  , 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 funnels
GROUP BY ALL
ORDER BY event_date