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

goo님의 프로필 이미지
goo

작성한 질문수

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

빠짝스터디 1주차 과제

작성

·

33

0

1. ARRAY, STRUCT 연습문제

  • 문제 1) array_exercise테이블에서 각 영화(title)별로 장르(genres)를 UNNEST 해서 보여주세요

SELECT  
  title,
  genres
FROM `analystic-project.advanced.array_exercises` , UNNEST(genres) AS genres
;

  • 문제 2) array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야합니다

SELECT
  title,
  actors.actor,
  actors.character
FROM `analystic-project.advanced.array_exercises` , UNNEST(actors) AS actors
;

  • 문제 3) array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르 (genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다

     

SELECT
  title,
  actors.actor,
  actors.character,
  genres
FROM `analystic-project.advanced.array_exercises` , UNNEST(actors) AS actors, UNNEST(genres) genres
;

  • 문제 4) 앱 로그 데이터(app_logs) 배열 풀기

SELECT  
  user_id,
  event_date,
  event_name,
  user_pseudo_id,
  pr.key,
  pr.value.string_value,
  pr.value.int_value
FROM `analystic-project.advanced.app_logs` , UNNEST(event_params) AS pr
WHERE event_date = "2022-08-01" 
LIMIT 1000
;

2. PIVOT 연습문제 풀이

  • 문제 1) orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT해주세요. 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다

SELECT  
  order_date,
  COALESCE(SUM(IF(user_id = 1, amount, null)),0) AS user_1,
  COALESCE(SUM(IF(user_id = 2, amount, null)),0) AS user_2,
  COALESCE(SUM(IF(user_id = 3, amount, null)),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)으로 만들어야 합니다

SELECT
  user_id,
  COALESCE(SUM(IF(order_date = '2023-05-01', amount, null)),0) AS `2023-05-01`,
  COALESCE(SUM(IF(order_date = '2023-05-02', amount, null)),0) AS `2023-05-02`,
  COALESCE(SUM(IF(order_date = '2023-05-03', amount, null)),0) AS `2023-05-03`,
  COALESCE(SUM(IF(order_date = '2023-05-04', amount, null)),0) AS `2023-05-04`,
  COALESCE(SUM(IF(order_date = '2023-05-05', amount, null)),0) AS `2023-05-05`,
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로 처리합니다

SELECT
  user_id,
  MAX(IF(order_date = '2023-05-01' AND order_id is not null, 1, 0)) AS `2023-05-01`,
  MAX(IF(order_date = '2023-05-02' AND order_id is not null, 1, 0)) AS `2023-05-02`,
  MAX(IF(order_date = '2023-05-03' AND order_id is not null, 1, 0)) AS `2023-05-03`,
  MAX(IF(order_date = '2023-05-04' AND order_id is not null, 1, 0)) AS `2023-05-04`,
  MAX(IF(order_date = '2023-05-05' AND order_id is not null, 1, 0)) AS `2023-05-05`,
FROM advanced.orders
GROUP BY user_id
ORDER BY user_id
;

  • 문제 4)

    user_id = 32888이 카트 추가하기(click_cart)를 누를때 어떤 음식(food_id)을 담았나요?

WITH app_order_raw AS (
SELECT
  user_id,
  event_date,
  event_name,
  user_pseudo_id,
  pr.key,
  pr.value.string_value,
  pr.value.int_value
FROM advanced.app_logs, UNNEST(event_params) AS pr
WHERE event_date = '2022-08-01'
)
SELECT
  user_id,
  event_date,
  event_name,
  user_pseudo_id,
  MAX(IF(key = 'firebase_screen', string_value, null)) AS firebase_screen,
  MAX(IF(key = 'food_id', int_value, null)) AS food_id,
  MAX(IF(key = 'session_id', string_value, null)) AS session_id,
FROM app_order_raw
GROUP BY user_id, event_date, event_name, user_pseudo_id
;

3. 퍼널분석

  • 문제 1) 각 퍼널의 유저 수를 집계 / 데이터 기준: 2022-08-01 ~ 2022-08-18

WITH funnel_data_raw AS (
SELECT  
  event_date,
  event_timestamp,
  event_name,
  user_id,
  user_pseudo_id,
  MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null)) AS screen_name,
  CONCAT(event_name, '-', MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null))) AS event_name_with_screen
FROM advanced.app_logs, UNNEST(event_params) AS pr
WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18'
GROUP BY 1,2,3,4,5
)
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 END AS step_number,
  COUNT(DISTINCT user_pseudo_id) AS cnt
FROM funnel_data_raw
WHERE event_name IN ('screen_view', 'click_payment')
  AND screen_name IN ('welcome', 'home', 'food_category', 'restaurant', 'cart')
GROUP BY 1,2
ORDER BY 2
;

  • 문제 2) 일자별 퍼널 유저 수 집계

     

WITH funnel_data_raw AS (
SELECT  
  event_date,
  event_timestamp,
  event_name,
  user_id,
  user_pseudo_id,
  MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null)) AS screen_name,
  CONCAT(event_name, '-', MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null))) AS event_name_with_screen
FROM advanced.app_logs, UNNEST(event_params) AS pr
WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18'
GROUP BY 1,2,3,4,5
)
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 END AS step_number,
  COUNT(DISTINCT user_pseudo_id) AS cnt
FROM funnel_data_raw
WHERE event_name IN ('screen_view', 'click_payment')
  AND screen_name IN ('welcome', 'home', 'food_category', 'restaurant', 'cart')
GROUP BY 1,2,3
ORDER BY 1,3
;

  • 문제 3) 일자별 퍼널 유저 수 집계

    형태를 PIVOT형태로 전환하기

WITH funnel_data_raw AS (
SELECT  
  event_date,
  event_timestamp,
  event_name,
  user_id,
  user_pseudo_id,
  MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null)) AS screen_name,
  CONCAT(event_name, '-', MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null))) AS event_name_with_screen
FROM advanced.app_logs, UNNEST(event_params) AS pr
WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18'
GROUP BY 1,2,3,4,5
), daily_funnel_user_count as (
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 END AS step_number,
  COUNT(DISTINCT user_pseudo_id) AS cnt
FROM funnel_data_raw
WHERE event_name IN ('screen_view', 'click_payment')
  AND screen_name IN ('welcome', 'home', 'food_category', 'restaurant', 'cart')
GROUP BY 1,2,3
ORDER BY 1,3
)
SELECT
  event_date,
  MAX(IF(step_number = 1, cnt, null)) AS `screen_view-welcome`,
  MAX(IF(step_number = 2, cnt, null)) AS `screen_view-home`,
  MAX(IF(step_number = 3, cnt, null)) AS `screen_view-food_category`,
  MAX(IF(step_number = 4, cnt, null)) AS `screen_view-restaurant`,
  MAX(IF(step_number = 5, cnt, null)) AS `screen_view-cart`,
  MAX(IF(step_number = 6, cnt, null)) AS `click_payment-cart`,
FROM daily_funnel_user_count
GROUP BY ALL
ORDER BY 1
;
sqlGoogle-Analyticsfirebasegoogle-sheetsbigquery

답변 1

0

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

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

BigQuery에서 다양한 SQL 기능을 활용하여 주요 데이터 분석을 수행하는 연습 문제를 잘 풀어주셨습니다. ARRAY와 STRUCT 같은 새로운 자료형을 이용해 데이터를 효율적으로 다루고, PIVOT 쿼리를 사용해 데이터를 다양한 관점에서 분석하는 것이 데이터 분석의 핵심입니다.

질문하신 내용을 바탕으로 유사한 질문 및 답변을 제공해드립니다.

이 외에도 개념 이해를 돕기 위해 BigQuery 및 SQL 관련한 강의 자료, 예시 자료들을 참고하시면 좋겠습니다. 추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.

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

goo님의 프로필 이미지
goo

작성한 질문수

전체 Q&A
질문하기