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

1. ARRAY, STRUCT 연습 문제

1-1)

ARRAY로 된 genres 컬럼을 평면화해 genre로 alias를 붙이고, title과 CROSS JOIN하였습니다.

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

1-2)

배우와 배역은 actor컬럼에 ARRAY<STRUCT<actor STRING, character STRING>> 타입으로 저장되어 있습니다. 우선 ARRAY를 평면화하고 .(dot)을 사용해 컬럼을 구분하였습니다.

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

1-3)

처음에는 UNNEST가 익숙하지 않아 단순히 두 데이터의 title을 키 값으로 CTE_ACTOR 테이블에 CTE_GENRE 테이블을 LEFT JOIN하자는 생각이 들었습니다.

WITH CTE_ACTOR AS (
  SELECT 
    title
    , actor.actor AS actor
    , actor.character AS character
  FROM advanced.array_exercises
  CROSS JOIN UNNEST(actors) AS actor
),
CTE_GENRE AS (
  SELECT
    title
    , genre
  FROM advanced.array_exercises
  CROSS JOIN UNNEST(genres) AS genre
)
SELECT
  A.title
  , A.actor
  , A.character
  , B.genre
FROM CTE_ACTOR A
JOIN CTE_GENRE B
  ON A.title = B.title

강의를 듣고는 CROSS JOIN을 두 번 해보았습니다. 배열이 각각의 행으로 풀리니 이를 두 번 실행해 결과를 얻을 수 있었고 쿼리도 훨씬 간단해졌습니다 😀

WITH CTE_ACTOR AS (
  SELECT 
    title
    , actor.actor AS actor
    , actor.character AS character
  FROM advanced.array_exercises
  CROSS JOIN UNNEST(actors) AS actor
),
CTE_GENRE AS (
  SELECT
    title
    , genre
  FROM advanced.array_exercises
  CROSS JOIN UNNEST(genres) AS genre
)
SELECT
  A.title
  , A.actor
  , A.character
  , B.genre
FROM CTE_ACTOR A
JOIN CTE_GENRE B
  ON A.title = B.title

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
, UNNEST(event_params) event_param
-- WHERE event_date='2022-08-02'

2. PIVOT 연습문제

2-1)

order_date를 기준으로 SUM을 사용해 유저 별 주문금액 합계를 구하고, 요구사항에 따라 빈 값은 0으로 채웠습니다.

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

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 user_id

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 user_id

2-4)

SELECT
  user_id
  , event_date
  , event_name
  , event_timestamp
  , user_pseudo_id
  , 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 `string_value`
  , MAX(IF(event_param.key='session_id', event_param.value.string_value, null)) `int_value`
FROM advanced.app_logs
, UNNEST(event_params) event_param
WHERE event_date='2022-08-25'
  AND event_name='click_cart'
GROUP BY ALL

3. 퍼널 쿼리 연습 문제

처음엔 CASE 함수를 바로 떠올리지 못해 순서를 저장한 테이블을 만들고 JOIN해 결과를 구했습니다.

WITH CTE_STEP AS (
  SELECT 'screen_view-welcome' `event_name_with_screen`, 1 `step_number`
  UNION ALL
  SELECT 'screen_view-home', 2 
  UNION ALL  
  SELECT 'screen_view-food_category', 3 
  UNION ALL
  SELECT 'screen_view-restaurant', 4 
  UNION ALL
  SELECT 'screen_view-cart', 5
  UNION ALL
  SELECT 'click_payment-cart', 6
),
CTE_EVENT AS (
  SELECT 
    event_date
    , event_name_with_screen
    , COUNT(DISTINCT user_pseudo_id) `cnt`
  FROM (
    SELECT 
        event_date
        , user_pseudo_id
        , CONCAT(event_name, '-', event_param.value.string_value) `event_name_with_screen`
      FROM advanced.app_logs
      , UNNEST(event_params) `event_param`
      WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18'
      AND event_name IN ('screen_view', 'click_payment')
      AND event_param.key = 'firebase_screen'
      AND event_param.value.string_value IN ('welcome', 'home', 'food_category', 'restaurant', 'cart')
  )
  GROUP BY event_date
    , event_name_with_screen
)
SELECT 
  A.event_date
  , A.event_name_with_screen
  , B.step_number
  , A.cnt
FROM CTE_EVENT A
JOIN CTE_STEP B 
  ON A.event_name_with_screen = B.event_name_with_screen
ORDER BY event_date, step_number

이후 강의를 참고해 피벗 테이블을 만들고 CASE문을 사용한 쿼리입니다.

WITH base AS (
  SELECT 
    event_date
    , event_timestamp
    , event_name
    , user_pseudo_id
    , MAX(IF(event_param.key='firebase_screen', event_param.value.string_value, null)) `firebase_screen`
    , MAX(IF(event_param.key='session_id', event_param.value.string_value, null)) `session_id`
  FROM advanced.app_logs
  , UNNEST(event_params) `event_param`
  WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18'
    AND event_name IN ('screen_view', 'click_payment')
  GROUP BY ALL
), filter_event_and_concat_evnet_and_screen AS (
  SELECT 
    * EXCEPT(event_name, firebase_screen, event_timestamp)
    , CONCAT(event_name, '-', firebase_screen) `event_name_with_screen`
    , DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') `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)
FROM filter_event_and_concat_evnet_and_screen
GROUP BY ALL
HAVING step_number IS NOT NULL
ORDER BY event_date

추가로 결과 테이블을 피벗 테이블로 만들기 위한 쿼리입니다.

SELECT
  event_date
  , MAX(IF(event_name_with_screen='screen_view-welcome', cnt, 0)) `screen_view-welcome`
  , MAX(IF(event_name_with_screen='screen_view-home', cnt, 0)) `screen_view-home`
  , MAX(IF(event_name_with_screen='screen_view-food_category', cnt, 0)) `screen_view-food_category`
  , MAX(IF(event_name_with_screen='screen_view-restaurant', cnt, 0)) `screen_view-restaurant`
  , MAX(IF(event_name_with_screen='screen_view-cart', cnt, 0)) `screen_view-cart`
  , MAX(IF(event_name_with_screen='click_payment-cart', cnt, 0)) `click_payment-cart`
FROM result
GROUP BY event_date
ORDER BY event_date

✔ 느낀 점

• 쿼리를 작성하기 전 자료형을 파악하자.

• 요구사항이 헷갈릴 땐 한번에 결과를 내려고 하지말고 단계별로 해결하자.

✔ 새롭게 알게된 점

• cmd + D를 여러 번 해서 같은 단어를 동시에 바꿀 수 있다.

• group by ALL을 사용하면 SELECT의 컬럼을 반복해서 적지 않아도 된다.

• 구글 스프레드 시트에서도 예약 시간을 사용하면 갱신되는 데이터를 제공할 수 있다(예약 시간 주의).

• 구글 스프레드 시트로 간단한 시각화를 해볼 수 있다.

퍼널 분석에 대한 부분을 재밌게 수강했습니다. 퍼널 분석은 개념 정도만 알고 있었는데 퍼널을 정의하는 것에서 부터 피벗 테이블을 만들고, 시각화하기까지 실무에서의 큰 흐름을 배운 것 같아요. 팀에서 로그 설계를 진행 중인데 완료되면 간단한 퍼널 분석을 시도해보겠습니다💪🏻