탐색함수 연습문제문제 1.SELECT *, LEAD(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) AS next_visit_month, LEAD(visit_month, 2) OVER (PARTITION BY user_id ORDER BY visit_month) AS nnext_visit_month FROM advanced.analytics_function_01문제2.SELECT *, LEAD(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) AS next_visit_month, LEAD(visit_month, 2) OVER (PARTITION BY user_id ORDER BY visit_month) AS nnext_visit_month, LAG(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) AS previous_visit_month FROM advanced.analytics_function_01문제3.SELECT *, SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date, order_id ) AS cumulative_sum_by_user문제 4.SELECT *, AVG(amount) OVER (ORDER BY order_date, order_id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS last_5_orders_avg_amout 총정리 연습문제문제 1.SELECT *, COUNT(query_date) OVER (PARTITION BY user ORDER BY user) AS total_query_cnt FROM advanced.query_logs문제2.WITH table AS ( SELECT EXTRACT(WEEK FROM query_date) AS week_number, team, user, COUNT(user) AS query_cnt FROM advanced.query_logs GROUP BY ALL ) SELECT *, RANK() OVER(PARTITION BY week_number ORDER BY query_cnt) AS team_rank FROM table QUALIFY team_rank = 1문제3.WITH table AS ( SELECT EXTRACT(WEEK FROM query_date) AS week_number, team, user, COUNT(user) AS query_cnt FROM advanced.query_logs GROUP BY ALL ) SELECT *, LAG(query_cnt) OVER (PARTITION BY user ORDER BY week_number) AS prev_week_query_cnt FROM table문제4.WITH query_count_table AS ( SELECT *, COUNT(*) AS query_count FROM advanced.query_logs GROUP BY ALL ) SELECT *, SUM(query_count) OVER (PARTITION BY user ORDER BY query_date) AS cululative_query_count FROM query_count_table문제5.WITH raw_data AS ( SELECT DATE '2024-05-01' AS date, 15 AS number_of_orders UNION ALL SELECT DATE '2024-05-02', 13 UNION ALL SELECT DATE '2024-05-03', NULL UNION ALL SELECT DATE '2024-05-04', 16 UNION ALL SELECT DATE '2024-05-05', NULL UNION ALL SELECT DATE '2024-05-06', 18 UNION ALL SELECT DATE '2024-05-07' , 20 UNION ALL SELECT DATE '2024-05-08', NULL UNION ALL SELECT DATE '2024-05-09', 13 UNION ALL SELECT DATE '2024-05-10', 14 UNION ALL SELECT DATE '2024-05-11', NULL UNION ALL SELECT DATE '2024-05-12', NULL UNION ALL ) SELECT *, LAST_VALUE(number_of_order IGNORE NULLS) OVER (ORDER BY date) AS last_value_orders FROM raw_data문제6.WITH raw_data AS ( SELECT DATE '2024-05-01' AS date, 15 AS number_of_orders UNION ALL SELECT DATE '2024-05-02', 13 UNION ALL SELECT DATE '2024-05-03', NULL UNION ALL SELECT DATE '2024-05-04', 16 UNION ALL SELECT DATE '2024-05-05', NULL UNION ALL SELECT DATE '2024-05-06', 18 UNION ALL SELECT DATE '2024-05-07' , 20 UNION ALL SELECT DATE '2024-05-08', NULL UNION ALL SELECT DATE '2024-05-09', 13 UNION ALL SELECT DATE '2024-05-10', 14 UNION ALL SELECT DATE '2024-05-11', NULL UNION ALL SELECT DATE '2024-05-12', NULL ), filled_data AS ( SELECT * EXCEPT(number_of_orders), **LAST_VALUE**(number_of_orders **IGNORE NULLS**) OVER (ORDER BY date) AS number_of_orders FROM raw_data ) SELECT *, AVG(number_of_orders) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM filled_data문제 7.WITH base AS( SELECT event_date, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime, event_name, user_id, user_pseudo_id FROM advanced.app_logs WHERE event_date = '2022-08-18' AND user_pseudo_id = "1997494153.8491999091" ), diff_data AS( SELECT *, #이전 이벤트 시간과 현재 이벤트시간의 간격을 SECOND 초단위로 구하기 / second_diff를 기반으로 새로운 세션의 시작일지 아닐지 판단 DATETIME_DIFF(event_datetime, prev_event_datetime, SECOND) AS second_diff FROM ( SELECT *, # 직전 이벤트 시간을 prev_event_datetime으로 만들기 LAG(event_datetime, 1) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS prev_event_datetime FROM base ) ) SELECT *, SUM(session_start) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS session_num FROM( SELECT *, CASE WHEN prev_event_datetime IS NULL THEN 1 WHEN second_diff >= 20 THEN 1 ELSE NULL END AS session_start FROM diff_data ) ORDER BY event_datetime

질문 1. 강의 내 GA화면은 모두 GA3 기준으로 설명해주시고 계신건가요? GA4와 아예 다른 것 같아서요... ㅠㅠ   질문 2. (아 참 firebase SDK 로 수집한 빅쿼리는 무료로 바뀐거 같아요..맞겠죠? 따로 결제 하지 않았는데 빅쿼리에 저장이 잘 되고 있어요..!) 어쨋든 이 raw 데이터를 보니 전혀 수집되지 않는 컬럼이 있습니다. 예를 들면. user id, item_id, item_brand_id.. 이런 컬럼은 전혀 데이터가 수집되지 않았는데요. 좀만 생각해보면 SDK 가 알아서 수집할 수 없는 데이터 같기는 한데요.(우리 서비스 서버에 저장된 정보가 필요하니까..?) 이런 정보는 개발자가 직접 컬럼을 채워주는? 연결해주는(?) 어떠한 작업을 해야하는 건가요?   질문 3. 만약 이런 작업이 개발자의 영역이라면, 개발자에게 저희 서비스 데이터에서 어떤 테이블의 어떤 값이 들어가게 해줘. 라고 요청을 하면 되나요? 예를들어 fire base 에서 user_id라고 적힌 컬럼에는 우리 DB의 user테이블의 id값이 들어가서 수집되게 해줘. 라는 것들을 리스트업해서 전달하면 되는지. 추가로 이런 작업을 비개발자인 제가 직접할 수는 없는 건가요? GA (웹) 사용하시는 분들을 보면 GTM 을 이용해 이벤트를 직접 정의하고 수집하던데, 앱에서는 불가능한지 여쭤봅니다.   질문 4. 제가 고민해봤을 때는 저희 서비스가 유저테이블과 상품테이블 크게 두가지 정보를 중심으로 이루어져 있다면 로그데이터에 딱 두가지 중요한 정보, 즉 유저 id와 상품id 두가지 값만 잘 맵핑 시켜두면 그 외의 정보는 얼마든지 조인을 이용해 연결해서 사용하면 되겠다라는 생각이 들었습니다. (로그데이터와 서비스데이터의 연결 성사..? ) 제가 SQL을 이제 join 함수를 배우고 있어서 들뜬 감이 있는데, 이렇게 접근하는 게 실무적으로 제대로, 접근한 걸까요..? ㅜㅜ   질문이 많았는데, 강의를 통해 너무 많은 인사이트를 얻어서라고 너그러이 양해 부탁드립니다. 지금껏 본 어떤 강의보다 훌륭해요! ㅎㅎ 감사합니다! 실제로 뵐 수 있다면 밥한끼 대접해드리고 싶은 마음이에용