hard · ~12 min
Growth wants a cohort retention report tracking exact Day-1 and Day-7 return rates by calendar signup date.
Write a query that computes D1 and D7 retention per signup cohort:
signup_date
signup_time
cohort_size
signup_date + 1 day
signup_date + 7 days
d1_pct = ROUND(d1_retained_users * 100.0 / cohort_size, 2)
d7_pct = ROUND(d7_retained_users * 100.0 / cohort_size, 2)
d1_pct
d7_pct
Input contract: user_id is unique and non-null in signups; event_id is unique and non-null in activity; signup_time and event_time are non-null. A user may generate zero, one, or many rows in activity on the same calendar day.
user_id
signups
event_id
activity
event_time