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

ARRAY, STRUCT 연습 문제

1. 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요.

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

2. 각 영화(title)별 배우(actor)와 배역(character)을 보여주세요.(별도 칼럼)

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

3. 각 영화(title)별로 배우(actor),배역(character),장르(genre)를 출력하세요.

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

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

SELECT 
  event_date,
  event_timestamp,
  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

PIVOT 연습 문제

1. 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT (날짜를 행, user_id를 열)

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. 날짜별로 유저들의 주문금액의 합계를 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

3. 사용자별, 날짜별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요 (user_id를 행, order_date를 열)

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

4. user_id = 32888이 카트 추가하기(click_cart)를 누를때 어떤 음식(food_id)을 담았나요?

SELECT 
  user_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
FROM advanced.app_logs
CROSS JOIN UNNEST(event_params) AS event_param
WHERE 
  user_id = 32888
  AND event_name = 'click_cart'
GROUP BY 
  user_id, event_timestamp

-- 카트에 담은 음식(food_id): 1559, 1942

퍼널 쿼리 연습 문제

일자별 이벤트 집계 후 PIVOT

WITH funnel_data AS (
  SELECT 
    *,
    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
  FROM (
    SELECT 
      event_date,
      event_timestamp,
      user_pseudo_id,
      concat(event_name, '-', event_param.value.string_value) AS event_name_with_screen
    FROM advanced.app_logs
    CROSS JOIN UNNEST(event_params) AS event_param
    WHERE event_param.key = 'firebase_screen'
  ) AS unnested_app_logs
  WHERE event_name_with_screen IN (
    'screen_view-welcome',
    'screen_view-home',
    'screen_view-food_category',
    'screen_view-restaurant',
    'screen_view-cart',
    'click_payment-cart'
  )
)SELECT 
  event_date,
  COUNT(IF(step_number = 1, user_pseudo_id, NULL)) AS `screen_view-welcome`,
  COUNT(IF(step_number = 2, user_pseudo_id, NULL)) AS `screen_view-home`,
  COUNT(IF(step_number = 3, user_pseudo_id, NULL)) AS `screen_view-food_category`,
  COUNT(IF(step_number = 4, user_pseudo_id, NULL)) AS `screen_view-restaurant`,
  COUNT(IF(step_number = 5, user_pseudo_id, NULL)) AS `screen_view-cart`,
  COUNT(IF(step_number = 6, user_pseudo_id, NULL)) AS `click_payment-cart`
FROM funnel_data
GROUP BY event_date
ORDER BY event_date