Generates a SQL query that builds a cohort-by-month-offset retention matrix, with clear metric definitions and commented CTEs.
Generates a SQL query that builds a cohort-by-month-offset retention matrix, with clear metric definitions and commented CTEs. Useful for product and data teams tracking how user cohorts retain over time.
Write a SQL cohort retention analysis using the table `{{table_name}}` in `{{database_type}}`.
Definitions:
- Cohort: the month of a user's first `{{cohort_event}}` recorded in `{{date_column}}`
- Retention: whether the user performed `{{retention_event}}` in each subsequent month
The query should:
1. Define cohorts using a CTE that finds each user's first event month
2. Join back to activity data to find which months each user was active
3. Calculate cohort size and retention count per month offset (0, 1, 2, ... N)
4. Return a cohort × month offset matrix with retention percentages
Include comments on each CTE. Database: `{{database_type}}`.
Source: https://mljar.com/ai-prompts/data-analyst/sql/prompt-cohort-retention-analysis/