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

1. ARRAY, STRUCT 연습문제

1-1

SELECT  title, movie_genres
FROM advanced.array_exercises
CROSS JOIN UNNEST(genres) AS movie_genres
LIMIT 100

1-2

SELECT title,
      actor.actor,
      actor.character
FROM advanced.array_exercises
CROSS JOIN UNNEST(actors) AS actor

1-3

SELECT title, actors.actor, actors.character, genres
FROM advanced.array_exercises
CROSS JOIN UNNEST(actors) AS actors
CROSS JOIN UNNEST(genres) AS genres
WHERE actor = 'ChrisEvans'

1-4

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   

2. PIVOT 연습문제

2-1

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

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

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

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

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

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