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

1주차.

1. ARRAY(배열), STRUCT(구조체) 연습문제

   • ARRAY = 같은 타입의(숫자, 문자 등) 여러 값을 하나의 컬럼에 저장할 수 있는 자료

     • 음식 메뉴판, 음악 플레이리스트-즐겨찾기한 음악 등

   • STRUCT = 서로 다른 타입의 여러 값을 하나의 컬럼에 저장할 수 있는 자료

     • 주소록, 영화 정보 등

✅ ARRAY, STRUCT 연습문제 1

# 연습문제 1번.
# array_exercises 테이블에서 각 영화(title) 별로 장르(genres)를 UNNEST해서 보여주세요.
SELECT
  title, 
  genre
FROM advanced.array_exercises
CROSS JOIN UNNEST(genres) as genre

✅ ARRAY, STRUCT 연습문제 2

# 연습문제 2번.
# array_exercises 테이블에서 각 영화(title) 별로 배우(actor)와 배역(character)을 보여주세요.
# 배우와 배역은 별도의 컬럼으로 나와야합니다. 
SELECT
  title, 
  actor.actor,
  actor.character
FROM advanced.array_exercises
CROSS JOIN UNNEST(actors) as actor

✅ ARRAY, STRUCT 연습문제 3

# 연습문제 3번.
# array_exercises 테이블에서 각 영화(title) 별로 배우(actor), 배역(character), 장르(genres)를 출력.
# 한 Row에 배우, 배역, 장르가 모두 표시되어야합니다.
SELECT
  title,
  actor.actor as actor,
  actor.character as character,
  genre
FROM advanced.array_exercises
CROSS JOIN UNNEST(genres) as genre
CROSS JOIN UNNEST(actors) as actor
ORDER BY 1, 2 desc

✅ ARRAY, STRUCT 연습문제 4

# 연습문제 4번.
# app_logs 데이터의 배열을 풀어라.
# 데이터 탐색, group by 활용, 하루 사용자 집계, 어떤 이벤트가 있는지 등.

WITH base AS ( 
    SELECT
    event_date,
    event_name,
    evnent_parm.key AS key,
    evnent_parm.value.string_value AS string_value,
    evnent_parm.value.int_value AS int_value,
    user_id
  FROM advanced.app_logs, UNNEST(event_params) AS evnent_parm
  WHERE event_date = '2022-08-01') 

SELECT
  event_date,
  event_name,
  count(distinct user_id) as cnt
FROM base
GROUP BY ALL
order by cnt desc 

• click_banner, cart, food, food_category, login, payment, recommend_extra_food, recommend_food, restaurant, restaruant_nearby, search / request_search, screen_view, view_recommend_extra_food → 총 14가지의 event_name.

• 기간 내 전체 사용자(중복 제거) → 총 49,678명, click_payment 를 실행한 이용자 총 11,467명.

• 2022-08-01 하루 사용자 129명. 8월 한달 사용자 6,424명. 8월 한달 click_payment 810명.

→ event_name을 screen_view로 설정했지만, 기간 내 전체 사용자, 0801 하루 사용자, 8월 한달 사용자의 수는 같았다(user_id 사용). screen_view는 앱에 들어오면 바로 체크가 되는 기본 단계임을 알 수 있음. 앱에 들어오고 바로 다음이 중요하다고 생각.

• 8월 한달 간의 ‘screen_view’ 이벤트의 분포가 월초 → 월말로 증가하는 형태가 나타남. screen_view 이벤트가 월말이 될 수록 커짐. 다른 달도 확인해봐야함. → 스프레드 시트로 월별로 확인해보면 좋을듯??

✅ Pivot 연습문제 1

# 데이터 PIVOT 연습문제 1번.
# orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT 해주세요.
# 날짜(order_rate)를 행으로, user_id를 열으로 만들어야 합니다.
# 기대하는 OUTPUT : order_date | user_1 | user_2 | user_3

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

✅ Pivot 연습문제 2

# 데이터 PIVOT 연습문제 2번.
# orders 테이블에서 날짜별로 유저들의 주문 금액의 합계를 PIVOT 해주세요.
# user_id 를 행으로, order_date를 열으로 만들어야합니다. 
# 기대하는 OUTPUT : user_id | order_date | 
-- `(백틱, 숫자 1 왼쪽)을 쓰면 영어 이외에도 한글도 쓸 수 있다는 점!! 
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 advanced.orders
GROUP BY user_id
ORDER BY user_id

✅ Pivot 연습문제 3

# 데이터 PIVOT 연습문제 3번.
# orders 테이블에서 사용자별, 날짜별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요.
# user_id를 행으로, order_date를 열로 만들고 주문을 많이 해도 1로 처리 합니다.
-- AMOUNT 대신에 1로 표시!!
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 advanced.orders
GROUP BY user_id
ORDER BY user_id

✅ Pivot 연습문제 4

# 앱 로그 데이터 PIVOT 하기
# user_id = 32888 카트 추가하기(click_cart)를 누를 때 어떤 음식(food_id)을 담았나요?

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

SELECT
  user_id,
  event_date,
  COUNT(user_id) AS user_cnt,
  food_id
FROM base
WHERE user_id = 32888 and event_name = 'click_cart'
GROUP BY ALL

✅ 퍼널 분석 (Funnel) 연습문제

# screen_view : welcome, home, food_category, cart, click_payment + step_number
# 데이터의 기간 : 2022-08-01 ~ 2022-08-18
# 사용할 테이블 : 앱 로그 데이터, GA/firebase 데이터 -> UNNEST -> PIVOT
# 기본이 되는 데이터프레임 만듬(base), 피벗으로 ARRAY 데이터 풀어줌.


WITH base AS (
SELECT
  event_date,
  event_timestamp,
  event_name,
  user_id,
  user_pseudo_id,
  platform,
  event_param,
  MAX(IF(event_param.key = 'firebase_screen', event_param.value.string_value, NULL)) AS firebase_screen,
  # food_id는 null 모두 null 값으로 필요없어서 주석처리함.
  -- MAX(IF(event_param.key = 'food_id', event_param.value.int_value, NULL)) AS food_id,
  MAX(IF(event_param.key = 'session_id', event_param.value.string_value, NULL)) AS session_id
FROM advanced.app_logs CROSS JOIN UNNEST(e
vent_params) AS event_param
WHERE
  event_date between '2022-08-01'and '2022-08-18'
GROUP BY ALL 
), fliter_event_and_concat_event_and_screen AS(
  SELECT
  * EXCEPT(event_name, firebase_screen, event_timestamp),
  CONCAT(event_name,'-',firebase_screen) AS event_name_with_screen,
  DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime
  FROM base
  WHERE
    event_name IN ('screen_view', 'click_payment')
  
)
# step_number 만들어주는 case when 구문
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 fliter_event_and_concat_event_and_screen
GROUP BY ALL
HAVING step_number IS NOT NULL # having으로 6가지를 제외한 나머지 이벤트는 제외
ORDER BY 1 

# 바로 위의 커리를 with문으로 감싸서 'CTE'라는 테이블을 만듬. 
# 집계한 데이터를 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 CTE
GROUP BY ALL
ORDER BY 1