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

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

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

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

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

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

SELECT  
  title,
  actor.actor as actor,
  actor.character as character,
  genre
FROM advanced.array_exercises
CROSS JOIN UNNEST(genres) as genre
CROSS JOIN UNNEST(actors) as actor
ORDER BY 1, 2 desc

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

SELECT
  user_id,
  event_date,
  event_name,
  user_pseudo_id,  
  evnent_parm.key AS key,
  evnent_parm.value.string_value AS string_value,
  evnent_parm.value.int_value AS int_value,
FROM advanced.app_logs, UNNEST(event_params) AS evnent_parm

PIVOT

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

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

SELECT
  user_id,
  COALESCE(SUM(IF(order_date = '2023-05-01', amount, null)),0) AS `2023-05-01`,
  COALESCE(SUM(IF(order_date = '2023-05-02', amount, null)),0) AS `2023-05-02`,
  COALESCE(SUM(IF(order_date = '2023-05-03', amount, null)),0) AS `2023-05-03`,
  COALESCE(SUM(IF(order_date = '2023-05-04', amount, null)),0) AS `2023-05-04`,
  COALESCE(SUM(IF(order_date = '2023-05-05', amount, null)),0) AS `2023-05-05`,
FROM advanced.orders
GROUP BY user_id
ORDER BY user_id;

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' AND order_id is not null, 1, 0)) AS `2023-05-01`,
  MAX(IF(order_date = '2023-05-02' AND order_id is not null, 1, 0)) AS `2023-05-02`,
  MAX(IF(order_date = '2023-05-03' AND order_id is not null, 1, 0)) AS `2023-05-03`,
  MAX(IF(order_date = '2023-05-04' AND order_id is not null, 1, 0)) AS `2023-05-04`,
  MAX(IF(order_date = '2023-05-05' AND order_id is not null, 1, 0)) AS `2023-05-05`,

FROM advanced.orders

GROUP BY user_id
ORDER BY user_id;

퍼널 분석

1) 퍼널 별 유저 수 집계
데이터 : 2022-08-01 ~ 2022-08-18

WITH funnel_data_raw AS (
SELECT  
  event_date,
  event_timestamp,
  event_name,
  user_id,
  user_pseudo_id,
  MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null)) AS screen_name,
  CONCAT(event_name, '-', MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null))) AS event_name_with_screen
FROM advanced.app_logs, UNNEST(event_params) AS pr
WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18'
GROUP BY 1,2,3,4,5
)
SELECT
  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 funnel_data_raw
WHERE event_name IN ('screen_view', 'click_payment')
  AND screen_name IN ('welcome', 'home', 'food_category', 'restaurant', 'cart')
GROUP BY 1,2
ORDER BY 2
;

2) 퍼널 별 유저 수 집계 (일자별)

WITH funnel_data_raw AS (
SELECT  
  event_date,
  event_timestamp,
  event_name,
  user_id,
  user_pseudo_id,
  MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null)) AS screen_name,
  CONCAT(event_name, '-', MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null))) AS event_name_with_screen
FROM advanced.app_logs, UNNEST(event_params) AS pr
WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18'
GROUP BY 1,2,3,4,5
)
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 funnel_data_raw
WHERE event_name IN ('screen_view', 'click_payment')
  AND screen_name IN ('welcome', 'home', 'food_category', 'restaurant', 'cart')
GROUP BY 1,2,3
ORDER BY 1,3
;

3) 집계한 데이터를 PIVOT

-- 연구 및 추가적 작업 필요

배운점

• 대용량의 복잡한 데이터를 의도에 따라 구조화 및 시각화하는 기초 지식을 습득함.

   

• 과업이 복잡해지면 쿼리가 깔끔하게 정리가 되지 않거나 스텝이 꼬이는 등의 어려움을 겪기도 함.

• 각 연습문제에 대해 '왜이 쿼리를 실행하는 것이며, 그 결과값을 통해 어떤 추가적인 Action을 취할 수 있는가' 실무 관점으로 고민해보는 계기가 되었음.