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_count 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
), division_before AS(
SELECT
*,
FIRST_VALUE(user_cnt) OVER(ORDER BY diff_of_week) AS first
FROM user_count
)
SELECT
*,
ROUND(SAFE_DIVIDE(user_cnt, first),2) AS retention_rate
FROM division_before답변 2
0
안녕하세요, 인프런 AI 인턴입니다.
강효주님, 작성하신 리텐션 SQL 쿼리에 대해서 검토해 보겠습니다. 현재 쿼리는 주간 리텐션(Weekly Retention)을 계산하기 위한 쿼리로, WITH 절을 통해 여러 단계의 중간 테이블을 생성하고 최종적으로 리텐션 비율을 계산하고 있습니다. 몇 가지 주의해야 할 요소와 설명을 덧붙이면 유용할 것 같아 관련 자료를 준비했습니다.
쿼리 구조 설명
기본 데이터 추출 (base CTE):
- 고유한
user_pseudo_id별로 이벤트 날짜 및 시간을 가져옵니다.
event_date필드는 한국 시간대에 맞춰 변환되어 있습니다.
- 고유한
첫 번째 주와 주차 간 차이 계산 (first_week_and_diff CTE):
- 각 사용자별 첫 번째 활동 주(
first_week)와 각 이벤트의 주(event_week)를 계산한 후, 이 둘의 차이를 계산하여diff_of_week컬럼에 저장합니다.
- 각 사용자별 첫 번째 활동 주(
사용자 수 계산 (user_count CTE):
- 주차 차이(
diff_of_week)를 기준으로 사용자 수를 집계합니다.
- 주차 차이(
리텐션 비율 계산 준비 (division_before CTE):
- 각 주차의 사용자 수와 첫 주의 사용자 수(
first)를 준비합니다.
- 각 주차의 사용자 수와 첫 주의 사용자 수(
최종 리텐션 비율 계산:
SAFE_DIVIDE함수로 각 주차의 사용자 수를 첫 주의 사용자 수로 나누어 리텐션 비율을 계산합니다.
관련 질문 및 해결 링크
아래의 유사한 질문 및 댓글에서는 리텐션 쿼리 작성 시 발생할 수 있는 문제점들과 해결책을 논의하고 있습니다:
3-7 리텐션 SQL 쿼리 작성하기 연습 (Weekly, Monthly)
- 리텐션 쿼리의 가독성과 CTE 사용 시 주의할 점에 대한 유용한 정보를 제공합니다.
- 리텐션 쿼리의 가독성과 CTE 사용 시 주의할 점에 대한 유용한 정보를 제공합니다.
-
- 중복 데이터로 인해 발생할 수 있는 문제를 이해하고 해결하는 데 도움이 되는 답변이 포함되어 있습니다.
- 중복 데이터로 인해 발생할 수 있는 문제를 이해하고 해결하는 데 도움이 되는 답변이 포함되어 있습니다.
이 자료들이 현재 강효주님의 SQL 쿼리 이해와 더불어 리텐션 분석 코드 최적화에 도움이 되길 바랍니다.
저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다. 현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏 추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.
최종 과제 제출
0
79
3
BigQuery 활용편 18강 질문있습니다!
0
80
1
리텐션 공부하다가 궁금한게 생겨 질문드립니다
0
79
2
안녕하세요 강사님 코호트 쿼리 공부하다가 의문점이 생겨서 문의드립니다
0
70
2
biquery 테이블 생성 오류 이슈
0
51
2
동일하게 쿼리를 작성했는데 화면과 다른 값이 나옵니다
0
72
2
[과제] 퍼널 PIVOT 테이블 작성하기
0
64
2
array 등
0
61
2
N day 리텐션 쿼리 관련 질문
0
61
2
이동평균 계산 시 order by 기본값은 뭔가요?
0
66
2
윈도우 연습문제 1번 질문
0
61
1
user_id에 NULL이 나오는데 정상인가요?
0
70
2
3-13 리텐션 과제 제출
0
92
2
최종 과제 제출
0
129
3
weekly retention 구하기 과제
0
90
2
1-9. 피벗 쿼리 작성
0
78
2
app_logs 테이블 생성 문제
0
70
2
[과제] 퍼널 쿼리(피벗테이블 적용) 작성 완료
0
104
2
3-7 Weekly, Monthly Retention 쿼리 작성
0
91
2
정성 데이터 분석 방법 문의
0
163
1
최종 과제 제출
0
105
3
1-6 예시 문제 풀이
0
66
2
최종과제 제출
0
141
2
3-13. 리텐션 과제
0
201
3