[인프런 빅쿼리 빠짝스터디 1주차] ARRAY, STRUCT 연습 문제/ PIVOT 연습문제/ 퍼널 쿼리 연습문제

1. ARRAY, STRUCT 연습 문제

연습문제 1

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

연습문제 2

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

연습문제 3

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

연습문제 4

SELECT
  user_id,
  event_date,
  event_name,
  user_pseudo_id,
  event_param.key,
  event_param.value.string_value,
  event_param.value.int_value,
  event_timestamp,
  platform,
FROM advanced.app_logs
CROSS JOIN UNNEST(event_params) as event_param

2. PIVOT 연습문제

연습문제 1

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

연습문제 2

SELECT 
  user_id,
  MAX(IF(order_date = "2023-05-01", sum_amount, 0)) as `2023-05-01`,
  MAX(IF(order_date = "2023-05-02", sum_amount, 0)) as `2023-05-02`,
  MAX(IF(order_date = "2023-05-03", sum_amount, 0)) as `2023-05-03`,
  MAX(IF(order_date = "2023-05-04", sum_amount, 0)) as `2023-05-04`,
  MAX(IF(order_date = "2023-05-05", sum_amount, 0)) as `2023-05-05`
FROM (
  SELECT user_id, order_date, SUM(amount) as sum_amount
  FROM advanced.orders
  GROUP BY user_id, order_date
  ORDER BY user_id, order_date
)
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

3. 퍼널 쿼리 연습 문제

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.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
), base2 as (
SELECT
  *,
  CONCAT(event_name, "-", firebase_screen) as event_screen
FROM base
WHERE event_name IN ("screen_view", "click_payment")
)
SELECT
  event_screen,
  event_date,
  CASE
    WHEN event_screen = "screen_view-welcome" THEN 1
    WHEN event_screen = "screen_view-home" THEN 2
    WHEN event_screen = "screen_view-food_category" THEN 3
    WHEN event_screen = "screen_view-restaurant" THEN 4
    WHEN event_screen = "screen_view-cart" THEN 5
    WHEN event_screen = "click_payment-cart" THEN 6
  ELSE NULL
  END as step_number,
  COUNT(DISTINCT user_pseudo_id) as cnt
FROM base2
GROUP BY ALL
HAVING step_number is not NULL
ORDER BY event_date

PIVOT 적용

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.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
), base2 as (
SELECT
  *,
  CONCAT(event_name, "-", firebase_screen) as event_screen
FROM base
WHERE event_name IN ("screen_view", "click_payment")
), base3 as (
SELECT
  event_screen,
  event_date,
  CASE
    WHEN event_screen = "screen_view-welcome" THEN 1
    WHEN event_screen = "screen_view-home" THEN 2
    WHEN event_screen = "screen_view-food_category" THEN 3
    WHEN event_screen = "screen_view-restaurant" THEN 4
    WHEN event_screen = "screen_view-cart" THEN 5
    WHEN event_screen = "click_payment-cart" THEN 6
  ELSE NULL
  END as step_number,
  COUNT(DISTINCT user_pseudo_id) as cnt
FROM base2
GROUP BY ALL
HAVING step_number is not NULL
ORDER BY event_date
)
SELECT
  event_date,
  MAX(IF(base3.event_screen ="screen_view-welcome", cnt, NULL)) AS screen_view_welcome,
  MAX(IF(base3.event_screen ="screen_view-home", cnt, NULL)) AS 	screen_vie_home,
  MAX(IF(base3.event_screen ="screen_view-food_category", cnt, NULL)) AS screen_view_food_category,
  MAX(IF(base3.event_screen ="screen_view-restaurant", cnt, NULL)) AS screen_view_restaurant,
  MAX(IF(base3.event_screen ="screen_view-cart", cnt, NULL)) AS screen_view_cart
FROM base3
GROUP BY ALL
ORDER BY event_date

배운점

1. 날짜를 사용할 때 백틱(`)을 사용해야 한다는 점

2. PIVOT 관련 쿼리를 처음 짜봤는데, GROUP BY로 먼저 그룹화한 뒤 사용하는 방법으로는 풀겠는데, 바로 PIVOT하는 방법은 아직 익숙하지 않아서 더 연습해야겠다. 구현 과정이 아직 머릿속에서 굴러가지 않는 느낌이었다.

3. 처음 쿼리를 짤 때보다 연습하면서 WITH 구문에 익숙해진 것 같다.

4. 풀고 나서 해설을 보니, EXCEPT 문을 사용하는 것을 까먹었다. 몇 개만 빼고 select 하는 경우 유용할 듯

5. 구문 오류 시 어떤 오류인지 아직 파악이 안되어 있어 강의에서 말씀하신 것처럼 하나하나 정리해놔야겠다.