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

1.ARRAY, STRUCT 연습문제

/* 
UNNEST를 사용하는 이유 : 중첩된 데이터를 평평하게 만들어 집계 및 분석을 쉽게 하기 위해

UNNEST된 결과를 사용하여 분석을 실행 : 1.프로그래밍 언어 선호도, 2.지역별 언어 선호도
분석을 통해 Action Itme을 도출 : 프로그래밍 강좌를 제공한다면 선호하는 언어 순으로 영상 제작 등
*/
SELECT
  name,
  pref_lang,
  hometown
FROM example_data
CROSS JOIN UNNEST(preferred_language) AS pref_lang;
# UNNEST란 장바구니(배열)에 있는 과일(배열의 값)을 모두 다 꺼내는 것
/*
연습문제 1
UNNEST된 결과를 사용하여 분석을 실행 : 1.영화 장르 선호도
분석을 통해 Action Itme을 도출 : 영화 제작사라면 어떤 장르가 선호되는 것을 보고 영화 제작
*/
SELECT
  title,
  genre
FROM advanced.array_exercises
CROSS JOIN UNNEST(genres) AS genre;
/*
연습문제 2
UNNEST된 결과를 사용하여 분석을 실행 : X
분석을 통해 Action Itme을 도출 : X
*/
SELECT
  title,
  actor.actor,
  actor.character
FROM advanced.array_exercises
CROSS JOIN UNNEST(actors) AS actor
/*
연습문제 3
UNNEST된 결과를 사용하여 분석을 실행 : 1.배우의 영화 장르 선호도
분석을 통해 Action Itme을 도출 : 영화 제작시 배우의 장르 선호도 확인 후 
*/
SELECT
  title,
  actor.actor,
  actor.character,
  genre
FROM advanced.array_exercises ,UNNEST(actors) AS actor, UNNEST(genres) AS genre
/*
연습문제 4
*/
WITH base AS (
  SELECT
    user_id,
    event_date,
    event_name,
    user_pseudo_id,
    event_param.key AS key,
    -- event_param.value AS value,
    event_param.value.string_value,
    event_param.value.int_value
  FROM advanced.app_logs AS al
  CROSS JOIN UNNEST(event_params) AS event_param
  WHERE
    1=1
    AND event_date = '2022-08-01'
)

SELECT
  event_date,
  event_name,
  COUNT(DISTINCT user_id) AS cnt
FROM base
GROUP BY
  ALL
ORDER BY
  cnt DESC

2.PIVOT 연습문제

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

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;

SELECT
  order_id,
  order_date,
  user_id,
  IF(order_date = '2023-05-01', amount, NULL) AS `2023-05-01`,
  IF(order_date = '2023-05-02', amount, NULL) AS `2023-05-02`,
  IF(order_date = '2023-05-03', amount, NULL) AS `2023-05-03`,
  IF(order_date = '2023-05-04', amount, NULL) AS `2023-05-04`,
  IF(order_date = '2023-05-05', amount, NULL) AS `2023-05-05`
FROM advanced.orders;

SELECT
   user_id,
   # amount 대신 1이라고 표시. IF 문 안에 TRUE 일 때의 값이 항상 특정 컬럼이 아니라 1이라고 할 수도 있음(유무에 따라서)
   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;

WITH base AS (
  SELECT
    # * EXCEPT(event_params), # * EXCEPT(컬럼) : 컬럼을 제외하고 모두 다 보여줘!
    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.int_value, NULL)) AS food_id2,
    MAX(IF(param.key = "session_id", param.value.string_value, NULL)) AS session_id,
  FROM advanced.app_logs
  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 base
WHERE
  event_name = "click_cart"
GROUP BY
  event_date

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 = "session_id", event_param.value.string_value, NULL)) AS session_id
  FROM advanced.app_logs
  CROSS JOIN UNNEST(event_params) AS event_param
  WHERE
    1=1
    AND event_date BETWEEN "2022-08-01" AND "2022-08-18"
  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 base
  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
  ELSE NULL
  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
ORDER BY
  event_date