1)WeeklyRetention을구하는쿼리를바닥부터스스로작성해보세요WITH event_log_base AS ( SELECT DISTINCT DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime, DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date, DATE_TRUNC(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul'), WEEK(MONDAY)) AS event_week, DATE_TRUNC(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul'), MONTH) AS event_month, user_id, user_pseudo_id, event_name, platform, event_params FROM advanced.app_logs ), user_first_visit AS ( SELECT user_pseudo_id, MIN(event_week) OVER (PARTITION BY user_pseudo_id) AS first_visit_week, event_week FROM event_log_base ), weekly_retention_base AS ( SELECT first_visit_week, DATE_DIFF(event_week, first_visit_week, WEEK) AS week_diff, COUNT(DISTINCT user_pseudo_id) AS active_users FROM user_first_visit GROUP BY first_visit_week, week_diff ) SELECT first_visit_week, week_diff, active_users, SAFE_DIVIDE(active_users, FIRST_VALUE(active_users) OVER (PARTITION BY first_visit_week ORDER BY week_diff ASC)) AS retention_rate FROM weekly_retention_base ORDER BY first_visit_week, week_diff; 2)RetainUser를New+Current+Resurrected+DormantUser로나누는쿼리를 작성해보세요.신규 사용자: 첫 방문 후 30일 이내에 활동한 사용자. 첫 방문 날짜와 마지막 방문 날짜 간의 차이가 30일 이하일 경우 현재 사용자: 첫 방문 후 30일이 지난 사용자 중, 최근 30일 이내에 접속한 사용자. 첫 방문과 마지막 방문 간 차이가 30일을 넘고, 가장 최근 활동으로부터 30일 이내에 접속한 경우 휴면 사용자: 마지막 방문 이후 30일 이상 비활동 상태에 있는 사용자. 마지막 방문이 가장 최근 이벤트로부터 30일 이상 경과한 경우 복귀 사용자: 30일 이상 비활동 상태였다가 다시 활동을 시작한 사용자. 이전 방문 이후 30일 동안 비활동이 이어지다 다시 방문한 경우 WITH event_data AS ( SELECT DISTINCT DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date, user_pseudo_id FROM advanced.app_logs ), -- 사용자별 첫 방문 및 마지막 방문 날짜 계산 user_first_last_visit AS ( SELECT user_pseudo_id, MIN(event_date) AS first_visit_date, MAX(event_date) AS last_visit_date FROM event_data GROUP BY user_pseudo_id ), -- 전체 이벤트 데이터에서 가장 최근의 이벤트 날짜를 계산 latest_event_date AS ( SELECT MAX(event_date) AS current_date FROM event_data ), -- 사용자의 이벤트 로그를 통해 비활동 기간을 기록하여 휴면 여부 판단 user_activity AS ( SELECT user_pseudo_id, event_date, LEAD(event_date) OVER (PARTITION BY user_pseudo_id ORDER BY event_date) AS next_event_date, CASE WHEN DATE_DIFF(LEAD(event_date) OVER (PARTITION BY user_pseudo_id ORDER BY event_date), event_date, DAY) > 30 THEN 1 ELSE 0 END AS dormant_history FROM event_data ), -- 사용자 유형 분류: 신규, 현재, 휴면, 복귀 사용자 user_category AS ( SELECT u.user_pseudo_id, u.first_visit_date, u.last_visit_date, led.current_date, MAX(ua.dormant_history) AS dormant_history, CASE WHEN DATE_DIFF(led.current_date, u.last_visit_date, DAY) > 30 THEN 'inactive_user' WHEN DATE_DIFF(u.last_visit_date, u.first_visit_date, DAY) <= 30 THEN 'first_month_user' WHEN MAX(ua.dormant_history) = 1 THEN 'returning_user' ELSE 'active_user' END AS user_type FROM user_first_last_visit AS u CROSS JOIN latest_event_date AS led LEFT JOIN user_activity AS ua ON u.user_pseudo_id = ua.user_pseudo_id GROUP BY u.user_pseudo_id, u.first_visit_date, u.last_visit_date, led.current_date ), -- 사용자 유형별 첫 방문 주차와 현재 주차 간 차이 계산 user_weekly_retention AS ( SELECT uc.user_type, ed.user_pseudo_id, ed.event_date, DATE_DIFF(DATE_TRUNC(ed.event_date, WEEK(MONDAY)), DATE_TRUNC(uc.first_visit_date, WEEK(MONDAY)), WEEK) AS week_difference FROM event_data AS ed JOIN user_category AS uc ON ed.user_pseudo_id = uc.user_pseudo_id ), -- 사용자 유형과 주차별 활성 사용자 수 집계 user_count_by_type_and_week AS ( SELECT user_type, week_difference, COUNT(DISTINCT user_pseudo_id) AS user_count FROM user_weekly_retention GROUP BY user_type, week_difference ), -- 유지율 계산을 위한 초기 사용자 수와 현재 주차별 유지율 계산 retention_calculation AS ( SELECT user_type, week_difference, user_count, FIRST_VALUE(user_count) OVER (PARTITION BY user_type ORDER BY week_difference) AS initial_user_count FROM user_count_by_type_and_week ) SELECT user_type, week_difference, ROUND(SAFE_DIVIDE(user_count, initial_user_count), 2) AS retention_rate FROM retention_calculation ORDER BY user_type, week_difference; 3)주어진데이터에서어떤사람들이리텐션이그나마높을까요?찾아보세요2번을 참고해서 고객을 4개의 타입으로 분류하였을 때first_month_user: 첫 주 이후 리텐션이 높지만 이후 감소하는 모습을 보인다. active_user: first_month_user와 비슷하게 첫 주 이후 빠르게 감소한다. returning_user: 2주부터 0.08로 떨어지지만 이후 완만하게 리텐션이 감소한다.오랜기간 10주차 이후에도 일부 사용자가 유지되고 있는 모습을 보인다. inactive_user: 리텐션이 다른 유저에 비해 높다고는 할 수 없지만 20주차까지 꾸준하게 소수의사용자가 유지되고 있는 모습을 보인다. --> 따라서 returning_use이 가장 완만하게 리텐션이 감소, 가장 오랫동안 리텐션이 지속했기때문에 가장 높은 리텐션을 유지했다고 판단하였습니다.  4)CoreEvent를“click_payment”라고설정하고WeeklyRetention을구해주세요WITH event_data AS ( SELECT DISTINCT user_pseudo_id, DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date, DATE_TRUNC(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul'), WEEK(MONDAY)) AS event_week, event_name FROM advanced.app_logs WHERE event_name = 'click_payment' -- Core event 필터링 ), -- 사용자별 첫 방문 주차와 현재 이벤트 주차 간의 차이 계산 user_first_week_data AS ( SELECT user_pseudo_id, MIN(event_week) OVER (PARTITION BY user_pseudo_id) AS first_visit_week, event_week AS current_week, DATE_DIFF(event_week, MIN(event_week) OVER (PARTITION BY user_pseudo_id), WEEK) AS week_difference FROM event_data ), -- 주차별 사용자 수 집계 user_count_by_week AS ( SELECT first_visit_week, week_difference, COUNT(DISTINCT user_pseudo_id) AS user_count FROM user_first_week_data GROUP BY first_visit_week, week_difference ORDER BY first_visit_week, week_difference ), -- 유지율 계산을 위한 초기 사용자 수와 주차별 유지율 계산 retention_base AS ( SELECT first_visit_week, week_difference, user_count, FIRST_VALUE(user_count) OVER (PARTITION BY first_visit_week ORDER BY week_difference) AS initial_user_count FROM user_count_by_week ) -- 최종 리텐션 비율 계산 및 결과 출력 SELECT first_visit_week, week_difference, ROUND(SAFE_DIVIDE(user_count, initial_user_count), 2) AS retention_rate FROM retention_base ORDER BY first_visit_week, week_difference;

안녕하세요 array_agg 처리해서 여러 값이 묶인 배열 형태로된 컬럼에 있는 값을다시 꺼내는 방법이 있을까요?

MariaDB 사용하고 있고 버전은 아래와 같은데 회사 DB로 버전 변경은 불가능한 상태입니다. 버전 : 10.4.12-6-MariaDB-enterprise-log EXPLAIN ANALYZE 사용 시 에러가 발생하여 구글링 해보니 MariaDB 에서는 사용이 불가능한 것 같더라고요.Mysql 에서 EXPLAIN ANALYZE 로 조회되는 내용을 동일하거나 유사하게 볼 수 있는 방법이 없을까요?

다중행쿼리 SQLD에서WHERE sal > ANY(SELECT min(sal) FROM emp WHERE job='SALESMAN');WHERE sal > (SELECT min(sal) FROM emp WHERE job='SALESMAN');이 두 개의 구문을 실행하였을 때값은 같은데 정렬이 되고 안되고 차이가 있는것 같은데요그러면 디폴트 값이 ANY, ALL사용시ASC로 잡혀있는게 맞을까요. ASC로 잡혀있는게 맞다면이 구문에 대해서 ORDER BY절을 ASC가 필요한 경우굳이 적지 않아도 되나요?WHERE sal > ANY(SELECT min(sal) FROM emp WHERE job='SALESMAN'); 3. ASC로 잡혀 있는경우) 가독성 고려..?위와 더불어 아니면 가독성을 위해서 ORDER BY ASC를 기입하는게 더 좋나요.WHERE sal > ANY(SELECT min(sal) FROM emp WHERE job='SALESMAN'); ORDER BY DESC;

1) 리텐션 연습 문제https://torch-cart-08d.notion.site/3-139701964d8c805d9e49f3c68ab9b0b5 2) 3주차 추가 문제1번 문제(15분)주차별 각 카테고리별 평균 할인율이 가장 높았던 기간과 할인율을 구하는 쿼리를 작성해주세요단, 날짜 데이터를 YYYY-MM-DD 23:59:39 이런 형태로 변경해주세요SELECT *, RANK() OVER(PARTITION BY category ORDER BY rate DESC) AS category_rank FROM ( SELECT category, FORMAT_DATETIME('%Y-%m-%d 23:59:39', PARSE_DATETIME('%m/%d/%Y %H:%M:%S', transaction_date)) AS transaction_date, actual_price, list_price, ((list_price - actual_price) / list_price) * 100 AS rate FROM transaction_data AS td LEFT JOIN item_info ii ON td.item_id = ii.item_id ) QUALIFY category_rank = 1 ORDER BY category, transaction_date 2번 문제(10분)2024년 1월에 가장 많은 매출을 기록한 카테고리를 구하는 쿼리를 작성해주세요SELECT category, SUM(actual_price) AS total_price FROM ( SELECT category, FORMAT_DATETIME('%Y-%m-%d 23:59:39', PARSE_DATETIME('%m/%d/%Y %H:%M:%S', transaction_date)) AS transaction_date, actual_price, FROM transaction_data AS td LEFT JOIN item_info ii ON td.item_id = ii.item_id WHERE EXTRACT(MONTH FROM PARSE_DATETIME('%m/%d/%Y %H:%M:%S', transaction_date)) = 1 ) GROUP BY ALL ORDER BY total_price DESC LIMIT 1 3번 문제(10분)유저별 총 구매 금액이 200만원 이상인 유저들이 가장 많이 구매한 카테고리를 찾는 쿼리를 작성해주세요over_200 AS ( SELECT td.user_id, SUM(actual_price) AS user_price FROM transaction_data AS td LEFT JOIN user_info AS ui ON td.user_id = ui.user_id GROUP BY ALL HAVING user_price >= 2000000 ) SELECT category, COUNT(*) AS cnt FROM over_200 AS O2 CROSS JOIN transaction_data AS td LEFT JOIN item_info AS ii ON td.item_id = ii.item_id GROUP BY ALL ORDER BY cnt DESC LIMIT 1약 20분/5분/8분 소요1번 문제에서 날짜 데이터를 변환하는 방법이 떠오르지 않아 시간이 많이 소요됐습니다.

# 1) Weekly Retention을 구하는 쿼리를 바닥부터 스스로 작성해보세요 WITH base AS ( SELECT DISTINCT user_id, event_name, DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime, user_pseudo_id FROM advanced.app_logs WHERE event_date BETWEEN '2022-08-01' AND '2023-08-03' ), first_diff_of_week 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_diff_of_week GROUP BY diff_of_week ) SELECT diff_of_week, user_cnt, ROUND(SAFE_DIVIDE(user_cnt, first_cnt), 3) AS retention_rate FROM ( SELECT *, FIRST_VALUE(user_cnt) OVER(ORDER BY diff_of_week) AS first_cnt FROM user_counts ) ORDER BY diff_of_week# 2) Retain User를 New + Current + Resurrected + Dormant User로 나누는 쿼리를 작성해보세요. # 신규유저(New) : 제품을 처음 사용하는 유저 # 기존유저(Current) : 제품을 지속적으로 사용하는 유저 # 복귀유저(Resurrected) : 과거에 사용 -> 비활성 -> 다시 제품을 사용한 유저 # 휴면유저(Dormant) : 일정 기간 제품을 사용하지 않은 비활성화 사용자 WITH base AS ( SELECT DISTINCT user_id, DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date, DATE_TRUNC(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul'), WEEK(MONDAY)) AS event_week, user_pseudo_id FROM advanced.app_logs WHERE event_date BETWEEN '2022-08-01' AND '2023-08-03' ), user_activity AS ( SELECT user_pseudo_id, event_week, LAG(event_week, 1) OVER(PARTITION BY user_pseudo_id ORDER BY event_week) AS prev_week, MIN(event_week) OVER(PARTITION BY user_pseudo_id) AS first_week FROM base ), user_classification AS ( SELECT user_pseudo_id, event_week, CASE WHEN event_week = first_week THEN 'New' WHEN prev_week IS NOT NULL AND DATE_DIFF(event_week, prev_week, WEEK) = 1 THEN 'Current' WHEN prev_week IS NOT NULL AND DATE_DIFF(event_week, prev_week, WEEK) > 1 THEN 'Resurrected' ELSE 'Dormant' END AS user_type FROM user_activity ) SELECT event_week, user_type, COUNT(DISTINCT user_pseudo_id) AS user_count FROM user_classification GROUP BY ALL ORDER BY event_week, user_type# 3) 주어진 데이터에서 어떤 사람들이 리텐션이 그나마 높을까요? WITH base AS ( SELECT DISTINCT user_id, DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date, DATE_TRUNC(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul'), WEEK(MONDAY)) AS event_week, user_pseudo_id FROM advanced.app_logs WHERE event_date BETWEEN '2022-08-01' AND '2023-08-03' ), user_activity AS ( SELECT user_pseudo_id, event_week, LAG(event_week, 1) OVER(PARTITION BY user_pseudo_id ORDER BY event_week) AS prev_week, MIN(event_week) OVER(PARTITION BY user_pseudo_id) AS first_week FROM base ), user_classification AS ( SELECT user_pseudo_id, event_week, CASE WHEN event_week = first_week THEN 'New' WHEN prev_week IS NOT NULL AND DATE_DIFF(event_week, prev_week, WEEK) = 1 THEN 'Current' WHEN prev_week IS NOT NULL AND DATE_DIFF(event_week, prev_week, WEEK) > 1 THEN 'Resurrected' ELSE 'Dormant' END AS user_type FROM user_activity ), user_counts AS ( SELECT event_week, user_type, COUNT(DISTINCT user_pseudo_id) AS user_count FROM user_classification GROUP BY ALL ORDER BY event_week, user_type ) SELECT event_week, DATE_DIFF(event_week, first_week, WEEK) AS weeks_after_first_week, user_type, user_count, cohort_users, ROUND(SAFE_DIVIDE(user_count, cohort_users), 3) AS retention_rate FROM ( SELECT DATE(event_week) AS event_week, DATE(MIN(event_week) OVER()) AS first_week, user_type, user_count, MAX(CASE WHEN user_type = 'New' THEN user_count END) OVER(PARTITION BY DATE(event_week)) AS cohort_users FROM user_counts ORDER BY event_week, CASE user_type WHEN 'New' THEN 1 WHEN 'Current' THEN 2 WHEN 'Resurrected' THEN 3 WHEN 'Dormant' THEN 4 END )2022-08-01 ~ 2023-08-03 기간으로 설정하여, 개인적인 의견으로 Retain User를 쪼개 보았습니다.배달어플은 유저들의 사용 간격이 짧다고 생각하기 때문에, 주의 주기로 리텐션으로 설정했습니다. 저는바로 전 주에 사용했고, 그 다음주에 사용했다면 기존유저(Current)로 설정했습니다.2주 이상 텀을 두고 다시 사용했다면 복귀유저(Resurrected)로 설정했습니다.처음 들어가고 사용하지 않았다면 휴면유저(Dormant)로 설정했습니다.주차마다 '신규/기존/복귀/휴면' 유저를 새로 계산하는 쿼리입니다. 데이터를 살펴보면,1) 신규유저(New) : 제품을 처음 사용하는 유저출시(2022-08-01을 출시기간으로 생각하면)로부터 현재(2023-01-16 => 0803으로 설정했지만 0116까지가 끝입니다.)까지의 신규유저 수의 변화를 살펴보겠습니다.런칭부터 10주까지의 신규유저의 수는 주춤한 경우도 있지만 평균 4~500명 정도 증가했습니다.하지만, 11주에 4000명 대에서 3100명으로 900명이 급감한 이후로 계속 하락세를 타고 24주차 현재 신규 가입자의 수는 500명 남짓하고 있습니다.11주차부터의 마케팅 효과는 빛을 보지 못하고 있는 것 같습니다. 코호트 리텐션 시각화를 보자면,2) 기존유저(Current) : 제품을 지속적으로 사용하는 유저(바로 전 주와 이번 주)출시로부터 현재까지 줄곧 주차마다의 신규유저 대비 기존유저의 비율이 증가하고 있습니다.특히, 24주차에 가입한 신규유저 대비 기존유저의 비율은 76%나 차지하고 있습니다.시간이 흐름에 따라 점차 어플이 안정화되면서 신규유저들이 만족을 느끼는 비율이 늘어가고 있다고 보입니다. 3) 복귀유저(Resurrected) : 과거에 사용 -> 비활성화 -> 다시 제품을 사용한 유저(2주 후 복귀)복귀유저는 12주차를 분기로 13주차부터는 신규가입자보다 복귀유저의 수가 많아지기 시작했습니다.24주차에는 신규유저 대비 복귀유저는 6배가 많습니다.복귀유저가 기존유저로 안정되는 현황을 추출할 수 있는 쿼리는 조금 더 연구해 보겠습니다. 4) 휴면유저(Dormant) : 일정기간 제품을 사용하지 않은 비활성화 사용자신규유저 대비 휴면유저는 꾸준히 늘어가고 있습니다(휴면유저가 누적됨).24주차 신규가입자의 5.7배가 휴면유저 입니다.# 4) Core Event를 'click_payment'라고 설정하고 Weekly Retention을 구해주세요 WITH base AS ( SELECT DISTINCT user_id, DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime, user_pseudo_id FROM advanced.app_logs WHERE event_date BETWEEN '2022-08-01' AND '2023-08-03' AND event_name = 'click_payment' ), 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 diff_of_week, user_cnt, ROUND(SAFE_DIVIDE(user_cnt, cohort_users), 3) AS retention_rate FROM ( SELECT *, FIRST_VALUE(user_cnt) OVER(ORDER BY diff_of_week) AS cohort_users FROM user_counts ) ORDER BY diff_of_week    

설치 중 이 단계에서 계속 에러가 납니다구글링 해보니까 pc명이 한글이라서 그렇다는데제 pc명은 영어거든요......그외의 블로그나 구글에서 할 수 있는 모든 조치를 했지만..안되네요이제 포맷 하나 남았는데..포맷까진 가고 싶지 않아요 너무 귀찮습니다..도와주세요.. 

첫번째 col2 + col3은 내부덧셈에 null값이 있어도 결과값이 정수로 나왔는데 왜 컬럼끼리의 덧셈은 null값이있으면 전부 null로 나오는지 궁금합니다.

안녕하세요 카일스쿨님:)강의 마지막에 선택과제(?)로 주신 집계데이터 pivot쿼리 작성해봤습니다.쿼리를 작성하면서, 지금은 피봇할 컬럼이 많지 않아서 하나하나 피봇해주는 것으로 진행했는데 이게 최선일지가 의문이 들어서요,피봇하는 컬럼 조건이 다를 뿐 과정은 동일해서 공통으로 적용할 수 있는 방법이 있을 것 같은데 제가 잘못 이해하고 있는 것인지, 아니면 피봇을 항상 이런식으로 진행해야 하는 것인지도 같이 질문드립니다.감사합니다:) with base as ( 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.int_value,null)) as `food_id` ,max(if(param.key = 'session_id',param.value.string_value,null)) as `session_id` from advanced.app_logs , unnest(event_params) as param where 1=1 and event_name in ('screen_view','click_payment') and event_date >= '2022-08-01' and event_date <= '2022-08-18' group by all order by 1 ) , before_pivot as ( select event_date ,concat(event_name,'-',firebase_screen) as `event_name_with_screen` ,case when event_name = 'screen_view' and firebase_screen = 'welcome' then 1 when event_name = 'screen_view' and firebase_screen = 'home' then 2 when event_name = 'screen_view' and firebase_screen = 'food_category' then 3 when event_name = 'screen_view' and firebase_screen = 'restaurant' then 4 when event_name = 'screen_view' and firebase_screen = 'cart' then 5 when event_name = 'click_payment' and firebase_screen = 'cart' then 6 end as `step_number` ,count(*) as cnt from base where 1=1 group by 1,2,3 having step_number is not null ) 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 before_pivot group by all order by 1

퀴즈 풀고 있는데 강의를 들어도 왜 여기서 오류가 나는지 모르겠어요 ㅜㅜ

 데이터 베이스 접속시 무한 로딩이 됩니다. 되어서 다시 만들어보니 접속이 잘 되지 않습니다. 이유가 있을까요?(다른 데이터베이스는 접속이 잘됩니다.)로컬만 안붙는것 같은데 이유를 잘 모르겠네요

노션 링크로 업로드 합니다!https://qkffpsxkdlwm.notion.site/3-13a35e3a8bef8019b7a6d9c6abc72193?pvs=4

노션에서 작성했습니다! https://salt-baron-5c5.notion.site/3-13aa734e64b880a1a9a1e84a92fa3593

Notion 링크로 대체합니다.https://colney4844.notion.site/3-13a59b98d5db80438b7fcef0146771f6?pvs=4

1. Weekly RetentionWITH base AS ( SELECT DISTINCT DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime, DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date, DATE_TRUNC(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul'), WEEK(MONDAY)) AS event_week, DATE_TRUNC(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul'), MONTH) AS event_month, user_id, user_pseudo_id, event_name, platform, event_params FROM advanced.app_logs ), user_visit_base AS ( SELECT user_pseudo_id, MIN(event_week) OVER (PARTITION BY user_pseudo_id) AS first_visit_week, event_week AS visit_week FROM base ), user_visit_weekdiff AS ( SELECT first_visit_week, DATE_DIFF(visit_week, first_visit_week, week) AS week_diff, COUNT(DISTINCT user_pseudo_id) AS visit_users FROM user_visit_base GROUP BY ALL ) SELECT first_visit_week, week_diff, visit_users, SAFE_DIVIDE(visit_users, FIRST_VALUE(visit_users) OVER (PARTITION BY first_visit_week ORDER BY week_diff ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) AS retention FROM user_visit_weekdiff 2. Retain User → New User, Current User, Resurrected User, Dormant User-- 한 달(30일)을 기준으로 구분하였다. -- 신규 유저: 최근 한 달 이내 새로 방문 -- 기존 유저: 최근 한 달 이내 재방문, 그 이전 한달에도 방문 -- 복귀 유저: 최근 한 달 이내 재방문, 그 이전 한달에는 방문 X -- 휴먼 유저: 최근 한 달 이내 재방문 X WITH base AS ( SELECT DISTINCT DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date, user_pseudo_id FROM advanced.app_logs ), user_first_last AS ( SELECT user_pseudo_id, MIN(event_date) AS first_date, MAX(event_date) AS last_date FROM base GROUP BY user_pseudo_id ), last_event_date AS ( SELECT MAX(event_date) AS current_date FROM base ), user_activity AS ( SELECT user_pseudo_id, event_date, LEAD(event_date) OVER (PARTITION BY user_pseudo_id ORDER BY event_date) AS next_event_date, CASE WHEN DATE_DIFF(LEAD(event_date) OVER (PARTITION BY user_pseudo_id ORDER BY event_date), event_date, DAY) > 30 THEN 1 ELSE 0 END AS dormant_history FROM base ), user_types AS ( SELECT u.user_pseudo_id, u.first_date, u.last_date, led.current_date, MAX(us.dormant_history) AS dormant_history, CASE WHEN DATE_DIFF(led.current_date, u.last_date, DAY) > 30 THEN 'dormant_user' WHEN DATE_DIFF(u.last_date, u.first_date, DAY) <= 30 THEN 'new_user' WHEN MAX(us.dormant_history) = 1 THEN 'resurrected_user' ELSE 'current_user' END AS user_type FROM user_first_last AS u CROSS JOIN last_event_date AS led LEFT JOIN user_activity AS us ON u.user_pseudo_id = us.user_pseudo_id GROUP BY u.user_pseudo_id, u.first_date, u.last_date, led.current_date ), first_week_and_diff AS ( SELECT ut.user_type, fw.user_pseudo_id, fw.event_date, DATE_DIFF(DATE_TRUNC(fw.event_date, WEEK(MONDAY)), DATE_TRUNC(ut.first_date, WEEK(MONDAY)), WEEK) AS diff_of_week FROM base AS fw JOIN user_types AS ut ON fw.user_pseudo_id = ut.user_pseudo_id ), user_cnt_by_type_and_week AS ( SELECT user_type, diff_of_week, COUNT(DISTINCT user_pseudo_id) AS user_cnt FROM first_week_and_diff GROUP BY user_type, diff_of_week ), retention_base AS ( SELECT user_type, diff_of_week, user_cnt, FIRST_VALUE(user_cnt) OVER (PARTITION BY user_type ORDER BY diff_of_week) AS first_user_cnt FROM user_cnt_by_type_and_week ) SELECT user_type, diff_of_week, ROUND(SAFE_DIVIDE(user_cnt, first_user_cnt), 2) AS retention_rate FROM retention_base ORDER BY user_type, diff_of_week 3. retention이 높은 그룹?current user: 최고 0.41 (4~5주차)new user: 최고 0.11 (1,3주차)resurrected user: 최고 0.14 (9~12주차)dormant user: 최고 0.09 (3주차)리텐션이 그나마 높은 그룹: current usercurrent user, resurrected user 리텐션 분포의 경우 상승세 → 하강세 경향성을 보여 피크를 찍을 때 즈음 어떤 이벤트가 있었다고도 추측할 수 있다. 4. click_payment 이벤트를 중점으로 본 Weekly Retention?WITH base AS ( SELECT DISTINCT DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime, event_name, user_id, user_pseudo_id FROM advanced.app_logs WHERE 1=1 AND event_name = "click_payment" ), event_week_and_first_week AS ( SELECT DISTINCT DATE_TRUNC(event_date, WEEK(MONDAY)) AS event_week, user_pseudo_id, DATE_TRUNC(MIN(event_date) OVER(PARTITION BY user_pseudo_id ORDER BY event_date), WEEK(MONDAY)) AS first_week FROM base ), retention_base AS ( SELECT *, FIRST_VALUE(user_cnt) OVER(ORDER BY day_of_week) AS total_user FROM ( SELECT DATE_DIFF(event_week, first_week, WEEK) AS day_of_week, COUNT(DISTINCT user_pseudo_id) AS user_cnt FROM event_week_and_first_week GROUP BY day_of_week ) ) SELECT day_of_week, ROUND(SAFE_DIVIDE(user_cnt, total_user), 2) AS retention FROM retention_base ORDER BY day_of_week  

Notion으로 작성하여 링크를 첨부합니다.https://polite-vinyl-a61.notion.site/3-13a4994b207d804f969dc98e5bce9794

과제. 직접 retention 구해보기(주별 weekly)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_date, first_date, DAY) AS diff_of_day, 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, event_date, DATE_TRUNC(event_date, WEEK(MONDAY)) AS event_week FROM base ) ), user_cnt_and_diff AS( SELECT diff_of_week, COUNT(DISTINCT user_pseudo_id) AS user_cnt FROM first_week_and_diff GROUP BY ALL ORDER BY diff_of_week ), retain_base AS ( SELECT *, FIRST_VALUE(user_cnt) OVER (ORDER BY diff_of_week) AS first_user_cnt FROM user_cnt_and_diff ) SELECT diff_of_week, ROUND(SAFE_DIVIDE(user_cnt, first_user_cnt), 2) AS retention FROM retain_base과제. 월별 리텐션 쿼리 작성해보기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_date, first_date, DAY) AS diff_of_day, -- DATE_DIFF(event_week, first_week, WEEK) AS diff_of_week 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, event_date, DATE_TRUNC(event_date, MONTH) AS event_month FROM base ) ), user_cnt_and_diff AS( SELECT diff_of_month, COUNT(DISTINCT user_pseudo_id) AS user_cnt FROM first_month_and_diff GROUP BY ALL ORDER BY diff_of_month ), retain_base AS ( SELECT *, FIRST_VALUE(user_cnt) OVER (ORDER BY diff_of_month) AS first_user_cnt FROM user_cnt_and_diff ) SELECT diff_of_month, user_cnt SAFE_DIVIDE(user_cnt, first_user_cnt) AS retention FROM retain_base 리텐션 연습문제Weekly Retention을 구하는 쿼리를 바닥부터 스스로 작성해보세요. # 답 -- Weekly Retention을 구하자! (Retention이란, 시간이 흐르면서 다시 제품을 사용하는지 측정하는 지표) -- 첫 방문 후 매주 재방문 비율(weekly retention)을 계산하자! WITH base AS ( SELECT DISTINCT ###### DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS 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 BETWEEN "2022-08-01" AND "2023-08-31" ), event_week_and_first_week AS ( SELECT DISTINCT DATE_TRUNC(event_date, WEEK(MONDAY)) AS event_week, user_pseudo_id, DATE_TRUNC(MIN(event_date) OVER(PARTITION BY user_pseudo_id ORDER BY event_date), WEEK(MONDAY)) AS first_week FROM base ), retention_base AS ( SELECT *, FIRST_VALUE(user_cnt) OVER(ORDER BY day_of_week) AS total_user FROM( SELECT DATE_DIFF(event_week, first_week, WEEK) AS day_of_week, COUNT(DISTINCT user_pseudo_id) AS user_cnt FROM event_week_and_first_week GROUP BY ALL ) -- ORDER BY day_of_week ) SELECT day_of_week, SAFE_DIVIDE(user_cnt, total_user) AS retention FROM retention_base Retain User를 New + Current + Resurrected + Dormant User로 나누는 쿼리를 작성해보세요# 답 -- 신규 유저: 첫 활동일 기준 7일 이내 -- 기존 유저: 첫 활동일 기준 7일 이후, 마지막 활동으로부터 90일 이내 -- 휴면 유저: 마지막 활동일 이후 90일 이상 경과 -- 복귀 유저: 90일 이상 비활성화 이후 다시 돌아온 유저 WITH base AS ( SELECT DISTINCT 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 "2023-08-31" ),user_first_last AS ( SELECT user_pseudo_id, MIN(event_date) AS first_date, #OVER(PARTITION BY user_pseudo_id)를 넣으면 윈도우함수사용과 GROUPBY졸 사용 비교했을 때처럼 중복행이 생긴다 MAX(event_date) AS last_date FROM base GROUP BY user_pseudo_id ), last_event_date AS ( -- 원래라면 CURRENT_DATE()로 해야하지만, 앱로그 데이터의 마지막 날로 설정. SELECT MAX(event_date) AS current_date FROM base ), user_activity AS ( -- 90일 이상 제품을 사용하지 않았다면 1, 아니면 0 SELECT user_pseudo_id, event_date, LEAD(event_date) OVER (PARTITION BY user_pseudo_id ORDER BY event_date) AS next_event_date, CASE WHEN DATE_DIFF(LEAD(event_date) OVER (PARTITION BY user_pseudo_id ORDER BY event_date), event_date, DAY) > 90 THEN 1 ELSE 0 END AS dormant_history FROM base ) -- user_types AS ( SELECT DISTINCT u.user_pseudo_id, u.first_date, u.last_date, led.current_date, us.dormant_history, CASE WHEN DATE_DIFF(led.current_date, u.last_date, DAY) > 90 THEN 'dormant_user' WHEN DATE_DIFF(u.last_date, u.first_date, DAY) <= 7 THEN 'new_user' WHEN dormant_history = 1 THEN 'resurrected_user' ELSE 'current_user' END AS user_type FROM user_first_last AS u CROSS JOIN last_event_date AS led LEFT JOIN user_activity AS us ON u.user_pseudo_id = us.user_pseudo_id주어진 데이터에서 어떤 사람들이 리텐션이 그나마 높을까요? 찾아보세요WITH base AS ( SELECT DISTINCT DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date, user_pseudo_id FROM advanced.app_logs ), user_first_last AS ( SELECT user_pseudo_id, MIN(event_date) AS first_date, MAX(event_date) AS last_date FROM base GROUP BY user_pseudo_id ), last_event_date AS ( SELECT MAX(event_date) AS current_date FROM base ), user_activity AS ( SELECT user_pseudo_id, event_date, LEAD(event_date) OVER (PARTITION BY user_pseudo_id ORDER BY event_date) AS next_event_date, CASE WHEN DATE_DIFF(LEAD(event_date) OVER (PARTITION BY user_pseudo_id ORDER BY event_date), event_date, DAY) > 90 THEN 1 ELSE 0 END AS dormant_history FROM base ), user_types AS ( SELECT u.user_pseudo_id, u.first_date, u.last_date, led.current_date, MAX(us.dormant_history) AS dormant_history, CASE WHEN DATE_DIFF(led.current_date, u.last_date, DAY) > 90 THEN 'dormant_user' WHEN DATE_DIFF(u.last_date, u.first_date, DAY) <= 7 THEN 'new_user' WHEN MAX(us.dormant_history) = 1 THEN 'resurrected_user' ELSE 'current_user' END AS user_type FROM user_first_last AS u CROSS JOIN last_event_date AS led LEFT JOIN user_activity AS us ON u.user_pseudo_id = us.user_pseudo_id GROUP BY u.user_pseudo_id, u.first_date, u.last_date, led.current_date ), first_week_and_diff AS ( SELECT ut.user_type, fw.user_pseudo_id, fw.event_date, DATE_DIFF(DATE_TRUNC(fw.event_date, WEEK(MONDAY)), DATE_TRUNC(ut.first_date, WEEK(MONDAY)), WEEK) AS diff_of_week FROM base AS fw JOIN user_types AS ut ON fw.user_pseudo_id = ut.user_pseudo_id ), user_cnt_by_type_and_week AS ( SELECT user_type, diff_of_week, COUNT(DISTINCT user_pseudo_id) AS user_cnt FROM first_week_and_diff GROUP BY user_type, diff_of_week ), retention_base AS ( SELECT user_type, diff_of_week, user_cnt, FIRST_VALUE(user_cnt) OVER (PARTITION BY user_type ORDER BY diff_of_week) AS first_user_cnt FROM user_cnt_by_type_and_week ) SELECT user_type, diff_of_week, ROUND(SAFE_DIVIDE(user_cnt, first_user_cnt), 2) AS retention_rate FROM retention_base ORDER BY user_type, diff_of_week current user의 1주차 리텐션은 0.11부터 시작해서 5~6주차에는 0.15까지 오릅니다.new user의 경우 1주차까지 밖에 리텐션을 구하지 못하고, 0.06입니다.resurrected user의 경우 1주차 리텐션은 0.04부터 시작해서 7주차에는 0으로 빠르게 감소합니다.dormant user의 경우 휴면고객의 리텐션을 구하는 것이 의미가 있는지 잘 모르겠지만, 1주차 리텐션은 0.05부터 시작해서 11주차까지 0으로 감소합니다.리텐션이 그나마 높은 유저는 current user이고, 리텐션이 주마다 서서히 증가하거나 감소하는데 2주차에 오른 것으로 보아 기능 배포나 이벤트가 있지 않았을까 예상해봅니다.Core Event를 “click_payment”라고 설정하고 Weekly Retention을 구해주세요WITH base AS ( SELECT DISTINCT DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS 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_name = "click_payment" ), event_week_and_first_week AS ( SELECT DISTINCT DATE_TRUNC(event_date, WEEK(MONDAY)) AS event_week, user_pseudo_id, DATE_TRUNC(MIN(event_date) OVER(PARTITION BY user_pseudo_id ORDER BY event_date), WEEK(MONDAY)) AS first_week FROM base ), retention_base AS ( SELECT *, FIRST_VALUE(user_cnt) OVER(ORDER BY day_of_week) AS total_user FROM ( SELECT DATE_DIFF(event_week, first_week, WEEK) AS day_of_week, COUNT(DISTINCT user_pseudo_id) AS user_cnt FROM event_week_and_first_week GROUP BY day_of_week ) ) SELECT day_of_week, ROUND(SAFE_DIVIDE(user_cnt, total_user), 2) AS retention FROM retention_base ORDER BY day_of_weekapp_logs 테이블에서 event_name이 click_payment인 것만 필터 걸어서 리텐션 구하기를 진행했는데 이렇게 푸는 것이 맞을까요..?

2판 기준으로 COL1에는 1,1,1,1이 들어있습니다

포켓몬의 수를 타입 별 집계, 수가 10 이상인 타입만 남기는 쿼리 작성 연습문제에 대하여 질문 드려요. 작성 전에 고민했던 부분은포켓몬 타입은 type 1, 2로 나뉘어 있다.type 2는 null 인 경우가 있다. => where 로 제외할 수 있을까?type 1, 2 을 포함해서 세야 할 것 같다. (총 포켓몬 수보다 합산 값이 클 수 있다)였거든요.Group by 에서 type2를 빼면 통합되지 않았다는 오류가 발생해서 어쩔 수 없이 넣긴 했는데, 제가 원한 형태로 구현되지는 않았습니다. 어떻게 해야 null 을 제외한 type2 의 값까지 전부 셀 수 있을까요?WHERE TYPE2 IS NOT NULL 을 넣게 되면 1개 타입만 가진 개체의 경우 아예 집계를 하지 않는 것 같아서 그 부분도 뺄 수 밖에 없었어요.최종적으로는 1열에 타입, 2열에 해당하는 개체 수가 나오게 구현하고 싶습니다.(바위, 불인 포켓몬의 경우 바위에 1개, 불에 1개 계산되도록이요.바위, 불 포켓몬 + 바위 포켓몬 + 불 포켓몬 = 바위 2개체 불 2개체)SELECT type1, type2, COUNT(id) as cnt FROM `basic.pokemon` GROUP BY type1, type2

 Battel 데이터 문제로 보이는데, 설정을 잘못한걸까요?