리텐션 SQL 작성하기

쿼리 작성하다가 DISTINCT에 대해 궁금한 점이 생겨 질문 남깁니다!

• WITH 문 base 와 first_week_data에서 DISTINCT는 중복 방지 즉, 비용 절약을 위해서 쓰는 것으로 이해했습니다.

  그렇다면 DISTINCT를 쓰지 않아도 쿼리상 문제는 없는 게 맞을까요?

• user_count에서 DISTINCT는 유니크 유저수를 위해 꼭 사용해야하는 것으로 이해했습니다.

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
)
,first_week_data AS (
  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 week_date
  FROM base
)
,first_week_diff_data AS (
  SELECT 
  *,
  DATE_DIFF(week_date,first_week,week) AS first_week_diff
  FROM first_week_data
)
,user_count AS (
SELECT 
 first_week_diff,
 COUNT(DISTINCT user_pseudo_id) AS user_cnt
FROM first_week_diff_data
GROUP BY ALL
)
,first_user_count AS (
SELECT 
 first_week_diff,
 user_cnt,
 FIRST_VALUE(user_cnt) OVER (ORDER BY first_week_diff) AS first_user_cnt
FROM user_count
)
SELECT 
 *,
 ROUND(SAFE_DIVIDE(user_cnt,first_user_cnt),2) AS rentention_ratio 
FROM first_user_count