You are given user signup data and product activity data for a self-serve AI product. Write a PostgreSQL query that builds monthly signup cohorts and measures retention by month offset. A user belongs to the cohort of their signup month, and they are retained in month n if they had at least one qualifying activity in the nth calendar month after signup. Return one row per cohort month and month offset for offsets 0, 1, and 2, including cohort size, retained users, and retention rate.
Output details
- Return columns in this order:
cohort_month, month_number, cohort_size, retained_users, and retention_rate.
- Sort rows by
cohort_month ascending, then month_number ascending.
- Count activity only when
event_name is prompt_sent or api_call and event_count is greater than 0.
- Include activity dates on or after the cohort month, with month offsets inclusively bounded from
0 through 2.
- Count each user at most once per cohort month and month offset.
- Round
retention_rate to four decimal places and report 0.0000 when no users are retained.
