Start by defining the signup cohort month for each user, then join to events and compute the month offset between event and signup. Aggregate to count distinct retained users per cohort and month number, then calculate retention rate as retained divided by cohort size.
Pro tip: Clarify whether 'month N' means calendar month difference or 30-day periods, and explicitly state that you're using calendar month difference as implied by the question. Also mention that you'd handle edge cases like users with no events by including them in cohort size but not in retained counts.
Create a CTE that selects user_id and truncates signup timestamp to month to get cohort_month. This establishes the cohort for each user.
Join the cohorts CTE to the user_events table on user_id, ensuring event_timestamp >= signup_timestamp to ignore pre-signup events.
Calculate month_number as the difference in months between the event month and cohort_month, using a function like DATEDIFF(month, cohort_month, event_month) or equivalent.
Group by cohort_month and month_number, count distinct users as retained users. Also compute cohort size as the total distinct users in each cohort (from the cohorts CTE).
Divide retained users by cohort size to get retention rate, and output the required columns: cohort_month, month_number, cohort_size, retained_users, retention_rate.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.