rt_signups (user_id, signed_up_at) has one row per user and rt_sessions (session_id, user_id, started_at) one row per session. Both timestamps are UTC.
A user's signup date is the calendar date of signed_up_at. They are active on day N when they started a session on the calendar date N days after their signup date: day 1 is the next calendar date, however few hours after signing up that is, and day 7 is the same weekday a week later.
For each signup date, return:
- •
cohort_size — the users who signed up that date - •
d1_users, d7_users — how many of them were active on day 1 and on day 7 (a user counts once, however many sessions they started that day) - •
d1_pct, d7_pct — those counts as a percentage of cohort_size, rounded to 1 decimal place
Every signup date appears, even when nobody came back.
Columns: signup_date (a DATE), cohort_size, d1_users, d7_users, d1_pct, d7_pct. Sort by signup_date.