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

안녕하세요!

인프런에서 `을 3번 치면 조금 더 가독성 있게 쿼리를 보여줄 수 있어요. 한줄로 쿼리를 보여주시면 보는 사람이 구조를 빠르게 파악하기 어렵거든요. 이렇게 쿼리를 전달할 때 어떻게 할지도 고민해보시면 좋을 것 같아요. 아래는 제가 정리를 해봤어요.

WITH base AS ( 
SELECT 
  DISTINCT 
    user_id, 
    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 "2022-11-03" 
), diff_of_week_data AS (
SELECT 
  *, 
  DATE_DIFF(event_week, first_week, week) AS diff_of_week
FROM (
  SELECT 
    DISTINCT 
      user_pseudo_id, 
      MIN(DATE_TRUNC(event_date,WEEK(MONDAY))) OVER (PARTITION BY user_pseudo_id) AS first_week, 
      DATE_TRUNC(event_date, WEEK(MONDAY)) AS event_week 
    FROM base)
)
SELECT 
  *, 
  SAFE_DIVIDE(cnt, first_value_cnt) AS retention_ratio
FROM(
  SELECT 
    *, 
    FIRST_VALUE(cnt) OVER (ORDER BY diff_of_week) AS first_value_cnt 
  FROM(
    SELECT 
      diff_of_week, 
      COUNT(user_pseudo_id) AS cnt 
    FROM diff_of_week_data 
    GROUP BY diff_of_week)
  )
ORDER BY diff_of_week

전반적으로 잘 작성해주셨는데, 한가지 부분은 수정해보면 좋을 것 같아요

SELECT user_id, user_pseudo_id가 같이 있는데 user_id가 로그인하기 전엔 NULL이 됩니다. 앱 로그 데이터에서 로그인하기 전엔 NULL이 있고, user_pseudo_id가 있다가 로그인을 하고 user_id에 NULL이 없어지게 됩니다. 그래서 지금 쿼리를 실행하면 중복이 발생할거에요

user_id가 NULL일 때

user_id가 NULL이 아닐 때(로그인 후)

그래서 이 부분 수정하면 될 것 같아요.

한가지 질문할 점은 event_week 기간 동안 한번 이상 접속을 한다고 하더라도 user_psuedo_id는 한번으로 집계되는지 궁금합니다!

이 부분은 특정 user_pseudo_id 하나를 선택해서 주차로 변경되는지 보면 좋을 것 같아요!. 일단 지금 쿼리에서는 DISTINCT로 유저 로그를 줄이기 때문에 Row는 1개일거에요. 만약 event_name을 추가한다고 하면 여러 Row가 나오게 될거에요(이벤트는 여러개 발생할 수 있으므로)

궁금한 것 있으면 또 남겨주셔요~!