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

<PART 1> ARRAY, STRUCT 연습문제

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

-- 출제의도: 배열 UNNEST의 기본 형태를 사용할 수 있는가? 
SELECT 
  title
  , genre
FROM advanced.array_exercises
CROSS JOIN UNNEST(genres) AS genre
ORDER BY title;

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

-- 출제의도: 다중 배열 구조에서 UNNEST를 사용할 수 있는가? 
SELECT 
  title
  , actor.actor
  , actor.character
FROM advanced.array_exercises
CROSS JOIN UNNEST(actors) AS actor
ORDER BY title;

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

-- 출제의도: 여러 칼럼을 동시에 UNNEST할 수 있는가? 
SELECT 
  title
  , actor.actor
  , actor.character
  , genre
FROM advanced.array_exercises
CROSS JOIN UNNEST(actors) AS actor
CROSS JOIN UNNEST(genres) AS genre
ORDER BY title;

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

-- 출제의도: 다중 struct 구조의 데이터를 평면화하여 쿼리로 호출할 수 있는가? 
SELECT 
  user_id
  , event_date
  , event_name
  , user_pseudo_id
  , event.key
  , event.value.string_value
  , event.value.int_value
FROM advanced.app_logs
CROSS JOIN UNNEST(event_params) AS event
WHERE event_date = '2022-08-01';

<PART 2> PIVOT 연습문제

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

-- 출제의도: 집계 함수와 조건 함수를 결합하여 PIVOT 테이블을 만들 수 있는가? 
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 ALL
ORDER BY order_date;

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

-- 출제의도 : PIVOT 테이블 구성 시, 날짜 칼럼을 이용하여 시계열 방식을 구성할 수 있는가? 
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 ALL
ORDER BY user_id;

Q3. orders 테이블에서 사용자(user_id)별, 날짜(order_date)별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다.

-- 출제의도 : PIVOT 테이블 구성 시, 집계 함수로 MAX를 사용할 수 있는가? 
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 ALL
ORDER BY user_id;

Q4. user_id = 32888 이 카트 추가하기(click_cart)를 누를 때 어떤 음식 (food_id)을 담았는지 구해주세요. key 를 Column 으로 두고, string_value 나 int_value를 Column의 값으로 설정해서 풀어주세요.

-- 출제의도 : PIVOT 테이블을 앱로그 데이터에 사용하여, 조건문으로 개별 유저 데이터를 특정할 수 있는가?
WITH base AS(
SELECT 
  event_date
  , event_timestamp
  , event_name
  , user_id
  , 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
CROSS JOIN UNNEST(event_params) AS params
WHERE event_date = '2022-08-01'
GROUP BY ALL
)

SELECT 
  *
FROM base
WHERE event_name = 'click_cart'
  and user_id = 32888

-- 실행결과 : food_id = 1942

<PART 3> 퍼널 연습문제

-- 출제의도: 앱 로그 데이터에서 원하는 이벤트를 추출해, 퍼널 분석을 위한 전처리를 진행할 수 있는가?

-- step 1. UNNEST를 통한 base 데이터 준비 
WITH base AS(
SELECT 
  event_date
  , event_timestamp
  , event_name
  , event.key AS event_key
  , event.value.string_value AS event_string_value
  , event.value.int_value AS event_int_value
  , user_id
  , user_pseudo_id
  , platform
FROM advanced.app_logs
CROSS JOIN UNNEST(event_params) AS event
WHERE event_date BETWEEN '2022-08-01' AND '2022-08-22'
),

-- step 2. 필요한 퍼널 이벤트에만 step_number를 세팅하여 준비 
sorted_events AS(
SELECT 
  event_date
  , CONCAT(event_name, "-", event_string_value) AS event_name_with_screen
  , CASE WHEN event_name = 'screen_view' AND event_string_value = 'welcome' THEN 1
      WHEN event_name = 'screen_view' AND event_string_value = 'home' THEN 2
      WHEN event_name = 'screen_view' AND event_string_value = 'food_category' THEN 3
      WHEN event_name = 'screen_view' AND event_string_value = 'restaurant' THEN 4
      WHEN event_name = 'screen_view' AND event_string_value = 'cart' THEN 5
      WHEN event_name = 'click_payment' AND event_string_value = 'cart' THEN 6
      ELSE NULL END AS step_number
  , user_pseudo_id
FROM base
WHERE event_key = 'firebase_screen'
)

-- step 3. 최종 조회 쿼리
SELECT 
  event_date
  , event_name_with_screen
  , step_number
  , COUNT(DISTINCT user_pseudo_id) AS cnt
FROM sorted_events
WHERE step_number IS NOT NULL
GROUP BY ALL
ORDER BY event_date, step_number