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

[ARRAY, STRUCT]

1. array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요.

SELECT
  title, #genres, 
  genre
FROM `advanced.array_excercises` ae
cross join unnest(genres) as genre;

2. array_exercies 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요 배우와 배역은 별도의 컬럼으로 나와야 합니다.

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

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

select
  title,
  actor.actor,
  actor.character,
  genre
from `advanced.array_excercises`

cross join unnest(actors) as actor
cross join unnest(genres) as genre;

4. 앱 로그 데이터(app_logs) 배열 풀기

select 
  user_id,
  event_date,
  event_name,
  user_pseudo_id,
  params.key, 
  params.value.string_value as string_value,
  params.value.int_value as int_value
from `advanced.app_logs`
cross join unnest(event_params) as params
where 
  event_date = "2022-08-01

[PIVOT]

1. orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT해주세요.

   날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다.

select
  order_date,
    # amount의 합
  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 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다

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;

orders 테이블에서 사용자(user_id)별, 날짜(order_date)별로 주문이 있다면 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;

4. user_id = 32888이 카트 추가하기(click_cart)를 누를때 어떤 음식(food_id)을 담았나요?

with base as (
  select
    -- * EXCEPT(event_params), # * except(컬럼) :컬럼을 제외하고 모두 다 보여줘
    -- param
    event_date,
    event_timestamp,
    event_name,
    user_id,
    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.string_value, null)) as food_id,
    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`
  cross join unnest(event_params) as param
  group by all
)

select
  user_id,
  event_date,
  count(user_id) as user_cnt,
  food_id
from base
where
 user_id = 32888 and event_name = "click_cart" 
group by all

[퍼널분석]

with base as (
  SELECT
    event_date,
    event_timestamp,
    event_name,
    user_id,
    user_pseudo_id,
    platform,
    max(if(event_param.key = "firebase_screen", event_param.value.string_value, null)) as firebase_screen,
    max(if(event_param.key = "session_id", event_param.value.string_value, null)) as session_id
  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
), filter_event as(
 select
 * except(event_name, firebase_screen, event_timestamp),
  concat(event_name, "-", firebase_screen) as event_name_with_screen,
  DATETIME(timestamp_micros(event_timestamp),'Asia/Seoul')AS event_datetime
from base
where
  event_name IN("screen_view", "click_payment")
),


 screen_view 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 screen_view
group by all
order by event_date