인프런 커뮤니티 질문&답변

1. ARRAY, STRUCT

1)

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

2)

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

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'

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

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)

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

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

4)

WITH base AS (
  SELECT
    user_id,
    event_date,
    event_name,
    user_pseudo_id,
    event_param.key AS key,
    event_param.value.string_value AS string_value,
    event_param.value.int_value AS int_value
  FROM advanced.app_logs
  CROSS JOIN UNNEST(event_params) AS event_param
)

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 base
WHERE 
 user_id = 32888
 AND event_name = 'click_cart'
GROUP BY ALL

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

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