3-7 리텐션 SQL 쿼리 작성하기 연습 (Weekly, Mont... - 인프런

리텐션 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

안녕하세요! 잘 작성해주셨네요.

쿼리 부분에서 가독성을 챙기도록 쿼리를 작성해보시면 더욱 좋을 것 같단 생각이 들어요. 입문편에 있는 가독성 있는 쿼리 부분을 수강해주셔요.

먼슬리 쿼리는 정돈되어 있는데 위클리 쿼리에선 상대적으로 가독성을 더 개선할 부분이 있다고 생각했어요

   , 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에 추가해줘야 하지요!)

문제 풀어주셔서 감사합니다.

인프런 커뮤니티 질문&답변

3-7 리텐션 SQL 쿼리 작성하기 연습 (Weekly, Monthly)