SQLRepPath
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
00/60 solved

Analytics Patterns · Cohorts · date_trunc · LEFT JOIN

54. Month-1 retention by signup cohort

Concept: Cohorts · date_trunc · LEFT JOINshow

A cohort is everyone who signed up in the same month. Month-1 retention is the share of a cohort that was active in the calendar month after their signup month. Cohort size must come from the signup table, so people who never came back still count in the denominator.

Example (different data):

SELECT to_char(joined_on, 'YYYY-MM') AS cohort,
  COUNT(*) AS members
FROM gym_members
GROUP BY 1;

Your task

Product wants to know if onboarding is improving: what share of each signup cohort came back the month after joining?

For users who signed up (users.created_at) in January through March 2024, return cohort_month (YYYY-MM), cohort_size, retained (cohort users with at least one app open in the calendar month after their signup month) and retention_pct (retained as a percentage of cohort_size, rounded to 1 decimal). Oldest cohort first.

Starting Postgres in your browser…

Checking your access…