Cohort Month and Month Offset per User

Write a query using rt_signups and rt_activity to return one row per user per active calendar month.

Return cohort_month as the first day of the signup month and activity_month as the first day of the activity month, both DATE. month_offset is their calendar-month difference, with signup-month activity at offset 0.

Count repeated activity in one month once. Drop activity in months before the cohort month, but keep activity earlier than signup within that same month. Users with no qualifying activity do not appear.

Ordering: Sort by user_id, then activity_month, ascending.

Returnuser_idcohort_monthactivity_monthmonth_offset

Columns

2 columns

columntype

user_idVARCHAR

signup_dateDATE

Sample data & expected output

5 rows · 2 columns

user_idsignup_date

u12025-11-20

u22025-12-28

u32026-01-05

u42026-01-10

u52026-02-20

If you get stuck

Show hint · 3 hints Walkthrough Reveal answer

About this problem

You practise the two columns every later retention query is built on, with nothing else in the way. One row per user per active month, the cohort month it belongs to, and the offset between them. Getting this CTE right is most of the work in a retention query; getting it slightly wrong moves every percentage downstream by an amount nobody can trace.

The sample is five users and six activity rows, and four of those rows exist to catch a specific shortcut. u1’s two November activities catch the missing deduplication – a repeated user-month doubles a cohort’s apparent activity, and in a retention numerator that is the difference between 50% and 100%. u2’s four-day gap across new year catches two things at once: that the offset is a calendar-month difference rather than elapsed time, and that subtracting month numbers without the year gives −11. u3’s activity a fortnight before signing up catches the missing filter, which would otherwise produce an offset of −1 and a row in a retention table for a month before the cohort existed. And u5 catches the overcorrection: filtering on the full dates rather than the truncated months looks safer and silently drops a user who was active in their own cohort month. That last pair is the one worth sitting with, because both rows look like the same kind of problem and the rule treats them differently – the month defines the cohort, so “before the cohort month” and “before the signup day” are not the same thing.

Read the lesson: Cohorts & Retention (P23) →

Your query · runs in your browser⌘ Enter to run

Loading editor…

Output · run to see rows