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

1. 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)을 보여주세요. (배우와 배역은 별도의 컬럼으로 나와야 합니다)

-- 동일한 단어에 대해 선택할 수 있는 함수 : cmd+d
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
  , actor.character
  , genre
FROM
    advanced.array_exercises
    CROSS JOIN UNNEST(actors) AS actor
    CROSS JOIN UNNEST(genres) AS genre
;

4) 앱 로그 데이터(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
, platform
FROM
  advanced.app_logs
  CROSS JOIN UNNEST(event_params) AS event_param
;

2. PIVOT

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

SELECT 
  order_date
, SUM(IF(user_id = 1, amount, NULL)) AS `user_id_1`
, SUM(IF(user_id = 2, amount, NULL)) AS `user_id_2`
, SUM(IF(user_id = 3, amount, NULL)) AS `user_id_3`
FROM
  advanced.orders
GROUP BY 1
ORDER BY 1
;

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

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

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', 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 1
;

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

WITH app_logs_info AS (
    SELECT
        user_id
      , event_name
      , 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
    GROUP BY
        1, 2
)

SELECT
    food_id
FROM
    app_logs_info
WHERE
      user_id = 32888
  AND event_name = 'click_cart'
;

3. Funnel

1) 일자별, 이벤트별 집계

WITH app_logs_info AS (
SELECT
  user_id
, event_date
, event_timestamp
, event_name
, user_pseudo_id
, event_param.key
, 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
    event_date BETWEEN '2022-08-01' AND '2022-08-18'
AND event_name IN ('screen_view', 'click_payment')
GROUP BY ALL
)

, add_step_number AS (
SELECT
  event_date
, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_date_time
, user_id
, user_pseudo_id
, CONCAT(event_name, '-', firebase_screen) AS event_name_with_screen 
, CASE CONCAT(event_name, '-', firebase_screen)
    WHEN 'screen_view-welcome' THEN 1
    WHEN 'screen_view-home' THEN 2
    WHEN 'screen_view-food_category' THEN 3
    WHEN 'screen_view-restaurant' THEN 4
    WHEN 'screen_view-cart' THEN 5
    WHEN 'click_payment-cart' THEN 6
  ELSE NULL
  END AS step_number
FROM
  app_logs_info
)

SELECT
  event_date
, step_number
, event_name_with_screen
, COUNT(DISTINCT user_pseudo_id) AS user_cnt
FROM
  add_step_number
WHERE
  step_number IS NOT NULL
GROUP BY 1, 2, 3
ORDER BY 1, 2
;

2) 집계 데이터 PIVOT

WITH app_logs_info AS (
SELECT
  user_id
, event_date
, event_timestamp
, event_name
, user_pseudo_id
, event_param.key
, 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
  event_date BETWEEN '2022-08-01' AND '2022-08-18'
GROUP BY ALL
)

, add_step_number AS (
SELECT
  event_date
, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_date_time
, user_id
, user_pseudo_id
, CONCAT(event_name, '-', firebase_screen) AS event_name_with_screen 
, CASE CONCAT(event_name, '-', firebase_screen)
    WHEN 'screen_view-welcome' THEN 1
    WHEN 'screen_view-home' THEN 2
    WHEN 'screen_view-food_category' THEN 3
    WHEN 'screen_view-restaurant' THEN 4
    WHEN 'screen_view-cart' THEN 5
    WHEN 'click_payment-cart' THEN 6
  ELSE NULL
  END AS step_number
FROM
  app_logs_info
WHERE
  event_name IN ('screen_view', 'click_payment')
)
, agg_user_cnt AS (
SELECT
  event_date
, step_number
, event_name_with_screen
, COUNT(DISTINCT user_pseudo_id) AS user_cnt
FROM
  add_step_number
WHERE
  step_number IS NOT NULL
GROUP BY 1, 2, 3
ORDER BY 1, 2
)

SELECT 
  event_date
, MAX(IF(step_number = 1, user_cnt, NULL)) AS `screen_view-welcome`
, MAX(IF(step_number = 2, user_cnt, NULL)) AS `screen_view-home`
, MAX(IF(step_number = 3, user_cnt, NULL)) AS `screen_view-food_category`
, MAX(IF(step_number = 4, user_cnt, NULL)) AS `screen_view-restaurant`
, MAX(IF(step_number = 5, user_cnt, NULL)) AS `screen_view-cart`
, MAX(IF(step_number = 6, user_cnt, NULL)) AS `click_payment-cart`
FROM 
  agg_user_cnt
GROUP BY 1
ORDER BY 1
;