Monthly Retention Analysis — Solution & ExplanationProblems / Monthly Retention Analysis / EditorialApproach

Identify cohorts: Find each user's first activity month using MIN(DATE_TRUNC('month', activity_date)).

Map user-month pairs: Get distinct months each user was active.

Compute month offsets: Calculate month_number as the difference in months between the activity month and cohort month.

Aggregate: Count distinct retained users per cohort and month_number.

Compute retention rate: Join with cohort sizes (month_number = 0) and calculate the percentage.

Key ConceptCohort retention analysis tracks how user engagement decays over time from their initial activity. This is a foundational metric in product analytics. The pattern combines DATE_TRUNC for period alignment, DATEDIFF for offset computation, and self-joins for comparing against baseline cohort sizes.Hints

First, determine each user's cohort by finding their earliest activity month with MIN(DATE_TRUNC('month', activity_date)).

Use DATEDIFF('month', cohort_month, activity_month) to compute the month offset.

Join the retention counts with cohort sizes (month_number = 0) to compute the retention rate.

Solve Monthly Retention Analysis yourself →