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

1. ARRAY, STRUCT 연습문제

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

select
  title,
  genre
from   
  `advanced.array_exercises`,
  unnest(genres) as genre

/*2) array_exercise 테이블에서 각 영화별로 배우와 배역을 보여주세요. 
배우와 배역은 별도의 칼럼으로 보여주세요*/

select 
  title,
  actor.actor,
  actor.character
from 
  `advanced.array_exercises`,
  unnest(actors) as actor

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

select 
  title,
  actor.actor,
  actor.character,
  genre
from 
  `advanced.array_exercises`
  ,unnest(actors) as actor
  ,unnest(genres) as genre
  
-- 문제의의도: UNNEST를 2번 연속 사용 가능하다
-- 데이터 중복은 CROSS JOIN으로 인한 어쩔 수 없는 이슈
-- SQL 실행순서 -> FROM -> JOIN -> SELECT
-- actors : ARRAY<STRUCT> -> UNNEST -> STRUCT
-- genres : ARRAY<STRING> -> UNNEST -> STRING

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

select
  event_date,
  datetime(timestamp_micros(event_timestamp),'Asia/Seoul') as event_timestamp,
  event_name,
  event_param.key as key,
  event_param.value.string_value as string_value,
  event_param.value.int_value as int_value,
  user_id
from 
  `advanced.app_logs`
  ,unnest(event_params) as event_param

-- event_params : ARRAY<STRUCT>
-- event_params.value : STRUCT<STRING, INT64>

2. PIVOT 연습문제

/*1) orders 테이블에서 유저별로 주문금액의 합계를 PIVOT해주세요. 
날짜를 행으로, user_id를 열로 만들어야합니다.
/*case when 사용한 풀이*/

select
  order_date,
  sum(case when user_id = 1 then amount else 0 end) as user_1,
  sum(case when user_id = 2 then amount else 0 end) as user_2,
  sum(case when user_id = 3 then amount else 0 end) as user_3
from 
  `advanced.orders`
group by 
  order_date
order by 
  order_date

/*if 사용한 풀이*/

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) orders 테이블에서 날짜별로 유저들의 주문금액의 합계를 PIVOT 해주세요. 
user_id를 행으로, 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

-- ANY_VALUE: 그룹화할 대상 중 임의의 값(NULL 제외)를 표시한다.
-- 나머지 값이 NULL이거나 확정적으로 값이 나올것이라 예상될 때 사용 

/*3)orders 테이블에서 사용자별, 날짜별 주문이 있다면 1 없다면 0으로 PIVOT 해주세요. 
user_id를 행으로 order_date를 칼럼으로 만들고 주문횟수에 상관없이 1로 처리합니다.*/

select 
  user_id,
  MAX(if(order_date = '2023-05-01' and amount is not null,1,0)) as `2023-05-01`,
  MAX(if(order_date = '2023-05-02' and amount is not null,1,0)) as `2023-05-02`,
  MAX(if(order_date = '2023-05-03' and amount is not null,1,0)) as `2023-05-03`,
  MAX(if(order_date = '2023-05-04' and amount is not null,1,0)) as `2023-05-04`,
  MAX(if(order_date = '2023-05-05' and amount is not null,1,0)) as `2023-05-05`
FROM 
  `advanced.orders`
group by 
  user_id
order by 
  user_id;
  
 /* 횟수를 구해달라고 하는 경우*/
 -- MAX를 SUM으로 바꾸면 된다. 
 
/*4) APP_LOG PIVOT 실습하기*/

select 
  user_id,
  event_date,
  datetime(timestamp_micros(event_timestamp), 'Asia/Seoul') as event_timestamp,
  event_name,
  max(if(params.key = 'firebase_screen',params.value.string_value,NULL)) as firebase_screen,
  max(if(params.key = 'food_id',params.value.int_value,NULL)) as food_id,
  max(if(params.key = 'session_id',params.value.string_value,NULL)) as session_id
from  
  `advanced.app_logs`
  , unnest(event_params) as params
where 
  event_date = '2022-08-01'
group by 
  1,2,3,4

3. 퍼널 분석 연습문제

/* 
일자별 퍼널별 유저 수 집계 
기간: 2022-08-01 ~ 2022-08-18
사용할 event => event_name과 firebase_screen의 값을 concat하여 사용 

1. screen_view-welcome
2. screen_view-home
3. screen_view-food_category
4. screen_view-restaurant
5. screen_view-cart
6. click_payment-cart 
-- screen_view-welcome에서 user_id는 NULL임 -> user_pseudo_id는 NULL이 아님
-- welcome에서 home으로 넘어가며 로그인을 하여서 그럼 

-- WHERE: FROM 절에서 바로 필터링을 하고 싶은 조건을 지정
-- HAVING: GROUP BY 후에 나오는 집계 결과에 대한 조건을 지정 */

with base as (
  select 
    event_date,
    datetime(timestamp_micros(event_timestamp),'Asia/Seoul') as event_timestamp,
    event_name,
    user_pseudo_id,
    max(if(params.key = 'firebase_screen',params.value.string_value,NULL)) as 
firebase_screen,
    max(if(params.key = 'food_id',params.value.int_value,NULL)) as food_id,
    max(if(params.key = 'session_id',params.value.string_value,NULL)) as session_id
  from  
    `advanced.app_logs`
    , unnest(event_params) as params
  where 
    event_date between '2022-08-01' and '2022-08-18'
  group by  
    1,2,3,4
) 
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 end as step_number,
    count(distinct user_pseudo_id) as cnt
from ( 
select 
  *,
  case when     
    event_name in ('screen_view','click_payment') then 
    concat(event_name,'-',firebase_screen) else null end as event_name_with_screen
from 
  base
)
group by 
  1,2,3
having 
  step_number is not null   
order by  
  event_date, step_number