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

1) ARRAY, STRUCT 연습문제

1-1)

SELECT
  title,
  genre
FROM
  advanced.array_exercises
CROSS JOIN UNNEST (genres) as genre
ORDER BY title;

1-2)

SELECT
  title,
  actor.actor,
  actor.character
FROM
  advanced.array_exercises
CROSS JOIN UNNEST(actors) as actor
ORDER BY title;

1-3)

SELECT
  title,
  actor.actor,
  actor.character,
  genre
FROM
  advanced.array_exercises
CROSS JOIN UNNEST(genres) as genre
CROSS JOIN UNNEST(actors) as actor
ORDER BY 1,2,3;

1-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
WHERE
  event_date = '2022-08-01';

2) PIVOT 연습문제

2-1)

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

2-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
  1
ORDER BY
  1;

2-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
  1
ORDER BY
  1;

2-4)

SELECT
  event_date,
  event_timestamp,
  event_name,
  event_timestamp,
  user_pseudo_id,
  MAX(CASE WHEN event_param.key = 'firebase_screen' THEN event_param.value.string_value END) as firebase_screen,
  MAX(CASE WHEN event_param.key = 'food_id' THEN event_param.value.int_value END) as food_id,
  MAX(CASE WHEN event_param.key = 'session_id' THEN event_param.value.string_value END) as session_id,
FROM
  advanced.app_logs
CROSS JOIN UNNEST(event_params) as event_param
WHERE
  event_date = '2022-08-01'
GROUP BY ALL 

3) 퍼널 쿼리 연습 문제

WITH main as (
  SELECT
    event_date,
    concat(event_name,'-', event_param.value.string_value) AS event_name_with_screen,
    CASE
      WHEN event_name = 'screen_view' AND event_param.value.string_value = 'welcome' THEN 1
      WHEN event_name = 'screen_view' AND event_param.value.string_value = 'home' THEN 2
      WHEN event_name = 'screen_view' AND event_param.value.string_value = 'food_category' THEN 3
      WHEN event_name = 'screen_view' AND event_param.value.string_value = 'restaurant' THEN 4
      WHEN event_name = 'screen_view' AND event_param.value.string_value = 'cart' THEN 5
      WHEN event_name = 'click_payment' AND event_param.value.string_value = 'cart' THEN 6
    END AS step_num,
    count(distinct user_pseudo_id) AS cnt
  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_param.key = 'firebase_screen'
  AND event_name IN ("screen_view",'click_payment')
  GROUP BY 1,2,3
  HAVING step_num IS NOT NULL
)
SELECT
  event_date,
  SUM(IF(step_num = 1, cnt, 0)) AS `screen_view-welcome`,
  SUM(IF(step_num = 2, cnt, 0)) AS `screen_view-home`,
  SUM(IF(step_num = 3, cnt, 0)) AS `screen_view-food_category`,
  SUM(IF(step_num = 4, cnt, 0)) AS `screen_view-restaurant`,
  SUM(IF(step_num = 5, cnt, 0)) AS `screen_view-cart`,
  SUM(IF(step_num = 6, cnt, 0)) AS `click_payment-cart`
FROM
  main
GROUP BY
  1
ORDER BY
  event_date