medium · ~12 min
Product analytics needs a monthly cohort retention table built from user_activity, where each user's cohort is the calendar month of their earliest recorded activity.
user_activity
Write a query that computes monthly cohort retention:
cohort_month
activity_date
month_number
0
1
retained_users
retention_rate = ROUND(retained_users * 100.0 / cohort_size, 2)
cohort_size
month_number = 0
retention_rate
Input contract: user_id and activity_date are non-null. A user may have multiple rows in user_activity within the same calendar month (and even on the same date).
user_id