The tricky part is that cohort assignment uses the earliest activity_ts, not signup_date.
Start by clarifying the schema and definitions, then outline a multi-step SQL approach using CTEs: first find each tenant's earliest activity month (cohort month), then join back to the activity table to get all subsequent activity months, and finally aggregate to compute cohort size, retained counts, and retention rates. Emphasize handling edge cases like tenants with no activity after cohort month and ensuring correct month numbering.
Pro tip: Mention that retention is typically calculated based on distinct active tenants per month, and that using date truncation to month and a self-join or window function can efficiently compute month numbers. Also, note that rounding should be applied at the final step to avoid precision issues.
Confirm the table structures, definitions of 'active', and that cohort is based on earliest activity timestamp, not signup. Ask about handling tenants with no subsequent activity.
Use a subquery or CTE to find the minimum activity timestamp per tenant, then truncate to month to get the cohort month.
Join the cohort month back to the activity table, calculate the month difference between activity month and cohort month as month_number, and count distinct active tenants per cohort and month_number.
Compute retention rate as retained count divided by cohort size, round to 4 decimal places, and ensure the output includes cohort month, cohort size, month number, retained count, and retention rate.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.