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

1. ARRAY, STRUCT

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

SELECT title, genre
FROM advanced.array_exercises
CROSS JOIN UNNEST(genres) as genre

-- array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주기. 단, 배우와 배역은 별도의 컬럼으로 나와야 함.
 
SELECT 
title,
actor.actor,
actor.character
FROM `advanced.array_exercises`
CROSS JOIN UNNEST(actors) as actor

-- array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르(genre) 출력하기 한 행에 배우, 배역, 장르가 모두 표시되어야 된다.

SELECT 
title, 
actor.actor,
actor.character,
genre
FROM `advanced.array_exercises`
CROSS JOIN UNNEST(actors) as actor
CROSS JOIN UNNEST(genres) as genre 

-- 앱 로그 데이터 (app_logs)의 배열 풀기
SELECT user_id, 
       event_date,
       event_name, 
       user_pseudo_id,
       event_param.key,
       event_param.value.string_value,
       event_param.value.int_value
FROM advanced.app_logs
CROSS JOIN UNNEST(event_params) as event_param
limit 500

2. 데이터 PIVOT

-- 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 order_date asc;

--  orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 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 ASC

-- orders 테이블에서 사용자(user_id)별, 날짜(order_date)별로  주문이 있다면 1, 없다면 0으로 피벗하기. user_id를 행으로, order_date를 열로 만들고 주문이 많아도 1로 처리.

SELECT user_id,
sum(if(order_date = '2023-05-01',1, 0)) AS `2023-05-01`,
sum(if(order_date = '2023-05-02',1, 0)) AS `2023-05-02`,
sum(if(order_date = '2023-05-03',1, 0)) AS `2023-05-03`,
sum(if(order_date = '2023-05-04',1, 0)) AS `2023-05-04`,
sum(if(order_date = '2023-05-05',1, 0)) AS `2023-05-05`
FROM advanced.orders
GROUP BY user_id 
ORDER BY user_id ASC

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

WITH app_pivot 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 app_pivot
WHERE user_id = 32888 AND event_name = 'click_cart'
GROUP BY user_id,event_date, event_name, user_pseudo_id
ORDER BY event_date ASC;

3. 퍼널 분석

-- 일자별 이벤트 별 집계형태를 PIVOT 형태로 전환하기

WITH param_pivot AS (
SELECT 
event_date,
event_timestamp,
event_name, 
user_id,
user_pseudo_id,
MAX(IF(key = 'firebase_screen', event_param.value.string_value,NULL)) AS `firebase_screen`,
MAX(IF(key = 'food_id',event_param.value.int_value,NULL)) AS `food_id`,
MAX(IF(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),

event_table AS (SELECT 
* EXCEPT(event_name,firebase_screen),
CONCAT(event_name,'_', firebase_screen) AS event_name_with_screen,
FROM param_pivot
WHERE event_name IN ('screen_view','click_payment')
GROUP BY all
ORDER BY event_date ASC )
,
final 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 = 'clik_payment_cart' THEN 6 
      ELSE NULL END ) AS step_number,
COUNT( DISTINCT user_pseudo_id) AS cnt
FROM event_table
GROUP BY event_date, event_name_with_screen
HAVING step_number IS NOT NULL
ORDER BY event_date)

SELECT event_date,
SUM(IF(event_name_with_screen = 'screen_view_welcome', cnt, 0)) AS `screen_view_welcom`,
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 final
GROUP BY event_date
ORDER BY event_date ASC;