Cohort Query Example

cohort.sql

with 

time_frame as (
select current_date - 14
),

population as (
  select created_at::date as cohort_date, id as unique_id
  from organizations
  where created_at > (select * from time_frame)
),

activity as (
  select created_at::date as activity_date, org_id as unique_id, cohort_date
  from events
  join population on population.unique_id = org_id
  where created_at > (select * from time_frame)
),

population_agg as (
  select cohort_date, count(distinct unique_id) as total
  from population
  group by 1
)

select activity.cohort_date as date, 
       date_part('day',activity_date - activity.cohort_date) as day, 
       count(distinct unique_id) as value, 
       total
from activity
join population_agg on activity.cohort_date = population_agg.cohort_date
group by 1 , 2, 4