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

1. ARRAY, STRUCT 연습문제

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

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

*UNNEST(ARRAY_Column) = UNNEST(배열)

(2) array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요.

배우와 배역은 별도의 컬럼으로 나와야 합니다.

SELECT
  title,
  actor.actor,
  actor.character
FROM advanced.array_exercises AS ae
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 AS ae
, UNNEST(actors) AS actor
, UNNEST(genres) AS genre

*연속해서 CROSS JOIN UNNEST 사용 가능

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

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
, UNNEST(event_params) AS event_param
-- WHERE
--   event_date = "2022-08-01"

*실습 시, 파티션 사용 필요

2. PIVOT 연습문제

(1) orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT 해주세요.

날짜(order_date)를 행(row)으로, user_id를 열(column)으로 만들어야 합니다.

SELECT 
  order_date,
  MAX(IF(user_id = 1, sum_of_amount, 0)) AS user_1,
  MAX(IF(user_id = 2, sum_of_amount, 0)) AS user_2,
  MAX(IF(user_id = 3, sum_of_amount, 0)) AS user_3
FROM (
  SELECT
    order_date,
    user_id,
    SUM(amount) AS sum_of_amount
  FROM advanced.orders
  GROUP BY order_date, user_id
  ORDER BY order_date
  )
GROUP BY order_date
ORDER BY order_date

*첫번째 풀이 내 집계 함수 사용 시, GROUP BY 잊지않기

*ctrl+d 사용

(2) orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요.

user_id를 행(row)으로, order_date를 열(column)으로 만들어야 합니다.

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

(3) orders 테이블에서 사용자(user_id)별, 날짜(order_date)별로 주문이 있다면 1, 없다면 0으로 해주세요.

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 user_id
ORDER BY user_id

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

WITH base AS (  
SELECT
  event_date,
  event_timestamp,
  event_name,
  user_id,
  user_pseudo_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,
  MAX(IF(event_param.key = 'session_id', event_param.value.string_value, null)) AS session_id
FROM advanced.app_logs
, UNNEST(event_params) AS event_param
GROUP BY ALL
)

SELECT
  food_id
FROM base
WHERE 
  user_id = 32888
  AND event_name = 'click_cart'
GROUP BY food_id

3. 퍼널 분석 연습문제

(1) 각 퍼널의 유저 수를 집계

데이터 : 2022-08-01 ~ 2022-08-18

WITH base AS(
  SELECT 
    event_date,
    event_timestamp,
    event_name,
    user_id,
    user_pseudo_id,
    platform,
    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,
    MAX(IF(event_param.key = "session_id", event_param.value.int_value, NULL)) AS session_id
  FROM `advanced.app_logs`
  CROSS JOIN UNNEST(event_params) AS event_param
  GROUP BY ALL
), filter_event_and_concat_event_and_screen AS(
  SELECT
    * EXCEPT(event_name, firebase_screen),
    CONCAT(event_name, "-", firebase_screen) AS event_name_with_screen,
    DATETIME(TIMESTAMP_MICROS(event_timestamp), "Asia/Seoul") AS event_datetime
  FROM base
  WHERE     
  event_date BETWEEN "2022-08-01" AND "2022-08-18"
    AND
    event_name IN ("screen_view", "click_payment") 
    AND
    firebase_screen IN ("welcome", "home", "food_category", "restaurant", "cart")
)

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
    ELSE NULL
  END AS step_number,
  COUNT(DISTINCT user_pseudo_id) AS cnt
FROM filter_event_and_concat_event_and_screen
GROUP BY ALL
HAVING step_number IS NOT NULL
ORDER BY event_date, step_number