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

ARRAY, STRUCT 연습문제

--#1
SELECT
  title,
  genre
FROM `advanced.array_exercises`
CROSS JOIN UNNEST(genres) AS genre;

--#2
SELECT
  title,
  actor_info.actor as actor,
  actor_info.character as character
FROM `advanced.array_exercises`
CROSS JOIN UNNEST(actors) as actor_info;

--#3
SELECT
  title,
  actor_info.actor as actor,
  actor_info.character as character,
  genre
FROM `advanced.array_exercises`
CROSS JOIN UNNEST(actors) as actor_info
CROSS JOIN UNNEST(genres) as genre;


--#4
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`
CROSS JOIN UNNEST(event_params) as event_param;

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-05`,
  SUM(IF(order_date="2023-05-04",amount,0)) AS `2023-05-05`,
FROM advanced.orders
GROUP BY user_id
ORDER BY user_id;

#3
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
SELECT 
  user_id
 ,event_date
 ,event_name
 ,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
 CROSS JOIN UNNEST(event_params) as event_param
 WHERE event_name = "click_cart"
       AND event_date = "2022-08-01"
       AND user_id = 32888
 GROUP BY user_id,event_date,event_name,user_pseudo_id

FUNNEL 연습문제

WITH base_tab AS (
  SELECT 
    event_date,
    event_name,
    user_id,
    user_pseudo_id,
    event_timestamp,
    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_tab 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_tab
  WHERE
    event_name IN ('screen_view','click_payment')
),
event_cnt AS (
  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_tab
  GROUP BY ALL
  HAVING step_number IS NOT NULL
  ORDER BY event_date
)
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 event_cnt 
GROUP BY ALL 
ORDER BY event_date