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

김해리님의 프로필 이미지
김해리

작성한 질문수

BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)

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

작성

·

33

·

수정됨

0

1. ARRAY, STRUCT 연습 문제

# 1. array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요.
# 쿼리를 작성하는 목표, 확인할 지표: title, genre
# 쿼리 계산 방법: UNNEST
# 데이터의 기간: X
# 사용할 테이블: advanced.array_exercises
# JOIN KEY: X
# 데이터 특징: genres는 배열임

SELECT
  title,
  genre
FROM advanced.array_exercises
CROSS JOIN UNNEST(genres) AS genre
ORDER BY
  title
# 2. array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야 합니다.
# 쿼리를 작성하는 목표, 확인할 지표: title, actors.actor, actors.character
# 쿼리 계산 방법: UNNEST
# 데이터의 기간: X
# 사용할 테이블: advanced.array_exercises
# JOIN KEY: X
# 데이터 특징: actors는 actor, character로 구성된 struct이고, actor, character은 배열임

SELECT
  title,
  a.actor,
  a.character
FROM advanced.array_exercises
CROSS JOIN UNNEST(actors) AS a
ORDER BY
  title
# 3. array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르(genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다.
# 쿼리를 작성하는 목표, 확인할 지표: title, actors.actor, actors.character, genres
# 쿼리 계산 방법: UNNEST
# 데이터의 기간: X
# 사용할 테이블: advanced.array_exercises
# JOIN KEY: X
# 데이터 특징: 
-- actors는 actor, charactor로 구성된 struct이고, actor, charactor은 배열임
-- genres는 배열임

SELECT
  title,
  a.actor,
  a.character,
  genre
FROM advanced.array_exercises
CROSS JOIN UNNEST(actors) AS a
CROSS JOIN UNNEST(genres) AS genre
ORDER BY
  title
# 4. 앱 로그 데이터(app_logs)의 배열을 풀어주세요
# 쿼리를 작성하는 목표, 확인할 지표: app_logs 데이터 평면화
# 쿼리 계산 방법: UNNEST
# 데이터의 기간: X
# 사용할 테이블: advanced.app_logs
# JOIN KEY: X
# 데이터 특징: 
-- event_params는 key, value로 구성된 struct이고,
-- key는 배열이며,
-- value는 string_value, int_value로 구성된 struct이고,
-- string_value, int_value 배열임

SELECT
  user_id,
  event_date,
  event_name,
  user_pseudo_id,
  ep.key,
  ep.value.string_value,
  ep.value.int_value
FROM advanced.app_logs
CROSS JOIN UNNEST(event_params) AS ep

 

2. PIVOT 연습 문제

# 1. orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT해주세요. 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다.
# 쿼리를 작성하는 목표, 확인할 지표: user_id 별 amount PIVOT
# 쿼리 계산 방법: PIVOT
# 데이터의 기간: X
# 사용할 테이블: advanced.orders
# JOIN KEY: X
# 데이터 특징: 원본 테이블에 order_date, user_id, amount가 열로 저장되어 있음

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
# 2. orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다.
# 쿼리를 작성하는 목표, 확인할 지표: order_date 별 SUM(amount)
# 쿼리 계산 방법: PIVOT
# 데이터의 기간: X
# 사용할 테이블: advanced.orders
# JOIN KEY: X
# 데이터 특징: 원본 테이블에 order_date, user_id, amount가 열로 저장되어 있음

SELECT
  user_id,
  SUM(IF(order_date = '2023-05-01', amount, 0)),
  SUM(IF(order_date = '2023-05-02', amount, 0)),
  SUM(IF(order_date = '2023-05-03', amount, 0)),
  SUM(IF(order_date = '2023-05-04', amount, 0)),
  SUM(IF(order_date = '2023-05-05', amount, 0))
FROM advanced.orders
GROUP BY
  user_id
ORDER BY
  user_id
# 3. orders 테이블에서 사용자(user_id)별, 날짜(order_date)별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다.
# 쿼리를 작성하는 목표, 확인할 지표: user_id 별 order_date에 주문했는지 안 했는지 출력
# 쿼리 계산 방법: PIVOT
# 데이터의 기간: X
# 사용할 테이블: advanced.orders
# JOIN KEY: X
# 데이터 특징: 원본 테이블에 order_date, user_id가 열로 저장되어 있음

SELECT
  user_id,
  MAX(IF(order_date = '2023-05-01', 1, 0)),
  MAX(IF(order_date = '2023-05-02', 1, 0)),
  MAX(IF(order_date = '2023-05-03', 1, 0)),
  MAX(IF(order_date = '2023-05-04', 1, 0)),
  MAX(IF(order_date = '2023-05-05', 1, 0))
FROM advanced.orders
GROUP BY
  user_id
ORDER BY
  user_id
# 4. user_id = 32888이 카트 추가하기(click_cart)를 누를때 어떤 음식(food_id)을 담았나요?
# 쿼리를 작성하는 목표, 확인할 지표: user_id = 32888의 click_cart 할 때 food_id
# 쿼리 계산 방법: PIVOT
# 데이터의 기간: X
# 사용할 테이블: advanced.app_logs
# JOIN KEY: X
# 데이터 특징: 
-- event_params-> key/value-> string_value/int_value
-- key: firebase_screen/food_id/session_id

SELECT
  user_id,
  event_date,
  event_name,
  event_timestamp,
  user_pseudo_id,
  MAX(IF(key = 'firebase_screen', string_value, NULL)) AS firevase_screen,
  MAX(IF(key = 'food_id', int_value, NULL)) AS food_id,
  MAX(IF(key = 'session_id', string_value, NULL)) AS session_id
FROM (
  SELECT
    user_id,
    event_date,
    event_name,
    event_timestamp,
    user_pseudo_id,
    event_param.key AS key,
    event_param.value.string_value AS string_value,
    event_param.value.int_value AS int_value
  FROM advanced.app_logs
  CROSS JOIN UNNEST(event_params) AS event_param
  WHERE
    user_id = 32888
    AND event_name = 'click_cart'
)
GROUP BY ALL
ORDER BY
  event_date

 

3. 퍼널 쿼리 연습 문제

# 1. 퍼널 별 유저 수 집계
# 쿼리를 작성하는 목표, 확인할 지표: 퍼널 별 유저 수 집계
# 쿼리 계산 방법: UNNEST -> event_name/string_value, event_date에 조건 설정 -> user_pseudo_id를 DISTINCT하게 COUNT
# 데이터의 기간: 2022-08-01 ~ 2022-08-18
# 사용할 테이블: advanced.app_logs
# JOIN KEY: X
# 데이터 특징:
-- screen name의 경우 event_params.key = 'firebase_screen'인 event_params.value.string_value를 추출

SELECT
  CONCAT(event_name, '-', string_value) AS event_name_with_screen,
  CASE
    WHEN event_name = 'screen_view' AND string_value = 'welcome' THEN 1
    WHEN event_name = 'screen_view' AND string_value = 'home' THEN 2
    WHEN event_name = 'screen_view' AND string_value = 'food_category' THEN 3
    WHEN event_name = 'screen_view' AND string_value = 'restaurant' THEN 4
    WHEN event_name = 'screen_view' AND string_value = 'cart' THEN 5
    WHEN event_name = 'click_payment' AND string_value = 'cart' THEN 6
  END
  AS step_number,
  COUNT(DISTINCT(user_pseudo_id)) AS cnt
FROM (
  SELECT
    event_date,
    event_name,
    event_param.value.string_value AS string_value,
    user_pseudo_id
  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 = 'screen_view' AND event_param.value.string_value = 'welcome')
      OR (event_name = 'screen_view' AND event_param.value.string_value = 'home')
      OR (event_name = 'screen_view' AND event_param.value.string_value = 'food_category')
      OR (event_name = 'screen_view' AND event_param.value.string_value = 'restaurant')
      OR (event_name = 'screen_view' AND event_param.value.string_value = 'cart')
      OR (event_name = 'click_payment' AND event_param.value.string_value = 'cart')
    )
)
GROUP BY ALL
ORDER BY step_number
# 2. 일자 별 퍼널 별 유저 수 집계
# 쿼리를 작성하는 목표, 확인할 지표: 일자별 퍼널 별 유저 수 집계
# 쿼리 계산 방법: UNNEST -> event_name/string_value, event_date에 조건 설정 -> user_pseudo_id를 DISTINCT하게 COUNT
# 데이터의 기간: 2022-08-01 ~ 2022-08-18
# 사용할 테이블: advanced.app_logs
# JOIN KEY: X
# 데이터 특징:
-- screen name의 경우 event_params.key = 'firebase_screen'인 event_params.value.string_value를 추출

SELECT
  event_date,
  CONCAT(event_name, '-', string_value) AS event_name_with_screen,
  CASE
    WHEN event_name = 'screen_view' AND string_value = 'welcome' THEN 1
    WHEN event_name = 'screen_view' AND string_value = 'home' THEN 2
    WHEN event_name = 'screen_view' AND string_value = 'food_category' THEN 3
    WHEN event_name = 'screen_view' AND string_value = 'restaurant' THEN 4
    WHEN event_name = 'screen_view' AND string_value = 'cart' THEN 5
    WHEN event_name = 'click_payment' AND string_value = 'cart' THEN 6
  END
  AS step_number,
  COUNT(DISTINCT(user_pseudo_id)) AS cnt
FROM (
  SELECT
    event_date,
    event_name,
    event_param.value.string_value AS string_value,
    user_pseudo_id
  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 = 'screen_view' AND event_param.value.string_value = 'welcome')
      OR (event_name = 'screen_view' AND event_param.value.string_value = 'home')
      OR (event_name = 'screen_view' AND event_param.value.string_value = 'food_category')
      OR (event_name = 'screen_view' AND event_param.value.string_value = 'restaurant')
      OR (event_name = 'screen_view' AND event_param.value.string_value = 'cart')
      OR (event_name = 'click_payment' AND event_param.value.string_value = 'cart')
    )
)
GROUP BY ALL
ORDER BY event_date
# 2-1. 일자 별 퍼널 별 유저 수 집계 PIVOT하기

SELECT
  event_date,
  MAX(IF(event_name_with_screen='screen_view-welcome', cnt, NULL)) AS `screen_view-welcome`,
  MAX(IF(event_name_with_screen='screen_view-home', cnt, NULL)) AS `screen_view-home`,
  MAX(IF(event_name_with_screen='screen_view-food_category', cnt, NULL)) AS `screen_view-food_category`,
  MAX(IF(event_name_with_screen='screen_view-restaurant', cnt, NULL)) AS `screen_view-restaurant`,
  MAX(IF(event_name_with_screen='screen_view-cart', cnt, NULL)) AS `screen_view-cart`,
  MAX(IF(event_name_with_screen='click_payment-cart', cnt, NULL)) AS `click_payment-cart`,
FROM (
  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
)
GROUP BY ALL
ORDER BY event_date
sqlGoogle-Analyticsfirebasegoogle-sheetsbigquery

답변 1

0

인프런 AI 인턴님의 프로필 이미지
인프런 AI 인턴

안녕하세요, 인프런 AI 인턴입니다. 김해리님.

BigQuery에서 ARRAY, STRUCT와 같은 복잡한 데이터 구조를 사용하는 것은 실제 데이터 분석에서 매우 중요한 부분입니다. 이러한 SQL 기능을 이해하면 데이터를 좀 더 유연하게 처리할 수 있게 됩니다. ARRAY와 STRUCT, 그리고 PIVOT을 다루는 연습들은 데이터를 여러 관점에서 분석할 수 있는 능력을 키워줍니다. 아래는 질문에서 언급하신 쿼리와 관련하여 참고할 수 있는 유사한 질문과 답변 링크들입니다:

  1. 구글 스프레드시트 사용 시 데이터 로그 설계에 대한 질문

    • 이 질문은 데이터 로그를 설계하고 사용하는 방법에 대한 논의를 포함하고 있습니다. 특히 ARRAY와 STRUCT와 같은 복합 유형의 데이터를 어떻게 처리할지를 이해하는 데 유용할 것입니다.

  2. 퍼널 SQL 쿼리 작성하기 - 오류 해결 사례

    • 퍼널 쿼리를 작성할 때 발생할 수 있는 일반적인 문제와 그 해결 방안을 다루고 있습니다. 퍼널 분석을 수행하면서 도움이 될 만한 정보가 담겨 있습니다.

  3. 퍼널별 유저들의 새로운 세션 시작 횟수의 총합 _ 윈도우 함수 마지막 문제 응용

    • 퍼널 분석의 응용 예시로, 유저들의 세션 시작 횟수를 계산하는 방법에 대해 설명하고 있습니다. 다양한 SQL 함수와 결합해서 어떻게 퍼널을 분석할 수 있는지를 이해하는 데 이를 참조할 수 있습니다.

이 자료들이 김해리님의 학습에 도움이 되길 바랍니다. 현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏 추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.

김해리님의 프로필 이미지
김해리

작성한 질문수

전체 Q&A
질문하기