# WEEKLY Retention 쿼리 WITH base AS( SELECT DISTINCT user_id, user_pseudo_id, event_name, DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime FROM advanced.app_logs WHERE event_date BETWEEN "2022-08-01" AND "2022-11-03" ),first_week_and_diff AS ( SELECT *, DATE_DIFF(event_week, first_week, WEEK) AS diff_of_week FROM ( SELECT DISTINCT user_pseudo_id, DATE_TRUNC(MIN(event_date) OVER(PARTITION BY user_pseudo_id), WEEK(MONDAY)) AS first_week, DATE_TRUNC(event_date, WEEK(MONDAY)) AS event_week FROM base ) ), user_counts AS ( SELECT diff_of_week, COUNT(DISTINCT user_pseudo_id) AS user_cnt FROM first_week_and_diff GROUP BY diff_of_week ORDER BY diff_of_week ) SELECT *, ROUND(SAFE_DIVIDE(user_cnt, first_week_user_cnt),3) AS retention_rate FROM( SELECT diff_of_week, user_cnt, FIRST_VALUE(user_cnt) OVER(ORDER BY diff_of_week) AS first_week_user_cnt FROM user_counts ) # Monthly Retention 쿼리 WITH base AS( SELECT DISTINCT user_id, user_pseudo_id, event_name, DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime FROM advanced.app_logs WHERE event_date BETWEEN "2022-08-01" AND "2022-11-03" ),first_month_and_diff AS ( SELECT *, DATE_DIFF(event_month, first_month, MONTH) AS diff_of_month FROM ( SELECT DISTINCT user_pseudo_id, DATE_TRUNC(MIN(event_date) OVER(PARTITION BY user_pseudo_id), MONTH) AS first_month, DATE_TRUNC(event_date, MONTH) AS event_month FROM base ) ), user_counts AS ( SELECT diff_of_month, COUNT(DISTINCT user_pseudo_id) AS user_cnt FROM first_month_and_diff GROUP BY diff_of_month ORDER BY diff_of_month ) SELECT *, ROUND(SAFE_DIVIDE(user_cnt, first_month_user_cnt),3) AS retention_rate FROM( SELECT diff_of_month, user_cnt, FIRST_VALUE(user_cnt) OVER(ORDER BY diff_of_month) AS first_month_user_cnt FROM user_counts )
카일스쿨님 안녕하세요, 사용자 VoC 나 SNS 등의 정성 데이터를 모아서 분석하는 방법이 있을까요? 데이터 분석 관련해서는 아직 기초적인 지식도 없다보니 정성 데이터는 어떤 방법으로 분석하는지, 어떤 방법이 가장 효과적일지 궁금합니다! (정성 데이터를 분석해서 문제 정의의 근거로 사용하려는 목적입니다. SQL 초급 강의는 아직 수강 전입니다 🥹)
안녕하세요! 현재도 최종과제 피드백을 해주시는지 잘 모르겠으나,, 피드백 받아보고 싶어서, 최종 과제 제출해보겠습니다! 3번 문제에서는 특히 생각처럼 인사이트가 안나와서 어려웠습니다.. ㅎㅎ https://www.notion.so/Foodie-express-SQL-223249eb444480eca214ed420d3699a4?source=copy_link
질문을 남겨주실 때 아래 부분을 같이 남겨주시면 더 빠르게 상황을 파악하고 답변드릴 수 있어요 🙂 어떤 강의를 들으면서 발생했나요? 1-6 PIVOT 예시 문제 풀이 어떤 문제가 생겼나요? 강의에서 사용하신 쿼리 그대로 입력했는데 추출되는 결과가 다릅니다. 왜 인지 이유를 모르겠습니다ㅠ 어떤 시도를 해보셨나요? 잘못입력한 쿼리가 있는지 확인해봤는데 어떤 것이 문제인지 잘 모르겠습니다. 어떤 쿼리를 사용했나요? #앱 로그 데이터 PIVOT SELECT 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_id2, 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 WHERE event_date = "2022-08-01" GROUP BY ALL LIMIT 100 환경이 어떻게 되나요?(윈도우, 맥 중 택1, 크롬 같은 웹브라우저 환경) 크롬 결과값 선생님께서 입력하신 쿼리와 무슨 차이가 있는건가요? 데이터가 표출되는 정렬이 달라서 이렇게 나오는 것인지 강의 영상 내 결과에서는 food_id2 컬럼의 값이 모두 채워져있는데 저는 NULL로 나옵니다. 왜인지 모르겠습니다 ㅠ
질문을 남겨주실 때 아래 부분을 같이 남겨주시면 더 빠르게 상황을 파악하고 답변드릴 수 있어요 🙂 어떤 강의를 들으면서 발생했나요? 어떤 문제가 생겼나요? 어떤 시도를 해보셨나요? 어떤 쿼리를 사용했나요? 환경이 어떻게 되나요?(윈도우, 맥 중 택1, 크롬 같은 웹브라우저 환경) 배운 것을 토대로 분석해보았는데, 홈페이지에 올렸습니다 링크
WITH base as ( SELECT event_date, event_timestamp, event_name, user_id, user_pseudo_id, platform, MAX(IF(params.key='firebase_screen', params.value.string_value, NULL)) as firebase_screen, -- MAX(IF(params.key='food_id', params.value.int_value, NULL)) as food_id, MAX(IF(params.key='session_id', params.value.string_value, NULL)) as session_id, FROM advanced.app_logs CROSS JOIN UNNEST(event_params) as params WHERE -- event_date = "2022-08-01" # 적은 데이터로 쿼리를 작성하기 위해 만든 조건 event_date BETWEEN "2022-08-01" AND "2022-08-18" GROUP BY ALL ), fileter_event_and_concat_event_and_screen AS( -- event_name + screen 하면서(필요한 이벤트만 WHERE 조건에 걸어서 사용) SELECT * EXCEPT(event_name, firebase_screen, event_timestamp), CONCAT(event_name, "-", firebase_screen) AS event_name_with_screen, DATETIME(TIMESTAMP_MICROS(event_timestamp), "Asia/Seoul") AS event_datetime FROM base WHERE event_name IN("screen_view", "click_payment") ) -- step_number + cnt 만들기 -- step_number : CASE WHEN을 사용해 숫자 지정 , pivot_base as ( SELECT event_date, # 일자별로 퍼널별 유저 수 쿼리, 일자별로 처리하기 위해 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 fileter_event_and_concat_event_and_screen GROUP BY ALL HAVING step_number IS NOT NULL ORDER BY event_date -- WHERE -- user_pseudo_id = "2173465695.8694960923" -- user_pseudo_id = "2173465695.8694960923" :event_name_with_screen = screen_view-welcome의 user_id는 NULL -> home에서 로그인 -- food_detal, search 등 event_name_with_scree의 여러 요소들을 확인해보기 ) # [자습] 위 테이블을 screen_view 타입별로 피벗 테이블 만들기 쿼리 (위 테이블을 with 절로 만들고 아래 쿼리 실행) SELECT event_date, MAX(IF(event_name_with_screen="screen_view-welcome", cnt, NULL)) as `scren_view-welcome`, MAX(IF(event_name_with_screen="screen_view-home", cnt, NULL)) as `scren_view-home`, MAX(IF(event_name_with_screen="screen_view-food_category", cnt, NULL)) as `scren_view-category`, MAX(IF(event_name_with_screen="screen_view-restaurant", cnt, NULL)) as `scren_view-restaurant` FROM pivot_base GROUP BY event_date ORDER BY event_date 과제 올립니다 감사합니다
안녕하세요, 강의 수강하면서 Weekly, Monthly Retention 쿼리를 작성해보았습니다! 그리고 질문이 하나 있는데, 첨부해드린 코드는 현재 첨부해드린 사진처럼 오류가 발생합니다. 그런데 Weekly Retention 쿼리를 전부 주석처리하면 Monthly Retention 쿼리는 정상 작동합니다. 반대로 Monthly Retention 쿼리를 주석처리하면 Weekly Retention 쿼리는 정상 작동합니다. 이 현상의 원리에 대한 감이 잡히지 않아서 질문드립니다! 그 외에 쿼리 관련해서 고칠 점이 있다면 편하게 말씀해주시면 감사하겠습니다! WITH base AS ( SELECT user_id, user_pseudo_id, event_name, DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime FROM advanced.app_logs WHERE event_date BETWEEN "2022-08-01" AND "2022-11-03" ), first_week_and_diff AS ( SELECT *, -- DATE_DIFF(event_date, first_date, DAY) diff_of_day DATE_DIFF(event_week, first_week, WEEK) diff_of_week FROM ( SELECT DISTINCT -- 일자별로 중복 제거 user_pseudo_id, -- DATE_TRUNC DATE_TRUNC(MIN(event_date) OVER (PARTITION BY user_pseudo_id), WEEK(MONDAY)) AS first_week, MIN(event_date) OVER (PARTITION BY user_pseudo_id) AS first_date, DATE_TRUNC(event_date, WEEK(MONDAY)) AS event_week, event_date FROM base ) ), first_month_and_diff AS ( SELECT *, -- DATE_DIFF(event_date, first_date, DAY) diff_of_day DATE_DIFF(event_month, first_month, MONTH) AS diff_of_month FROM ( SELECT DISTINCT user_pseudo_id, --DATE_TRUNC DATE_TRUNC(MIN(event_date) OVER (PARTITION BY user_pseudo_id), MONTH) AS first_month, DATE_TRUNC(event_date, MONTH) AS event_month, MIN(event_date) OVER (PARTITION BY user_pseudo_id) AS first_date, event_date FROM base ) ) -- Weekly Retention SELECT *, ROUND(SAFE_DIVIDE(user_cnt, first_cnt), 3) AS week_retention_rate FROM ( SELECT *, FIRST_VALUE(user_cnt) OVER (ORDER BY diff_of_week) AS first_cnt FROM ( SELECT diff_of_week, COUNT(DISTINCT user_pseudo_id) AS user_cnt FROM first_week_and_diff GROUP BY diff_of_week ) ) ORDER BY diff_of_week; -- 추가. Monthly Retention SELECT *, ROUND(SAFE_DIVIDE(user_cnt, first_cnt), 3) AS month_retention_rate FROM ( SELECT *, FIRST_VALUE(user_cnt) OVER (ORDER BY diff_of_month) AS first_cnt FROM ( SELECT diff_of_month, COUNT(DISTINCT user_pseudo_id) AS user_cnt FROM first_month_and_diff GROUP BY diff_of_month ) ) ORDER BY diff_of_month;
안녕하세요 카일스쿨님! 1-9 PIVOT문제를 풀어봤습니다. 먼저 PIVOT전에 제방식대로 푼코드를 드리겠습니다. 제가 WITH문을 자주 쓰지않고 그냥 한코드에 다쓰는 습관이 있어서 이런식으로 코드를 짜면 안좋은점이 있는지 피드백받고싶습니다. 또한 PIVOT문제를 풀긴 했는데 맞는 답인지 피드백받고싶습니다. -- PIVOT하기전에 제가 푼 것 -- 내가 쓴 답 -- SELECT -- event_date, -- concat(event_name,'-', param.value.string_value) AS event_name_with_screen, -- CASE -- WHEN (event_name='screen_view' AND param.value.string_value='welcome') THEN 1 -- WHEN (event_name='screen_view' AND param.value.string_value='home') THEN 2 -- WHEN (event_name='screen_view' AND param.value.string_value='food_category') THEN 3 -- WHEN (event_name='screen_view' AND param.value.string_value='restaurant') THEN 4 -- WHEN (event_name='screen_view' AND param.value.string_value='cart') THEN 5 -- WHEN (event_name = 'click_payment' AND param.value.string_value='home') THEN 6 -- ELSE 0 -- END AS step_number, -- COUNT(DISTINCT user_id) AS cnt -- FROM `expanded-idiom-446706-d4.advanced.app_logs` -- CROSS JOIN UNNEST(event_params) AS param -- WHERE -- (event_date BETWEEN "2022-08-01"AND '2022-08-18') AND key='firebase_screen' -- GROUP BY event_date,event_name,param.value.string_value,step_number -- HAVING step_number !=0 -- ORDER BY step_number ,event_date -- PIVOT문제 푼것 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 = '2022-08-01' # 적은 데이터로 쿼리를 작성하기 위해 만들어둔 조건 event_date BETWEEN '2022-08-01' AND '2022-08-18' GROUP BY ALL ) , fliter_event_and_concat_evnet_and_screen AS( -- screen과 event_name을 합치자(필요한 이벤트만 where 조건에걸어서 사용) 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, SUM(IF(event_name_with_screen='screen_view-welcome'),cnt,0) AS `screen_view-welcome`, SUM(IF(event_name_with_screen='screen_view-home'),cnt,0) AS `screen_view-home`, SUM(IF(event_name_with_screen=' screen_view-food_category'),cnt,0) AS `screen_view-food_category`, SUM(IF(event_name_with_screen='screen_view-restaurant'),cnt,0) AS `screen_view-restaurant`, SUM(IF(event_name_with_screen='screen_view-cart'),cnt,0) AS `screen_view-cart`, SUM(IF(event_name_with_screen='click_payment-cart'),cnt,0) AS `click_payment-cart` FROM fliter_event_and_concat_evnet_and_screen GROUP BY ALL ORDER BY event_date
리텐션 과제 제출합니다 ! 쿼리를 짜는 것보다 유저를 어떤 기준으로 나눌 것인지, 리텐션을 어떻게 해석할 것인지 등의 부분이 항상 어려운 것 같습니다 . . ㅠㅠ 최대한 배운 내용을 활용해서 인사이트를 도출해보고자 했습니다. 한번 봐주시면 감사하겠습니다 ㅎㅎ! https://bubble-supernova-783.notion.site/19defe88476380ef91abc06719a01ba9#1a4efe88476380e6bd84d4ac9602522d
안녕하세요 카일님 강의 마지막에 주신 연습문제 해보았습니다. PIVOT에 어떤 연산자를 사용할 것인가? MAX로 할 경우 -> 별도의 연산된 결과값이 있는 base`가 필요할 것 같음. 이 경우 강의 중 작성된 cnt를 참조하여 작성할 수 있을 것 같으나 3중 쿼리라는게 마음에 걸림. SUM으로 할 경우 -> 강의 중 작성한 base 만으로 충분할 것 같음. 2중 쿼리로 충분해보임. 따라서 B안으로 작성해보았습니다. WITH base AS ( SELECT event_date, event_timestamp, event_name, user_id, user_pseudo_id, platform, -- event_param, MAX(IF(event_param.key = 'firebase_screen',event_param.value.string_value, NULL)) AS firebase_screen, -- 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 = '2022-08-01' -> 적은 데이터로 쿼리를 작성하기 위해 입력한 임시 조건 event_date BETWEEN '2022-08-01' AND '2022-08-18' GROUP BY ALL -- LIMIT 100 -> 기초 구문 작성 완료 시 삭제 필요! ), filter_event_and_concat_event_and_screen AS ( -- name, screen concatate (필요한 이벤트만 WHERE 조건 걸어 사용) 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') ) # PIVOT 연습문제 SELECT event_date, SUM(IF(event_name_with_screen = 'screen_view-welcome',1,0)) AS `screen_view-welcome`, SUM(IF(event_name_with_screen = 'screen_view-home',1,0)) AS `screen_view-home`, SUM(IF(event_name_with_screen = 'screen_view-food_category',1,0)) AS `screen_view-food_category`, SUM(IF(event_name_with_screen = 'screen_view-restaurant',1,0)) AS `screen_view-restaurant`, SUM(IF(event_name_with_screen = 'screen_view-cart',1,0)) AS `screen_view-cart`, SUM(IF(event_name_with_screen = 'click_payment=cart',1,0)) AS `click_payment-cart`, FROM filter_event_and_concat_event_and_screen GROUP BY ALL ORDER BY event_date ASC
안녕하세요. Weekly retention을 구하고자 아래와 같이 쿼리를 작성했습니다. WITH base AS (SELECT DISTINCT user_id, event_name, DATETIME(TIMESTAMP_MICROS(event_timestamp),'Asia/Seoul') AS event_datetime, DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp),'Asia/Seoul')) AS event_date, user_pseudo_id FROM `advanced.app_logs` WHERE event_date BETWEEN "2022-08-01" AND "2022-11-03" ), first_week_and_diff AS ( SELECT *, DATE_DIFF(event_week, first_week, WEEK) AS diff_of_week FROM ( SELECT DISTINCT user_pseudo_id, DATE_TRUNC(MIN(event_date) OVER(PARTITION BY user_pseudo_id), WEEK(MONDAY)) AS first_week, DATE_TRUNC(event_date, WEEK(MONDAY)) AS event_week FROM base ) ), user_cnt AS ( SELECT diff_of_week, COUNT(DISTINCT user_pseudo_id) AS user_cnt FROM first_week_and_diff GROUP BY diff_of_week ) SELECT *, ROUND(SAFE_DIVIDE(user_cnt, first_week_user_cnt), 2) AS retention_rate FROM ( SELECT diff_of_week, user_cnt, FIRST_VALUE(user_cnt) OVER(ORDER BY diff_of_week ASC) AS first_week_user_cnt FROM user_cnt ); 이렇게 쿼리 작성을 하니 오류가 발생했고 user_cnt AS ( SELECT diff_of_week, COUNT(DISTINCT user_pseudo_id) AS user_cnt FROM first_week_and_diff GROUP BY diff_of_week ) 이 부분에서 테이블 이름을 user_counts로 수정하니 제대로 동작함을 확인했습니다. 왜 이름 때문에 오류가 발생하는 것인지 궁금합니다.
안녕하세요 카일님 1-5 학습 중 PIVOT 예제용 TABLE을 만들어 보려고 하였습니다. 결론부터 말씀드리면 TABLE 생성에 성공하였어요. 다만 궁금한 것은 제가 겪은 오류에 대한 해석이 올바른 것인지 확인 받고 싶어서 질문 드립니다. 1-4 강에 있던 array_exercise 생성 구문을 참고하여 아래와 같은 구문으로 실행하였더니 생성되지 않았습니다. 가장 안쪽의 select 부터 실행하면서 찾아보니 최하단의 UNION ALL이 원인이었더라구요. UNION ALL 은 아래에도 추가 ROW를 입력해야 할 때, 한 행으로 밀어넣는 명령어이므로, 마지막 행 다음에는 넣지 말아야 한다. 넣을 경우 그 뒤에 추가적인 데이터의 입력을 요구하나, 데이터가 없으므로 실행이 되지 않는 것이다. 라고 이해했는데, 맞게 이해했을까요? CREATE OR REPLACE TABLE `advanced.grade` AS SELECT student, grades FROM ( SELECT 'A' AS student, ARRAY<STRUCT<subject STRING, score INT64>>[ STRUCT('수학', 80), STRUCT('영어', 90), STRUCT('과학', 85) ] AS grades UNION ALL SELECT 'B', ARRAY<STRUCT<subject STRING, score INT64>>[ STRUCT('수학', 75), STRUCT('영어', 95), STRUCT('과학', 80) ] UNION ALL )
안녕하세요, 빅쿼리 활용편 강의 수강 중입니다. 디스코드 접속 시 초대 수락이 불가해서 확인 부탁드리겠습니다. 어떤 강의를 들으면서 발생했나요? 빅쿼리 활용편 어떤 문제가 생겼나요? 첨부파일 내 링크를 통해 접속 후 초대 수락하기 클릭 시 불가하다는 메세지가 나옵니다 환경이 어떻게 되나요? 맥 m1 사용중입니다
