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

1. ARRAY, STRUCT 연습문제

문제 1)

SELECT
  movie_id,
  title,
  genre

FROM advanced.array_exercises
CROSS JOIN UNNEST(genres) AS genre;

문제 2)

SELECT
  title,
  aa.actor,
  aa.character

FROM advanced.array_exercises AS ae
CROSS JOIN UNNEST(actors) AS aa;

문제 3)

SELECT
  title,
  aa.actor,
  aa.character,
  gg AS genre

FROM advanced.array_exercises AS ae
CROSS JOIN UNNEST(actors) AS aa
CROSS JOIN UNNEST(genres) AS gg;

문제 4)

SELECT 
  user_id,
  event_date,
  event_name,
  user_pseudo_id,
  event_param.key,
  event_param.value.string_value,
  event_param.value.int_value

FROM `advanced.app_logs`
CROSS JOIN UNNEST(event_params) AS event_param;

2. PIVOT 연습문제

문제 1)

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 )

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

문제 3)

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;

문제 4)

WITH app_order_raw AS (
SELECT
  user_id,
  event_date,
  event_name,
  user_pseudo_id,
  pr.key,
  pr.value.string_value,
  pr.value.int_value

FROM advanced.app_logs, UNNEST(event_params) AS pr
WHERE event_date = '2022-08-01'
)

SELECT
  user_id,
  event_date,
  event_name,
  user_pseudo_id,
  MAX(IF(key = 'firebase_screen', string_value, null)) AS firebase_screen,
  MAX(IF(key = 'food_id', int_value, null)) AS food_id,
  MAX(IF(key = 'session_id', string_value, null)) AS session_id,

FROM app_order_raw
GROUP BY user_id, event_date, event_name, user_pseudo_id;

3. 퍼널 연습문제

WITH base AS (
  SELECT
    event_date,
    event_timestamp,
    event_name,
    user_id,
    user_pseudo_id,
    platform,
    -- event_param
    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
  CROSS JOIN
    UNNEST(event_params) AS event_param
  WHERE
    event_date BETWEEN '2022-08-01' AND '2022-08-18'
  GROUP BY ALL
), filter_event_and_concat_event_and_screen AS (

  SELECT
    * EXCEPT(event_name, firebase_screen, event_timestamp),
    concat(event_name, '-', firebase_screen) AS event_name_with_screen,
    datetime(timestamp_micros(event_timestamp), 'Asia/Seoul') AS event_datetime
  FROM
    base
  WHERE
    event_name IN ('screen_view', 'click_payment' )
)

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 1, 3


SELECT
  event_date,
  MAX(IF(event_name_with_screen ="screen_view-welcome", cnt, NULL)) AS `screen_view-welcome`,
  MAX(IF(event_name_with_screen ="screen_view-home", cnt, NULL)) AS `screen_view-home`,
  MAX(IF(event_name_with_screen ="screen_view-food_category", cnt, NULL)) AS `screen_view-food_category`,
  MAX(IF(event_name_with_screen ="screen_view-restaurant", cnt, NULL)) AS `screen_view-restaurant`,
  MAX(IF(event_name_with_screen ="screen_view-cart", cnt, NULL)) AS `screen_view-cart`,
  MAX(IF(event_name_with_screen ="click_payment-cart", cnt, NULL)) AS `click_payment-cart`
FROM main
GROUP BY ALL
ORDER BY
  all;