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

연습 문제

(1) ARRAY, STRUCT 연습문제

1-1.

--1-1) array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요.
SELECT title
     , genre
  FROM advanced.array_exercises
     , unnest(genres) as genre

1-2.

--1-2) array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야 합니다.
select title
     , actor.actor
     , actor.character     
  from advanced.array_exercises
     , unnest(actors) as actor  

1-3.

--1-3) array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르(genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다.
select title
     , actor.actor as actor
     , actor.character as character
     , genre 
  from advanced.array_exercises
     , unnest(actors) as actor
     , unnest(genres) as genre 

1-4.

--1-4) 앱 로그 데이터(app_logs)의 배열을 풀어주세요
select user_id
     , event_date
     , event_name
     , user_pseudo_id
     , param.key as key
     , param.value.string_value as string_value
     , param.value.int_value as int_value
  from advanced.app_logs
     , unnest(event_params) as param     
 where event_date = '2022-08-01'

(2) PIVOT 연습문제

2-1.

--2-1) 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 advanced.orders
 group by order_date
 order by order_date

2-2.

--2-2) orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다.
select user_id
     , max(if(order_date = '2023-05-01',amount,0)) as `2023-05-01`
     , max(if(order_date = '2023-05-02',amount,0)) as `2023-05-02`
     , max(if(order_date = '2023-05-03',amount,0)) as `2023-05-03`
     , max(if(order_date = '2023-05-04',amount,0)) as `2023-05-04`
     , max(if(order_date = '2023-05-05',amount,0)) as `2023-05-05`
  from advanced.orders
 group by user_id
 order by user_id

2-3.

--2-3) orders 테이블에서 사용자별, 날짜별로 주문이 있따면 1, 없다면 0으로 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다.
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

2-4.

--2-4) 앱 로그 데이터 배열 PIVOT 하기
select user_id
     , event_date
     , event_name
     , user_pseudo_id
     , max(if(param.key = 'firebase_screen', param.value.string_value, null)) as firebase_screen
     , max(if(param.key = 'food_id', param.value.int_value, null)) as food_id
     , max(if(param.key = 'session_id', param.value.string_value, null)) as session_id
  from advanced.app_logs
     , unnest(event_params) as param
 where event_date = '2022-08-01'
 group by all 

(3) 퍼널 연습문제

3-1.

--3-1) 퍼널 별 유저 수 집계(일자별)
with base as
(
  select event_date     
       , event_name
       , event_timestamp     
       , user_id
       , user_pseudo_id
       , platform
       , max(if(param.key = 'firebase_screen', param.value.string_value, null)) as firebase_screen
    from advanced.app_logs
       , unnest(event_params) as param
   where event_date between '2022-08-01' and '2022-08-18'
   group by all
),
filter_event as 
(
  select 
    * except(event_name, firebase_screen)
    , concat(event_name, "-", firebase_screen) as event_name_with_screen      
    from base    
  where event_name in ('screen_view', 'click_payment')
)
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
 group by all
 having step_number is not null
 order by event_date

3-2.

--3-2) 퍼널 별 유저 수 집계 PIVOT
with base as
(
  select event_date     
       , event_name
       , event_timestamp     
       , user_id
       , user_pseudo_id
       , platform
       , max(if(param.key = 'firebase_screen', param.value.string_value, null)) as firebase_screen
    from advanced.app_logs
       , unnest(event_params) as param
   where event_date between '2022-08-01' and '2022-08-18'
   group by all
),
filter_event as 
(
  select 
    * except(event_name, firebase_screen)
    , concat(event_name, "-", firebase_screen) as event_name_with_screen      
    from base    
  where event_name in ('screen_view', 'click_payment')
),
daily_group 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
 group by all
 having step_number is not null
 order by event_date
)
select event_date
     , 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 daily_group
 group by all
 order by event_date