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

1. 자료형: Array, Struct & 퍼널 분석

주요 학습 Point

• 기존 SQL 작성할 때 없던 개념: Array와 Struct 및 Unnest(Flatten) 개념파악

• Pivot 등 퍼널 분석 기본 쿼리 작성 ✍🏼 및 목적에 맞는 퍼널분석 방법론 비교

1-1. 자료형: Array & Struct

• 연습문제 1-4

  /* 연습문제 1
  - 테이블: array_exercises
  - Row 기준: 각 영화 Title 별
  - Column 기준: 장르 Unnest
  */
  
  select title
    , genre
  from advanced.array_exercises
  cross join unnest(genres) as genre -- genre가 array 형태임
  ;
  
    /* 연습문제 2
    - 테이블: array_exercises
    - Row 기준: 각 영화 Title 별
    - Column 기준: actor, character 
    */
  
    select title
        , actor
    from advanced.array_exercises
    cross join unnest(actors) as actor
  
    ;
  
    /* 연습문제 3
    - 테이블: array_exercises
    - Column 기준: title, actor, character, genre
    */
  
    select title
      , actor
      , genre
    from advanced.array_exercises
    cross join unnest(actors) as actor
    cross join unnest(genres) as genre
    limit 100
    ;
  
    /* 연습문제 4
    - 문제: app_logs 배열 풀기
    - 주어진 배열: event_params(>key, string_value or int_value)
    */
  
    select event_date
        , event_timestamp
        , event_name
        , param.key
        , param.value.string_value
        , param.value.int_value
        , user_id
        , user_pseudo_id
        , platform
    from advanced.app_logs
    cross join unnest(event_params) as param
    limit 10
    ;
  
  

1-2. Pivot 쿼리 작성

• Pivot 연습문제

  /* 연습문제 1
  - 테이블: orders
  - Row 기준: order_date
  - Column 기준: user_id 별
  - value 기준: 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 order_date
  order by order_date
  ;
  
  /* 연습문제 2
  - 테이블: orders
  - Row 기준: user_id
  - Column 기준: order_date 날짜 별
  - value 기준: sum(amount)
  */
  
  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 `2024-05-04`
    , sum(if(order_date = '2023-05-05', amount, 0)) as `2023-05-05`        
  from advanced.orders
  group by user_id
  ;
  
  /* 연습문제 3
  - 테이블: orders
  - Row 기준: user_id
  - Column 기준: order_date 날짜 별
  - value 기준: 주문 있으면 1 없으면 0 (Indicator)
  */
  
  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 `2024-05-04`
    , max(if(order_date = '2023-05-05', 1, 0)) as `2023-05-05`        
  from advanced.orders
  group by user_id
  ;
  
  /* 연습문제4
  - 테이블: app_logs
  - Pivot
  - where 기준: user_id = 32888, event_name: click_cart
  */
  
  with tmp as (
    select user_id
        , event_date
        , event_name
        , user_pseudo_id
        , param.key
        , param.value.string_value
        , param.value.int_value
    from advanced.app_logs
    cross join unnest(event_params) as param
    where 1=1
    and user_id = 32888
    and event_name = 'click_cart' 
    and key = 'food_id'
  )
  select int_value
  from tmp
    limit 10
  	  ;
  

1-3. 퍼널 분석

• 퍼널 연습문제

  /* 문제1 
  - 테이블: app_logs
  - 문제: 각 퍼널 유저수 집계 
  - where 기준: 2022-08-01 ~ 2022-08-18
  */
  
  WITH tmp 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 screen_name,
    CONCAT(event_name, '-', MAX(IF(param.key = 'firebase_screen', param.value.string_value, null))) AS event_name_with_screen
  FROM advanced.app_logs
  cross join  UNNEST(event_params) AS param
  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 tmp
  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
  - 테이블: app_logs
  - 문제: 일자별 퍼널 유저수 집계 
  - where 기준: 2022-08-01 ~ 2022-08-18
  */
  
  WITH tmp 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 screen_name,
    CONCAT(event_name, '-', MAX(IF(param.key = 'firebase_screen', param.value.string_value, null))) AS event_name_with_screen
  FROM advanced.app_logs
  cross join  UNNEST(event_params) AS param
  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 tmp
  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
  - 테이블: app_logs
  - 문제: 집계한 데이터 pivot
  - where 기준: 2022-08-01 ~ 2022-08-18
  */
  
  WITH tmp 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 screen_name,
    CONCAT(event_name, '-', MAX(IF(param.key = 'firebase_screen', param.value.string_value, null))) AS event_name_with_screen
  FROM advanced.app_logs
  cross join  UNNEST(event_params) AS param
  WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18'
  GROUP BY 1,2,3,4,5
  ), tmp2 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 tmp
  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
      , sum(if(step_number = 1, cnt, 0)) as `screen_view-welcome`
      , sum(if(step_number = 2, cnt, 0)) as `screen_view-home`
      , sum(if(step_number = 3, cnt, 0)) as `screen_view-food_category`
      , sum(if(step_number = 4, cnt, 0)) as `screen_view-restaurant`
      , sum(if(step_number = 5, cnt, 0)) as `screen_view-cart`
      , sum(if(step_number = 6, cnt, 0)) as `click_payment-cart`                
  from tmp2
  group by event_date
  order by 1
  ;
  
  

Q1. 질문사항

• pivot 쿼리 연습문제 2번에서, 모든 날짜를 기입하는 방식으로만 Pivot화 할 수 있는지 궁금합니다. 예를 들어, 날짜가 20개 있는 경우라면 일일이 날짜 기입하지 않고 일반화해서 pivot할 수 있는 방법이 있을지 여쭙습니다.