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

ARRAY, STRUCT 연습문제

1)

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

2)

SELECT
  title,
  actors,
  actors[SAFE_OFFSET(0)].actor AS frist_actor, 
  actors[SAFE_OFFSET(0)].character AS first_character,
  actors[SAFE_OFFSET(1)].actor AS second_actor,
  actors[SAFE_OFFSET(1)].character AS second_character
FROM advanced.array_exercises AS ae

3)

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

4)

SELECT
  event_Date,
  event_timestamp,
  event_name,
  event_params,
  user_id,
  event_param.key AS key,
  event_param.value AS value,
  event_param.value.string_value AS string_value,
  event_param.value.int_value AS int_value
FROM advanced.app_logs_temp
CROSS JOIN UNNEST(event_params) AS event_param
WHERE event_date = '2022-08-01'

PIVOT 연습문제

1)

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

2)

SELECT 
    user_id,
    SUM(IF(order_date = '2023-05-01', sum_of_amount, 0)) AS `2023-05-01`,
    SUM(IF(order_date = '2023-05-02', sum_of_amount, 0)) AS `2023-05-02`,
    SUM(IF(order_date = '2023-05-03', sum_of_amount, 0)) AS `2023-05-03`,
    SUM(IF(order_date = '2023-05-04', sum_of_amount, 0)) AS `2023-05-04`,
    SUM(IF(order_date = '2023-05-05', sum_of_amount, 0)) AS `2023-05-05`
FROM (
  SELECT 
      user_id,
      order_date,
      SUM(amount) AS sum_of_amount
  FROM advanced.orders
  GROUP BY user_id,order_date)
GROUP BY user_id

3)

SELECT 
  user_id, 
  MAX(IF(order_date = '2023-05-01' AND user_id IS NOT NULL, 1, 0)) AS `2023-05-01`,
  MAX(IF(order_date = '2023-05-02' AND user_id IS NOT NULL, 1, 0)) AS `2023-05-02`,
  MAX(IF(order_date = '2023-05-03' AND user_id IS NOT NULL, 1, 0)) AS `2023-05-03`,
  MAX(IF(order_date = '2023-05-04' AND user_id IS NOT NULL, 1, 0)) AS `2023-05-04`,
  MAX(IF(order_date = '2023-05-05' AND user_id IS NOT NULL, 1, 0)) AS `2023-05-05`
FROM advanced.orders 
GROUP BY user_id

4)

SELECT 
    event_date,
    event_timestamp,
    event_name,
    user_id,
    user_pseudo_id,
    MAX(IF(param.key = 'firebase_screen',  param.value.string_value, NULL)) AS firebase_screen,
    MAX(IF(param.key = 'food_id',  param.value.string_value, NULL)) AS food_id, 
    MAX(IF(param.key = 'food_id',  param.value.int_value, NULL)) AS food_id2,
    MAX(IF(param.key = 'session_id',  param.value.string_value, NULL)) AS session_id
FROM advanced.app_logs_temp
CROSS JOIN UNNEST(event_params) AS param
WHERE event_date = '2022-08-01'
GROUP BY ALL 

퍼널분석

WITH Funnel AS (SELECT 
    event_date,
    event_timestamp,
    event_name,
    user_id,
    user_pseudo_id,
    MAX(IF(param.key = 'firebase_screen',  param.value.string_value, NULL)) AS firebase_screen,
    MAX(IF(param.key = 'food_id',  param.value.string_value, NULL)) AS food_id, 
    MAX(IF(param.key = 'food_id',  param.value.int_value, NULL)) AS food_id2,
    MAX(IF(param.key = 'session_id',  param.value.string_value, NULL)) AS session_id
FROM advanced.app_logs_temp
CROSS JOIN UNNEST(event_params) AS param
WHERE event_date = '2022-08-01'
GROUP BY ALL )

SELECT 
  event_date,
  COUNT(user_id) AS user_cnt
FROM Funnel
WHERE
  event_name = 'click_cart'
GROUP BY event_date;