Array, Struct 연습문제

-- genre unnest
select title, genre from `advanced.array_exercises` cross join UNNEST(genres) as genre;

-- actor unnest
select title, actor.actor, actor.character from `advanced.array_exercises` cross join UNNEST(actors) as actor;

-- actor, genre unnest
select title, actor.actor, actor.character, genre from `advanced.array_exercises` cross join UNNEST(actors) as actor cross join UNNEST(genres) as genre;

PIVOT 연습문제

-- 일자 별 사용자의 이벤트 발생 횟수
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;

-- 사용자 별 일일 이벤트 발생 횟수
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;

-- 사용자 별 일일 이벤트 발생 여부
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;

퍼널 연습문제

with data_event_name_with_screen_step as (
  with data_event_name_with_screen as (
    with base as (select 
    event_date,
    DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') as event_time,
    event_name,
    user_id,
    user_pseudo_id, 
    max(if(event_param.key = 'firebase_screen',event_param.value.string_value , null)) as firebase_screen
    from `advanced.app_logs` 
    cross join unnest(event_params) as event_param 
    where event_date between '2022-08-01' and '2022-08-18'
    group by all
    )
      select * except(event_name, firebase_screen), concat(event_name, '-', firebase_screen) as event_name_with_screen from base
  )

  select 
  COUNT(DISTINCT user_pseudo_id) AS cnt,
  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
  from data_event_name_with_screen
  group by all
  having step_number is not null
  order by event_date
)

-- 이벤트로 pivot
select 
event_date, 
sum(if(event_name_with_screen='screen_view-welcome', cnt, 0)) as `screen_view-welcome`,
sum(if(event_name_with_screen='screen_view-home', cnt, 0)) as `screen_view-home`,
sum(if(event_name_with_screen='screen_view-food_category', cnt, 0)) as `screen_view-food_category`,
sum(if(event_name_with_screen='screen_view-restaurant', cnt, 0)) as `screen_view-restaurant`,
sum(if(event_name_with_screen='screen_view-cart', cnt, 0)) as `screen_view-cart`,
sum(if(event_name_with_screen='click_payment-cart', cnt, 0)) as `click_payment-cart`
from data_event_name_with_screen_step
group by all
order by event_date
;

이전 부터 쿼리로 퍼널 분석하기 좋은 방법이 없을까 생각을 했었는데 방향성을 알게 되어 좋은 것 같습니다.