해결된 질문
24.07.23 19:03 작성
·
92
0
리텐션 SQL 쿼리 작성하기, Weekly와 Monthly 쿼리를 작성한 부분을 게시판에 올립니다.
-- Weekly 리텐션 작성하기. 마지막 부분
, analysis AS
(SELECT diff_of_week,
user_cnt,
FIRST_VALUE(user_cnt) OVER (ORDER BY diff_of_week) AS first_user_cnt
FROM (SELECT diff_of_week,
COUNT(DISTINCT user_pseudo_id) AS user_cnt
FROM first_date_and_diff
GROUP BY 1))
SELECT diff_of_week,
user_cnt,
ROUND(SAFE_DIVIDE(user_cnt, first_user_cnt), 3) AS retention_week_rate
FROM analysis
ORDER BY 1;
-- 월별 리텐션 계산
WITH base AS (
SELECT
DISTINCT
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_date_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
)
), analysis AS (
SELECT
diff_of_month,
user_cnt,
FIRST_VALUE(user_cnt) OVER(ORDER BY diff_of_month) AS first_user_cnt
FROM (
SELECT
diff_of_month,
COUNT(DISTINCT user_pseudo_id) AS user_cnt
FROM first_date_and_diff
GROUP BY 1
)
)
SELECT
diff_of_month,
user_cnt,
ROUND(SAFE_DIVIDE(user_cnt, first_user_cnt), 3) AS retention_month_rate
FROM analysis
답변 1
0
2024. 07. 24. 11:59
안녕하세요! 잘 작성해주셨네요.
쿼리 부분에서 가독성을 챙기도록 쿼리를 작성해보시면 더욱 좋을 것 같단 생각이 들어요. 입문편에 있는 가독성 있는 쿼리 부분을 수강해주셔요.
먼슬리 쿼리는 정돈되어 있는데 위클리 쿼리에선 상대적으로 가독성을 더 개선할 부분이 있다고 생각했어요
, analysis AS
(SELECT diff_of_week,
user_cnt,
FIRST_VALUE(user_cnt) OVER (ORDER BY diff_of_week) AS first_user_cnt
FROM (SELECT diff_of_week,
COUNT(DISTINCT user_pseudo_id) AS user_cnt
FROM first_date_and_diff
GROUP BY 1))
SELECT diff_of_week,
여기서 FROM 절에 있는 것이 어? 이거 뭐지?라는 생각을 했었어요. 제가 만든다면
, analysis AS (
SELECT
diff_of_week,
user_cnt,
FIRST_VALUE(user_cnt) OVER (ORDER BY diff_of_week) AS first_user_cnt
FROM (
SELECT
diff_of_week,
COUNT(DISTINCT user_pseudo_id) AS user_cnt
FROM first_date_and_diff
GROUP BY 1
)
)
SELECT
diff_of_week,
user_cnt,
ROUND(SAFE_DIVIDE(user_cnt, first_user_cnt), 3) AS retention_week_rate
FROM analysis
ORDER BY 1;
물론 취향으로 볼 수도 있긴한데, 이렇게 작성하면 FROM 절 안에 있는 것을 구조적으로 빠르게 파악할 수 있답니다. FROM 절이 아닌 SELECT가 궁금하면 그 위쪽을 보면 되는것이지요.
월별 리텐션에서 DATE_DIFf 라고 f는 소문자네요. 요것도 수정해도 좋겠네요!
그 외에 문법적인 부분 너무 잘해주셨어요. 핵심은 DATE_TRUNC로 MONTH를 자르는 것이에요.
지금은 FIRST_VALUE(user_cnt) OVER (ORDER BY diff_of_week) AS first_user_cnt 이걸 사용했는데 유저를 여러개로 나눈다고 하면, PARTITION BY를 추가해야 할거에요(예를 들어 유저 구분을 new, dormant 등으로 나눌 때 컬럼을 하나 만들고 PARTITION BY에 추가해줘야 하지요!)
문제 풀어주셔서 감사합니다.