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

임현민님의 프로필 이미지
임현민

작성한 질문수

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

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

작성

·

16

0

1. ARRAY, STRUCT 연습문제

연습문제 1

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

 

연습문제 2

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

 

연습문제 3

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

[메모]

여기서 CROSS JOIN 다음 라인에 WHERE actor = 'Chris Evans' 이렇게 조건을 걸어줄 경우 오류가 발생한다. 오류는 실행 순서와 관련이 있다. 실행 순서: FROM -> JOIN -> SELECT

따라서 SELECT 에서 알리아스로 이름 붙인 actor 가 아닌, CROSS JOIN 결과를 사용하여 조건을 만들어야 한다. => WHERE actor.actor = 'Chris Evans'

 

연습문제 4

SELECT user_id
     , event_date
     , event_name
     , user_pseudo_id
     , parameter.key AS key
     , parameter.value.string_value AS string_value
     , parameter.value.int_value AS int_value
FROM `inflearn-bigquery-437203.advanced.app_logs`
CROSS JOIN UNNEST (event_params) AS parameter 
WHERE event_date = "2022-08-01"

 

 

2. PIVOT 연습문제

연습문제 1

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 `inflearn-bigquery-437203.advanced.orders`
GROUP BY order_date
ORDER BY order_date

 

연습문제 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 `inflearn-bigquery-437203.advanced.orders`
GROUP BY user_id
ORDER BY user_id

[메모]

알리아스로 컬럼명 지정할 때, 영어 제외하고 다른 문자열가 포함될 경우 → backtick (`) 으로 감싸준다.

 

연습문제 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 `inflearn-bigquery-437203.advanced.orders`
GROUP BY user_id
ORDER BY user_id

 

연습문제 4

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

 

 

3. 퍼널 쿼리 연습문제

연습문제 1

: 각 퍼널별 유저 수 집계

-- 퍼널 단계: 6
-- screen_view(welcome)
-- screen_view(home)
-- screen_view(food_category)
-- screen_view(restaurant)
-- screen_view(cart)
-- click_payment(cart)

WITH funnel AS (
  SELECT CONCAT(event_name, '-', param.value.string_value) AS event_name_with_screen
      , CASE
            WHEN event_name = 'screen_view' AND param.value.string_value = 'welcome' THEN 1
            WHEN event_name = 'screen_view' AND param.value.string_value = 'home' THEN 2
            WHEN event_name = 'screen_view' AND param.value.string_value = 'food_category' THEN 3
            WHEN event_name = 'screen_view' AND param.value.string_value = 'restaurant' THEN 4
            WHEN event_name = 'screen_view' AND param.value.string_value = 'cart' THEN 5
            WHEN event_name = 'click_payment' AND param.value.string_value = 'cart' THEN 6
            END AS step_number
      , user_pseudo_id
  FROM `inflearn-bigquery-437203.advanced.app_logs`
  CROSS JOIN UNNEST(event_params) AS param
  WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18'
    AND event_name IN ('screen_view', 'click_payment')
    AND param.key = 'firebase_screen'
    AND param.value.string_value IN ('welcome', 'home', 'food_category', 'restaurant', 'cart')
  )

SELECT event_name_with_screen
     , MAX(step_number) AS step_number
     , COUNT(DISTINCT user_pseudo_id) AS cnt
FROM funnel
GROUP BY event_name_with_screen

 

연습문제 2

: 일자별 각 퍼널의 유저 수 집계

-- 퍼널 단계: 6
-- screen_view(welcome)
-- screen_view(home)
-- screen_view(food_category)
-- screen_view(restaurant)
-- screen_view(cart)
-- click_payment(cart)

WITH funnel AS (
  SELECT event_date -- 날짜 컬럼 추가
       , CONCAT(event_name, '-', param.value.string_value) AS event_name_with_screen
       , CASE
            WHEN event_name = 'screen_view' AND param.value.string_value = 'welcome' THEN 1
            WHEN event_name = 'screen_view' AND param.value.string_value = 'home' THEN 2
            WHEN event_name = 'screen_view' AND param.value.string_value = 'food_category' THEN 3
            WHEN event_name = 'screen_view' AND param.value.string_value = 'restaurant' THEN 4
            WHEN event_name = 'screen_view' AND param.value.string_value = 'cart' THEN 5
            WHEN event_name = 'click_payment' AND param.value.string_value = 'cart' THEN 6
            END AS step_number
      , user_pseudo_id
  FROM `inflearn-bigquery-437203.advanced.app_logs`
  CROSS JOIN UNNEST(event_params) AS param
  WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18'
    AND event_name IN ('screen_view', 'click_payment')
    AND param.key = 'firebase_screen'
    AND param.value.string_value IN ('welcome', 'home', 'food_category', 'restaurant', 'cart')
  )

SELECT event_date -- 날짜 컬럼 추가
     , event_name_with_screen
     , MAX(step_number) AS step_number
     , COUNT(DISTINCT user_pseudo_id) AS cnt
FROM funnel
GROUP BY ALL
ORDER BY event_date, step_number

 

연습문제 3

: 일자별 각 퍼널의 유저 수 집계한 결과 → PIVOT 하기

-- 퍼널 단계: 6
-- screen_view(welcome)
-- screen_view(home)
-- screen_view(food_category)
-- screen_view(restaurant)
-- screen_view(cart)
-- click_payment(cart)

WITH funnel AS (
  SELECT event_date
       , CONCAT(event_name, '-', param.value.string_value) AS event_name_with_screen
       , CASE
            WHEN event_name = 'screen_view' AND param.value.string_value = 'welcome' THEN 1
            WHEN event_name = 'screen_view' AND param.value.string_value = 'home' THEN 2
            WHEN event_name = 'screen_view' AND param.value.string_value = 'food_category' THEN 3
            WHEN event_name = 'screen_view' AND param.value.string_value = 'restaurant' THEN 4
            WHEN event_name = 'screen_view' AND param.value.string_value = 'cart' THEN 5
            WHEN event_name = 'click_payment' AND param.value.string_value = 'cart' THEN 6
            END AS step_number
      , user_pseudo_id
  FROM `inflearn-bigquery-437203.advanced.app_logs`
  CROSS JOIN UNNEST(event_params) AS param
  WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18'
    AND event_name IN ('screen_view', 'click_payment')
    AND param.key = 'firebase_screen'
    AND param.value.string_value IN ('welcome', 'home', 'food_category', 'restaurant', 'cart')
  ),

-- 일자별 각 퍼널의 유저수 집계
funnel_daily AS(
  SELECT event_date
      , event_name_with_screen
      , MAX(step_number) AS step_number
      , COUNT(DISTINCT user_pseudo_id) AS cnt
  FROM funnel
  GROUP BY ALL
  ORDER BY event_date, step_number
  )

-- 일자별 각 퍼널의 유저수 집계 -> 피벗하기
SELECT event_date
     , SUM(IF(event_name_with_screen = 'screen_view-welcome', cnt, 0)) AS `screen_view-welcome`
     , SUM(IF(event_name_with_screen = 'screen_view-home', cnt, 0)) AS `scree_view-home`
     , SUM(IF(event_name_with_screen = 'screen_view-food_category', cnt, 0)) AS `screen_view-food_category`
     , SUM(IF(event_name_with_screen = 'screen_view-restaurant', cnt, 0)) AS `screen_view-restaurant`
     , SUM(IF(event_name_with_screen = 'screen_view-cart', cnt, 0)) AS `screen_view-cart`
     , SUM(IF(event_name_with_screen = 'click_payment-cart', cnt, 0)) AS `click_payment-cart`
FROM funnel_daily
GROUP BY event_date
ORDER BY event_date

 

sqlGoogle-Analyticsfirebasegoogle-sheetsbigquery

답변 1

0

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

안녕하세요, 인프런 AI 인턴입니다. 임현민님.

BigQuery에서 다양한 SQL 기능을 활용하여 ARRAY, STRUCT, PIVOT 등을 연습하고 퍼널 쿼리를 작성하는 것은 데이터 분석에 매우 유용한 기술입니다. 이러한 기능들은 복잡한 데이터를 효과적으로 처리하고 다양한 분석을 수행하는 데 큰 도움을 줍니다.

현재 ARRAY 및 STRUCT 연습문제를 통해 데이터의 구조를 이해하고, PIVOT 연습문제로 데이터의 집계와 변환을 학습하시면서 퍼널 쿼리 연습문제로 사용자 행동 패턴을 분석하는 방법을 습득하고 계시는 것 같습니다. 이 과정에서 특히 데이터의 CROSS JOIN과 UNNEST를 통한 배열 데이터의 처리, 그리고 CASE 및 GROUP BY와 같은 SQL 문법을 적용하시면서 실질적인 데이터를 다루는 능력을 기르게 됩니다.

몇 가지 관련된 질문과 답변을 통해 추가적인 도움을 드릴 수 있는 자료를 공유합니다:

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

임현민님의 프로필 이미지
임현민

작성한 질문수

전체 Q&A
질문하기