데.분.레 - 복습 17

지속률 : 등록일 기준으로 이후 지정일 동안 사용자가 서비스를 얼마나 이용했는지 나타내는 지표

정착률 : 등록일 기준으로 지정한 7일 동안 사용자가 서비스를 사용했는지 나타내는 지표

WITH action_log_with_mst_users AS (
  SELECT u.user_id
       , u.register_date
       , CAST(a.stamp AS date) AS action_date
       , MAX(CAST(a.stamp AS date)) OVER() AS latest_date
       , DATEADD(day, 1, u.register_date::date) AS next_day_1
    FROM mst_users AS u
  LEFT OUTER JOIN action_log AS a 
                  ON u.user_id = a.user_id
  )
SELECT *
  FROM action_log_with_mst_users
  ORDER BY register_date

CAST : 데이터 형식을 다른 데이터 형식으로 변환하는 역할
DATEADD : 날짜, 시간, 타임스탬프를 지정하는 값만큼 늘리는 역할
OUTER JOIN : A, B 테이블을 조인할 경우, 조건에 맞지 않는 데이터도 표시하고 싶을 때 사용한다.

WITH action_log_with_mst_users AS (
  SELECT u.user_id
       , u.register_date
       , CAST(a.stamp AS date) AS action_date
       , MAX(CAST(a.stamp AS date)) OVER() AS latest_date
       , dateadd(day, 1, u.register_date::date) AS next_day_1
    FROM mst_users AS u
  LEFT OUTER JOIN action_log AS a 
                  ON u.user_id = a.user_id
  )
, user_action_flag AS (
  SELECT user_id
       , register_date
       , SIGN(SUM(CASE WHEN next_day_1 <= latest_date THEN
                   CASE WHEN next_day_1 = action_date THEN 1 ELSE 0 END END))
         AS next_1_day_action
    FROM action_log_with_mst_users
    GROUP BY user_id, register_date
  )
SELECT *
  FROM user_action_flag
  ORDER BY register_date, user_id

지정한 날의 다음날에 액션을 했는지 0과 1의 플래그로 표현했다.

WITH action_log_with_mst_users AS (
  SELECT u.user_id
       , u.register_date
       , CAST(a.stamp AS date) AS action_date
       , MAX(CAST(a.stamp AS date)) OVER() AS latest_date
       , dateadd(day, 1, u.register_date::date) AS next_day_1
    FROM mst_users AS u
  LEFT OUTER JOIN action_log AS a 
                  ON u.user_id = a.user_id
  )
, user_action_flag AS (
  SELECT user_id
       , register_date
       , SIGN(SUM(CASE WHEN next_day_1 <= latest_date THEN
                   CASE WHEN next_day_1 = action_date THEN 1 ELSE 0 END END))
         AS next_1_day_action
    FROM action_log_with_mst_users
    GROUP BY user_id, register_date
  )
SELECT register_date
     , AVG(100.0 * next_1_day_action) AS repeat_rate_1_day
  FROM user_action_flag
  GROUP BY register_date
  ORDER BY register_date