Cohort Users Who Never Returned

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

A user never returned if they have no activity in any calendar month after their cohort month. Signup-month activity is not a return, and earlier-month activity is ignored. A user with no activity also never returned.

Calculate the percentage using the full cohort size and round to one decimal place. Include cohorts where everybody returned and cohorts where nobody did.

Ordering: Sort by cohort_month ascending.

Returncohort_monthcohort_sizenever_returnedpct_never_returned

Columns

2 columns

columntype

user_idVARCHAR

signup_dateDATE

Sample data & expected output

7 rows · 2 columns

user_idsignup_date

n012026-01-03

n022026-01-12

n032026-01-21

n042026-01-30

n052026-02-05

n062026-02-24

n072026-03-18

If you get stuck

Show hint · 3 hints Walkthrough Reveal answer

About this problem

You practise a definition of retention written as what it excludes. One row per cohort and a count of the users who never came back after the month they joined in. The SQL is short; the whole problem is in the three rules about what does not count as a return, and each one is in the sample as a user.

Activity inside the signup month is the rule that catches most people. n01 signed up on 3 January and was active on the 5th and the 19th, which is two activity rows, a perfectly normal-looking user, and nobody who came back. A new user doing things in their first week is not retention – it is the signup flow – and a query that counts it as retention reports that only one of January’s four users was lost when in fact three were. The other two rules are smaller and cost just as much: n03’s only activity predates their signup, which is what backfills and clock skew look like, and n04 has no activity row at all, which is what a left join is for and what an inner join silently removes. Those three users are three different reasons for the same answer, and a query that catches two of them reports 50% or 66.7% where the truth is 75%. It is worth noticing that this number is not simply the complement of month-1 retention: n05 skipped February entirely and came back in April, so they are retained by this definition and absent from any month-1 figure. “Never returned” and “not retained in month 1” are different questions, and the gap between them is the users who come back late.

Read the lesson: Cohorts & Retention (P23) →

Your query · runs in your browser⌘ Enter to run

Loading editor…

Output · run to see rows