Cohort Query Example
Cohorts are a good tool to review retention of users over a defined period of time.
To create your own cohort report, you'll need to define your cohort time frame, population, and activeness of users.
Here is an example of a cohort query in PostgreSQL, step by step:
WITHclause in this example -
WITHclauses allow you to name a sub-query block, this way your query is modular (and sometimes runs faster), it can later be referred to inside the main query instead of making a large main query with many aliases,
JOINs or other complexities. You can have multiple sub-queries, just be sure to have a comma between them
WITH time_frame AS ( SELECT CURRENT_DATE - 14 ),
population AS ( select created_at::DATE AS cohort_date, id AS unique_id FROM users 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',age(activity_date, activity.cohort_date)) AS day_number, 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