Write a query using rt_signups and rt_activity to return one row per signup-month cohort, measured from an extract taken on 31 March 2026. cohort_month is a month-start DATE; cohort_size counts every signup, including never-active users.
Return m0 through m3 as the percentage of the full cohort active in each corresponding calendar month after signup, rounded to one decimal place. Count each user once per month and exclude activity before their cohort month. Signup-month activity counts for m0.
Return NULL when a target month ends after 31 March 2026, because that period is not yet observable. For an observable month with no activity, return 0.0. Keep the same cohort-size denominator in every column.
Ordering: Sort by cohort_month ascending.
Returncohort_monthcohort_sizem0m1m2m3
About this problem
You practise the output a retention analysis is actually presented in: cohorts down the side, month offsets across the top, one percentage per cell. The long form is easier to compute with and nobody reads it, so the pivot is not a cosmetic step – it is the deliverable. The skill is reshaping without changing what the numbers mean, and the two ways to change them are the denominator and the empty cells.
The empty cells are the interesting half. A retention matrix is a triangle because a cohort can only be observed for as long as it has existed, and the matrix has to say so. The 2026-03 cohort signed up three weeks before the extract: its month 1 is April, April has not happened, and the honest cell is blank. Writing 0.0 there says something much stronger and much worse – that a month of data came in and nobody returned – and because the 0.0 sits in a column next to real numbers, anyone reading the matrix will average it, chart it, and conclude the newest cohorts are collapsing. The lesson calls this out directly and it is the one mistake that survives review most often, because coalesce(retention, 0) is written to fix the opposite problem: an observed month with no activity, where 0.0 is correct. Both cases are in the expected output, two cells apart, and the query has to tell them apart. The other half is the denominator, which must stay the cohort’s original size in every column. The version that divides by the previous month’s active users produces 200.0 for December’s m3, and a percentage over 100 is at least loud; the version that counts only users who were ever active is quieter, reporting 100.0 for December’s m0 by dropping the user who never came back from the denominator.
Read the lesson: Cohorts & Retention (P23) →