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

1. ARRAY, STRUCT 연습문제
1)array_exercises테이블에서각영화(title)별로장르(genres)를UNNEST해서 보여주세요

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

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

SELECT
  title,
  actors.actor,
  actors.character

FROM advanced.array_exercises

CROSS JOIN UNNEST(actors) AS ac;

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

SELECT 
  title,
  actor.actor,
  actor.character,
  genre

FROM `advanced.array_exercises`

CROSS JOIN UNNEST(actors) as actor
CROSS JOIN UNNEST(genres) as genre;


4)앱로그데이터(app_logs)의배열을풀어주세요

SELECT 
  user_id,
  event_date,
  event_name,
  user_pseudo_id,
  event_param.key,
  event_param.value.string_value,
  event_param.value.int_value

FROM `advanced.app_logs`

CROSS JOIN UNNEST(event_params) AS event_param

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;

*) 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.  느낀점

퍼널이 무엇언지에 대해서 자세히 배울 수 있었음(학과 수업에선 퍼널에 대해서 딱히 알려주는 수업이 없어서 이렇게 재대로 배울 기회가 없었다.)
시작하기 앞서 빅쿼리에 대해 아는 것이 적어 잘할 수 있을지 고민이 많이 되었지만 강의 내용이 이해가 잘되어 열심히 노력하면 따라갈 수 있겠다는 생각이 듬
2주차도 잘부탁드립니다~