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

1-4. Array, Struct 연습문제 (1~4번)

연습문제1

• 문제

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

     

• 쿼리

select 
  title,
  genres2
from advanced.array_exercises
cross join unnest(genres) as genres2
;

• 결과

   

연습문제2

• 문제

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

• 쿼리

select 
  title,
  actor.actor,
  actor.character
from advanced.array_exercises
cross join unnest(actors) as actor
order by movie_id
;

• 결과

   

연습문제3

• 문제

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

• 쿼리

select 
  title, 
  actor.actor,
  actor.character
  genre2 # array<string>
from advanced.array_exercises
cross join unnest (actors) as actor
cross join unnest (genres) as genre
order by movie_id
;

• 결과

연습문제4

• 문제

  • 앱 로그 데이터(app_logs)의 배열을 풀어주세요

• 쿼리

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
where event_date = "2022-08-01"
limit 10
;

• 결과

   

1-9. 퍼널 SQL 쿼리 작성하기

연습문제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 1 
  ;

• 결과

연습문제2

• 문제

  • orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다

• 쿼리

select 
  user_id,
  # 컬럼의 이름을 지정할때, 영어를 제외하고 backtick(`)사용 
  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 1 
;

• 결과

연습문제3

• 문제

  • 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 1 
;

• 결과

연습문제4

• 문제

  • 앱 로그 데이터 배열 PIVOT하기 - user_id = 32888이 카트 추가하기(click_cart)를 누를때 어떤 음식(food_id)을 담았나요?

• 쿼리

select 
  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
where event_date = "2022-08-01" and user_id = 32888 and event_name = "click_cart"
group by all
limit 100
; 

• 결과

퍼널 분석

• 문제

  • step_number별 count, 일자별 퍼널별 유저 수 쿼리

• 쿼리

with base as (select 
    event_date,
    event_timestamp,
    event_name,
    user_id,
    user_pseudo_id,
    max(if(event_param.key = 'firebase_screen', event_param.value.string_value, null)) as firebase_screen,
    -- max(if(event_param.key = 'food_id', event_param.value.int_value, null)) as food_id,  
    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 1=1
    and event_date between "2022-08-01" and "2022-08-18"
  group by all
  ), filter_event_and_concat_event_and_acreen AS (
  -- event_name + screen
  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 1=1
    and event_name in ("screen_view", "click_payment"))

# 일자별로 퍼널별 유저 수
select 
  -- distinct(event_name_with_screen)
  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_and_concat_event_and_acreen
group by all
having step_number is not null
order by event_date
;

• 결과