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 *, DATETIME_DIFF(event_datetime, prev_event_datetime, SECOND) AS second_diff FROM ( SELECT *, 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. user들의 다음 접속 월과 다다음 접속 월을 구하는 쿼리를 작성해주세요.SELECT * , LEAD(visit_month, 1) OVER(PARTITION BY user_id ORDER BY visit_month) AS next1_visit_month , LEAD(visit_month, 2) OVER(PARTITION BY user_id ORDER BY visit_month) AS next2_visit_month FROM advanced.analytics_function_01 ORDER BY user_id, visit_month 문제 2. user들의 다음 접속 월과 다다음 접속 월, 이전 접속 월을 구하는 쿼리를 작성해주세요.SELECT * , LEAD(visit_month, 1) OVER(PARTITION BY user_id ORDER BY visit_month) AS next1_visit_month , LEAD(visit_month, 2) OVER(PARTITION BY user_id ORDER BY visit_month) AS next2_visit_month , LAG(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) AS before_visit_month FROM advanced.analytics_function_01 ORDER BY user_id, visit_month 문제 3. user가 접속했을 때 다음 접속까지의 간격을 구하시오.SELECT * , next_visit_month - visit_month AS diff_month FROM ( SELECT * , LEAD(visit_month, 1) OVER(PARTITION BY user_id ORDER BY visit_month) AS next_visit_month FROM advanced.analytics_function_01 ORDER BY user_id, visit_month ) AS tmp 추가 문제. 유저의 첫 번째 방문 월, 마지막 방문 월을 구하는 쿼리를 작성해주세요.SELECT * , FIRST_VALUE(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_visit_month , LAST_VALUE(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_visit_month FROM advanced.analytics_function_01 ORDER BY user_id, visit_month  윈도우 함수(프레임, QUALIFY) 연습 문제문제 1. -- amount_total: 전체 SUM -- cumulative_sum: row 시점에 누적 SUM -- cumulative_sum_user: row 시점에 유저별 누적 SUM -- last_5_avg: order_id 기준으로 정렬하고, 직전 5개의 주문의 평균 amount SELECT * , SUM(amount) OVER(ORDER BY order_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS amount_total , SUM(amount) OVER(ORDER BY order_id) AS cumulative_sum , SUM(amount) OVER(PARTITION BY user_id ORDER BY order_id) AS cumulative_sum_user , AVG(amount) OVER(ORDER BY order_id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS last_5_avg FROM `inflearn-bigquery-437203.advanced.orders` ORDER BY order_id 문제 2. QUALIFY 로 윈도우 함수 조건 걸기SELECT * , SUM(amount) OVER(ORDER BY order_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS amount_total_1 , SUM(amount) OVER() AS amout_total_2 -- OVER 안에 아무것도 쓰지 않는 경우도 있다. 그러면 amount_total와 같은 결과가 계산된다. , SUM(amount) OVER(ORDER BY order_id) AS cumulative_sum , SUM(amount) OVER(PARTITION BY user_id ORDER BY order_id) AS cumulative_sum_user , AVG(amount) OVER(ORDER BY order_id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS last_5_avg FROM `inflearn-bigquery-437203.advanced.orders` QUALIFY amount_total_1 >= 500  윈도우 함수 연습 문제 (1~7번)문제 1. 사용자별 쿼리를 실행한 총 횟수를 구하는 쿼리를 작성해주세요. 단, GROUP BY를 사용해서 집계하는 것이 아닌 query_logs의 데이터의 우측에 새로운 컬럼을 만들어주세요.SELECT * , COUNT(*) OVER(PARTITION BY user) AS total_query_cnt FROM advanced.query_logs 문제 2. 주차별로 팀 내에서 쿼리를 많이 실행한 수를 구한 후, 실행한 수를 활용해 랭킹을 구해주세요. 단, 랭킹이 1등인 사람만 결과가 보이도록 해주세요.WITH query_cnt_by_team AS ( SELECT EXTRACT(WEEK FROM query_date) AS week_number , team , user , COUNT(*) AS query_cnt FROM advanced.query_logs GROUP BY ALL ) SELECT * , RANK() OVER(PARTITION BY week_number, team ORDER BY query_cnt DESC) AS rk FROM query_cnt_by_team QUALIFY rk = 1 -- 윈도우 함수 결과에 대해 조건 걸 때 사용 ORDER BY week_number, team, query_cnt DESC 문제 3. (2번 문제에서 사용한 주차별 쿼리 사용) 쿼리를 실행한 시점 기준 1주 전에 쿼리 실행 수를 별도의 컬럼으로 확인할 수 있는 쿼리를 작성해주세요.WITH query_cnt_by_team AS ( SELECT EXTRACT(WEEK FROM query_date) AS week_number , team , user , COUNT(*) AS query_cnt FROM advanced.query_logs GROUP BY ALL ) SELECT user , team , week_number , query_cnt , LAG(query_cnt, 1) OVER(PARTITION BY user ORDER BY week_number) as prev_week_query_cnt FROM query_cnt_by_team ORDER BY user, team, week_number 문제 4. 시간의 흐름에 따라, 일자별로 유저가 실행한 누적 쿼리 수를 작성해주세요.SELECT * , SUM(query_cnt) OVER(PARTITION BY user ORDER BY query_date) AS cumulative_query_cnt FROM ( SELECT * , COUNT(*) AS query_cnt FROM advanced.query_logs GROUP BY ALL) AS tmp -- 윈도우 함수 집계 함수 프레임 Default -- : RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 문제 5. 다음 데이터는 주문 횟수를 나타낸 데이터. 만약 주문 횟수가 없으면 NULL로 기록된다. 이런 데이터에서 NULL 값이라고 되어 있는 부분을 바로 이전 날짜의 값으로 채워주는 쿼리를 작성해주세요. (어려움!)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 ) SELECT * , LAST_VALUE(number_of_orders IGNORE NULLS) OVER(ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS last_value_orders FROM raw_data 문제 6. 5번 문제에서 NULL을 채운 후, 2일 전~ 현재 데이터의 평균을 구하는 쿼리를 작성해주세요. (이동평균)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 * , LAST_VALUE(number_of_orders IGNORE NULLS) OVER(ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS last_value_orders FROM raw_data ) SELECT date , last_value_orders , AVG(last_value_orders) OVER(ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM filled_data 문제 7. 세션 id 만들기 WITH step1 AS ( SELECT event_date , event_timestamp , 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" ) -- 이전 이벤트가 발생한 시각을 나타내는 컬럼 추가 , step2 AS ( SELECT * , LAG(event_datetime, 1) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS before_event_datetime FROM step1 ) -- 현재 이벤트와 이전 이벤트의 발생 시간의 차이를 나타내는 컬럼 추가 , step3 AS ( SELECT * , DATETIME_DIFF(event_datetime, before_event_datetime, SECOND) AS second_diff FROM step2 ) -- 세션 id 부여하는 컬럼 추가 SELECT * , SUM(session_start) OVER (PARTITION BY user_pseudo_id ORDER BY event_datetime) AS session_id FROM ( SELECT * , IF(second_diff IS NULL OR second_diff > 20, 1, NULL) AS session_start FROM step3 ) AS tmp 

 탐색 함수 연습 문제 -- 문제 1) user들의 다음 접속 월과 다다음 접속 월을 구하는 쿼리를 작성해주세요. -- SELECT -- user_id, -- visit_month, -- 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 next_next_visit_month -- FROM advanced.analytics_function_01 -- 문제 2) user들의 다음 접속 월과 다다음 접속 월, 이전 접속 월을 구하는 쿼리를 작성해주세요. -- SELECT -- user_id, -- visit_month, -- 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 next_next_visit_month, -- LAG(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) as last_visit_month -- FROM advanced.analytics_function_01 -- 추가 문제 : 이 데이터셋을 기준으로 user_id의 첫 방문월, 마지막 방문월을 구하는 쿼리를 작성해주세요. SELECT DISTINCT user_id, FIRST_VALUE(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as first_visit_month, LAST_VALUE(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as last_visit_month FROM advanced.analytics_function_01  FramePIVOT 연습 문제에서 사용한 테이블 활용 SELECT *, SUM(amount) OVER () as amount_total, SUM(amount) OVER (ORDER BY order_id) as cumluative_sum, SUM(amount) OVER (PARTITION BY user_id ORDER BY order_id) as cumluative_sum_user, AVG(amount) OVER (ORDER BY order_id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) as last_5_avg FROM advanced.orders ORDER BY order_id  윈도우 함수 연습문제 -- 1) 사용자별 쿼리를 실행한 총 횟수를 구하는 쿼리를 작성해주세요. 단, GROUP BY를 사용해서 집계하는 것이 아닌 query_logs의 데이터의 우측에 새로운 컬럼을 만들어주세요. -- SELECT -- *, -- COUNT(*) OVER (PARTITION BY user) as total_query_count -- FROM advanced.query_logs -- 2) 주차별로 팀 내에서 쿼리를 많이 실행한 수를 구한 후, 실행한 수를 활용해 랭킹을 구해주세요. 단, 랭킹이 1등인 사람만 결과가 보이도록 해주세요 -- SELECT -- DISTINCT *, -- RANK() OVER (PARTITION BY week_number, team ORDER BY query_cnt desc) as team_rank -- FROM -- ( -- SELECT -- week_number, -- team, -- user, -- COUNT(query_date) OVER (PARTITION BY user, week_number) as query_cnt -- FROM -- (SELECT -- *, -- EXTRACT(WEEK FROM query_date) as week_number -- FROM advanced.query_logs -- ) -- ) -- QUALIFY team_rank = 1 -- ORDER BY week_number, team -- 문제 의도 : 원본 데이터는 row 마다 데이터가 있고, 그걸 집계해서 활용. GROUP BY 사용 후에 윈도우 함수 -- 3) (2번 문제에서 사용한 주차별 쿼리 사용) 쿼리를 실행한 시점 기준 1주 전에 쿼리 실행 수를 별도의 컬럼으로 확인할 수 있는 쿼리를 작성해주세요 -- SELECT -- *, -- LAG(query_cnt) OVER (PARTITION BY user ORDER BY week_number) as prev_week_query_count -- FROM -- (SELECT -- distinct week_number, -- team, -- user, -- COUNT(query_date) OVER (PARTITION BY user, week_number) as query_cnt -- FROM -- (SELECT -- *, -- EXTRACT(WEEK FROM query_date) as week_number -- FROM advanced.query_logs -- ) -- ) -- ORDER BY user, week_number -- 4) 시간의 흐름에 따라, 일자별로 유저가 실행한 누적 쿼리 수를 작성해주세요 -- SELECT -- distinct *, -- COUNT(query_date) OVER (PARTITION BY user, query_date ORDER BY query_date) as query_cnt, -- COUNT(query_date) OVER (PARTITION BY user ORDER BY query_date) as cumulative_query_cnt -- FROM advanced.query_logs -- ORDER BY user, query_date -- 출제 의도 : Default Frame 이해 // 집계분석에서 Frame의 Default 값 = BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- 5) 다음 데이터는 주문 횟수를 나타낸 데이터입니다. 만약 주문 횟수가 없으면 NULL로 기록됩니다. 이런 데이터에서 NULL 값이라고 되어있는 부분을 바로 이전 날짜의 값으로 채워주는 쿼리를 작성해주세요 -- 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 -- ) -- SELECT -- date, -- CASE WHEN number_of_orders is not null THEN number_of_orders -- ELSE LAG(number_of_orders) OVER (ORDER BY date) -- END AS number_of_orders -- FROM raw_data -- 수정 -- SELECT -- *, -- LAST_VALUE(number_of_orders IGNORE NULLS) OVER (ORDER BY date) as last_value_orders -- FROM raw_data -- 출제 의도 : first_value, last_value 사용할 때 null을 포함하고 싶으면 ignore nulls 활용 -- 6) 5번 문제에서 NULL을 채운 후, 2일 전 ~ 현재 데이터의 평균을 구하는 쿼리를 작성해주세요(이동 평균) -- 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 -- ) -- SELECT -- *, -- AVG(number_of_orders) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg -- FROM -- (SELECT -- date, -- CASE WHEN number_of_orders is not null THEN number_of_orders -- ELSE LAG(number_of_orders) OVER (ORDER BY date) -- END AS number_of_orders -- FROM raw_data -- ) -- 7) app_logs 테이블에서 Custom Session을 만들어 주세요. 이전 이벤트 로그와 20초가 지나면 새로운 Session을 만들어 주세요. Session은 숫자로 (1, 2, 3 ...) 표시해도 됩니다 -- 2022-08-18일의 user_pseudo_id(1997494153.8491999091)은 session_id가 4까지 나옵니다 WITH base AS ( SELECT event_date, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') as event_datetime, 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 *, DATETIME_DIFF(event_datetime, prev_event_datetime, SECOND) as second_diff FROM ( SELECT *, LAG(event_datetime) OVER (PARTITION BY user_pseudo_id ORDER BY base.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 -- session 을 구할 때 쿼리가 길어질 수 있음. 하루에 접속을 여러번 하는 서비스 -> session 기반이 좋을 수 있고, 아니라면 일자별 유저 집계가 나을 수 있음 FROM ( SELECT *, CASE WHEN second_diff IS NULL THEN 1 WHEN second_diff >= 20 THEN 1 #세션을 나누는 기준. 데이터 탐색 후 결정 / 보통 앱 로그는 30초 or 60초 ELSE 0 END as session_start FROM diff_data ) ORDER BY event_datetime -- 세션 정리 -- 이전 이벤트 로그와 현재 이벤트 로그의 diff => 초나 분을 구한다 -- 기준을 가지고 그 기준 보다 높으면 새로운 세션이라고 한다 -- 첫번째 값엔 null이 있을 수 있어, 이 부분도 챙겨야 한다 -- 새로운 세션, session_start 기반으로 누적합 => session_num이 된다

문제 1번SELECT user , team , query_date , COUNT(query_date) OVER (PARTITION BY user) AS total_query_cnt FROM advanced.query_logs ORDER BY user, query_date; 문제 2번WITH base AS ( SELECT EXTRACT(week FROM query_date) AS week_number , user , team , COUNT(query_date) AS total_query_cnt FROM advanced.query_logs GROUP BY week_number, user, team ) SELECT week_number , team , user , total_query_cnt , RANK() OVER (PARTITION BY team ORDER BY total_query_cnt DESC) AS ranking_in_team FROM base QUALIFY ranking_in_team = 1 ORDER BY week_number, team;  문제 3번WITH base AS ( SELECT EXTRACT(week FROM query_date) AS week_number , user , team , COUNT(query_date) AS query_cnt FROM advanced.query_logs GROUP BY week_number, user, team ) SELECT user , team , week_number , query_cnt , LAG(query_cnt) OVER (PARTITION BY user ORDER BY week_number) AS prev_week_query_cnt FROM base ORDER BY user, week_number; 문제 4번SELECT user , team , query_date , query_count , SUM(query_count) OVER (PARTITION BY user ORDER BY query_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_query_count FROM ( SELECT user , team , query_date , COUNT(query_date) AS query_count FROM advanced.query_logs GROUP BY 1,2,3 ) ORDER BY user, query_date; 문제 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 ) SELECT date , LAST_VALUE(number_of_orders ignore nulls) OVER (ORDER BY date) AS number_of_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 ) SELECT date , number_of_orders , AVG(number_of_orders) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM ( SELECT date , LAST_VALUE(number_of_orders ignore nulls) OVER (ORDER BY date) AS number_of_orders FROM raw_data ) 문제 7번WITH base AS ( SELECT event_date , event_timestamp , DATETIME(TIMESTAMP_MICROS(event_timestamp),'Asia/Seoul') AS event_datetime , event_name , user_id , user_pseudo_id , LAG(DATETIME(TIMESTAMP_MICROS(event_timestamp),'Asia/Seoul')) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS before_event_datetime FROM advanced.app_logs WHERE 1=1 AND event_date = '2022-08-18' ) SELECT * ,DATETIME_DIFF(event_datetime, before_event_datetime, SECOND) AS second_diff , CASE WHEN DATETIME_DIFF(event_datetime, before_event_datetime, SECOND) IS NULL OR DATETIME_DIFF(event_datetime, before_event_datetime, SECOND) >= 20 THEN 1 ELSE 0 END AS session_start , SUM(CASE WHEN DATETIME_DIFF(event_datetime, before_event_datetime, SECOND) >= 20 THEN 1 ELSE 0 END) OVER (PARTITION BY user_pseudo_id ORDER BY event_datetime) + 1 AS session_temp FROM base

 <FRAME 문제> 1번문제: 총 수량(amount_total), 수량의 누적 합(cumulativ_sum), 유저별 수량의 누적 합(cumulative_sum(user)), 최근 5개 수량의 평균(last_5_avg) 출력쿼리를 작성하는 목표, 확인할 지표: 수량의 총합 또는 누적 합 구하기쿼리 계산 방법: 윈도우 함수 - AVG, SUM데이터의 기간: X사용할 테이블: advanced.ordersJOIN KEY: X데이터 특징: XSELECT order_id, -- 각 주문의 고유 식별자 order_date, -- 주문 날짜 user_id, -- 주문한 사용자 ID amount, -- 주문 금액 SUM(amount) OVER (ORDER BY order_date, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS amount_total, -- 주문 데이터 전체의 총 금액을 계산합니다. -- 'ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING'은 모든 행을 참조한다는 의미로, 테이블의 전체 합계를 계산합니다. SUM(amount) OVER (ORDER BY order_date, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum, -- 날짜와 주문 ID 순서로 누적 합계를 계산합니다. -- 'ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW'는 현재 행까지의 모든 이전 행을 포함하여 누적 합계를 계산합니다. SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum_by_user, -- 사용자별로 누적 합계를 계산합니다. -- 'PARTITION BY user_id'는 각 사용자별로 데이터를 분리해 누적 합계를 계산하도록 합니다. -- 즉, 각 사용자가 주문한 금액의 누적 합계를 구합니다. AVG(amount) OVER (ORDER BY order_date, order_id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS last_5_orders_avg_amount -- 현재 행을 기준으로 바로 이전 5개의 행의 주문 금액 평균을 계산합니다. -- 'ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING'은 현재 행 이전의 5개 행에서 1개 행까지 포함하여 평균을 구합니다. -- 이를 통해 최근 5건의 주문에 대한 평균 금액을 알 수 있습니다. FROM advanced.orders -- advanced 데이터셋의 orders 테이블에서 데이터를 가져옵니다. --QUALIFY last_5_orders_avg_amount >= 150 -- 마지막 5건의 주문 평균 금액이 150 이상인 주문만을 결과로 필터링하기 위한 조건입니다. 현재는 주석 처리되어 있어 실행되지 않습니다. ORDER BY order_id; -- 주문 ID 기준으로 결과를 정렬하여 각 주문에 대해 계산된 값을 순서대로 확인합니다.   <총 정리 문제> 1번문제:사용자별 쿼리를 실행한 총 횟수를 구하는 쿼리를 작성해주세요. 우측에 새로운 컬럼을 만들어주세요. SELECT * -- 모든 열을 선택합니다. advanced.query_logs 테이블의 모든 컬럼을 그대로 포함합니다. , COUNT(user) OVER (PARTITION BY user) AS total_query_cnt -- 'user'별로 쿼리 실행 횟수를 계산합니다. -- 'PARTITION BY user'를 사용하여 각 사용자별로 나누고, 그 안에서 해당 사용자가 몇 번의 쿼리를 실행했는지 계산합니다. -- 이 값은 각 사용자가 테이블에 몇 번 등장했는지를 의미합니다. FROM advanced.query_logs -- advanced 데이터셋의 query_logs 테이블에서 데이터를 가져옵니다. ORDER BY query_date, user; -- 'query_date'와 'user'를 기준으로 데이터를 정렬합니다. 'query_date'를 우선적으로, 같은 날짜 내에서는 'user'를 기준으로 정렬합니다.   2번문제: 주차별로 팀 내에서 쿼리를 많이 실행한 수를 구한 후, 실행한 수를 활용해 랭킹을 구해주세요. 단, 랭킹이 1등인 사람만 결곽가 보이도록 해주세요.SELECT * -- 모든 열을 선택합니다. 서브쿼리에서 생성된 모든 컬럼을 포함합니다. , RANK() OVER (PARTITION BY week_number, team ORDER BY query_cnt DESC) AS team_rank -- RANK() 함수를 사용하여 각 팀 내에서 쿼리 실행 횟수(query_cnt)에 따라 랭킹을 부여합니다. -- 'PARTITION BY week_number, team'은 팀과 주차별로 데이터를 나누어 그 안에서 랭킹을 계산합니다. -- 'ORDER BY query_cnt DESC'는 쿼리 실행 횟수에 따라 랭킹을 내림차순으로 정렬합니다. -- 이 결과로 각 팀 내에서 쿼리 실행 횟수에 따라 랭킹이 부여된 'team_rank'라는 새로운 열이 추가됩니다. FROM (SELECT EXTRACT(WEEK FROM query_date) AS week_number -- query_date에서 주차를 추출하여 'week_number' 열을 만듭니다. , team -- 팀을 나타내는 컬럼을 선택합니다. , user -- 사용자를 나타내는 컬럼을 선택합니다. , COUNT(user) AS query_cnt -- 사용자가 쿼리를 실행한 횟수를 집계하여 'query_cnt'라는 열로 만듭니다. FROM advanced.query_logs -- advanced 데이터셋의 query_logs 테이블에서 데이터를 가져옵니다. GROUP BY ALL) -- 모든 선택된 컬럼을 그룹화합니다. 여기서는 'week_number', 'team', 'user'로 데이터를 그룹화합니다. QUALIFY team_rank = 1 -- QUALIFY 절은 필터링 조건을 적용하는 역할을 하며, 여기서는 RANK가 1인 경우만 선택합니다. -- 즉, 각 팀 내에서 쿼리 실행 횟수가 가장 많은 사용자(1등)만 필터링됩니다. ORDER BY week_number, team; -- 주차별로, 그리고 각 주차 내에서 팀별로 데이터를 정렬합니다.    3번문제: (2번 문제에서 사용한 주차별 쿼리 사용) 쿼리를 실행한 시점 기준 1주 전에 쿼리 실행 수를 별도의 컬럼으로 확인할 수 있는 쿼리를 작성해주세요.SELECT * -- 모든 열을 선택합니다. 서브쿼리에서 생성된 모든 컬럼을 포함합니다. , LAG(query_cnt) OVER (PARTITION BY user ORDER BY week_number) AS prev_week_query_count -- LAG() 함수를 사용하여 이전 주차(week)의 쿼리 실행 수를 가져옵니다. -- 'PARTITION BY user'를 통해 각 사용자별로 데이터를 나누고, 각 사용자에 대해 'week_number' 순서로 정렬하여 이전 주차의 값을 가져옵니다. -- 결과적으로, 이전 주차의 쿼리 실행 횟수를 'prev_week_query_count'라는 새로운 열로 추가합니다. FROM (SELECT user -- 쿼리를 실행한 사용자를 나타내는 열입니다. , team -- 사용자가 속한 팀을 나타내는 열입니다. , EXTRACT(WEEK FROM query_date) AS week_number -- query_date에서 주차(week)를 추출하여 'week_number'라는 열로 만듭니다. , COUNT(user) AS query_cnt -- 사용자가 실행한 쿼리 횟수를 집계하여 'query_cnt' 열로 만듭니다. FROM advanced.query_logs -- advanced 데이터셋의 query_logs 테이블에서 데이터를 가져옵니다. GROUP BY ALL) -- 선택된 모든 열을 그룹화합니다. ORDER BY user, week_number; -- 최종 결과를 사용자별로 정렬하고, 그다음 각 사용자 내에서 주차별로 정렬합니다.  4번문제:시간의 흐름에 따라, 일자별로 유저가 실행한 누적 쿼리 수를 작성해주세요.SELECT * -- 모든 열을 선택합니다. 서브쿼리에서 생성된 모든 컬럼을 포함합니다. , SUM(query_cnt) OVER (PARTITION BY user ORDER BY query_date) AS cumulative_query_count -- SUM 윈도우 함수를 사용하여 누적 쿼리 실행 수를 계산합니다. -- 'PARTITION BY user'를 통해 각 사용자별로 데이터를 나누고, 각 사용자에 대해 'query_date' 순서로 정렬하여 누적 값을 계산합니다. -- 결과적으로, 각 사용자에 대한 누적 쿼리 실행 횟수가 'cumulative_query_count'라는 새로운 열로 추가됩니다. FROM (SELECT user -- 쿼리를 실행한 사용자를 나타내는 열입니다. , team -- 사용자가 속한 팀을 나타내는 열입니다. , query_date -- 쿼리가 실행된 날짜를 나타내는 열입니다. , COUNT(user) AS query_cnt -- 사용자가 쿼리를 실행한 횟수를 집계하여 'query_cnt' 열로 만듭니다. FROM advanced.query_logs -- advanced 데이터셋의 query_logs 테이블에서 데이터를 가져옵니다. GROUP BY ALL) -- 선택된 모든 열을 그룹화하여 중복되지 않게 집계합니다. ORDER BY user, query_date; -- 최종 결과를 사용자별로 정렬하고, 그다음 각 사용자의 쿼리 실행 날짜별로 정렬합니다.  5번문제:다음 데이터는 주문 횟수를 나타낸 데이터입니다. 만약 주문 횟수가 없으면 NULL로 기록됩니다. 이런 데이터에서 NULL 값이라고 되어있는 부분을 바로 이전 날짜의 값으로 채워주는 쿼리를 작성해주세요.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 ) -- 임시 데이터를 활용하여 쿼리의 기능을 확인하기 위한 서브쿼리입니다. SELECT * -- 모든 열을 선택합니다 (날짜와 주문 수). , LAST_VALUE(number_of_orders IGNORE NULLS) OVER (ORDER BY date) AS number_of_orders -- NULL 값을 제외하고 마지막으로 발견된 'number_of_orders' 값을 가져옵니다. -- 'ORDER BY date'를 사용하여 날짜 순서대로 정렬하며, -- 각 행에서 가장 최근의 유효한 (NULL이 아닌) 'number_of_orders' 값을 반환합니다. FROM raw_data -- 데이터가 저장된 'raw_data' 임시 테이블에서 데이터를 조회합니다. ORDER BY date; -- 최종 결과를 날짜 순서대로 정렬하여 출력합니다.   6번문제: 5번 문제에서 NULL을 채운 후, 2일 전 ~ 현재 데이터의 평균을 구하는 쿼리를 작성해주세요.(이동 평균)WITH raw_data AS ( -- 임시 데이터를 정의합니다. 날짜(date)와 해당 날짜의 주문 수(number_of_orders)를 포함한 테이블을 생성합니다. 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 ) -- 누락된 데이터를 보완하고, 이동 평균을 계산하는 쿼리를 작성합니다. SELECT * -- 모든 열을 선택한 뒤, 이동 평균(moving_avg)을 추가로 계산합니다. , ROUND(AVG(number_of_orders) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS moving_avg -- AVG 함수를 사용하여 현재 행을 포함해 이전 두 행의 값에 대한 이동 평균을 계산합니다. -- 계산된 결과를 소수점 첫 번째 자리까지 반올림합니다. FROM ( SELECT date, -- IFNULL 함수를 사용하여 NULL 값을 보완합니다. -- 이전 날짜의 주문 수를 가져와 현재 행의 주문 수가 NULL인 경우 보완합니다. IFNULL(number_of_orders, LAG(number_of_orders) OVER (ORDER BY date)) AS number_of_orders FROM raw_data ) -- 최종 결과를 날짜 순서로 정렬하여 출력합니다. ORDER BY date;   7번문제: app_logs 테이블에서 Custom Session을 만들어 주세요. 이전 이벤트 로그와 20초가 지나면 새로운 Session을 만들어 주세요. Session은 숫자로 (1, 2, 3 …) 표시해도 됩니다. 2022-08-18일의 user_pseudo_id(1997494153.8491999091)은 session_id가 4까지 나옵니다.WITH base AS ( -- Step 1. 기초 데이터 추출 및 이전 이벤트 시간 계산 SELECT event_date, -- 이벤트가 발생한 날짜 event_timestamp, -- 마이크로초 단위의 이벤트 발생 시각 DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime, -- 이벤트 발생 시각을 'Asia/Seoul' 시간대로 변환하여 가독성을 높임 event_name, -- 이벤트의 이름 user_id, -- 사용자 ID user_pseudo_id, -- 사용자 고유의 익명화된 ID DATETIME(TIMESTAMP_MICROS(LAG(event_timestamp) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp)), 'Asia/Seoul') AS before_event_datetime -- 이전 이벤트의 발생 시각을 'Asia/Seoul' 시간대로 변환 -- LAG() 함수를 사용해 각 사용자의 이전 이벤트 발생 시각을 가져옴 FROM advanced.app_logs WHERE event_date = '2022-08-18' -- 특정 날짜의 이벤트만 필터링 AND user_pseudo_id = '1997494153.8491999091' -- 특정 사용자에 대한 로그만 조회 ), -- Step 2. 세션 유지 시간 및 신규 세션 여부 계산 session_info AS ( SELECT *, TIMESTAMP_DIFF(event_datetime, before_event_datetime, SECOND) AS second_diff, -- 이전 이벤트와 현재 이벤트 사이의 시간 차이를 초 단위로 계산 CASE WHEN TIMESTAMP_DIFF(event_datetime, before_event_datetime, SECOND) >= 20 OR TIMESTAMP_DIFF(event_datetime, before_event_datetime, SECOND) IS NULL THEN 1 -- 두 이벤트 사이의 시간 차이가 20초 이상이거나 이전 이벤트가 없는 경우 새로운 세션 시작으로 간주 ELSE NULL END AS session_start -- 새로운 세션이 시작되면 1, 그렇지 않으면 NULL FROM base ) -- Step 3. 신규 세션 ID 세팅 SELECT *, SUM(session_start) OVER (PARTITION BY user_pseudo_id ORDER BY event_datetime) AS session_id -- 각 사용자의 이벤트 시퀀스에서 새로운 세션이 시작될 때마다 session_start 값을 누적하여 세션 ID를 부여 FROM session_info ORDER BY event_date, event_timestamp; -- 이벤트 발생 시각을 기준으로 정렬하여 결과를 출력    

문제 1번-- 1. 사용자별 쿼리를 실행한 횟수의 총합 -- GROUP BY 사용 X. 우측에 새로운 컬럼 생성하기. SELECT user , team , query_date , COUNT(query_date) OVER (PARTITION BY user) AS total_query_cnt FROM advanced.query_logs ORDER BY user, query_date; 문제 2번-- 2. 주차별로 팀내에서 쿼리를 많이 실행한 수 → 랭킹 구하기 -- 팀별로 랭킹이 1위인 사람만 출력하기 WITH base AS ( SELECT EXTRACT(week FROM query_date) AS week_number , user , team , COUNT(query_date) AS total_query_cnt FROM advanced.query_logs GROUP BY week_number, user, team ) SELECT week_number , team , user , total_query_cnt , RANK() OVER (PARTITION BY team ORDER BY total_query_cnt DESC) AS ranking_in_team FROM base QUALIFY ranking_in_team = 1 ORDER BY week_number, team;  문제 3번-- 3. (2번 문제에서 사용한 주차별 쿼리 사용) 쿼리를 실행한 시점 -- 1주 전에 쿼리를 실행한 횟수를 별도의 컬럼으로 출력 WITH base AS ( SELECT EXTRACT(week FROM query_date) AS week_number , user , team , COUNT(query_date) AS query_cnt FROM advanced.query_logs GROUP BY week_number, user, team ) SELECT user , team , week_number , query_cnt , LAG(query_cnt) OVER (PARTITION BY user ORDER BY week_number) AS prev_week_query_cnt FROM base ORDER BY user, week_number; 문제 4번-- 4. 일자별 유저가 쿼리한 횟수 누적합 SELECT user , team , query_date , query_count , SUM(query_count) OVER (PARTITION BY user ORDER BY query_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_query_count FROM ( SELECT user , team , query_date , COUNT(query_date) AS query_count FROM advanced.query_logs GROUP BY 1,2,3 ) ORDER BY user, query_date; 문제 5번-- 주문 횟수 테이블: 데이터 없으면 NULL로 표기됨. -- 5. NULL값을 바로 전달 데이터로 채우기 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 ) SELECT date , LAST_VALUE(number_of_orders ignore nulls) OVER (ORDER BY date) AS number_of_orders FROM raw_data; 문제 6번-- 6. (5번 완료 후) 2일 전 ~ 현재 데이터의 평균 구하기 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 ) SELECT date , number_of_orders , AVG(number_of_orders) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM ( SELECT date , LAST_VALUE(number_of_orders ignore nulls) OVER (ORDER BY date) AS number_of_orders FROM raw_data ) 문제 7번-- 7. app_logs 테이블에서 커스텀 세션 추가 -- 이전 이벤트 로그와 20초 이상 차이가 나면 새로운 세션으로 정의 -- 세션은 숫자로(1,2,3...) 표시 가능 WITH base AS ( SELECT event_date , event_timestamp , DATETIME(TIMESTAMP_MICROS(event_timestamp),'Asia/Seoul') AS event_datetime , event_name , user_id , user_pseudo_id , LAG(DATETIME(TIMESTAMP_MICROS(event_timestamp),'Asia/Seoul')) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS before_event_datetime FROM advanced.app_logs WHERE 1=1 AND event_date = '2022-08-18' ) SELECT * ,DATETIME_DIFF(event_datetime, before_event_datetime, SECOND) AS second_diff , CASE WHEN DATETIME_DIFF(event_datetime, before_event_datetime, SECOND) IS NULL OR DATETIME_DIFF(event_datetime, before_event_datetime, SECOND) >= 20 THEN 1 ELSE 0 END AS session_start , SUM(CASE WHEN DATETIME_DIFF(event_datetime, before_event_datetime, SECOND) >= 20 THEN 1 ELSE 0 END) OVER (PARTITION BY user_pseudo_id ORDER BY event_datetime) + 1 AS session_temp FROM base

문제 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

2주차윈도우 함수 - 탐색 함수프로덕트 측면에서 윈도움 탐색 함수를 활용해서 할 수 있는 것?어떤 user가 앱/웹에 접속한 후, 어떤 화면으로 이동했는지를 알 수 있음. 즉 시간의 흐름에 따른 행동을 볼 수 있음(다음 ROW의 Page를 확인 가능)엡 로그 상에서 직전 이벤트와 현재 이벤트가 동일한 것들을 필터링할 수 있음(LEAD한 값과 기존 컬럼과의 비교). 같은 Page를 연속으로 접근한 경우 하나로 처리해서 ‘퍼널’을 구할 때 활용할 수 있음.리텐션 쿼리를 작성할때 기준점을 만들 수 있음(유저의 첫 접속일, FIRST_VALUE 이용)2. 윈도우 함수의 번호 지정 함수코드 작성(윈도우 함수 Frame)# 윈도우 함수 Frame 연습 문제 SELECT *, SUM(amount) OVER() AS amount_total, # over안에 아무것도 들어가지 않는 경우도 있음!, 전체의 SUM. SUM(amount) OVER(ORDER BY order_id) AS cumulative_sum, SUM(amount) OVER(PARTITION BY user_id ORDER BY order_id) AS cumulative_sum_by_user, AVG(amount) OVER(ORDER BY order_id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING ) AS last_5_orders_avg_amount FROM advanced.orders ORDER BY user_id 3. 윈도우 함수 연습 문제코드 작성# 문제 1) 사용자별 쿼리를 실행한 총 횟수를 구하는 쿼리 작성. # 단 , GROUP BY를 사용해서 집계하는 것이 아닌 데이터 우측에 새로운 컬럼 생성. SELECT *, COUNT(query_date) OVER (PARTITION BY user) AS total_query_cnt FROM advanced.query_logs order by user, query_date # 문제 2) 주차별로 팀 내에서 쿼리를 많이 실행한 수를 구한 후(먼저 집계 한 후) # 실행한 수를 활용해 랭킹을 구해주세요.(윈도우 함수 활용) # 단, 랭킹이 1등인 사람만 결과가 보이도록 해주세요. **문제의 의도 : 원본 데이터는 1 row 마다 데이터가 있고, 그걸 집계해서 사용(GROUP BY) => 그 다음에 윈도우 함수 사용.** with query_cnt_by_team AS ( SELECT EXTRACT(week from query_date) AS week_num, team, user, COUNT(query_date) AS query_cnt -- RANK() OVER(ORDER BY) FROM advanced.query_logs GROUP BY ALL ) SELECT *, RANK() OVER(PARTITION BY week_num, team ORDER BY query_cnt DESC) AS rk FROM query_cnt_by_team QUALIFY rk = 1 ORDER BY week_num, team, query_cnt DESC # 문제 3) (2번 문제에서 사용한 주차별 쿼리 사용) 쿼리를 실행한 시점 기준 , # 1주 전에 쿼리 실행 수를 별도의 컬럼으로 확인할 수 있는 쿼리를 작성. with query_cnt_by_team AS ( SELECT EXTRACT(week from query_date) AS week_num, team, user, COUNT(query_date) AS query_cnt -- RANK() OVER(ORDER BY) FROM advanced.query_logs GROUP BY ALL ) SELECT *, LAG(query_cnt,1) OVER(PARTITION BY user ORDER BY week_num) AS last_week_query_cnt FROM query_cnt_by_team # 문제 4) 시간의 흐름(query_date)에 따라 일자별로 유저가 실행한 누적 쿼리 수를 작성해주세요. Fram 관련 문제(누적합) 누적 쿼리 : 과거의 시간부터(UNBOUNDED PRECEDING)부터 현재까지(CURRENT ROWS) Frame의 Default 값 : UNBOUNDED PRECEDING ~ CURRENT ROWS => 출제 의도 SELECT *, SUM(query_cnt) OVER(PARTITION BY user ORDER BY query_date ) AS cumulative_sum, SUM(query_cnt) OVER(PARTITION BY user ORDER BY query_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum2 FROM ( SELECT query_date, team, user, count(user) as query_cnt FROM advanced.query_logs GROUP BY ALL ) QUALIFY cumulative_sum != cumulative_sum2 ORDER BY user,query_date # 문제 5) 다음 데이터는 주문 횟수를 나타낸 데이터입니다. # 만약 주문 횟수가 없으면 NULL로 기록됩니다. # 이런 데이터에서 NULL 값이라고 되어있는 부분을 바로 이전 날짜의 값으로 채워주는 쿼리를 작성. 출제 의도 : null을 제외하고 연산하고 싶을때는 IGONORE NULLS 활용!! 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 ) SELECT *, LAST_VALUE(number_of_orders IGNORE nulls) OVER(ORDER BY date) AS last_value_orders FROM raw_data # 문제 6) 5번 문제에서 NULL을 채운 후, 2일 전~ 현재 데이터의 평균을 구하는 쿼리를 작성(이동평균) 출제 의도 : Frame을 지정할 수 있는가? 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_date AS ( SELECT *, LAST_VALUE(number_of_orders IGNORE nulls) OVER(ORDER BY date) AS number_of_order FROM raw_data ) SELECT *, AVG(number_of_order) OVER(ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM filled_date # 문제 7) app_logs 테이블에서 Custom Session을 만들어 주세요. # 이전 이벤트 로그와 20호가 지나면 새로운 Session을 만들어 주세요. # Session은 숫자로 (1,2,3,---) 표시해도 됩니다 # 2022-08-18일의 user_pseudo_id(1997494153.8491999091)은 sessio_id가 4까지 나옵니다. session을 구할 때 쿼리가 길어질 수 있음. 하루에 접속을 여러번 하는 서비스 => session 기반이 좋을 수 있고, 아니라고 하면 일자별 유저 집계가 나을 수 있음. - 세션 정리 - 이전 이벤트 로그와 현재 이벤트 로그의 diff => 초나 분을 구한다 - 그 기준을 가지고 기준보다 높으면 새로운 세션이라고 생각한다 - 첫번째 값엔 null이 있을 수 있어서, 이 부분도 챙겨야 한다.=> 1로 바꿔준다(Case 문 활용) - 새로운 세션, session_start 값을 기반으로 누적합 => session_num이 된다! 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' ORDER BY event_timestamp ), diff_date AS ( SELECT *, DATETIME_DIFF(event_datetime,prev_event_datetime, second) AS second_diff FROM ( SELECT *, LAG(event_datetime,1) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS prev_event_datetime # event_datetime이랑 prev_datetime을 빼서 20초가 넘으면 새로운 세션으로 정의. # 20초가 넘지 않으면 기존 세션 / DATETIME_DIFF() => 차이를 구할 수 있음. 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 # session을 나누는 기준 초. 데이터 탐색하면서 결정. 보통 앱 록느에서는 30초, 60초 등으로 정함. ELSE 0 END AS session_start # 세션이 시작되됨을 알리는 session_start FROM diff_date )

--윈도우함수(Analytics Function) - 탐색 함수 : LEAD, LAG, FIRST_VALUE(유저들의 첫 방문일은 언제인가요?), LAST_VALUE - 탐색 함수 연습 문제 - 번호 지정 함수 : ROW_NUMBER, RANK - 집계 분석 함수 : AVG, SUM - 데이터의 범위를 제한하고 싶은 경우 : Frame 설정 - ROWS BETWEEN UNBOUNDED PRECEDING AND FOLLOWING - 윈도우 함수 조건 설정 : QUALIFY - 연습 문제 RANK() OVER (PARTITION BY 학년 ORDER BY 키 DESC) AS 학년 별 키 순위 SELECT user_id , visit_month , LEAD(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_visit_month , LEAD(visit_month,2) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_visit_month , FIRST_VALUE(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) --얘는 NULL을 포함해서 값을 가져옴 , FIRST_VALUE(numbers IGNORE NULLS) OVER(ORDER BY date) AS first_num , LAST_VALUE(numbers IGNORE NULLS) OVER(ORDER BY date) AS last_num , RANK() OVER (PARTITION BY product_type ORDER BY revenue DESC) AS rank --중복이 있으면 1위 같이 부여하고 3위를 줌 --상위 30%를 뽑고 싶을땐 이거 사용 , DENSE_RANK() OVER (PARTITION BY product_type ORDER BY revenue DESC) AS rank --중복이 있으면 1위 같이 부여하고 2위를 줌 , ROW_NUMBER() OVER (PARTITION BY product_type ORDER BY revenue DESC, id) AS rank --중복이 있으면 랜덤으로 숫자 1,2를 줌 , AVG(revenue) OVER(PARTITION BY product_type) AS avg_revenue , SUM(revenue) OVER(PARTITION BY product_type) AS sum_revenue FROM Table Frame: 1) 우리 회사의 모든 주문량은? 2) 특정 주문 시점에서 누적 주문량은? 3) 고객별 주문 시점에서 누적 주문량은?(누적 100만원 이상 회원 => 골드 등급!) 4) 최근 직전 5개의 평균 주문량은? - PRECEDING : 현재 행 기준으로 이전 행 - CURRENT ROW : 현재 행 - FOLLOWING : 현재 행 기준으로 이후 행 - UNBOUNDED : 처음부터 또는 끝까지(사전적 의미 : 묶이지 않고 제한되지 않음) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING : default)파티션내 모든 값을 가져오겠다는 의미 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING : 현재 행과 그 앞뒤 한 행씩을 포함해서 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW : 파티션의 처음부터 현재 행을 포함한 평균 SELECT order_id, order_date, user_id, amount, SUM(amount) OVER (PARTITION BY user_id) AS amount_total FROM advanced.orders WHERE 1=1 QUALIFY amount_total >= 500 --이게 이제는 가능 문제 1) user들의 다음 접속 월과 다다음 접속 월을 구하는 쿼리를 작성해주세요. SELECT user_id , visit_month , LEAD(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_1step_visit_month , LEAD(visit_month,2) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_2step_visit_month FROM advanced.analytics_fuction_01 ORDER BY user_id, visit_month 문제 2) user들의 다음 접속 월과 다다음 접속 월, 이전 접속 월을 구하는 쿼리를 작성해주세요. WITH raw as ( SELECT user_id , visit_month , LEAD(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_1step_visit_month , LEAD(visit_month,2) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_2step_visit_month , LAG(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS before_1step_visit_month FROM advanced.analytics_fuction_01 ORDER BY user_id, visit_month ) --LEAD는 다음, LAG는 이전(밀리고 당긴다고 생각하니 살짝 헷갈림) --3) 간격구하는 쿼리 SELECT user_id , visit_month , after_1step_visit_month - visit_month as diff FROM raw ORDER BY user_id, visit_month -- 4)첫번째, 마지막 방문월 SELECT user_id , visit_month , FIRST_VALUE(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) as first_visit_month , LAST_VALUE(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) as last_visit_month FROM advanced.analytics_fuction_01 ORDER BY user_id, visit_month --Frame 연습문제 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING : default)파티션내 모든 값을 가져오겠다는 의미 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING : 현재 행과 그 앞뒤 한 행씩을 포함해서 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW : 파티션의 처음부터 현재 행을 포함한 평균 select SUM(amount) OVER () AS amount_total --ORDER BY는 적는 순간 위에서부터 현재행까지 되는것 같음 , SUM(amount) OVER (ORDER BY order_date, order_id ) AS cumulative_sum , SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date, order_id) AS cumulative_sum_user --same SUM(amount) OVER (ORDER BY order_date, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS amount_total , SUM(amount) OVER (ORDER BY order_date, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum , SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum_user , AVG(amount) OVER (ORDER BY order_date, order_id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING AS last_5_avg --본인 값은 포함 안함 FROM advanced.analytics_fuction_01 --연습문제 7개 --업로드 예정 1) 사용자별 쿼리를 실행한 총 횟수를 구하는 쿼리를 작성해주세요. 단, GROUP BY를 사용해서 집계하는 것이 아닌 query_logs의 데이터의 우측에 새로운 컬럼을 만들어주세요. select user , team , query_date , count(query_date) over(partition by user) as total_query_cnt , count(*) over(partition by user) as total_query_cnt --이것도 되는지 확인 from advanced.query_logs 2) 주차별로 팀 내에서 쿼리를 많이 실행한 수를 구한 후, 실행한 수를 활용해 랭킹을 구해주세요. 단, 랭킹이 1등인 사람만 결과가 보이도록 해주세요 with raw as ( select user , team , extract(week from query_date) as weeks --week() 작동안됨 , count(*) as total_query_cnt --, count(user) as total_query_cnt --명시해주기 from advanced.query_logs group by 1,2,3 ) select * , rank() over(partition by weeks, team order by total_query_cnt) as team_rank from qualify rn=1 3) (2번 문제에서 사용한 주차별 쿼리 사용) 쿼리를 실행한 시점 기준 1주 전에 쿼리 실행 수를 별도의 컬럼으로 확인할 수 있는 쿼리를 작성해주세요 with raw as ( select user , team , week(query_date) as weeks , count(*) as query_cnt from advanced.query_logs group by 1,2,3 ) select * , lag(query_cnt) over(partition by team, user order by weeks) as prev_week_query_cnt -- , lag(query_cnt,1) --디폴트 from raw 4) 시간의 흐름에 따라, 일자별로 유저가 실행한 누적 쿼리 수를 작성해주세요 with raw as ( select user , team , query_date as query_date , count(*) as query_cnt from advanced.query_logs group by 1,2,3 --group by all 해줘도 됨 ) select * , sum(query_cnt) over(partition by team, user order by query_date) as prev_week_query_cnt --default frame: unbounded preceding and current row from raw --qualify로 프레임 넣은것과 아닌것이 실제로 차이가 나는지를 더블체크 해보는 습관을 들이자 --정말 데이터가 내 생각대로 생겼는지 확인해보는 작업은 중요하다는 생각 5) 다음 데이터는 주문 횟수를 나타낸 데이터입니다. 만약 주문 횟수가 없으면 NULL로 기록됩니다. 이런 데이터에서 NULL 값이라고 되어있는 부분을 바로 이전 날짜의 값으로 채워주는 쿼리를 작성해주세요 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 ) SELECT * , last_value(number_of_orders ignore nulls) over(order by date) as new_number_of_orders --요 접근법 생각해낸거 강의 덕분 FROM raw_data 6) 5번 문제에서 NULL을 채운 후, 2일 전 ~ 현재 데이터의 평균을 구하는 쿼리를 작성해주세요(이동 평균) 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 ) , total as ( SELECT * except(number_of_orders) , last_value(number_of_orders ignore nulls) over(order by date) as new_number_of_orders --요 접근법 생각해낸거 강의 덕분 FROM raw_data ) select avg(new_number_of_orders) over(order by date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg from total 7) app_logs 테이블에서 Custom Session을 만들어 주세요. 이전 이벤트 로그와 20초가 지나면 새로운 Session을 만들어 주세요. Session은 숫자로 (1, 2, 3 ...) 표시해도 됩니다 2022-08-18일의 user_pseudo_id(1997494153.8491999091)은 session_id가 4까지 나옵니다 with raw as ( select event_date , DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime --new , event_name , user_id , user_pseudo_id from advanced.app_logs where event_date = '2022-08-16' ) , date_diff as ( select *, lag(event_datetime) over(partition by user_pseudo_id order by event_datetime) as prev_event_datetime from raw ) , total as ( select * , DATETIME_DIFF(event_datetime, before_event_datetime, SECOND) as second_diff , case when second_diff is null then 1 when second_diff >= 20 then 1 --20 seconds else 0 end AS session_start from date_diff ) select * , sum(session_start) over(partition by user_pseudo_id order by event_datetime) as session_id from total

강의 문제 탐색 함수, Frame 연습 문제 (1~3)1번SELECT user_id, visit_month, LEAD(visit_month,1) OVER(PARTITION BY user_id ORDER BY visit_month) AS next_month, LEAD(visit_month,2) OVER(PARTITION BY user_id ORDER BY visit_month) AS the_month_after_next FROM `avdanced.analytics_function_01`2번SELECT user_id, visit_month, LEAD(visit_month,1) OVER(PARTITION BY user_id ORDER BY visit_month) AS next_month, LEAD(visit_month,2) OVER(PARTITION BY user_id ORDER BY visit_month) AS the_month_after_next, LAG(visit_month,1) OVER(PARTITION BY user_id ORDER BY visit_month) AS last_month FROM `avdanced.analytics_function_01` FrameSELECT -- 1번 모든 주문량 SUM(amount) OVER() AS amount_total, -- 2번 특정주문시점 누적주문량 #SUM(amount) OVER(partition by order_date) AS cumulative_sum, SUM(amount) OVER (ORDER BY order_date) AS cumulative_sum, -- 3번 고객별 주문 시점에서 누적 주문량 #SUM(amount) OVER(partition by user_id) AS cumulative_sum_by_user, SUM(amount) OVER(partition by user_id ORDER BY order_id) AS cumulative_sum_by_user, -- 4번 최근 직전 5개 평균 주문량 AVG(amount) OVER(ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS last_5_orders_avg_amount, AVG(amount) OVER(ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS last_5_unbounded_orders_avg_amount, AVG(amount) OVER(ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS all_orders_avg_amount FROM `avdanced.orders` 윈도우함수 연습문제(1~7)1번SELECT user, team, query_date, count(user) over(PARTITION BY user) AS total_query_cnt FROM advanced.query_logs ORDER BY query_date, user2번WITH query_cnt_by_team 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, team ORDER BY query_cnt DESC) AS rk FROM query_cnt_by_team QUALIFY rk = 1 ORDER BY week_number, team, query_cnt DESC3번WITH query_cnt_by_team 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, 1) OVER (PARTITION BY user ORDER BY week_number) AS prev_week_query_cnt FROM query_cnt_by_team ORDER BY user, week_number4번SELECT query_date, team, user, query_cnt, SUM(query_cnt) OVER (PARTITION BY user ORDER BY query_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum FROM ( SELECT query_date, team, user, COUNT(user) AS query_cnt FROM advanced.query_logs GROUP BY ALL ) ORDER BY user, query_date5번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 ) SELECT date, IF(number_of_orders is null , last_value(number_of_orders IGNORE NULLS) OVER(ORDER BY date asc), number_of_orders) AS number_of_orders_not_null 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_data7번WITH base AS( SELECT event_date, event_timestamp, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime, event_name, user_id, user_pseudo_id, DATETIME(TIMESTAMP_MICROS(LAG(event_timestamp) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp)), 'Asia/Seoul') AS before_event_datetime FROM advanced.app_logs WHERE event_date = '2022-08-18' AND user_pseudo_id = '1997494153.8491999091' ), session_info AS( SELECT *, TIMESTAMP_DIFF(event_datetime, before_event_datetime, SECOND) AS second_diff, CASE WHEN TIMESTAMP_DIFF(event_datetime, before_event_datetime, SECOND) >= 20 OR TIMESTAMP_DIFF(event_datetime, before_event_datetime, SECOND) IS NULL THEN 1 ELSE NULL END AS session_start FROM base ) SELECT *, SUM(session_start) OVER (PARTITION BY user_pseudo_id ORDER BY event_datetime) AS session_id FROM session_info ORDER BY event_date, event_timestamp;

강의 2-4: 연습 문제1user들의 다음 접속 월과 다다음 접속 월을 구하는 쿼리를 작성해주세요.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 second_next_visit_month FROM `bqmaster.advanced.analytics_function_01` ORDER BY user_id 강의 2-4: 연습 문제2user들의 다음 접속 월과 다다음 접속 월, 이전 접속 월을 구하는 쿼리를 작성해주세요.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 second_next_visit_month, LAG(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) AS previous_visit_month FROM `bqmaster.advanced.analytics_function_01` ORDER BY user_id 강의 2-4: 연습 문제3user가 접속했을 때, 다음 접속까지의 간격을 구하시오.SELECT *, LEAD(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) - visit_month AS next_visit_month_diff FROM `bqmaster.advanced.analytics_function_01` ORDER BY user_id -- 서브 쿼리 활용 SELECT user_id, visit_month, next_visit_month - visit_month AS next_visit_month_diff FROM ( SELECT *, LEAD(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) AS next_visit_month FROM `bqmaster.advanced.analytics_function_01` ) ORDER BY user_id 강의 2-4: 연습 문제4이 데이터셋을 기준으로 user_id의 첫번째 방문 월, 마지막 방문 월을 구하는 쿼리를 작성해주세요.SELECT *, FIRST_VALUE(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) AS first_visit_month, LAST_VALUE(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) AS last_visit_month FROM `bqmaster.advanced.analytics_function_01` ORDER BY user_id  강의 2-8: 연습 문제1amount_total, cumulative_sum, cumulative_sum_by_user, last_5_orders_avg_amount 컬럼 구하기SELECT *, SUM(amount) OVER() AS amount_total, SUM(amount) OVER(ORDER BY order_id) AS cumulative_sum, SUM(amount) OVER(PARTITION BY user_id ORDER BY order_id) AS cumulative_sum_by_user, AVG(amount) OVER(ORDER BY order_id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS last_5_orders_avg_amount FROM `bqmaster.advanced.orders` ORDER BY order_id  강의 2-11: 연습 문제1사용자별 쿼리를 실행한 총 횟수를 구하는 쿼리를 작성해주세요. 단, GROUP BY를 사용해서 집계하는 것이 아닌, query_logs의 데이터의 우측에 새로운 컬럼을 만들어주세요.SELECT *, COUNT(user) OVER(PARTITION BY user) AS total_query_cnt FROM `bqmaster.advanced.query_logs` ORDER BY query_date 강의 2-11: 연습 문제2주차별로 팀 내에서 쿼리를 많이 실행한 수를 구한 수, 실행한 수를 활용해 랭킹을 구해주세요. 단, 랭킹이 1등인 사람만 결과가 보이도록 해주세요.SELECT *, RANK() OVER(PARTITION BY week_number, team ORDER BY query_cnt DESC) AS team_rank FROM ( SELECT EXTRACT(WEEK FROM query_date) AS week_number, team, user, COUNT(user) query_cnt FROM `bqmaster.advanced.query_logs` GROUP BY ALL ) QUALIFY team_rank = 1 ORDER BY week_number, team 강의 2-11: 연습 문제3쿼리를 실행한 시점 기준 1주 전에 쿼리 실행 수를 별도의 컬럼으로 확인할 수 있는 쿼리를 작성해주세요.SELECT *, LAG(query_count) OVER(PARTITION BY user ORDER BY week_number) AS prev_week_query_count FROM ( SELECT user, team, EXTRACT(WEEK FROM query_date) AS week_number, COUNT(user) query_count FROM `bqmaster.advanced.query_logs` GROUP BY ALL ) ORDER BY user, team 강의 2-11: 연습 문제4시간의 흐름에 따라, 일자별로 유저가 실행한 누적 쿼리 수를 작성해주세요.SELECT *, SUM(query_count) OVER(PARTITION BY user ORDER BY query_date) AS cumulative_query_count FROM ( SELECT *, COUNT(user) AS query_count FROM `bqmaster.advanced.query_logs` GROUP BY ALL ) ORDER BY user, query_date 강의 2-11: 연습 문제5다음 데이터는 주문 횟수를 나타낸 데이터입니다. 만약 주문 횟수가 없으면 NULL로 기록됩니다. 이런 데이터에서 NULL 값이라고 되어있는 부분을 바로 이전 날짜의 값으로 채워주는 쿼리를 작성해주세요.SELECT date, LAST_VALUE(number_of_orders IGNORE NULLS) OVER(ORDER BY date) AS number FROM raw_data ORDER BY date 강의 2-11: 연습 문제65번 문제에서 NULL을 채운 후, 2일 전 ~ 현재 데이터의 평균을 구하는 쿼리를 작성해주세요(이동 평균)SELECT *, AVG(number) OVER(ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM ( SELECT date, LAST_VALUE(number_of_orders IGNORE NULLS) OVER(ORDER BY date) AS number FROM raw_data ) ORDER BY date 강의 2-11: 연습 문제7app_logs 테이블에서 Custom Session을 만들어 주세요. 이전 이벤트 로그와 20초가 지나면 새로운 Session을 만들어 주세요. Session은 숫자로(1, 2, 3) 표시해도 됩니다.WITH base AS ( SELECT event_date, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime, event_name, user_id, user_pseudo_id FROM `bqmaster.advanced.app_logs` ), add_before_datetime AS( SELECT *, LAG(event_datetime) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS before_event_datetime FROM base ), add_second_diff AS ( SELECT *, DATE_DIFF(event_datetime, before_event_datetime, SECOND) AS second_diff FROM add_before_datetime ), add_session_start AS ( SELECT *, CASE WHEN second_diff IS NULL THEN 1 WHEN second_diff >= 20 THEN 1 ELSE NULL END AS session_start FROM add_second_diff ) SELECT *, SUM(session_start) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS session_id FROM add_session_start ORDER BY event_datetime 

연습 문제 1데이터 테이블 : query_logs 사용자별 쿼리를 실행한 총 횟수를 구하는 쿼리를 작성해주세요. 단, group by 를 사용해서 집계하는 것이 아닌 query_logs의 데이터의 우측에 새로운 컬럼을 만들어주세요. -- 사용자별 쿼리를 실행한 총 횟수 : count() 전체 실행-- over(partition by user)select *, count(query_date) over(partition by user) as total_query_cnt from advanced.query_logs order by user, query_date  결과   연습 문제 2데이터 테이블 : query_logs주차별로 팀 내에 쿼리를 많이 실행한 수를 구한 후, 실행한 수를 활용해 랭킹을 구해주세요.단, 랭킹이 1등인 사람만 결과가 보이도록 해주세요.with query_cnt_by_team 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, team order by query_cnt desc) as rk from query_cnt_by_team -- qualify : 윈도우 함수의 조건을 설정할 때 사용한다. -- where 을 쓸 수 있지만 그럴 경우 서브쿼리를 활용해야함 qualify rk = 1 order by week_number, team, query_cnt desc -- 결과    연습 문제 3데이터 테이블 : 2번 문제에서 사용한 주차별 쿼리 사용쿼리를 실행한 시점 기준 1주 전에 쿼리 실횅수를 별도의 컬럼으로 확인할 수 있는 쿼리를 작성해주세요.with query_cnt_by_team 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,1) over(partition by user order by week_number) as prev_week_qeury_cnt from query_cnt_by_team-- over(partition by user) 결과    연습 문제 4시간의 흐름에 따라, 일자별로 유저가 실행한 누적 쿼리 수를 작성해 주세요.--누적 쿼리 : 과거의 시간(unbounded preceding) 부터 curren row 까지 --출제 의도 : default frame에 대해 알려드리고 싶었음. select *, sum(query_cnt) over(partition by user order by query_date) as cumulative_sum, sum(query_cnt) over(partition by user order by query_date rows between unbounded preceding and current row) as cumulative_sum2 -- frame의 default 값 : unbounded preceding ~~ current row from( select query_date, team, user, count(user) as query_cnt from advanced.query_logs group by all) -- # qualify cumulative_sum != cumulative_sum2 --where, qualify 조건 설정해서 2가지 값이 모두 같은지 비교 => 모두 같으면 != 연산 결과에 반환하는 값이 없을 것 order by user, query_date 결과     연습 문제 5다음 데이터는 주문 횟수를 나타낸 데이터입니다. 만약 주문 횟수가 없으면 NULL로 기록됩니다. 이런 데이터에서 NULL 값이라고 되어있는 부분을 바로. 이전 날짜의 값으로 채워주는 쿼리를 작성해주세요. 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 )-- 윈도우 함수의 first_value, last_value 에선 기본적으로 null을 포함해서 연상 -- null을 제외하고 싶으면 ignore nulls  함수를 쓰자--ignore x select *, last_value(number_of_orders) over(order by date) as last_value_orders from raw_data결과    -- ignore 사용 select *, last_value(number_of_orders ignore nulls ) over(order by date) as last_value_orders from raw_data 결과     연습 문제 65번 문제에서 null을 채운 후, 2일 전 ~ 현재 데이터의 평균을 구하는 쿼리를 작성해주세요 (이동평균) 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 *, last_value(number_of_orders ignore nulls ) over(order by date) as number_orders from raw_data) -- with 문을 또 쓸 수 없으니 , 로 구분해 주면 된다. select * , avg(number_of_orders) over(order by date rows between 2 preceding and current row) as moving_avg from filled_data -- frame: 2일 전 => between 2 preceding and current row 결과   

CREATE OR REPLACE TABLE workspace.analytics_function_01 AS ( SELECT 1004 AS user_id, 1 AS visit_month UNION ALL SELECT 1004, 3 UNION ALL SELECT 1004, 7 UNION ALL SELECT 1004, 8 UNION ALL SELECT 2112, 3 UNION ALL SELECT 2112, 6 UNION ALL SELECT 2112, 7 UNION ALL SELECT 3912, 4 ) ; #문제1 SELECT user_id, visit_month, lead(visit_month, 1) over(partition by user_id order by visit_month asc) as next_visit, lead(visit_month, 2) over(partition by user_id order by visit_month asc) as next_next_visit FROM workspace.analytics_function_01 ORDER BY user_id, visit_month ; #문제2 SELECT user_id, visit_month, lead(visit_month, 1) over(partition by user_id order by visit_month asc) as next_visit, lead(visit_month, 2) over(partition by user_id order by visit_month asc) as next_next_visit, lag(visit_month, 1) over(partition by user_id order by visit_month asc) as prev_visit FROM workspace.analytics_function_01 ORDER BY user_id, visit_month ; #문제3 SELECT user_id, visit_month, lead(visit_month, 1) over(partition by user_id order by visit_month asc) as next_visit_month, lead(visit_month, 1) over(partition by user_id order by visit_month asc) - visit_month as next_visit_month_diff FROM workspace.analytics_function_01 ORDER BY user_id, visit_month ; #추가문제 SELECT DISTINCT user_id, first_value(visit_month) over(partition by user_id order by visit_month asc rows between unbounded preceding and unbounded following) as first_visit_month, last_value(visit_month) over(partition by user_id order by visit_month asc rows between unbounded preceding and unbounded following) as last_visit_month FROM workspace.analytics_function_01 ORDER BY user_id ; #문제4 SELECT *, sum(amount) over() as total_amount, sum(amount) over(order by order_id asc rows between unbounded preceding and current row) as cumulative_sum, sum(amount) over(partition by user_id order by order_id asc rows between unbounded preceding and current row) as cumulative_sum_by_user, avg(amount) over(order by order_id asc rows between 5 preceding and 1 preceding) as last_five_orders_avg_amount FROM workspace.orders ORDER BY order_id ; #연습문제1 SELECT *, count(*) over(partition by user) as query_count_by_users FROM workspace.query_logs ; #연습문제2 SELECT query_weeknum, team, user, query_count, rank() over(partition by query_weeknum, team order by query_count desc) as query_rank FROM ( SELECT extract(week from query_date) as query_weeknum, team, user, count(1) as query_count FROM workspace.query_logs GROUP BY ALL ) QUALIFY query_rank = 1 ORDER BY query_weeknum ; #연습문제3 SELECT team, user, query_weeknum, query_count, lag(query_count, 1) over(partition by team, user order by query_weeknum asc) as prev_week_query_count FROM ( SELECT team, user, extract(week from query_date) as query_weeknum, count(1) as query_count FROM workspace.query_logs GROUP BY ALL ) #연습문제4 SELECT team, user, query_date, query_count, sum(query_count) over(partition by team, user order by query_date asc rows between unbounded preceding and current row) as cumulative_sum FROM ( SELECT team, user, query_date, count(1) as query_count FROM workspace.query_logs GROUP BY ALL ) ORDER BY team, user, query_date ; #연습문제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 ) SELECT date, ifnull(number_of_orders, real_prev_number_of_orders) as number_of_orders FROM ( SELECT date, number_of_orders, last_value(number_of_orders ignore nulls) over(order by date asc rows between unbounded preceding and 1 preceding) as real_prev_number_of_orders FROM raw_data ) ORDER BY date asc ; #연습문제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 ) SELECT date, number_of_orders, avg(number_of_orders) over(order by date asc rows between 2 preceding and current row) as moving_avg FROM ( SELECT date, ifnull(number_of_orders, real_prev_number_of_orders) as number_of_orders FROM ( SELECT date, number_of_orders, last_value(number_of_orders ignore nulls) over(order by date asc rows between unbounded preceding and 1 preceding) as real_prev_number_of_orders FROM raw_data ) ) ORDER BY date asc ; #연습문제7 WITH total_logs AS ( SELECT user_pseudo_id, event_name, timestamp_micros(event_timestamp) as event_datetime FROM workspace.app_logs ) SELECT user_pseudo_id, event_name, event_datetime, prev_event_datetime, second_diff, sum(session_change) over(partition by user_pseudo_id order by event_datetime asc) as session_id FROM ( SELECT *, case when event_datetime = first_event_datetime then 1 end as session_id, case when second_diff is null or second_diff >= 20 then 1 else 0 end as session_change FROM ( SELECT *, datetime_diff(event_datetime, prev_event_datetime, second) as second_diff FROM ( SELECT user_pseudo_id, event_name, event_datetime, lag(event_datetime, 1) over(partition by user_pseudo_id order by event_datetime asc) as prev_event_datetime, first_value(event_datetime) over(partition by user_pseudo_id order by event_datetime asc) as first_event_datetime FROM total_logs ) ) ) ORDER BY user_pseudo_id, event_datetime ;

탐색함수 연습문제문제 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. 탐색 함수 연습 문제# 1. user들의 다음 접속 월과 다다음 접속 월을 구하는 쿼리를 작성해주세요. # 쿼리를 작성하는 목표, 확인할 지표: user_id의 다음/다다음 visit_month 출력 # 쿼리 계산 방법: LEAD # 데이터의 기간: X # 사용할 테이블: advanced.analytics_function_01 # JOIN KEY: X # 데이터 특징: X SELECT user_id, visit_month, LEAD(visit_month, 1) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_visit_month, LEAD(visit_month, 2) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_two_visit_month, FROM advanced.analytics_function_01 ORDER BY user_id# 2. user들의 다음 접속 월과 다다음 접속 월, 이전 접속 월을 구하는 쿼리를 작성해주세요. # 쿼리를 작성하는 목표, 확인할 지표: user_id의 다음/다다음 visit_month 출력 # 쿼리 계산 방법: LEAD # 데이터의 기간: X # 사용할 테이블: advanced.analytics_function_01 # JOIN KEY: X # 데이터 특징: X SELECT user_id, visit_month, LEAD(visit_month, 1) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_visit_month, LEAD(visit_month, 2) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_two_visit_month, LAG(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS before_visit_month FROM advanced.analytics_function_01 ORDER BY user_id# 3. 유저가 접속했을 때, 다음 접속까지의 간격을 구하시오. SELECT *, (after_visit_month - visit_month) AS interval_of_after_visit_month FROM ( SELECT *, LEAD(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_visit_month, FROM advanced.analytics_function_01 ORDER BY user_id, visit_month )# 4. 유저의 첫번째 방문 월, 마지막 방문 월을 구하는 쿼리를 작성해주세요. SELECT *, FIRST_VALUE(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS first_visit_month, LAST_VALUE(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS last_visit_month FROM advanced.analytics_function_01 ORDER BY user_id, visit_month 2. FRAME 연습 문제# Frame 연습 문제: 총 수량(amount_total), 수량의 누적 합(cumulativ_sum), 유저별 수량의 누적 합(cumulative_sum(user)), 최근 5개 수량의 평균(last_5_avg) 출력 # 쿼리를 작성하는 목표, 확인할 지표: 수량의 총합 또는 누적 합 구하기 # 쿼리 계산 방법: 윈도우 함수 - AVG, SUM # 데이터의 기간: X # 사용할 테이블: advanced.orders # JOIN KEY: X # 데이터 특징: X SELECT *, SUM(amount) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS amount_total, SUM(amount) OVER (ORDER BY order_date, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum, SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum_user, AVG(amount) OVER (ORDER BY order_date, order_id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS last_5_avg FROM advanced.orders ORDER BY order_id 3. 총정리 연습 문제# 1. 사용자별 쿼리를 실행한 총 횟수를 구하는 쿼리를 작성해주세요. 우측에 새로운 컬럼을 만들어주세요. # 쿼리를 작성하는 목표, 확인할 지표: 유저별 쿼리 실행 총 횟수 # 쿼리 계산 방법: 윈도우 함수 COUNT # 데이터의 기간: X # 사용할 테이블: advanced.query_logs # JOIN KEY: X # 데이터 특징: X SELECT *, COUNT(user) OVER (PARTITION BY user) AS total_query_cnt FROM advanced.query_logs ORDER BY query_date, user# 2. 주차별로 팀 내에서 쿼리를 많이 실행한 수를 구한 후, 실행한 수를 활용해 랭킹을 구해주세요. 단, 랭킹이 1등인 사람만 결곽가 보이도록 해주세요. # 쿼리를 작성하는 목표, 확인할 지표: 주차별 쿼리 실행 수 랭킹, 팀 내 1등인 사람만 출력 # 쿼리 계산 방법: 윈도우 함수 COUNT, RANK # 데이터의 기간: X # 사용할 테이블: advanced.query_logs # JOIN KEY: X # 데이터 특징: X WITH create_week_number AS ( SELECT CASE WHEN query_date BETWEEN '2024-04-24' AND '2024-04-26' THEN 16 ELSE 17 END AS week_number, team, user FROM advanced.query_logs ), create_query_cnt AS ( SELECT *, COUNT(user) OVER(PARTITION BY week_number, user) AS query_cnt FROM create_week_number ) SELECT DISTINCT *, RANK() OVER (PARTITION BY week_number, team ORDER BY query_cnt DESC) AS team_rank FROM create_query_cnt QUALIFY team_rank = 1 ORDER BY week_number, team# 3. (2번 문제에서 사용한 주차별 쿼리 사용) 쿼리를 실행한 시점 기준 1주 전에 쿼리 실행 수를 별도의 컬럼으로 확인할 수 있는 쿼리를 작성해주세요. # 쿼리를 작성하는 목표, 확인할 지표: 주차별 쿼리 실행 수, 1주 전 쿼리 실행 수 출력 # 쿼리 계산 방법: 윈도우 함수 COUNT # 데이터의 기간: X # 사용할 테이블: advanced.query_logs # JOIN KEY: X # 데이터 특징: X WITH create_week_number AS ( SELECT user, team, CASE WHEN query_date BETWEEN '2024-04-24' AND '2024-04-26' THEN 16 ELSE 17 END AS week_number FROM advanced.query_logs ), create_query_cnt AS ( SELECT DISTINCT *, COUNT(user) OVER(PARTITION BY week_number, user) AS query_cnt FROM create_week_number ) SELECT *, LAG(query_cnt) OVER (PARTITION BY user ORDER BY week_number) AS prev_week_query_cnt FROM create_query_cnt ORDER BY user, week_number# 4. 시간의 흐름에 따라, 일자별로 유저가 실행한 누적 쿼리 수를 작성해주세요. # 쿼리를 작성하는 목표, 확인할 지표: 유저별 누적 쿼리 수 시간순 출력 # 쿼리 계산 방법: 윈도우 함수 COUNT, SUM # 데이터의 기간: X # 사용할 테이블: advanced.query_logs # JOIN KEY: X # 데이터 특징: X WITH create_query_cnt AS ( SELECT DISTINCT *, COUNT(user) OVER (PARTITION BY user, query_date ORDER BY user, query_date) AS query_cnt FROM advanced.query_logs ) SELECT *, SUM(query_cnt) OVER (PARTITION BY user ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_query_cnt FROM create_query_cnt ORDER BY user, query_date# 5. 다음 데이터는 주문 횟수를 나타낸 데이터입니다. 만약 주문 횟수가 없으면 NULL로 기록됩니다. 이런 데이터에서 NULL 값이라고 되어있는 부분을 바로 이전 날짜의 값으로 채워주는 쿼리를 작성해주세요. # 쿼리를 작성하는 목표, 확인할 지표: NULL로 작성된 주문 횟수를 이전 날짜의 값으로 출력 # 쿼리 계산 방법: 윈도우 함수 LAG # 데이터의 기간: X # 사용할 테이블: 쿼리에서 새로 만든 raw_data # JOIN KEY: X # 데이터 특징: 일자별(date) 주문횟수(number_of_orders)를 나타냄 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 ) SELECT date, IF (number_of_orders IS NULL, LAG(number_of_orders) OVER (ORDER BY date), number_of_orders) AS number_of_orders FROM raw_data ORDER BY date# 6. 5번 문제에서 NULL을 채운 후, 2일 전 ~ 현재 데이터의 평균을 구하는 쿼리를 작성해주세요.(이동 평균) # 쿼리를 작성하는 목표, 확인할 지표: 2일 전부터 현재까지의 number_of_orders의 평균 값 출력 # 쿼리 계산 방법: 윈도우 함수 AVG # 데이터의 기간: X # 사용할 테이블: 쿼리에서 새로 만든 raw_data # JOIN KEY: X # 데이터 특징: X 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 ), fill_null AS ( SELECT date, IF (number_of_orders IS NULL, LAG(number_of_orders) OVER (ORDER BY date), number_of_orders) AS number_of_orders FROM raw_data ORDER BY date ) SELECT *, AVG(number_of_orders) OVER (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM fill_null# 7. app_logs 테이블에서 Custom Session을 만들어 주세요. 이전 이벤트 로그와 20초가 지나면 새로운 Session을 만들어 주세요. Session은 숫자로 (1, 2, 3 …) 표시해도 됩니다. 2022-08-18일의 user_pseudo_id(1997494153.8491999091)은 session_id가 4까지 나옵니다. # 쿼리를 작성하는 목표, 확인할 지표: event_date, event_timestamp, event_name, user_id, user_pseudo_id 추출 => event_datetime, before_event_datetime, second_diff, session_start, session_id 생성 # 쿼리 계산 방법: 윈도우 함수 LAG (before_event_datetime 생성 시), # 데이터의 기간: X # 사용할 테이블: advanced.app_logs # JOIN KEY: X # 데이터 특징: X WITH create_event_datetime AS ( SELECT event_date, event_timestamp, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime, event_name, user_id, user_pseudo_id FROM advanced.app_logs ), create_before_event_datetime AS ( SELECT *, LAG(event_datetime) OVER (PARTITION BY user_pseudo_id ORDER BY event_datetime) AS before_event_datetime FROM create_event_datetime ), create_second_diff AS ( SELECT *, DATETIME_DIFF(event_datetime, before_event_datetime, second) AS second_diff FROM create_before_event_datetime ), create_session_start AS ( SELECT *, IF ((second_diff IS NULL) OR (second_diff >= 20), 1, 0) AS session_start FROM create_second_diff ) SELECT * EXCEPT(session_start), IF (session_start = 0, NULL, session_start) AS session_start, SUM(session_start) OVER (PARTITION BY user_pseudo_id, event_date ORDER BY event_datetime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_id FROM create_session_start ORDER BY user_pseudo_id, event_timestamp

1. 윈도우 함수 연습문제 1번/* 1. 사용자별 쿼리를 실행한 횟수의 총합을 보여주는 쿼리를 작성하세요 단, GROUP BY를 통해 집계하는게 아니라 우측에 새로운 칼럼으로 만들어주세요 */ select user, team, query_date, count(query_date) over (partition by user) as total_query_cnt from `advanced.query_logs` order by user,query_date;2. 윈도우 함수 연습문제 2번/* 2. 주차별로 팀내에서 쿼리를 많이 실행한 수를 구한 후, 실행한 수를 활용해 랭킹을 구해주세요. 단, 팀별로 랭킹이 1위인 사람만 보여주세요 */ -- 풀이1: 서브쿼리 사용 with base as ( select extract(week from query_date) as week_number, user, team, count(query_date) as total_query_cnt from `advanced.query_logs` group by week_number, user, team ) select week_number, team, user, total_query_cnt, ranking_in_team from ( select week_number, team, user, total_query_cnt, rank() over (partition by team order by total_query_cnt desc) as ranking_in_team from base ) where ranking_in_team = 1 order by week_number, team; -- 풀이2: QUALIFY 사용 with base as ( select extract(week from query_date) as week_number, user, team, count(query_date) as total_query_cnt from `advanced.query_logs` group by week_number, user, team ) select week_number, team, user, total_query_cnt, rank() over (partition by team order by total_query_cnt desc) as ranking_in_team from base qualify ranking_in_team = 1 order by week_number, team; 3. 윈도우 함수 연습문제 3번 /* 3. (2번 문제에서 사용한 주차별 쿼리 사용) 쿼리를 실행한 시점 1주 전에 쿼리를 실행한 횟수를 별도의 칼럼으로 확인할 수 있는 쿼리를 짜주세요 */ with base as ( select extract(week from query_date) as week_number, user, team, count(query_date) as query_cnt from `advanced.query_logs` group by week_number, user, team ) select user, team, week_number, query_cnt, lag(query_cnt) over (partition by user order by week_number) as prev_week_query_cnt from base order by user, week_number;4. 윈도우 함수 연습문제 4번 /* 4. 시간의 흐름별로(일자별로) 유저가 쿼리한 횟수의 누적합을 구하세요 */ select user, team, query_date, query_count, -- 윈도우함수의 FRAME의 DEFAULT값이 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW임 sum(query_count) over (partition by user order by query_date rows between unbounded preceding and current row) as cumulative_query_count from ( select user, team, query_date, count(query_date) as query_count from `advanced.query_logs` group by 1,2,3 ) order by user,query_date; 5. 윈도우 함수 연습문제 5번/* 5. 다음 데이터는 주문 횟수를 나타낸 테이블입니다. 만약 주문 데이터가 없으면 NULL로 기록됩니다. 이런 데이터에서 NULL값을 바로 전날의 데이터로 채워주는 쿼리를 작성하세요. */ 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 ) select date, last_value(number_of_orders ignore nulls) over (order by date) as number_of_orders from raw_data; -- 기본적으로 FIRST_VALUE, LAST_VALUE 연산시에 NULL도 포함하여 출력하지만 -- IGNORE NULLS를 사용하면 NULL 제외한 값으로 출력됨 6. 윈도우 함수 연습문제 6번 /* 6. 5번 문제에서 NULL을 채운 후, 2일 전 ~ 현재 데이터의 평균을 구하는 쿼리를 작성해주세요 */ 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 ) select date, number_of_orders, avg(number_of_orders) over (order by date rows between 2 preceding and current row) as moving_avg from ( select date, last_value(number_of_orders ignore nulls) over (order by date) as number_of_orders from raw_data )7. 윈도우 함수 연습문제 7번/* app_logs 테이블에서 커스텀세션을 만들어주세요. 이전 이벤트로그와 20초 이상 차이가 나면 새로운 세션을 만들어주세요. 세션은 숫자로(1,2,3...) 표시해도 됩니다. */ with base as ( select event_date, event_timestamp, datetime(timestamp_micros(event_timestamp),'Asia/Seoul') as event_datetime, event_name, user_id, user_pseudo_id, lag(datetime(timestamp_micros(event_timestamp),'Asia/Seoul')) over (partition by user_pseudo_id order by event_timestamp) as before_event_datetime from advanced.app_logs where event_date = '2022-08-18' ) select *, datetime_diff(event_datetime,before_event_datetime, second) as second_diff, case when datetime_diff(event_datetime,before_event_datetime, second) is null or datetime_diff(event_datetime,before_event_datetime, second) >= 20 then 1 else 0 end as session_start, sum(case when datetime_diff(event_datetime,before_event_datetime, second) >= 20 then 1 else 0 end) over (partition by user_pseudo_id order by event_datetime) + 1 as session_temp from base

탐색함수 연습문제문제 1. user들의 다음 접속 월과 다다음 접속 월을 구하는 쿼리를 작성해주세요.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 LEAD가 다음!!!! 은근 헷갈린다 정말. L, E, A, D. D로 끝나니까 다음이라고 생각해야겠다.문제2. user들의 다음 접속 월과 다다음 접속 월, 이전 접속 월을 구하는 쿼리를 작성해주세요.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 LAG함수를 쓴 컬럼에서 NULL이 나온다? → 아 이 row가 처음이다.LEAD 함수를 쓴 컬럼에서 NULL이 나온다? → 아 이 row가 마지막이다.문제3. 유저가 접속했을 때, 다음 접속까지의 간격을 구하시오.#답 x, after_visit_month에서 에러남 SELECT *, LEAD(visit_month, 1) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_visit_month, after_visit_month - visit_month FROM advanced.analytics_function_01 →SELECT 절은 가장 마지막에 실행되기 때문에 LEAD함수를 실행하면서 after_visit_month라 이름 붙인 것이다. 따라서 아직 SELECT 절에서 after_visit_month라는 것을 인식하지 못한다.#물론 쿼리가 짧을 때에는 이것도 가능. #하지만 이러한 쿼리가 굉장히 많아지면 복잡해지고, 실수하기 좋다. #중복된 쿼리는 줄이는 것이 좋다. SELECT *, LEAD(visit_month, 1) OVER (PARTITION BY user_id ORDER BY visit_month) - visit_month AS diff_visit_month, FROM advanced.analytics_function_01 → 윈도우 함수를 이렇게 쓰는 것이 좋을까? 중복된 쿼리는 줄이는 것이 좋다.→ 서브쿼리 사용하는 것이 더 낫다. 서브쿼리나 WITH문과 같은 CTE에서는 윈도우함수를 여러 개 쓰더라도 같은 정렬기준과 파티션 기준을 갖는다면 한 번의 데이터 스캔을 거친다.→ 쿼리문 수정시에도 좋다. 만약 쿼리를 수정해야할 때 서브쿼리 내의 쿼리문만 수정하면 되기 때문이다.SELECT *, after_visit_month - visit_month AS diff FROM ( SELECT *, LEAD(visit_month, 1) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_visit_month, FROM advanced.analytics_function_01 ) 문제 4. 이 데이터셋을 기준으로 user_id의 첫번째 접근 월을 구하는 쿼리를 작성해주세요.SELECT *, FIRST_VALUE(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) AS first_month LAST_VALUE(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) AS last_month FROM advanced.analytics_function_01 Frame 연습문제문제 1. 우리 회사의 모든 주문량은?SELECT *, SUM(amount) OVER (ORDER BY order_date, order_id #OVER 안에 아무것도 안 들어가도 됨 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS amount_total FROM advanced.orders 문제 2. 특정 주문 시점에서 누적 주문량은?SELECT *, SUM(amount) OVER (ORDER BY order_date, order_id) AS cumulative_sum FROM advanced.orders 문제 3. 고객별 주문 시점에서 누적 주문량은?SELECT *, SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date, order_id ) AS cumulative_sum_by_user 문제 4. 최근 직전 5개의 평균 주문량은?SELECT *, AVG(amount) OVER (ORDER BY order_date, order_id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS last_5_orders_avg_amout 총정리 연습문제문제 1. 사용자별 쿼리를 실행한 총 횟수를 구하는 쿼리를 작성해주세요. 단, GROUP BY를 사용해서 집계하는 것이 아닌 query_logs의 데이터의 우측에 새로운 컬럼을 만들어주세요SELECT *, COUNT(query_date) OVER (PARTITION BY user ORDER BY user) AS total_query_cnt FROM advanced.query_logs 문제 2. 주차별로 팀 내에서 쿼리를 많이 실행한 수를 구한 후, 실행한 수를 활용해 랭킹을 구해주세요. 단, 랭킹이 1등인 사람만 결과가 보이도록 해주세요.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. (2번 문제에서 사용한 주차별 쿼리 사용) 쿼리를 실행한 시점 기준 1주 전에 쿼리 실행 수를 별도의 컬럼으로 확인할 수 있는 쿼리를 작성해주세요.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 LAG(어떤 이전 값이 들어가야 하는지 = 1주 전 쿼리실행수)PARTITION BY로 쓸 기준은 user : 왜냐면 user 단위로 1주 전 쿼리실행수를 가져오기 때문ORDER BY로 쓸 기준은 week_number : 1주 전 쿼리이기 때문에 날짜 관련으로 정렬문제 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 처음에는 PARTITION BY에 query_date도 같이 넣었다가 결과가 날짜를 기준으로 구분되고 있는 것이 보여서 아차 하고 query_date를 뺐더니 정답이 되었다…..자꾸 문제에서 말하는 “일자 별로” 와 같은 말 때문에 PARTITION BY에 어떤 컬럼이 와야 하는지 헷갈리는 것 같다.문제 5. 다음 데이터는 주문 횟수를 나타낸 데이터입니다. 만약 주문 횟수가 없으면 NULL로 기록됩니다 이런 데이터에서 NULL값이라고 되어 있는 부분을 바로 이전 날짜의 값으로 채워주는 쿼리를 작성해주세요.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_dataLAST_VALUE()는 원래 NULL을 포함해서 연산하기 때문에 위에서 그냥 쓰면 NULL이 마지막 값으로 인정돼서 들어오지만, IGNORE NULLS를 사용하면 NULL값은 제외하고 값이 있는 것만 마지막값으로 생각하고 가져오기 때문에 쓰는 것. 문제 6. 5번 문제에서 NULL을 채운 후, 2일전 ~ 현재 데이터의 평균을 구하는 쿼리를 작성해주세요(이동평균)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. app_logs 테이블에서 custom session을 만들어주세요. 이전 이벤트 로그와 20초가 지나면 새로운 session을 만들어주세요. session은 숫자로 (1, 2, 3 …) 표시해도 됩니다.2022-08-18일의 user_pseudo_id(1997494153.8491999091)은 session_id가 4까지 나옵니다.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

<윈도우 함수>연습문제 1SELECT user_id, visit_month, LEAD(visit_month, 1) 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 two_next_visit_month, FROM advanced.analytics_function_01 ORDER BY user_id연습문제 2SELECT user_id, visit_month, LAG(visit_month, 1) OVER(PARTITION BY user_id ORDER BY visit_month) as prev_visit_month, LEAD(visit_month, 1) 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 two_next_visit_month, FROM advanced.analytics_function_01 ORDER BY user_id추가문제 - 유저의 첫번째 방문월과 마지막 방문월 구하기SELECT user_id, visit_month, FIRST_VALUE(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) as first_visit_month, LAST_VALUE(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) as last_visit_month, FROM advanced.analytics_function_01 ORDER BY user_id<FRAME 설정>advanced.order 문제SELECT *, SUM(amount) OVER() as amount_total, SUM(amount) OVER(ORDER BY order_id) as cumulative_sum, SUM(amount) OVER(PARTITION BY user_id ORDER BY order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as cumulative_sum_by_user, AVG(amount) OVER(ORDER BY order_id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) as last_5_orders_avg_amount, FROM advanced.orders ORDER BY order_id, user_id<연습문제>연습문제 1SELECT *, COUNT(*) OVER(PARTITION BY user) as total_query_cnt FROM advanced.query_logs ORDER BY user연습문제 2WITH base 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, team ORDER BY query_cnt DESC) as team_rank FROM base QUALIFY team_rank=1 ORDER BY week_number연습문제 3WITH base as ( SELECT user, team, EXTRACT(WEEK FROM query_date) as week_number, COUNT(user) as query_cnt, FROM advanced.query_logs GROUP BY ALL ) SELECT *, LAG(query_cnt, 1) OVER(PARTITION BY user ORDER BY week_number) as prev_week_query_count FROM base연습문제 4WITH base as ( SELECT user, team, query_date, COUNT(user) as query_count, FROM advanced.query_logs GROUP BY ALL ) SELECT *, SUM(query_count) OVER(PARTITION BY user ORDER BY query_date) as cumulative_query_count FROM base ORDER BY user연습문제 5-- SELECT -- *, -- IF(number_of_orders is NULL, prev_number_of_orders, number_of_orders) as result_number_of_orders, -- FROM ( -- SELECT -- *, -- LAG(number_of_orders, 1) OVER(ORDER BY date) as prev_number_of_orders -- FROM raw_data -- ) -- 이 방법은 전의 값들이 Null이 여러개면 제대로 안나옴...! SELECT *, IF(number_of_orders is NULL, last_number_of_orders, number_of_orders) as result_number_of_orders, FROM ( SELECT *, LAST_VALUE(number_of_orders IGNORE NULLS) OVER(ORDER BY date) as last_number_of_orders FROM raw_data )연습문제 6SELECT *, AVG(result_number_of_orders) OVER(ORDER BY date ROWS BETWEEN 2 PRECEDING and CURRENT ROW) as moving_avg FROM ( SELECT *, IF(number_of_orders is NULL, last_number_of_orders, number_of_orders) as result_number_of_orders, FROM ( SELECT *, LAST_VALUE(number_of_orders IGNORE NULLS) OVER(ORDER BY date) as last_number_of_orders FROM raw_data ) )연습문제 7WITH base as ( SELECT event_date, event_timestamp, 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' ), base2 as ( SELECT *, LAG(event_datetime, 1) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) as before_event_datetime FROM base ) SELECT *, SUM(session_start) OVER(PARTITION BY user_pseudo_id ORDER BY event_timestamp) as session_id FROM ( SELECT *, IF(second_diff is NULL or second_diff > 20, 1, NULL) as session_start FROM ( SELECT *, DATETIME_DIFF(event_datetime, before_event_datetime, second) as second_diff FROM base2 ) ) ORDER BY event_timestamp배운점&느낀점EXTRACT(WEEK FROM query_date) as week_number : date 정보에서 week 정보 등을 추출할 수 있는 함수DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') as event_datetime : timestamp 형태의 데이터를 날짜로 바꿀 수 있는 함수LAST_VALUE(number_of_orders IGNORE NULLS)... 그냥 마지막 값은 LAST_VALUE, 이전 데이터 중 null값이 아닌 마지막 값을 가져오고 싶을 때는 IGNORE NULLS를 사용해야 함. (5번 연습문제에서 처음에는 LAG 함수를 사용해서 null이 여러 개 연속인 경우가 있어 제대로 값을 가져오지 못했고, LAST_VALUE 윈도우 함수에서IGNORE NULLS 를 까먹어서 null 처리가 또 안됐었다.)With절과 서브쿼리 절을 어떤 상황에 사용할지 아직 잘 모르겠다.CASE WHEN절 사용법이 익숙치 않았다. (문제를 풀 때 IF절을 사용했던 부분에서 강의에서는 CASE WHEN을 사용)윈도우 함수를 제대로 처음 배워봤는데, 어려웠지만 유용한 부분이 많아 사용할 일이 많을 것 같다. 

연습 문제 (1)-- 1) SELECT user_id , visit_month , LEAD(visit_month, 1) OVER (PARTITION BY user_id ORDER BY visit_month) AS lead_visit_month , LEAD(visit_month, 2) OVER (PARTITION BY user_id ORDER BY visit_month) AS lead2_visit_month FROM advanced.analytics_function_01 ORDER BY user_id -- 2) SELECT user_id , visit_month , LEAD(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_visit_month , LEAD(visit_month, 2) OVER (PARTITION BY user_id ORDER BY visit_month) AS after2_visit_month , LAG(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS before_visit_month FROM advanced.analytics_function_01 ORDER BY user_id, visit_month -- 3) 유저가 접속했을 때, 다음 접속까지의 간격을 구하시오. SELECT * , after_visit_month - visit_month AS diff_month FROM ( SELECT user_id , visit_month , LEAD(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_visit_month FROM advanced.analytics_function_01 ) ORDER BY user_id , visit_month -- 4) 이 데이터셋을 기준으로 user_id의 첫번째 방문 월, 마지막 방문 월을 구하는 쿼리를 작성해주세요. SELECT * , FIRST_VALUE(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS first_month , LAST_VALUE(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS last_month FROM advanced.analytics_function_01 ORDER BY user_id , visit_month -- 윈도우 함수 - 데이터 범위 지정 SELECT * , SUM(amount) OVER () AS amount_sum , SUM(amount) OVER (ORDER BY order_id) AS cumulative_sum , SUM(amount) OVER (PARTITION BY user_id ORDER BY order_id) AS cumulative_sum_by_user , AVG(amount) OVER (ORDER BY order_id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS last_5_orders_avg_amount FROM advanced.orders ORDER BY order_id 연습 문제 (2)-- 1) SELECT * , COUNT(user) OVER (PARTITION BY user) AS total_query_cnt FROM advanced.query_logs ORDER BY user -- 2) WITH query_cnt_by_team 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, team ORDER BY query_cnt DESC) AS rk FROM query_cnt_by_team QUALIFY rk = 1 ORDER BY week_number , team , query_cnt DESC -- 3) WITH query_cnt_by_team 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, 1) OVER (PARTITION BY user ORDER BY week_number) AS prev_week_query_cnt FROM query_cnt_by_team ORDER BY user , week_number -- 4) SELECT query_date , team , user , query_cnt , SUM(query_cnt) OVER (PARTITION BY user ORDER BY query_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum FROM ( SELECT query_date , team , user , COUNT(user) AS query_cnt FROM advanced.query_logs GROUP BY ALL ) ORDER BY user , query_date -- 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 ) SELECT * , LAST_VALUE(number_of_orders IGNORE NULLS) OVER (ORDER BY date) AS before_number_of_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 step1 AS ( SELECT * EXCEPT(event_params, platform) , DATETIME(TIMESTAMP_MICROS(event_timestamp), "Asia/Seoul") AS event_datetime FROM advanced.app_logs WHERE user_pseudo_id = "1997494153.8491999091" AND event_date = '2022-08-18' ), step2 AS ( SELECT * , DATETIME_DIFF(event_datetime, prev_event_datetime, SECOND) AS second_diff FROM ( SELECT * , LAG(event_datetime) OVER (ORDER BY event_datetime) AS prev_event_datetime FROM step1 ) ORDER BY event_datetime ) SELECT * , SUM(session_start) OVER (ORDER BY event_datetime) AS session_num FROM ( SELECT * , CASE WHEN second_diff IS NULL THEN 1 WHEN second_diff >= 20 THEN 1 ELSE 0 END AS session_start FROM step2 ) ORDER BY event_datetime

연습 문제(1) 윈도우 함수-- 1) user들의 다음 접속 월과 다다음 접속 월을 구하는 쿼리를 작성해주세요. SELECT user_id, visit_month, LEAD(visit_month, 1) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_visit_month_1, LEAD(visit_month, 2) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_visit_month_2, FROM `advanced.analytics_function_01` ORDER BY user_idLEAD 함수와 적절한 수를 이용해 작성LEAD 함수에 들어가는 인자에 따라 다음인지 다다음인지 정할 수 있음따로 정해주지 않을 경우 1로 인식OVER의 뒷 내용이 생각보다 조금 복잡해 한번에 떠오르지는 않았음OVER의 ORDER BY의 기본은 오름차순 -- 2) user들의 다음 접속 월과 다다음 접속 월, 이전 접속 월을 구하는 쿼리를 작성해주세요. SELECT user_id, visit_month, LEAD(visit_month, 1) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_visit_month_1, LEAD(visit_month, 2) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_visit_month_2, LAG(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS previous_visit_month_1, FROM `advanced.analytics_function_01` ORDER BY user_idLAG 사용해 이전 값 구해봄LAG 안에 특별한 숫자를 주지않으니 1로 인식하는 것을 확인함LEAD 값이 NULL → 해당 값이 마지막 값LAG 값이 NULL → 해당 값이 첫번째 값-- 3) user의 다음 접속까지의 간격을 구하시오 SELECT user_id, visit_month, LEAD(visit_month, 1) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_visit_month_1, (LEAD(visit_month, 1) OVER (PARTITION BY user_id ORDER BY visit_month) - visit_month) AS diff_month, FROM `advanced.analytics_function_01` ORDER BY user_idSELECT 절에서 만들어진 컬럼은 그대로 사용할 수는 없음하지만 위와 같이 할 경우 너무 길어지고 복잡해짐(중복됨) → 서브쿼리 사용 SELECT *, (after_visit_month_1 - visit_month) AS diff_month FROM ( SELECT user_id, visit_month, LEAD(visit_month, 1) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_visit_month_1, FROM `advanced.analytics_function_01` ORDER BY user_id )쿼리를 최대한 덜 수정하는 방향으로 작성해 볼 것(2) QUALIFY-- amount_total : 전체 SUM -- cumulative_sum : row 시점에 누적 SUM -- cumulative_sum_by_user : row 시점에 유저별 누적 SUM -- last_5_orders_avg_amount : order_id 기준으로 정렬하고, 직전 5개 주문의 평균 amount SELECT *, SUM(amount) OVER() AS amount_total, SUM(amount) OVER(ORDER BY order_id) AS cumulative_sum, SUM(amount) OVER(PARTITION BY user_id ORDER BY order_id) AS cumulative_sum_by_user, AVG(amount) OVER(ORDER BY order_id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS last_5_orders_avg_amount FROM advanced.orders ORDER BY order_idBETWEEN 앞에 ROWS를 빼먹어서 계속 오류를 냈음-- 1) 사용자별 쿼리를 실행한 총 횟수를 구하는 쿼리를 작성해주세요. -- 단, GROUP BY를 사용해서 집계하는 것이 아닌 query_logs의 데이터의 우측에 새로운 컬럼을 만들어주세요. SELECT *, COUNT(query_date) OVER(PARTITION BY user) AS cnt_by_user FROM advanced.query_logs데이터에 NULL값이 없으므로 어떤 열을 세던지 상관 없음-- 2) 주차별로 팀 내에서 쿼리를 많이 실행한 수를 구한 후, 실행한 수를 활용해 랭킹을 구해주세요. -- 단, 랭킹이 1등인 사람만 결과가 보이도록 해주세요 SELECT *, RANK() OVER(PARTITION BY team, week_key ORDER BY cnt_by_user DESC) AS rnk FROM ( SELECT user, team, IF(query_date < '2024-05-01', 1, 2) AS week_key, COUNT(user) AS cnt_by_user FROM advanced.query_logs GROUP BY ALL ) QUALIFY rnk = 1 ORDER BY team, week_key'주차별’에 대한 아이디어가 잘 떠오르지 않았음 → 데이터의 범위가 좁기 때문에 일단은 IF를 통해 주차를 구분해줌 → 날짜 범위가 넓어지면 어떻게 할지 아직은 모르겠음서브 쿼리 사용해봄PARTITION이 2개임(주차별, 팀별)QUALIFY 사용할 것(생각 못하고 LIMIT 쓰려다 막힘)-- 강의 코드 WITH query_cnt_by_team 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, team ORDER BY query_cnt DESC) AS rk FROM query_cnt_by_team QUALIFY rk = 1 ORDER BY week_number, team, query_cntEXTRACT 함수 통해 ‘주차’ 추출GROUP BY 후 윈도우 함수 사용 → 유연하게 사용할 것-- 3) (2번 문제에서 사용한 주차별 쿼리 사용) 쿼리를 실행한 시점 기준 1주 전에 쿼리 실행 수를 별도의 컬럼으로 확인할 수 있는 쿼리를 작성해주세요 SELECT *, LAG(query_cnt) OVER(PARTITION BY user ORDER BY week_number) AS previous_week_cnt FROM query_cnt_by_team ORDER BY userWITH AS로 만든 테이블 그대로 사용-- 4) 시간의 흐름에 따라, 일자별로 유저가 실행한 누적 쿼리 수를 작성해주세요 SELECT *, SUM(query_cnt) OVER(PARTITION BY user ORDER BY query_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumul_cnt FROM ( SELECT user, team, query_date, COUNT(*) AS query_cnt FROM advanced.query_logs GROUP BY ALL ) ORDER BY user, query_dateGROUP BY한 서브쿼리 사용FRAME 사용이 그렇게 까다롭진 않았음FRAME의 defalut 값 → UNBOUNDED PRECEDING ~ CURRENT ROW-- 5) 다음 데이터는 주문 횟수를 나타낸 데이터입니다. 만약 주문 횟수가 없으면 NULL로 기록됩니다. -- 이런 데이터에서 NULL 값이라고 되어있는 부분을 바로 이전 날짜의 값으로 채워주는 쿼리를 작성해주세요 SELECT date, IF(number_of_orders IS NULL, LAG(raw_data.number_of_orders, 1) OVER(ORDER BY date), number_of_orders) AS number_of_orders FROM raw_data조건문 사용해서 IS NULL인 값들만 LAG 사용기존의 number_of_orders가 사라지는 문제가 있음마지막 날짜는 안채워짐(연속으로 NULL이라) -- 강의 코드 SELECT *, LAST_VALUE(number_of_orders IGNORE NULLS) OVER(ORDER BY date) AS last_value_orders FROM raw_dataLAST_VALUE + IGNORE NULLS 사용-- 6) 5번 문제에서 NULL을 채운 후, 2일 전 ~ 현재 데이터의 평균을 구하는 쿼리를 작성해주세요(이동 평균) SELECT *, AVG(last_value_orders) OVER(ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average FROM ( SELECT *, LAST_VALUE(number_of_orders IGNORE NULLS) OVER(ORDER BY date) AS last_value_orders FROM raw_data서브쿼리 사용했지만 WITH로 정의해도 됨 → 문제에서는 WITH가 연속 두번 나오는데, WITH는 한번만 작성해도 됨(, 로 구분)-- 7) app_logs 테이블에서 Custom Session을 만들어 주세요. 이전 이벤트 로그와 20초가 지나면 새로운 Session을 만들어 주세요. -- Session은 숫자로 (1, 2, 3 ...) 표시해도 됩니다 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") ORDER BY event_timestamp ) SELECT *, SUM(diff_classification) OVER(ORDER BY event_datetime) + 1 AS session_id FROM ( SELECT *, IF(DATETIME_DIFF(event_datetime, before_datetime, second) > 20, 1, 0) AS diff_classification FROM ( SELECT *, LAG(event_datetime) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS before_datetime, FROM base ) ) ORDER BY event_datetimetimestamp와 1초의 관계 : timestamp 1당 1초 아닌가? → 그렇다면 굳이 datetime으로 바꿀 필요가 있나서브쿼리 2번 중첩해서 사용IF문 사용해 20초 차이남 → 1 차이 안남 → 0 으로 파생해 누적합 + 1로 session_id 도출첫 행 before_datetime에 예외처리 해줄 것그런데 PARTITION BY 를 매 OVER 안에 무조건 써야 하나?EDA(2)요일별 접속자 수 + 기간 내 이벤트날요일별 접속자 평균을 내보자일 > 토 > 수 > 금 > 목 > 화 > 월 → 역시 쉬는날이 더 배달 수요가 많은건가? → 수요일은 왜 일까?2022-08-01(월) ~ 2023-01-20(금) 의 데이터주말(토, 일)이 아닌 공휴일 목록 → 네이버 캘린더 참조2022-08-15 월 : 광복절2022-09-09 금 : 추석연휴2022-09-12 월 : 추석연휴2022-10-03 월 : 개천절2023-01-23 월 : 설 연휴2023-01-24 화 : 설 연휴(대체공휴일)주요 이벤트 → 특정 사건이 있을 경우 배달 수요가 늘거나 줄지 않을까? / 위키 사이트 참조2022-08-02 화 : 코로나19 누적 감염자 2천만 명 돌파2022-08-08 월 : 수도권 기록적인 폭우 및 홍수2022-10-31 월 : 할로윈데이 / 2022-10-29 토에 이태원 압사 사고 발생2022-11-08 화 : 한국시리즈2022-11-17 목 : 2023학년도 대학수학능력시험2022-11-24 목 : 카타르 월드컵 vs 우루과이2022-11-28 월 : 카타르 월드컵 vs 가나2022-12-03 토 : 카타르 월드컵 vs 포르투갈2022-12-19 월 : 카타르 월드컵 결승2022-12-25 일 : 크리스마스등등…“특정 사건”의 중요도를 정성적으로 평가하기에는 어렵다고 느낌 → 오히려 반대로 갑자기 이용자 수의 변화가 급격하게 나타나는 날짜를 위주로 봐야하나? → 그런데 앱 이용자 수 성장 시기에는 항상 상승만해서 보기 애매할 듯 → 요일별 경향을 따져야 할 수도일요일 < 월요일(개천절) < 화요일 → 앱 이용자 수 상승 예시월드컵에 따른 이용자 수 변화는 뚜렷하지 않음수요일에 딱히 뭔가 보이진 않음 → 평일의 절반이 지남에 따른 보상 심리가 원인일수도?시간대에 따른 이용자 수마찬가지로 시간대별 평균 이용자 수 구해봄저녁 시간대(19시 ~ 22시)가 가장 이용자 수 많음점심 시간대(12시 ~ 14시)가 그 다음당연하게도 식사 시간에 배달 앱 수요가 많음시간대 별로 food_id 에 따른 수요가 다른지 확인해볼 것 → 시간대 별 이용자에게 추천해주는 음식 다르게 설정할 수 있음하루의 기준을 0시로 잡아도 되나?에 대한 의문이 생김(야식 수요) → 이거에 대한 기준을 다시 잡고 일별 이용자 수 다시 구해야 할수도