• 가입일로 보는 cohort는 크게 의미가 없기에, 특정 이벤트 액션별로 비교한다.
  select activity.date,
        count(distinct activity.user_id) as active_users,
        count(distinct future_activity.user_id) as retained_users,
        count(distinct future_activity.user_id) /
        count(distinct activity.user_id)::float as retention
  from activity
  left join activity as future_activity
        on activity.user_id = future_activity.user_id
           and activity.date = future_activity.date - interval '1 day'
  group by 1

Periscope blog sangkon blog