인프런 커뮤니티 질문&답변
작성한 질문수
[바짝스터디 1주차 과제] ARRAY,STRUCT,PIVOT,FUNNEL
작성
·
29
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
;
답변 1
0
안녕하세요, 인프런 AI 인턴입니다. omo님, BigQuery를 활용하여 다양한 SQL 쿼리를 실습하면서 ARRAY, STRUCT, PIVOT, FUNNEL 기능을 활용한 시도는 매우 유익합니다. 각 연습 문제에서 시도하신 방식과 방법론은 충분히 이해가 되며, 관련된 질문에 대한 해결을 위한 자료를 제공해 드리고자 합니다. 유사한 질문과 답변을 확인할 수 있는 URL 링크를 제공합니다.
ARRAY 및 STRUCT 활용 문제
- ARRAY와 STRUCT를 활용하여 데이터를 UNNEST하는 방법 및 쿼리 작성 방안에 대한 이전 질문의 사례를 참고해보세요.
- UNNEST와 관련된 다른 질문 사례
- ARRAY와 STRUCT를 활용하여 데이터를 UNNEST하는 방법 및 쿼리 작성 방안에 대한 이전 질문의 사례를 참고해보세요.
PIVOT 연습문제 해결 방법
- PIVOT을 이용하여 데이터를 변환 및 집계하는 방법을 익히는 것은 데이터 분석에 매우 유용합니다. 아래의 링크에서 어떻게 피벗을 활용하여 데이터를 정리할 수 있는지 참고해볼 수 있습니다.
- 피벗테이블을 활용한 다양한 쿼리 사례
- PIVOT을 이용하여 데이터를 변환 및 집계하는 방법을 익히는 것은 데이터 분석에 매우 유용합니다. 아래의 링크에서 어떻게 피벗을 활용하여 데이터를 정리할 수 있는지 참고해볼 수 있습니다.
퍼널 분석을 위한 SQL 작성
- 퍼널 분석에서 사용자 여정을 추적하는 방법은 데이터 기반 의사 결정을 위한 중요한 기술입니다. 퍼널 구조 설정 및 결과 해석 방법을 아래 링크에서 확인해 보세요.
- 퍼널 분석 쿼리 작성 및 오류 해결
- 퍼널 분석에서 사용자 여정을 추적하는 방법은 데이터 기반 의사 결정을 위한 중요한 기술입니다. 퍼널 구조 설정 및 결과 해석 방법을 아래 링크에서 확인해 보세요.
각 링크는 이전에 같은 주제로 논의됐던 질문과 답변을 포함하고 있어 이해를 돕는데 큰 도움이 될 것입니다.
저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다. 현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏 추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.