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

SELECT [1, 2, 3, 4, 5] AS some_numbers
;
SELECT ARRAY<INT64>[1, 2, 3, 4, 5] AS some_numbers
;
SELECT GENERATE_ARRAY(1, 5, 1) AS some_numbers
;
SELECT [SAFE_OFFSET()]
;
SELECT (1, 2, 3) AS struct_test
;
SELECT STRUCT<hi INT64, hello INT64>(1, 2) AS struct_test
;

SELECT a.title, b AS genre
FROM workspace.array_exercises AS a
JOIN UNNEST(genres) AS b
;
SELECT a.title, b.actor, b.character
FROM workspace.array_exercises AS a
JOIN UNNEST(actors) AS b
;
SELECT a.title, b.actor, b.character, c AS genre
FROM workspace.array_exercises AS a
JOIN UNNEST(actors) AS b
JOIN UNNEST(genres) AS c
;
SELECT a.user_id, a.event_date, a.event_name, a.user_pseudo_id, b.key, b.value.string_value, b.value.int_value
FROM workspace.app_logs AS a
JOIN UNNEST(event_params) AS b 
;
SELECT key, string_value, count(distinct user_pseudo_id)
FROM 
  (
  SELECT a.user_id, a.event_date, a.event_name, a.user_pseudo_id, b.key, b.value.string_value, b.value.int_value
  FROM workspace.app_logs AS a
  JOIN UNNEST(event_params) AS b 
  )
WHERE event_name = 'screen_view'
GROUP BY ALL
;

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 workspace.orders
GROUP BY ALL
ORDER BY order_date
;
SELECT
  user_id,
  sum(if(order_date = '2023-05-01', amount, 0)) `2023-05-01`,
  sum(if(order_date = '2023-05-02', amount, 0)) `2023-05-02`,
  sum(if(order_date = '2023-05-03', amount, 0)) `2023-05-03`,
  sum(if(order_date = '2023-05-04', amount, 0)) `2023-05-04`,
  sum(if(order_date = '2023-05-05', amount, 0)) `2023-05-05`
FROM workspace.orders
GROUP BY ALL
ORDER BY user_id
;
SELECT
  user_id,
  if(sum(if(order_date = '2023-05-01', amount, 0)) > 0, 1, 0) `2023-05-01`,
  if(sum(if(order_date = '2023-05-02', amount, 0)) > 0, 1, 0) `2023-05-02`,
  if(sum(if(order_date = '2023-05-03', amount, 0)) > 0, 1, 0) `2023-05-03`,
  if(sum(if(order_date = '2023-05-04', amount, 0)) > 0, 1, 0) `2023-05-04`,
  if(sum(if(order_date = '2023-05-05', amount, 0)) > 0, 1, 0) `2023-05-05`
FROM workspace.orders
GROUP BY ALL
ORDER BY user_id
;

WITH events AS (
  SELECT
    event_date,
    event_timestamp,
    event_name,
    user_id,
    user_pseudo_id,
    platform,
    MAX(IF(b.key = "firebase_screen", b.value.string_value, NULL)) AS firebase_screen,
    MAX(IF(b.key = "session_id", b.value.string_value, NULL)) AS session_id
  FROM workspace.app_logs AS a
  JOIN UNNEST(event_params) AS b
  WHERE event_date >= '2022-08-01'
    AND event_date <  '2022-08-19'
  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 events
  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 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
;