Average Active Months per Cohort

Write a query using rt_signups and rt_activity to return every signup-month cohort, with month-start DATE cohort_month and full cohort_size.

active_user_months counts distinct user-month pairs from the signup month onwards. Repeated events in one month count once; activity before the cohort month is excluded.

avg_active_months is that total divided by the full cohort size, rounded to two decimal places, including never-active members in the denominator. max_active_months is the largest number of qualifying active months for one cohort member, or 0 when nobody was active.

Ordering: Sort by cohort_month ascending.

Returncohort_monthcohort_sizeactive_user_monthsavg_active_monthsmax_active_months

Columns

2 columns

columntype

user_idVARCHAR

signup_dateDATE

Sample data & expected output

6 rows · 2 columns

user_idsignup_date

a012026-01-05

a022026-01-12

a032026-01-26

a042026-02-07

a052026-02-21

a062026-03-14

If you get stuck

Show hint · 3 hints Walkthrough Reveal answer

About this problem

You practise a cohort summary where the only difficulty is the denominator. Count each cohort’s distinct user-months, divide by the number of users who joined, and report the largest. Every wrong answer here comes from quietly measuring the users who stayed instead of the users who joined.

That substitution is easy to make because the natural way to write the query is to start from the activity and group upwards, and everything that starts from the activity has already dropped the people who were never active. January then reports an average of 2.0, which is a true statement about a01 and a02 and a flattering one about the cohort. March is the clearest case: one user, no activity, and the honest row is three zeros. A query built from the activity side returns no March row at all, and a cohort that disappears from a report is worse than one that reports badly, because nobody goes looking for it. The second thing this problem drills is the deduplication, which matters more here than in a retention percentage. A percentage is bounded, so counting events instead of users caps out at 100% and often still looks sane; an average of months is unbounded, so a01’s four activity rows become four months out of a three-month span, and max_active_months reports 4 for a cohort whose data covers January to April inclusive. Those two mistakes pull in opposite directions, which means a query with both can land close to the right answer for the wrong reasons.

Read the lesson: Cohorts & Retention (P23) →

Your query · runs in your browser⌘ Enter to run

Loading editor…

Output · run to see rows