Write a query using rt_signups and rt_activity to return every signup-month cohort at month_offset 0, 1, 2 and 3, with full cohort_size and month-start DATE cohort_month.
Return three distinct-user counts at each offset:
01period_users: Active in exactly that offset month, regardless of earlier gaps.
02continuous_users: Active in every month from offset 0 through this offset, with no gap. Once a month is missed, the user cannot be continuous at that or any later offset.
03rolling_users: Active in the target month or any later month available in the data, including months beyond offset 3.
Deduplicate user activity within a month and ignore activity before the cohort month. Report all four offsets even when their months have not occurred; this problem does not mask future periods as NULL.
Ordering: Sort by cohort_month, then month_offset, ascending.
Returncohort_monthmonth_offsetcohort_sizeperiod_userscontinuous_usersrolling_users
About this problem
You practise the fact that “retained” is not one thing. Three columns, one query, the same five users, and at offset 2 the answers are one, two and three. Period retention asks whether someone was there that month. Continuous asks whether they never left. Rolling asks whether they were still around at some point from then on. Each is the right answer to a different question, and none of them is the default.
The one that behaves least like people expect is rolling. At offset 0 it counts four of January’s five users, which is more than were active in January, because c04’s first activity is in February and rolling at month 0 only asks whether they showed up at some point from month 0 onwards. That makes rolling retention a monotonically falling curve by construction – it cannot go up – which is exactly why product teams like it: the chart always looks like a decay and never has the confusing bumps that period retention has. The bumps are real information, though. February’s period retention goes from 1 at offset 0 to 2 at offset 1, because c07 did not do anything in their signup month and turned up the month after, and a rolling curve hides that entirely. Continuous retention is the strictest and the one with the sharpest rule: the run has to start at month 0 and have no gap, so c04 is never continuous at any offset despite being active twice, and c02 is locked out from offset 2 onward even though they return at offset 3. That permanence is the thing to get right in the SQL – it is tempting to compute continuous as “has at least N+1 active months”, which counts c02 at offset 2 because they have three active months in total, or as “maximum offset at least N”, which is just rolling under a different name. Both are in the traps, and both produce a column that looks like a plausible retention curve.
Read the lesson: Cohorts & Retention (P23) →