cohort.sql

-- Demonstration on how Cohort Analysis is done. In order to do this we just need 2 tables:
-- auth_user (id, timestamp)
-- reaction_view (user_id, timestamp)

-- The below example use the following table names and fields, feel free to replace them with yours.
-- - auth_user (id, date_joined)
-- - reaction_view (user_id, created_at)

-- Demo: https://demo.holistics.io/queries/11118-buyer-cohort-retention-analysis
-- Forked from https://gist.github.com/nvquanghuy/bd0fda7b88e5b2fd8e46e047e391d25f


with cohort_items as (
  select
    date_trunc('day', U.date_joined)::date as cohort_day,
    id as user_id
  from auth_user U
  order by 1, 2
),
-- (user_id, cohort_day): user X has activity in day number X
user_activities as (
  select
    A.user_id,
    date_trunc('day', A.created_at)::date - C.cohort_day as day_number
  from reactions_view A
  left join cohort_items C ON A.user_id = C.user_id
  group by 1, 2
),
-- (cohort_day, size)
cohort_size as (
  select cohort_day, count(1) as num_users
  from cohort_items
  group by 1
  order by 1
),
-- (cohort_day, day_number, cnt)
B as (
  select
    C.cohort_day,
    A.day_number,
    count(1) as num_users
  from user_activities A
  left join cohort_items C ON A.user_id = C.user_id
  group by 1, 2
)
-- our final value: (cohort_day, size, day_number, percentage)
select
  B.cohort_day,
  S.num_users as total_users,
  B.day_number,
  B.num_users::float * 100 / S.num_users as percentage
from B
left join cohort_size S ON B.cohort_day = S.cohort_day
where B.cohort_day IS NOT NULL
order by 1, 3