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

차붐기붐님의 프로필 이미지
차붐기붐

작성한 질문수

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

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

작성

·

48

·

수정됨

0

ARRAY / STRUCT 

연습문제1

# ARRAY, STRUCT 연습 문제1
-- genres 배열을 UNNEST하여 펼치고, 각 장르를 개별 행으로 분리하여 영화 제목별 장르를 나타냈습니다.
SELECT
  title,
  genre
FROM `bqmaster.advanced.array_exercises`, UNNEST(genres) AS genre

연습문제2

# ARRAY, STRUCT 연습 문제1
-- genres 배열을 UNNEST하여 펼치고, 각 장르를 개별 행으로 분리하여 영화 제목별 장르를 나타냈습니다.
SELECT
  title,
  genre
FROM `bqmaster.advanced.array_exercises`, UNNEST(genres) AS genre

연습문제3

# ARRAY, STRUCT 연습 문제1
-- genres 배열을 UNNEST하여 펼치고, 각 장르를 개별 행으로 분리하여 영화 제목별 장르를 나타냈습니다.
SELECT
  title,
  genre
FROM `bqmaster.advanced.array_exercises`, UNNEST(genres) AS genre

연습문제4

# ARRAY, STRUCT 연습 문제1
-- genres 배열을 UNNEST하여 펼치고, 각 장르를 개별 행으로 분리하여 영화 제목별 장르를 나타냈습니다.
SELECT
  title,
  genre
FROM `bqmaster.advanced.array_exercises`, UNNEST(genres) AS genre

 

PIVOT 

연습문제1

# PIVOT 연습 문제1
-- 날짜별로 사용자별 amount 합산을 나타냈습니다.
-- IF 문을 사용해 user_id가 1, 2, 3인 경우에만 amount를 집계하고, 나머지는 0으로 처리했습니다.
-- 데이터셋 특성상 SUM과 MAX 모두 사용할 수 있습니다.
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 `bqmaster.advanced.orders`
GROUP BY ALL
ORDER BY order_date

연습문제2

# PIVOT 연습 문제2
-- 사용자별로 날짜별 amount 합산을 나타냈습니다.
-- IF 문을 사용해 order_date가 지정된 날짜에 해당하는 경우에만 amount를 집계하고, 나머지는 0으로 처리했습니다.
-- 데이터셋 특성상 SUM과 MAX 모두 사용할 수 있습니다.
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 `bqmaster.advanced.orders`
GROUP BY ALL
ORDER BY user_id

연습문제3

# PIVOT 연습 문제3
-- 사용자별로 날짜별 주문 여부를 나타냈습니다.
-- IF 문을 사용해 해당 날짜에 주문이 있는 경우 1, 없는 경우 0으로 표시하고, MAX를 사용해 날짜별로 최대값(1 또는 0)을 반환합니다.
-- 데이터셋 특성상 SUM과 MAX 모두 사용할 수 있습니다.
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 `bqmaster.advanced.orders`
GROUP BY ALL
ORDER BY user_id

연습문제4

# PIVOT 연습 문제4
-- user_id가 32888인 사용자가 click_cart 이벤트 발생 시의 데이터를 나타냈습니다.
-- event_params 배열을 UNNEST하여 param으로 분리한 후, firebase_screen, food_id, session_id를 개별 컬럼으로 표시했습니다.
WITH base 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.int_value, NULL)) AS food_id,
    MAX(IF(param.key = 'session_id', param.value.string_value, NULL)) AS session_id
  FROM `bqmaster.advanced.app_logs`, UNNEST(event_params) AS param
  GROUP BY ALL
)

SELECT
  *
FROM base
WHERE 1=1
  AND user_id = 32888
  AND event_name = 'click_cart'

 

FUNNEL

연습문제1

# FUNNEL 연습 문제1
-- WITH 구문을 사용해 base 테이블을 생성하여 이벤트 이름과 화면 이름을 포함한 event_name_with_screen 컬럼을 만들었습니다.
-- event_date는 2022-08-01에서 2022-08-18 사이로 설정했습니다.
WITH base AS (
  SELECT
    event_date,
    CONCAT(event_name, '-', param.value.string_value) AS event_name_with_screen,
    user_pseudo_id
  FROM `bqmaster.advanced.app_logs`, UNNEST(event_params) AS param
  WHERE 1=1
    AND event_name IN ('screen_view', 'click_payment')
    AND param.key = 'firebase_screen'
    AND event_date BETWEEN '2022-08-01' AND '2022-08-18'
)

# 일자 상관 없이 퍼널의 유저 수를 집계
SELECT
  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,
  COUNT(DISTINCT user_pseudo_id) AS user_cnt
FROM base
GROUP BY ALL
HAVING step IS NOT NULL
ORDER BY step


# 일자별 퍼널의 유저 수를 집계
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,
  COUNT(DISTINCT user_pseudo_id) AS user_cnt
FROM base
GROUP BY ALL
HAVING step IS NOT NULL
ORDER BY event_date, step

연습문제2

# FUNNEL 연습 문제2
-- 윈도우 함수를 사용해 이탈률을 계산해 봤습니다.
-- LAG을 사용하여 이전 사용자수 값을 가져와 계산에 활용했습니다.
WITH base AS (
  SELECT
    event_date,
    CONCAT(event_name, '-', param.value.string_value) AS event_name_with_screen,
    user_pseudo_id
  FROM `bqmaster.advanced.app_logs`, UNNEST(event_params) AS param
  WHERE 1=1
    AND event_name IN ('screen_view', 'click_payment')
    AND param.key = 'firebase_screen'
    AND event_date BETWEEN '2022-08-01' AND '2022-08-18'
), add_step AS (
  SELECT
    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 funnel_step,
    COUNT(DISTINCT user_pseudo_id) AS user_cnt
  FROM base
  GROUP BY ALL
  HAVING funnel_step IS NOT NULL
), add_total_user_cnt AS (
  SELECT
    *,
    LAG(user_cnt) OVER(ORDER BY funnel_step) AS previous_step_user_cnt
  FROM add_step
)

SELECT
  *,
  ROUND((previous_step_user_cnt - user_cnt) / previous_step_user_cnt, 2) AS bounce_rate
FROM add_total_user_cnt
ORDER BY funnel_step

연습문제3

# FUNNEL 연습 문제3
-- PIVOT을 사용해 화면별로 사용자 수를 컬럼으로 나열하여 각 화면별 날짜별 사용자 수를 확인할 수 있도록 구성했습니다.
WITH base AS (
  SELECT
    event_date,
    CONCAT(event_name, '-', param.value.string_value) AS event_name_with_screen,
    user_pseudo_id
  FROM `bqmaster.advanced.app_logs`, UNNEST(event_params) AS param
  WHERE 1=1
    AND event_name IN ('screen_view', 'click_payment')
    AND param.key = 'firebase_screen'
    AND event_date BETWEEN '2022-08-01' AND '2022-08-18'
), add_user_cnt AS (
  SELECT
    event_date,
    event_name_with_screen,
    COUNT(DISTINCT user_pseudo_id) AS user_cnt
  FROM base
  GROUP BY ALL
)

SELECT
  event_date,
  MAX(IF(event_name_with_screen = 'screen_view-welcome', user_cnt, NULL)) AS screen_view_welcome,
  MAX(IF(event_name_with_screen = 'screen_view-home', user_cnt, NULL)) AS screen_view_home,
  MAX(IF(event_name_with_screen = 'screen_view-food_category', user_cnt, NULL)) AS screen_view_food_category,
  MAX(IF(event_name_with_screen = 'screen_view-restaurant', user_cnt, NULL)) AS screen_view_restaurant,
  MAX(IF(event_name_with_screen = 'screen_view-cart', user_cnt, NULL)) AS screen_view_cart,
  MAX(IF(event_name_with_screen = 'click_payment-cart', user_cnt, NULL)) AS click_payment_cart
FROM add_user_cnt
GROUP BY ALL
ORDER BY event_date
sqlGoogle-Analyticsfirebasegoogle-sheetsbigquery

답변 1

0

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

안녕하세요, 인프런 AI 인턴입니다. 차붐기붐님.

BigQuery에서 ARRAY와 STRUCT를 사용하는 연습 문제와 관련하여 도움이 될 수 있는 링크와 간단한 설명을 제공드립니다. ARRAY와 STRUCT는 데이터베이스에서 서로 관련 있는 데이터를 논리적으로 묶어 저장하거나, 반복 구조를 표현하는 데 유용합니다. 이러한 기능을 사용하면 데이터의 조직과 접근이 더 유연해지고, 복잡한 쿼리를 더 쉽게 작성할 수 있습니다.

아래는 유사한 주제의 링크입니다:

  1. ARRAY와 STRUCT를 사용한 데이터 변환 방법

    • 이 질문에서는 ARRAY와 STRUCT를 사용하여 데이터베이스 내의 데이터를 더 유의미하게 변환하는 방법에 대해 설명하고 있습니다. 기본적인 사용법과 함께 데이터를 UNNEST하여 더 직관적으로 변환하는 방법에 대한 논의가 담겨져 있습니다.

  2. BigQuery에서 ARRAY와 STRUCT의 활용 사례

    • 퍼널 분석에서 ARRAY와 STRUCT를 활용하는 구체적인 사례를 통해 데이터 분석의 효율성을 높이는 방법을 설명합니다. 각각의 자료형을 통해 어떻게 데이터를 구조화하고 분석할 수 있는지에 대한 예시가 포함되어 있습니다.

이 자료들이 차붐기붐님의 연습 문제 해결에 도움이 되길 바랍니다. 저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다. 현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏 추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.

차붐기붐님의 프로필 이미지
차붐기붐

작성한 질문수

전체 Q&A
질문하기