안녕하세요 카일님! 강의를 오랜만에 복습하려고 들어왔는데 4월말에 클라우드 콘솔 체험판 시작했던 뒤로 이미 90일 넘게 지난 상태입니다.이경우에 새로운 계정을 사용해서 다시 설치해야 할까요? 기존에 설정해놨던 테이블이 이미 만료된 것 같아서요. 

🔐 이번주차 중요 키워드 : 윈도우 함수, FRAME, QUALIFY ✅ 윈도우 함수 연습문제 1번1) 사용자별 쿼리를 실행한 총 횟수를 구하는 쿼리 작성 단, group by를 사용해서 집계하는 것이 아닌 query_logs의 데이터 우측에 새로운 컬럼을 만들어주세요. select *, count(query_date) over (partition by user) as query_cnt from advanced.query_logs order by query_cnt desc ✅ 윈도우 함수 연습문제 2번2) 주차별로 팀 내에서 쿼리를 많이 실행한 수 구하기 2-1) 실행한 수를 활용해 랭킹 구하기 -- 단, 랭킹이 1등인 사람만 결과가 보이도록 해주세요. week_number | team | user | query_cnt | team_rank select * , rank() over(partition by week_number, team order by query_cnt) as team_rank from ( select EXTRACT(week FROM query_date) as week_number, team, user, count(query_date) as query_cnt from advanced.query_logs group by all ) qualify team_rank = 1 order by week_number, team, query_cnt DESC ❗ 새롭게 알게된 함수 : EXTRACT(week FROM query_date) as week_number → 기존의 알고 있던 함수와 같은 결과값 : DATE_TRUNC(query_date, WEEK) AS week_number❗ qualify team_rank = 1 : QUALIFY 덕분에 서브쿼리 없이 바로 조건에 사용가능함! ✅ 윈도우 함수 연습문제 3번3) (2번 문제에서 사용한 주차별 쿼리 사용) 쿼리를 실행한 시점 기준 1주전에 쿼리 실행 수를 별도의 컬럼으로 확인할 수 있는 쿼리 작성 select * , LAG(query_cnt, 1) over (partition by user order by week_number) as pre_week_query_cnt from ( select EXTRACT(week FROM query_date) as week_number, team, user, count(query_date) as query_cnt from advanced.query_logs group by all ) ✅ 윈도우 함수 연습문제 4번4) 시간의 흐름에 따라, 일자별로 유저가 실행한 누적 쿼리수 작성 ## FRAME의 default값은 UNBOUNDED PRECEDING AND CURRENT ROW with query_cnt_by_team as ( select EXTRACT(week FROM query_date) as week_number, team, user, query_date, count(query_date) as query_cnt from advanced.query_logs group by all ) select *, sum(query_cnt) over (partition by user order by query_date ASC) as cumulative_SUM1, sum(query_cnt) over (partition by user order by query_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as cumulative_SUM2, from query_cnt_by_team ❗FRAME의 default값은 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 * , ifnull(number_of_orders, LAST_VALUE(number_of_orders IGNORE NULLS) over(order by date)) as filled_orders from raw_data -- 조건절 ifnull 사용할 수 있음. -- LAG()를 사용하면 마지막 NULL값인 경우 채우는 값도 NULL! -- 그렇기 떄문에, LAST_VALUE()인데, NULL은 무시하라는 IGNORE NULLS! ❗️LAST_VALUE에서 IGNORE NULLS 안하면 값은 NULL✅ 윈도우 함수 연습문제 6번6) 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(filled_orders) over (order by date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_orders from ( select * , ifnull(number_of_orders, LAST_VALUE(number_of_orders IGNORE NULLS) over(order by date)) as filled_orders from raw_data ) ✅ 윈도우 함수 연습문제 7번7) app_logs 테이블에서 custom_session을 만들어 주세요:) 이전 이벤트 로그와 20초가 지나면 새로운 session을 만들어 주세요. event_date | event_timestamp | event_datetime | evnet_name | user_id | user_pseudo_id | before_event_datetime | second_diff | session_start | session_id with base as ( select event_date, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') as event_time, 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(session_start) over (partition by user_pseudo_id order by event_time) as session_num from ( select *, CASE WHEN before_event_datetime IS NULL THEN 1 WHEN second_diff >=20 THEN 1 ELSE 0 END AS session_start from ( select *, DATETIME_DIFF(event_time, before_event_datetime, SECOND) AS second_diff from ( select *, LAG(event_time,1) over (partition by user_pseudo_id order by event_time) as before_event_datetime from base order by event_time ) ) ) ❗새롭게 알게 된 함수 DATETIME_DIFF : 처음에 날짜-시간 차이를 단순히 (-)로만 생각했다가 잘못된 결과 도출 → DATETIME_DIFF 함수로 정답도출!

안녕하세요 카일님, 6-5. 데이터 결과 검증 예시 강의 중 아래 쿼리에서 total_battle_count 와 win_ratio 를 각각 구할때 COUNT(battle_id) 와 COUNT(DISTINCT battle_id) 로 쓰신 이유가 궁금합니다.무엇이 됐건 둘은 통일되어야 하는게 아닌지, total_battle_count 를 구할때도 엄밀하게는 DISTINCT 를 썼어야 하는게 아닌지 궁금합니다 !SELECT trainer_id, COUNTIF(battle_result = 'Win') AS win_count, COUNT(battle_id) AS total_battle_count, COUNTIF(battle_result = 'Win') / COUNT(DISTINCT battle_id) AS win_ratio FROM battle_with_result GROUP BY trainer_id

강의 문제 1) user들의 다음 접속월과 다다음 접속월을 구하는 쿼리를 작성해주세요.-- 출제의도: 윈도우 함수(LEAD)를 사용하여 파티션을 나눠 데이터를 탐색할 수 있는가? SELECT user_id , visit_month AS visit_month_m0 , LEAD(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS visit_month_m1 , LEAD(visit_month, 2) OVER (PARTITION BY user_id ORDER BY visit_month) AS visit_month_m2 FROM advanced.analytics_function_01 ORDER BY user_id;강의 문제 2) user들의 다음 접속월과 다다음 접속월, 이전 접속월을 구하는 쿼리를 작성해주세요-- 출제의도: 윈도우 함수(LEAD)와 (LAG)을 함께 사용하여 파티션을 나눠 데이터를 탐색할 수 있는가? SELECT user_id , visit_month AS visit_month_m0 , LEAD(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS visit_month_m1 , LEAD(visit_month, 2) OVER (PARTITION BY user_id ORDER BY visit_month) AS visit_month_m2 , LAG(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS visit_month_p1 FROM advanced.analytics_function_01 ORDER BY user_id;강의 문제 3) Frame 설정을 활용한 윈도우 함수 사용SELECT order_id , order_date , user_id , amount , 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_by_user , AVG(amount) OVER (ORDER BY order_date, order_id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS last_5_orders_avg_amount FROM advanced.orders --QUALIFY last_5_orders_avg_amount >= 150 ORDER BY order_id;윈도우 함수 연습문제 1) 사용자별 쿼리를 실행한 총 횟수를 구하는 쿼리를 작성해주세요. 단, GROUP BY를 사용해서 집계하는 것이 아닌, query_logs의 데이터 우측에 새로운 컬럼을 만들어주세요. -- 출제의도: 윈도우 함수의 집계 함수 중 COUNT를 사용할 수 있는가? SELECT * , COUNT(user) OVER (PARTITION BY user) AS total_query_cnt FROM advanced.query_logs ORDER BY query_date, user;윈도우 함수 연습문제 2) 주차별로 팀 내에서 쿼리를 많이 실행한 수를 구한 후, 실행한 수를 활용해 랭킹을 구해주세요. 단, 랭킹이 1등인 사람만 결과가 보이도록 해주세요. -- 출제의도: GROUP BY와 윈도우 함수(순위)를 함께 사용할 수 있는가? 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) AS query_cnt FROM advanced.query_logs GROUP BY ALL) QUALIFY team_rank = 1 ORDER BY week_number, team;윈도우 함수 연습문제 3) (2번 문제에서 사용한 주차별 쿼리 사용) 쿼리를 실행한 시점 기준, 1주 전에 쿼리 실행 수를 별도의 컬럼으로 확인할 수 있는 쿼리를 작성해주세요. -- 출제의도: GROUP BY와 윈도우 함수(LAG)를 함께 사용할 수 있는가? SELECT * , LAG(query_cnt) 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) AS query_cnt FROM advanced.query_logs GROUP BY ALL) ORDER BY user, week_number;윈도우 함수 연습문제 4) 시간 흐름에 따라, 일자별로 유저가 실행한 누적 쿼리 수를 작성해주세요. -- *FRAME의 DEFAULT 값: UNBOUNDED PRECEDING ~ CURRENT ROW -- 출제의도: GROUP BY와 윈도우 함수(SUM-누계합)를 함께 사용할 수 있는가? SELECT * , SUM(query_cnt) OVER (PARTITION BY user ORDER BY query_date) AS cumulative_query_count FROM (SELECT user , team , query_date , COUNT(user) AS query_cnt FROM advanced.query_logs GROUP BY ALL) ORDER BY user, query_date;윈도우 함수 연습문제 5) 다음 데이터는 주문 횟수를 나타낸 데이터입니다. 만약 주문 횟수가 없으면 NULL로 기록됩니다. 이런 데이터에서 NULL 값이라고 되어있는 부분을 바로 이전 날짜의 값으로 채워주는 쿼리를 작성해주세요. -- 출제의도: 윈도우 함수(LAST_VALUE)에서 IGNORE 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 -- date -- , IFNULL(number_of_orders, LAG(number_of_orders) OVER (ORDER BY date)) AS number_of_orders -- FROM raw_data -- ORDER BY date; SELECT * , LAST_VALUE(number_of_orders IGNORE NULLS) OVER (ORDER BY date) AS number_of_orders FROM raw_data ORDER BY date;윈도우 함수 연습문제 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 * , ROUND(AVG(number_of_orders) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) , 1) AS moving_avg FROM( SELECT date , 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까지 나옵니다 -- 출제의도: 윈도우 함수를 웹 로그 데이터에 적용하여 활용할 수 있는가? -- Step 1. Session 정보 추출 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' ), -- 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 ELSE NULL END AS session_start FROM base ) -- Step 3. 신규 세션 id 세팅 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; 

안녕하세요! SUBQUERY 이해 (업데이트) 강의에서 예2의 비교 부분의 쿼리문을 JOIN으로 바꿔서 표현해보려고 하는데 실행 결과 category_id가 1부터 전부 나옵니다. 어떻게 해야 5('Comedy')보다 큰 것만 추출할 수 있을까요?pdf파일 7page JOIN으로 바꿔서 표현해본 쿼리문 실행 결과 

안녕하세요 카일님, JOIN 연습문제 4번에서 작성 순서 및 효율성 관련해서 질문드리고 싶습니다.카일님의 풀이에서는 Active, Training 의 Status 를 필터링 후 pokemon, trainer 테이블을 LEFT JOIN 하시고 마지막에 'Master' 를 필터링하셨는데,저 같은 경우에는 각각의 테이블마다 필요한 부분을 먼저 필터링 후에 마지막에는 COUNT 만 할 수 있게 진행하였습니다. Trainer 테이블에서는 'Master' 필터Trainer_pokemon 테이블에서는 'Active', 'Training' 필터결과적으로 결과값은 올바르게 나오는데 카일님께서 앞선 강의에서 필터링을 먼저 하는 것이 효율적이라 하셨던 것 같아서 질문드리게 되었습니다. 감사합니다.SELECT p.type1, COUNT(tp.id) AS pokemon_cnt -- t.*, -- tp.pokemon_id, -- tp.status, -- p.type1 FROM( SELECT id, achievement_level FROM `basic.trainer` WHERE achievement_level = 'Master' ) AS t LEFT JOIN( SELECT * FROM `basic.trainer_pokemon` WHERE status IN ('Active', 'Training') ) AS tp ON t.id = tp.trainer_id LEFT JOIN `basic.pokemon` AS p ON tp.pokemon_id = p.id WHERE tp.pokemon_id IS NOT NULL GROUP BY p.type1 ORDER BY 2 DESC LIMIT 1;

윈도우 탐색 함수 연습문제(1) 연습문제 1-- 문제 1) USER의 다음 접속월, 다다음 접속 월 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) 연습문제 2-- 문제 2) USER의 다음 접속월, 다다음 접속 월, 이전 접속 월 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` 윈도우 함수 FRAME 연습문제연습문제 (1~4)SELECT -- 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) 연습문제 1-- 연습문제1) 사용자별 쿼리 실행 횟수 WITH base AS( SELECT user, team, query_date, COUNT(*) OVER(PARTITION BY user) AS total_query_cnt, FROM `avdanced.query_logs` ) SELECT * FROM base(2) 연습문제 2-- 연습문제2) 주차별 팀내 쿼리 실행한 수 (RANK 1만 보이도록) WITH base2 AS( SELECT EXTRACT(WEEK FROM query_date) AS week_number, team, user, COUNT(*) OVER(PARTITION BY EXTRACT(WEEK FROM query_date) ,user ORDER BY EXTRACT(WEEK FROM query_date) ) AS query_cnt, FROM `avdanced.query_logs` ORDER BY EXTRACT(WEEK FROM query_date) ) SELECT DISTINCT *, RANK() OVER(PARTITION BY team,week_number ORDER BY total_query_cnt DESC) AS team_rank FROM base2 QUALIFY team_rank = 1 ORDER BY week_number, team강의자료의 코드-- 2) 주차별로 팀 내에서 쿼리를 많이 실행한 수를 구한 후, 실행한 수를 활용해 랭킹을 구해주세요. 단, 랭킹이 1등인 사람만 결과가 보이도록 해주세요 -- 주차별로 개인당 실행한 쿼리 횟수 -- 위 쿼리 횟수를 기반으로 랭킹 -- 랭킹을 기반으로 필터링(랭킹=1) -- 문제의 의도 : 원본 데이터 => 1 row마다 데이터가 있고, 그걸 집계해서 사용. GROUP BY => 윈도우 함수 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 : 윈도우 함수의 조건을 설정할 때 사용 QUALIFY rk = 1 ORDER BY week_number, team, query_cnt DESCCOUNT의 윈도우 함수 대신에 GROUP BY를 사용하는 풀이도 있다는 것을 알게 되었다! 너무 어렵게 생각하지 말기!(3) 연습문제 3WITH base2 AS( SELECT EXTRACT(WEEK FROM query_date) AS week_number, team, user, COUNT(*) OVER(PARTITION BY EXTRACT(WEEK FROM query_date) ,user ORDER BY EXTRACT(WEEK FROM query_date) ) AS query_cnt, FROM `avdanced.query_logs` #QUALIFY team_rank = 1 ORDER BY EXTRACT(WEEK FROM query_date) ), base3 AS( SELECT DISTINCT *, RANK() OVER(PARTITION BY team,week_number ORDER BY query_cnt DESC) AS team_rank FROM base2 QUALIFY team_rank = 1 ORDER BY week_number, team ) -- 연습문제3) 쿼리 실행 시점 1주전 쿼리 실행 SELECT DISTINCT *, LAG(query_cnt,1) OVER(PARTITION BY user ORDER BY week_number) AS prev_week_query_count FROM base2 GROUP BY ALL ORDER BY user, week_number(4) 연습문제 4--연습문제4) SELECT *, SUM(query_count) OVER(PARTITION BY user ORDER BY query_date) AS culmulative_query_count, SUM(query_cnt) OVER(PARTITION BY user ORDER BY query_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum2 FROM( SELECT DISTINCT *, COUNT(user) OVER(PARTITION BY query_date, user) AS query_count, FROM `avdanced.query_logs` ) ORDER BY user,query_date QUALIFY 로 조건설정을 하여 두 값이 같은 지 비교하는 법이 인상깊었던 문제 (QUALIFY cumulative_sum != cumulative_sum2) (5) 연습문제 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 ) --연습문제 5) null에 이전 값 삽입 SELECT raw_data.date, IF(raw_data.number_of_orders IS NULL, LAG(raw_data.number_of_orders,1) OVER(ORDER BY date), raw_data.number_of_orders) FROM raw_data강의풀이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 ), -- LAG로 직전 값 가져오면 되지 않을까? -- number_of_orders가 null이면, before_number_of_orders를 가져와라! -- 아래 쿼리는 어려운 방법 -- 그 다음 방법 : LAST VALUE를 쓰자! => 값이 없으면 NULL이 뜬다! -- FIRST_VALUE, LAST_VALUE => NULL을 포함해서 연산 -- 출제 의도 : NULL을 제외해서 연산하고 싶으면 IGNORE NULLS을 쓰면 된다! -- SELECT -- *, -- IF(number_of_orders IS NULL, before_number_of_orders, number_of_orders) AS filled_orders -- -- Number of arguments does not match for function IF. Supported signature: IF(BOOL, ANY, ANY) at [89:3] -- -- False일 때 인자를 추가하지 않아서 생긴 오류 -- FROM ( -- SELECT -- *, -- LAG(number_of_orders) OVER(ORDER BY date) AS before_number_of_orders -- FROM raw_data -- ) 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 -- Syntax error: Expected keyword DEPTH but got identifier "filled_data" at [104:6] : WITH문을 두개 작성했는데 WITH 쉼표 쓰고 구분! )LAST_VALUE, FIRST_VALUE를 사용하는 풀이 법에 대하여 알게 됨(6) 연습문제 6WITH 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 ), null_is_lag AS( --연습문제 5) null에 이전 값 삽입 SELECT raw_data.date, IF(raw_data.number_of_orders IS NULL, LAG(raw_data.number_of_orders,1) OVER(ORDER BY date), raw_data.number_of_orders) AS number_of_orders FROM raw_data ) -- 연습문제 6) 이동평균 SELECT *, AVG(nl.number_of_orders) OVER(ORDER BY nl.date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM null_is_lag AS nlFRAME절을 사용할 때, AND를 기준으로 앞에는 뒤의 값보다 반드시 이전 행을 가리키는 구문이 와야 함!(7) 연습문제 7-- 1. TIMESTAMP → DATETIME -- 2. SECOND_DIFF 생성 : uSER로 묶어서 - -- 3. SESSION_START생성 : USER로 묶어서 LAG(DATA,1)이 NULL이면 1, SECOND_DIFF가 20이상이면 +1 -- 4. SESSION_ID생성: SESSION_START가 1일 경우 SESSION_ID +1, NULL일 경우 LAG(DATA,1) WITH add_date AS ( -- 1. TIMESTAMP → DATETIME SELECT event_date, event_timestamp, DATETIME(TIMESTAMP_MICROS(event_timestamp)) AS event_datetime, event_name, user_id, user_pseudo_id, LAG(DATETIME(TIMESTAMP_MICROS(event_timestamp))) OVER(PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS before_event_datetime FROM `avdanced.app_logs_temp` --,UNNEST(event_params) AS param -- FROM 절 안에서 UNNEST를 사용 WHERE event_date ="2022-08-18" AND user_pseudo_id = "1997494153.8491999091" ), add_diff AS ( -- 2. SECOND_DIFF 생성 : uSER로 묶어서 - SELECT *, DATE_DIFF(event_datetime, before_event_datetime,SECOND) AS second_diff, FROM add_date ), add_session AS( -- 3. SESSION_START생성 : USER로 묶어서 LAG(DATA,1)이 NULL이면 1, SECOND_DIFF가 20이상이면 +1 SELECT *, IF(second_diff IS NULL OR second_diff >=20, 1, NULL) AS session_start FROM add_diff ) -- 4. SESSION_ID생성 *, SUM(session_start) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS session_num FROM add_session ORDER BY event_datetime user_id와 user_pseudo_id는 다르다. (계정이 존재하면 USER_ID, 없어도 USER_PSEUDO_ID를 통해 활동기록이 남는다.)IF문은 행 단위로 작동, SUM은 특정 파티션에 대한 누적합을 계산함으로 아래 코드가 작동하지 않음 SUM(session_start) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS session_numCTE를 사용하는 것과 서브쿼리를 사용하는 방법 중 상황에 맞게 적절히 혼용할 수 있다는 것을 알게 됨!

master 옆에 NAN라고 되어있고 누르면 master하나만 있고 sqld가 없으면 제가 db를 만들어서 입력해야하는건가요?..

[1~2번]윈도우 함수(탐색 함수) 연습 문제 1번 select *, 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 2번select *, 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, lag(visit_month, 1) over(partition by user_id order by visit_month) as before_visit_month from `advanced.analytics_function_01`  [1~4번] 윈도우 함수 Frame 연습 문제  1번/* 전체 계산 할 때 왜 over()를 써야 하는지 몰랐습니다. 그러나 일반 집계 함수의 방식과 비교해보니, 사용 이유를 알게 됐습니다. 집계함수를 사용할 때 over()를 사용했다는 건 '윈도우 함수가 적용된 결과값(각 행마다 결과 값이 반환 되는 것)으로 보겠다'는 의미인 걸 알게 됐습니다. 같은 집계함수를 사용하더라도 '어떤 방식(결과를 1개 행으로만 받아볼지, 여러개 값으로 받아볼지)으로 결과 값을 받아 볼 지' 생각해 봐야 한다는 걸 알았습니다. */ select *, sum(amount) over() as amount_total from `advanced.orders` 2번/* 처음에는 누적합을 구하는 건데 왜 over() 안에 order by를 써야하는지 이해하지 못했습니다. 그러나 누적합도 "어떤 방식으로 계산할지 '계산 방식'"을 지정해 줘야 한다는 걸 알게 됐습니다. /* select *, sum(amount) over(order by order_id) as cumulative_sum from `advanced.orders` 3번select *, sum(amount) over(partition by user_id order by order_id) as cumulative_sum_by_user from `advanced.orders` 4번/* over() 안에 사용하는 order by와 over() 밖에서 사용하는 order by를 명확히 이해했습니다. 윈도우 함수에서 사용하는 order by는 '계산 순서'를 정하는 것이고, 윈도우 함수 밖에서 사용한 order by는 '눈에 보이는 정렬'을 컨트롤 한다는 걸 이해했습니다. 이해할 때 '쿼리 작동 순서'를 생각했더니, 큰 도움이 됐습니다. */ select *, 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  [1~7번] 윈도우 함수 연습 문제 1번select *, count(user) over(partition by user) as total_query_cnt from `advanced.query_logs` order by query_date 2번/* - date 형식 데이터를 extract()로 주 형식으로 바꾸는 걸 알게 됐습니다 - partition by 내에 2개 이상 컬럼을 넣어도 된다는 걸 알게 됐습니다. */ with user_query_cnt 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 user_query_cnt qualify team_rank = 1 order by 1,2 3번/* - lag()와 함께 쓰는 over() 사용법이 헷갈렸는데, 실행 후 수정하는 방향으로 쿼리를 작성해 보라고 하신 점이 인상 깊게 와닿았습니다. */ with base as ( select user, team, extract(week FROM query_date) as week_number, count(user) as query_count from advanced.query_logs group by all ) select *, lag(query_count, 1) over(partition by user order by week_number) as pre_query_cnt from base4번/* qualify cumulative_query_cnt != cumulative_query_cnt2 위 쿼리로 정합성을 체크하는 방식을 배우게 됐습니다. */ ## 4번 with query_cnt as ( select *, 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_cnt, sum(query_count) over(partition by user order by query_date rows between unbounded preceding and current row) as cumulative_query_cnt2 from query_cnt -- qualify cumulative_query_cnt != cumulative_query_cnt2 5번/* - 4번에서 배운 정합성 체크하는 쿼리를 이용해서 컬럼 내 값이 있는지 테스트 해봤습니다. */ ## 정합성 테스트 -- select -- * -- from ( -- select -- *, -- last_value(number_of_orders) over(order by date) as last_values_order -- from raw_data -- ) as test1 -- where -- number_of_orders != last_values_order select *, last_value(number_of_orders ignore nulls) over(order by date) as last_values_order from raw_data 6번/* 처음에는 1. 평균을 구하고 2. 해당 테이블을 서브쿼리로 두고, lag(컬럼, 2)를 해야 하는줄 알았습니다. 그러나 Frame 사용 방법이 생각났고, 조건에 맞게 범위를 설정해서 문제를 해결했습니다. */ ## 6번 , base2 as ( select *, last_value(number_of_orders ignore nulls) over(order by date) as last_values_order, from raw_data ) select *, avg(last_values_order) over(order by date rows between 2 preceding and current row) as moving_avg from base2 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 from `advanced.app_logs` where event_date = '2022-08-18' and user_pseudo_id = '1997494153.8491999091' ) select *, sum(session_start) over(order by event_datetime) as session_id from ( select *, case when second_diff >= 20 then 1 when second_diff is null then 1 else 0 end session_start from ( select *, timestamp_diff(event_datetime, before_event_datetime, second) as second_diff from ( select *, lag(event_datetime, 1) over(partition by user_pseudo_id order by event_datetime) as before_event_datetime from base ) as base2 ) as base3 ) as base4  

윈도우 함수 (탐색 함수) 연습 문제1번SELECT user_id, visit_month, lead(visit_month, 1) OVER(PARTITION BY user_id ORDER BY visit_month) AS next_1month, lead(visit_month, 2) OVER(PARTITION BY user_id ORDER BY visit_month) AS next_2month, FROM advanced.analytics_function_01 ORDER BY 1,2;2번SELECT user_id, visit_month, lead(visit_month, 1) OVER(PARTITION BY user_id ORDER BY visit_month) AS next_1month, lead(visit_month, 2) OVER(PARTITION BY user_id ORDER BY visit_month) AS next_2month, lag(visit_month, 1) OVER(PARTITION BY user_id ORDER BY visit_month) AS pre_1month FROM advanced.analytics_function_01 ORDER BY 1,2;윈도우 함수 연습 문제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 base AS ( SELECT EXTRACT(week FROM query_date) AS week_number, team, user, count(user) AS query_cnt FROM advanced.query_logs GROUP BY 1,2,3 ) 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, query_cnt desc;3번WITH base AS ( SELECT EXTRACT(week FROM query_date) AS week_number, team, user, count(user) AS query_cnt FROM advanced.query_logs GROUP BY 1,2,3 ) SELECT *, lag(query_cnt) OVER(PARTITION BY team, user ORDER BY week_number asc) AS prev_week_query_cnt FROM base ORDER BY team, user, week_number;4번WITH base AS ( SELECT user, team, query_date, COUNT(user) as query_count FROM advanced.query_logs GROUP BY 1,2,3 ) SELECT user, team, query_date, query_count, SUM(query_count) OVER(PARTITION BY team, user ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_query_count FROM base 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, 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 ), fill_null AS ( 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 ) SELECT date, number_of_orders_not_null, avg(number_of_orders_not_null) OVER(ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM fill_null;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 FROM advanced.app_logs WHERE event_date = '2022-08-18' ), diff_date AS ( SELECT *, DATETIME_DIFF(event_datetime, pre_event_time, second) AS date_diff_sec FROM ( SELECT *, LAG(event_datetime, 1) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime asc) AS pre_event_time FROM base ) ), session_start AS ( SELECT *, CASE WHEN pre_event_time IS NULL THEN 1 WHEN date_diff_sec >= 20 THEN 1 END AS start_session FROM diff_date ) SELECT event_date, event_datetime, event_name, user_id, user_pseudo_id, date_diff_sec, SUM(start_session) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS session_id FROM session_start ORDER BY user_pseudo_id, event_datetime;

안녕하세요~리텐션 연습문제 피드백 부탁드립니다! 감사합니다 https://www.notion.so/BigQuery-Retention-12eb0851d79c804389e8caaa3412d282?pvs=4

강사님 강좌를 여러 개 보면서 너무 만족을 하던 차라관심있던 fastapi의 목차를 자세히 보지 못하고 구매부터 했습니다.fastapi를 주로 api 서버로 많이 사용하는 것으로 알던 저는 jwt와 같은 토큰방식 로그인이 강좌에 있을 줄 알았는데, 없어서 많이 아쉬웠습니다.차후에 이러한 강좌들이 추가될 여지가 있을지 알고 싶습니다. 꼭 vue, react가 아닌, 자바스크립트를 이용한 간단한 통신방식의 프론트와의 연동으로도 구현이 될거 같은데.. 너무 아쉬운 마음에 요청 아닌 질문을 조심스레 드려 봅니다.

저는 위와 같이 풀었는데 해설에서는 다른 풀이로 나오더라구요!위와 같은 코드도 정답이 옳다고 나오긴하던데 위처럼 한꺼번에 쓰는 것보다 강의 속 풀이처럼 OR로 나눠 작성하는 것이 더 좋은 걸까요?

깃과 노션 양쪽에서 못찾겠습니다 ㅠㅠ

안녕하세요!강의에서 수강취소의 경우 수강번호(AK)를 WHERE절로 삭제한다고 말씀하신 부분에 추가적으로 궁금한게 있어서 질문드립니다. 예를들어 수강취소를 한다고 할 때 수강 테이블에 DEL_YN이라는 컬럼이 있고 삭제의 경우 delete가 아닌 DEL_YN을 N에서 Y로 update한다면, 학생코드/과목코드/DEL_YN이 1/1/Y인 레코드가 여러개 있을 수 있고 1/1/N인 레코드는 하나만 존재해야할 것 같습니다. 제가 생각하는 방법이 정상적인 방법인지도 궁금하고, 또 이 경우에는 학생코드, 과목코드, DEL_YN=N 이렇게 3개를 묶어서 PK를 해야하는지 그리고 수강취소 예시에서 delete가 아닌 DEL_YN같은 값을 update해야한다면 보통 어떤식으로 제약조건을 추가하는지 말씀 부탁드립니다.감사합니다.

connect to a database 창에서 최초로 연결하려고 하는데 ORA-01017가 뜹니다몇번이고 맞게 적어도 그렇습니다 왜이럴까요..?! ㅠ ㅠ 그런데 오라클만 이렇게 뜨고sql server은 잘 연결되어 테이블까지 잘 불러와집니다.. +아무리 시도해도 오라클은 연결이 안되고 sql server만 연결되는데앞으로 실습할 때 sql server에서 실습해도 괜찮을까요? 추후에 문제가 있을까요?? 

[ARRAY, STRUCT]array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요.SELECT title, #genres, genre FROM `advanced.array_excercises` ae cross join unnest(genres) as genre;2. array_exercies 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요 배우와 배역은 별도의 컬럼으로 나와야 합니다.select title, actor.actor, actor.character from `advanced.array_excercises` cross join unnest(actors) as actor; array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르 (genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다.select title, actor.actor, actor.character, genre from `advanced.array_excercises` cross join unnest(actors) as actor cross join unnest(genres) as genre;앱 로그 데이터(app_logs) 배열 풀기select user_id, event_date, event_name, user_pseudo_id, params.key, params.value.string_value as string_value, params.value.int_value as int_value from `advanced.app_logs` cross join unnest(event_params) as params where event_date = "2022-08-01 [PIVOT]orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT해주세요.날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다.select order_date, # amount의 합 sum(if(user_id=1, amount, 0)) as user_1, sum(if(user_id=2, amount, 0)) as user_2, sum(if(user_id=3, amount, 0)) as user_3 FROM `advanced.orders` group by order_date order by order_date; orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다select user_id, sum(if(order_date = "2023-05-01", amount, 0)) as `2023-05-01`, sum(if(order_date = "2023-05-02", amount, 0)) as `2023-05-02`, sum(if(order_date = "2023-05-03", amount, 0)) as `2023-05-03`, sum(if(order_date = "2023-05-04", amount, 0)) as `2023-05-04`, sum(if(order_date = "2023-05-05", amount, 0)) as `2023-05-05` from `advanced.orders` group by user_id order by user_id;orders 테이블에서 사용자(user_id)별, 날짜(order_date)별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다.select user_id, max(if(order_date = "2023-05-01", 1, 0)) as `2023-05-01`, max(if(order_date = "2023-05-02", 1, 0)) as `2023-05-02`, max(if(order_date = "2023-05-03", 1, 0)) as `2023-05-03`, max(if(order_date = "2023-05-04", 1, 0)) as `2023-05-04`, max(if(order_date = "2023-05-05", 1, 0)) as `2023-05-05` from `advanced.orders` group by user_id order by user_id;user_id = 32888이 카트 추가하기(click_cart)를 누를때 어떤 음식(food_id)을 담았나요?with base as ( select -- * EXCEPT(event_params), # * except(컬럼) :컬럼을 제외하고 모두 다 보여줘 -- param event_date, event_timestamp, event_name, user_id, user_pseudo_id, max(if(param.key = "firebase_screen", param.value.string_value, null)) as firebase_screen, -- max(if(param.key = "food_id", param.value.string_value, null)) as food_id, max(if(param.key = "food_id", param.value.int_value, null)) as food_id, max(if(param.key = "session_id", param.value.string_value, null)) as session_id from `advanced.app_logs` cross join unnest(event_params) as param group by all ) select user_id, event_date, count(user_id) as user_cnt, food_id from base where user_id = 32888 and event_name = "click_cart" group by all [퍼널분석]with base as ( SELECT event_date, event_timestamp, event_name, user_id, user_pseudo_id, platform, max(if(event_param.key = "firebase_screen", event_param.value.string_value, null)) as firebase_screen, max(if(event_param.key = "session_id", event_param.value.string_value, null)) as session_id from `advanced.app_logs` cross join unnest(event_params) as event_param where event_date between "2022-08-01" and "2022-08-18" group by all ), filter_event as( select * except(event_name, firebase_screen, event_timestamp), concat(event_name, "-", firebase_screen) as event_name_with_screen, DATETIME(timestamp_micros(event_timestamp),'Asia/Seoul')AS event_datetime from base where event_name IN("screen_view", "click_payment") ), screen_view as( select event_date, event_name_with_screen, case when event_name_with_screen = "screen_view-welcome" then 1 when event_name_with_screen = "screen_view-home" then 2 when event_name_with_screen = "screen_view-food_category" then 3 when event_name_with_screen = "screen_view-restaurant" then 4 when event_name_with_screen = "screen_view-cart" then 5 when event_name_with_screen = "click_payment-cart" then 6 else null end as step_number, count(distinct user_pseudo_id) as cnt from filter_event group by all having step_number is not null order by event_date ) select event_date, max(if(event_name_with_screen = "screen_view-welcome", cnt, null)) as screen_view_welcome, max(if(event_name_with_screen = "screen_view-home", cnt, null)) as screen_view_home, max(if(event_name_with_screen = "screen_view-food_category", cnt, null)) as screen_view_food_category, max(if(event_name_with_screen = "screen_view-restaurant", cnt, null)) as screen_view_restaurant, max(if(event_name_with_screen = "screen_view-cart", cnt, null)) as screen_view_cart, max(if(event_name_with_screen = "click_payment-cart", cnt, null)) as click_payment_cart from screen_view group by all order by event_date 

--연습문제 CREATE OR REPLACE TABLE advanced.array_exercises AS SELECT movie_id, title, actors, genres FROM ( SELECT 1 AS movie_id, 'Avengers: Endgame' AS title, ARRAY<STRUCT<actor STRING, character STRING>>[ STRUCT('Robert Downey Jr.', 'Tony Stark'), STRUCT('Chris Evans', 'Steve Rogers') ] AS actors, ARRAY<STRING>['Action', 'Adventure', 'Drama'] AS genres UNION ALL SELECT 2, 'Inception', ARRAY<STRUCT<actor STRING, character STRING>>[ STRUCT('Leonardo DiCaprio', 'Cobb'), STRUCT('Joseph Gordon-Levitt', 'Arthur') ], ARRAY<STRING>['Action', 'Adventure', 'Sci-Fi'] UNION ALL SELECT 3, 'The Dark Knight', ARRAY<STRUCT<actor STRING, character STRING>>[ STRUCT('Christian Bale', 'Bruce Wayne'), STRUCT('Heath Ledger', 'Joker') ], ARRAY<STRING>['Action', 'Crime', 'Drama'] ) -- 1) array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요 select title , genres_new from advanced,.array_exercise AS a, UNNEST(genres) as genres_new -- 2) array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야 합니다 select title , actors_new.actor , actors_new.character from advanced,.array_exercise AS a, UNNEST(actors) as actors_new --3) array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르 (genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다 --방법 1 with gen as ( select title , genres_new from advanced,.array_exercise AS a, UNNEST(genres) as genres_new ) , actors as ( select title , actors_new.actor , actors_new.character from advanced,.array_exercise AS a, UNNEST(actors) as actors_new ) select from gen g join actors a on g.title=a.title --방법 2 select title , actors_new.actor , actors_new.character , genre_new from advanced,.array_exercise AS a, UNNEST(actors) as actors_new, UNNEST(genres) as genre_new --방법 3 select title , actors_new.actor , actors_new.character , genre_new from advanced.array_exercise cross join UNNEST(actors) as actors_new cross join UNNEST(genres) as genre_new where actors_new.actor 로 조건을 걸어야함 --actor(키값바로) 또는 actors_new로는 안된다 actors_new는 스트럭트 구조이고 actor는 이전 값임 --4) 앱 로그 데이터(app_logs)의 배열을 풀어주세요 --하루 사용자 집계, 어떤 이벤트가 있는가? select user_id , event_date , event_name , user_pseudo_id , event_component.key , event_component.value.string_value , event_component.value.int_value from app_logs as app, UNNEST(event_pharams) as event_component where event_date = '2022-08-11' --피봇 과제 --1) orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT해주세요. 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다 with raw as ( select user_id , order_date , sum(amount) as amounts from orders ) SELECT order_date , MAX(IF(user_id=1, amounts, NULL)) AS user_1 , MAX(IF(user_id=2, amounts, NULL)) AS user_2 , MAX(IF(user_id=3, amounts, NULL)) AS user_3 ... FROM raw GROUP BY order_date --2) orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다 with raw as ( --혹시나 하나의 유저가 하루에 여러 주문을 했을수도 있을것 같아서 이것 사용(MAX 쓸 예정이라서) select user_id , order_date , sum(amount) as amounts from orders ) SELECT user_id , MAX(IF(order_date='2023-05-01', amounts, NULL)) AS '2023-05-01' , MAX(IF(order_date='2023-05-02', amounts, NULL)) AS '2023-05-02' , MAX(IF(order_date='2023-05-03', amounts, NULL)) AS '2023-05-03' ... FROM raw GROUP BY user_id --3) orders 테이블에서 사용자(user_id)별, 날짜(order_date)별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다 with raw as ( select user_id , order_date , sum(amount) as amounts , count(distinct order_id) as order_cnt from orders ) SELECT user_id , MAX(IF(order_date='2023-05-01', 1, 0)) AS '2023-05-01' , MAX(IF(order_date='2023-05-02', 1, 0)) AS '2023-05-02' , MAX(IF(order_date='2023-05-03', 1, 0)) AS '2023-05-03' --second case , MAX(IF(order_date='2023-05-01', order_cnt, 0)) AS '2023-05-01' , MAX(IF(order_date='2023-05-02', order_cnt, 0)) AS '2023-05-02' , MAX(IF(order_date='2023-05-03', order_cnt, 0)) AS '2023-05-03' ... FROM raw GROUP BY user_id -- 4)user_id = 32888이 카트 추가하기(click_cart)를 누를때 어떤 음식(food_id)을 담았나요? -- ##데이터 조회할때 유용한 except(column):특정 컬럼 제외하고 모두 다 -- select * except(event_params) -- GROUP BY ALL with raw as ( select user_id , event_date , event_name , user_pseudo_id , event_component.key , event_component.value.int_value as food_id from app_logs as app, UNNEST(event_pharams) as event_component where user_id = 32888 and event_name = 'click_cart' and event_component.key = 'food_id' ) select distinct event_time --나는 일별로 보고 싶어서 추가 , food_id from raw --성윤님 강의 내용 select user_id , event_name , event_date , event_timestamp --같은일에 중복 주문이 있을까봐서 , MAX(IF(event_component.key='food_id', event_component.value.int_value, NULL)) AS food_id from app_logs as app, UNNEST(event_pharams) as event_component where user_id = 32888 and event_name = 'click_cart' and event_component.key = 'food_id' GROUP BY ALL --알아서 컬럼들 픽 --퍼널 별 유저 수 집계 with raw as ( select user_id , event_date , event_timestamp , event_name , user_pseudo_id , platform , event_component.key , event_component.value.string_value , event_component.value.int_value , MAX(IF(event_component.key = "firebase_screen", event_component.value.string_value, NULL)) AS firebase_screen -- , MAX(IF(event_component.key = "food_id", event_component.value.int_value, NULL)) AS food_id , MAX(IF(event_component.key = "session_id", event_component.value.int_value, NULL)) AS session_id from app_logs as app, UNNEST(event_pharams) as event_component where event_date BETWEEN "2022-08-01" AND "2022-08-18" group by all ) , filter_event_and_concat_event_and_screen AS( SELECT * EXCEPT(event_name, firebase_screen,event_timestamp) , CONCAT(event_name, "-", firebase_screen) AS event_name_with_screen , DATETIME(TIMESTAMP_MICROS(event_timestamp), "Asia/Seoul") AS event_datetime FROM base WHERE event_name IN ("screen_view", "click_payment") ) SELECT event_date, event_name_with_screen, CASE WHEN event_name_with_screen = 'screen_view-welcome' THEN 1 WHEN event_name_with_screen = 'screen_view-home' THEN 2 WHEN event_name_with_screen = 'screen_view-food_category' THEN 3 WHEN event_name_with_screen = 'screen_view-restaurant' THEN 4 WHEN event_name_with_screen = 'screen_view-cart' THEN 5 WHEN event_name_with_screen = 'click_payment-cart' THEN 6 ELSE NULL END AS step_number, COUNT(DISTINCT user_pseudo_id) AS cnt FROM filter_event_and_concat_event_and_screen GROUP BY ALL HAVING step_number IS NOT NULL ORDER BY event_date, step_number  강의 노트select [0,1,1,2,3,4] as array_practice array<int64>[0,1,3] as array_practice generate_array(1,5,2) generate_date_array('2024-01-01', '2024-02-01', interval 1 week) WITH programming_languages AS ( SELECT "python" AS programming_language UNION ALL SELECT "go" UNION ALL SELECT "scala" ) select array_agg(programming_languages) as output from programming_languages --배열에 접근하기 offset: #0 ordinal: #1 #out of range를 방지하기 위해서 safe_ 추가하기 --사용 예시 select some_numbers[safe_offset(1)] as second_value 컬럼명[safe_offset(가져오고 싶은 위치)] Array(like list): 비슷한 카테고리에 대해 데이터를 저장할때 예시) 메뉴(컬럼): 돼지국밥, 떡볶이, 치킨 Struct(like dict): 다양한 속성에 대해 데이터를 한 컬럼에 다 넣고 싶을때 예시) 주소록(컬럼): 이름, 전화번호,이메일, 생일 등등 SELECT (1,2,3) AS struct_test SELECT STRUCT<hi INT64, hello INT64, awesome STRING>(1, 2, 'HI') AS struct_test SELECT struct_test.hi, struct_test.hello FROM ( SELECT STRUCT<hi INT64, hello INT64, awesome STRING>(1, 2, 'HI') AS struct_test ) -- UNNEST를 사용해 중첩된 데이터 구조 풀기(평면화, Flatten) WITH example_data AS( SELECT 'kyle' AS name, ['Python', 'SQL', 'R', 'Julia', 'Go'] AS preferred_language, 'Incheon' AS hometown UNION ALL SELECT 'max' AS name, ['Python', 'SQL', 'Scala', 'Java', 'Kotlin'] AS preferred_language, 'Seoul' AS hometown UNION ALL SELECT 'yun' AS name, ['Python', 'SQL'] AS preferred_language, 'Incheon' AS hometown ) SELECT name, pref_lang, hometown FROM example_data CROSS JOIN UNNEST(preferred_language) AS pref_lang FROM exaple_data AS a, UNNEST(preferred_language) AS pref_lang --그럼 unnest안에는 array만? struct는? SELECT student , MAX(IF(subject="수학", score, NULL)) AS 수학 , MAX(IF(subject="영어", score, NULL)) AS 영어 , MAX(IF(subject="과학", score, NULL)) AS 과학 FROM Table GROUP BY student ###팁 #같은 단어를 수정할 때,빨리하고 싶은 - 단어를 커서위에 올리고 커맨드 디 범위설정하고 수정하면 일괄수정 -> 인텔리데이에서는 어떻게 하지? #기대하는 아웃풋의 형태를 적어보는것 좋다 -> 쉐어포인트 컬럼에 만들기 프로젝트 시작전 - 어떤 업무를 함에 있어서 흐름을 아는 것이 중요하다(흐름을 모르면 어떤것을 왜 해야하는지 모를 수 있음) - 맥락 -> 목적 -> 퍼널 -> 가설 -> 분석 서비스의 목표 파악(어떤 문제를 해결하려고 하는지) 문제 정의: 핵심 문제 목표 정의 퍼널 정의 -> 우리도 이 데이터가 있는지 물어보기 

1. ARRAY, STRUCT ### 1) array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요 # ARRAY : 같은 타입의 여러 데이터를 저장하고 싶을 때 # ARRAY를 Flatten(평면화) => UNNEST # UNNEST를 할 때는 CROSS JOIN + UNNEST(ARRAY_COLUMN) # UNNEST(ARRAY_COLUMN) AS 새로운 이름 # SELECT 절에서 새로운 이름으로 사용한다. 기존의 ARRAY_COLUMN은 사용하지 않는다! -- SELECT -- title -- , genre -- FROM `advanced.array_exercises` -- CROSS JOIN UNNEST(genres) AS genre -- ORDER BY 1, 2 ## 같은 결과를 출력하기 위해 정렬함. -- ; ### 2) array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야 합니다. # 직접 접근하려면 actors = [ STRUCT(STRING, STRING)] # actors[SAFE_OFFSET(0)].actor # actors[SAFE_OFFSET(0)].character -- SELECT -- title -- , act.actor# AS actor -- , act.character# AS character -- FROM `advanced.array_exercises` -- CROSS JOIN UNNEST(actors) AS act -- ORDER BY 1 -- ; ### 3) array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르(genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다. # 데이터의 중복이 어느정도 생기는데, 그것은 어쩔 수 없는 이슈(CROSS JOIN) -- SELECT -- title -- -- actors, # ARRAY<STRUCT(STRING, STRING)> -- , act.actor# AS actor -- , act.character# AS character -- -- genres # ARRAY<STRING> -- , genre -- FROM `advanced.array_exercises` -- CROSS JOIN UNNEST(actors) AS act -- CROSS JOIN UNNEST(genres) AS genre -- -- WHERE 1=1 -- -- ## 강의 촬영 시점 이후에 수정된 듯 두 쿼리 모두 오류없이 실행 되는 것 같아요 ! -- -- AND act.actor = "Chris Evans" -- -- AND actor = "Chris Evans" -- ORDER BY 1 -- ; ### 4) 앱 로그 데이터(app_logs)의 배열을 풀어주세요. -- SELECT -- user_id -- , event_date -- , event_name -- , user_pseudo_id -- , evt_prm.key AS key -- , evt_prm.value.string_value AS string_value -- , evt_prm.value.int_value AS int_value -- FROM `advanced.app_logs` -- CROSS JOIN UNNEST(event_params) AS evt_prm -- WHERE 1=1 -- AND event_date = "2022-08-01" -- ORDER BY 2 -- ; ### WITH 문 변경 WITH base AS ( SELECT user_id , event_date , event_name , user_pseudo_id , evt_prm.key AS key , evt_prm.value.string_value AS string_value , evt_prm.value.int_value AS int_value FROM `advanced.app_logs` CROSS JOIN UNNEST(event_params) AS evt_prm WHERE 1=1 AND event_date = "2022-08-01" ) SELECT event_date , event_name , COUNT(DISTINCT user_id) AS cnt FROM base GROUP BY ALL ORDER BY cnt DESC 2. PIVOT# 1) orders 테이블에서 유저(user_id)별로 주문금액(amount)의 합계를 PIVOT해주세요. 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다. -- 기대하는 output의 형태 -- order_date | user_1 | user_2 | user_3 -- PIVOT : MAX(IF(조건, TRUE일 때의 값, FALSE일 때의 값)) AS new_column + GROUP BY -- MAX 대신 집계 함수를 사용할 수도 있음. SUM -- FALSE일 때의 값은 NULL -- SELECT -- order_date -- , SUM(IF(user_id = 1, amount, 0)) AS user_1 -- , SUM(IF(user_id = 2, amount, 0)) AS user_2 -- , SUM(IF(user_id = 3, amount, 0)) AS user_3 -- FROM `advanced.orders` -- GROUP BY 1 -- ORDER BY 1 -- ; # 2) orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요.user_id를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다. -- 기대하는 output의 형태 -- user_id | 2023-05-01 | 2023-05-02 | 2023-05-03 | 2023-05-04 | 2023-05-05 -- SELECT -- user_id -- , SUM(IF(order_date="2023-05-01", amount, 0)) AS `2023-05-01` -- , SUM(IF(order_date="2023-05-02", amount, 0)) AS `2023-05-02` -- , SUM(IF(order_date="2023-05-03", amount, 0)) AS `2023-05-03` -- , SUM(IF(order_date="2023-05-04", amount, 0)) AS `2023-05-04` -- , SUM(IF(order_date="2023-05-05", amount, 0)) AS `2023-05-05` -- 컬럼의 이름을 지정할 때, 영어 제외하고 backtick(`) -- ANY_VALUE : 그훕화 할 대상 중에 임의의 값을 선택한다 (NULL을 제외하고). ANY_VALUE에선 나머지 값들이 NULL이거나 확정적으로 값을 기대할 수 있을 때 사용한다! -- ANY_VALUE(IF(order_date="2023-05-01", amount, NULL)) AS `2023-05-01` -- FROM `advanced.orders` -- GROUP BY 1 -- ORDER BY 1 -- ; # 3) orders 테이블에서 사용자(user_id)별, 날짜(order_date)별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다. -- SELECT -- user_id -- , MAX(IF(order_date="2023-05-01", 1, 0)) AS `2023-05-01` -- , MAX(IF(order_date="2023-05-02", 1, 0)) AS `2023-05-02` -- , MAX(IF(order_date="2023-05-03", 1, 0)) AS `2023-05-03` -- , MAX(IF(order_date="2023-05-04", 1, 0)) AS `2023-05-04` -- , MAX(IF(order_date="2023-05-05", 1, 0)) AS `2023-05-05` -- FROM `advanced.orders` -- GROUP BY 1 -- ORDER BY 1 -- ; ## 앱 로그 PIVOT WITH base AS( SELECT -- * EXCEPT(event_params) # * EXCEPT(column) : 컬럼을 제외하고 다 보여줘! event_date , event_timestamp , event_name , user_id , user_pseudo_id , MAX(IF(param.key = "fierbase_screen", param.value.string_value, NULL)) AS fierbase_screen -- , MAX(IF(param.key = "food_id", param.value.string_value, NULL)) AS food_id # string_value엔 food_id 값들이 저장되어 있지 않음. , MAX(IF(param.key = "food_id", param.value.int_value, NULL)) AS food_id , MAX(IF(param.key = "session_id", param.value.string_value, NULL)) AS sessioon_id FROM `advanced.app_logs` CROSS JOIN UNNEST(event_params) AS param WHERE 1=1 AND event_date = "2022-08-01" GROUP BY ALL ) SELECT event_date , COUNT(user_id) AS user_cnt FROM base WHERE 1=1 AND event_name = "click_cart" -- AND food_id = 1544 GROUP BY event_date 3. 퍼널 연습 문제# 퍼널 분석 -- 퍼널 데이터 -- 우리가 사용할 이벤트 => 단계 -- - screen_view : welcome, home, food_category, restaurant, cart -- - click_payment -- step_number : 추후에 정렬을 위해 만들 것 -- 사용할 데이터 : 앱 로그 데이터, GA/Firebase => UNNEST => PIVOT -- 기간 : 2022-08-01 ~ 2022-08-18 WITH base as ( SELECT event_date , event_timestamp , event_name , user_id , user_pseudo_id , platform , MAX(IF(event_param.key = "firebase_screen", event_param.value.string_value, NULL)) as firebase_screen , MAX(IF(event_param.key = "food_id", event_param.value.int_value, NULL)) as food_id , MAX(IF(event_param.key = "session_id", event_param.value.string_value, NULL)) as session_id FROM advanced.app_logs CROSS JOIN UNNEST(event_params) as event_param WHERE event_date BETWEEN "2022-08-01" AND "2022-08-18" GROUP BY ALL ), base2 as ( SELECT * , CONCAT(event_name, "-", firebase_screen) as event_screen FROM base WHERE 1=1 AND event_name IN ("screen_view", "click_payment") ), base3 as ( SELECT event_screen , event_date , CASE WHEN event_screen = "screen_view-welcome" THEN 1 WHEN event_screen = "screen_view-home" THEN 2 WHEN event_screen = "screen_view-food_category" THEN 3 WHEN event_screen = "screen_view-restaurant" THEN 4 WHEN event_screen = "screen_view-cart" THEN 5 WHEN event_screen = "click_payment-cart" THEN 6 ELSE NULL END as step_number , COUNT(DISTINCT user_pseudo_id) as cnt FROM base2 GROUP BY ALL HAVING step_number is not NULL ORDER BY event_date ) SELECT event_date , MAX(IF(base3.event_screen ="screen_view-welcome", cnt, NULL)) AS screen_view_welcome , MAX(IF(base3.event_screen ="screen_view-home", cnt, NULL)) AS screen_vie_home , MAX(IF(base3.event_screen ="screen_view-food_category", cnt, NULL)) AS screen_view_food_category , MAX(IF(base3.event_screen ="screen_view-restaurant", cnt, NULL)) AS screen_view_restaurant , MAX(IF(base3.event_screen ="screen_view-cart", cnt, NULL)) AS screen_view_cart FROM base3 GROUP BY ALL ORDER BY event_date

[ARRAY, STRUCT] 문제 1) array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요.SELECT title, genre FROM advanced.array_exercises CROSS JOIN UNNEST(genres) AS genre쿼리 결과 1)문제 2) array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요.SELECT title, actor.actor, actor.character FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor쿼리 결과 2) 문제 3) array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르 (genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다.SELECT title, actor.actor AS actor, actor.character AS character, genre FROM advanced.array_exercises CROSS JOIN UNNEST(genres) AS genre CROSS JOIN UNNEST(actors) AS actor쿼리 결과 3)문제 4) 앱 로그 데이터(app_logs) 배열 풀기SELECT event_date, event_timestamp, event_name, event_param.key AS key, event_param.value.string_value AS string_value, event_param.value.int_value AS int_value, user_id, user_pseudo_id, platform FROM `advanced.app_logs` CROSS JOIN UNNEST(event_params) AS event_param WHERE event_date = "2022-08-01" LIMIT 100쿼리 결과 4)[PIVOT]문제 1) orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT해주세요.날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다.SELECT order_date, SUM(IF(user_id = 1, amount, 0)) AS user_1, SUM(IF(user_id = 2, amount, 0)) AS user_2, SUM(IF(user_id = 3, amount, 0)) AS user_3 FROM advanced.orders GROUP BY ALL ORDER BY order_date쿼리 결과 1)문제 2) orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다SELECT user_id, SUM(IF(order_date = "2023-05-01", amount, 0)) AS `2023-05-01`, SUM(IF(order_date = "2023-05-02", amount, 0)) AS `2023-05-02`, SUM(IF(order_date = "2023-05-03", amount, 0)) AS `2023-05-03`, SUM(IF(order_date = "2023-05-04", amount, 0)) AS `2023-05-04`, SUM(IF(order_date = "2023-05-05", amount, 0)) AS `2023-05-05`, FROM advanced.orders GROUP BY ALL ORDER BY user_id 쿼리 결과 2)문제 3) orders 테이블에서 사용자(user_id)별, 날짜(order_date)별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다SELECT user_id, MAX(IF(order_date = "2023-05-01", 1, 0)) AS `2023-05-01`, MAX(IF(order_date = "2023-05-02", 1, 0)) AS `2023-05-02`, MAX(IF(order_date = "2023-05-03", 1, 0)) AS `2023-05-03`, MAX(IF(order_date = "2023-05-04", 1, 0)) AS `2023-05-04`, MAX(IF(order_date = "2023-05-05", 1, 0)) AS `2023-05-05`, FROM advanced.orders GROUP BY ALL ORDER BY user_id 쿼리 결과 3)문제 4)user_id = 32888이 카트 추가하기(click_cart)를 누를때 어떤 음식(food_id)을 담았나요?WITH base AS ( SELECT event_date, event_timestamp, event_name, user_id, user_pseudo_id, MAX(IF(event_param.key = 'firebase_screen',event_param.value.string_value, NULL)) AS firebase_screen, MAX(IF(event_param.key = 'food_id',event_param.value.int_value, NULL)) AS food_id, MAX(IF(event_param.key = 'session_id',event_param.value.string_value, NULL)) AS session_id, FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS event_param GROUP BY ALL ) SELECT user_id, event_date, COUNT(user_id) AS user_cnt, food_id FROM base WHERE user_id = 32888 and event_name = 'click_cart' GROUP BY ALL쿼리 결과 4) [퍼널분석]WITH base AS ( SELECT event_date, event_timestamp, event_name, user_id, user_pseudo_id, platform, MAX(IF(event_param.key = "firebase_screen", event_param.value.string_value, NULL)) AS firebase_screen, MAX(IF(event_param.key = "session_id", event_param.value.string_value, NULL)) AS session_id FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS event_param WHERE event_date BETWEEN "2022-08-01" AND "2022-08-18" GROUP BY ALL ), filter_event_and_concat_event_and_screen AS ( SELECT * EXCEPT(event_name, firebase_screen, event_timestamp), CONCAT(event_name, "-", firebase_screen) AS event_name_with_screen, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime FROM base WHERE event_name IN ("screen_view", "click_payment") ) SELECT event_date, event_name_with_screen, CASE WHEN event_name_with_screen = "screen_view-welcome" THEN 1 WHEN event_name_with_screen = "screen_view-home" THEN 2 WHEN event_name_with_screen = "screen_view-food_category" THEN 3 WHEN event_name_with_screen = "screen_view-restaurant" THEN 4 WHEN event_name_with_screen = "screen_view-cart" THEN 5 WHEN event_name_with_screen = "click_payment-cart" THEN 6 ELSE NULL END AS step_number, COUNT(DISTINCT user_pseudo_id) AS cnt FROM filter_event_and_concat_event_and_screen GROUP BY ALL HAVING step_number IS NOT NULL 쿼리 결과