The task and original case
Create a cohort table whose denominator stays attached to the people who started together. Your report should distinguish returning at least once in a month from the number of events generated. A declining row count is not automatically evidence of improving retention.
| Case element | Value or requirement |
|---|---|
| Inputs | Four January starters. Two return in February; one returns in March. |
| Build | A cohort table with a definition of returning and an observation cutoff. |
This is an original invented exercise. It is independent practice, with no employer assessment or open-vacancy claim. Download the inputs, attempt your own result, then open the separate answer key.
Data and worked inputs
Build a result another person can follow
Define the January cohort as the four unique users in cohort.csv. For this exercise, a return means at least one recorded activity by a cohort member during the named calendar month. The observation cutoff is March 31, 2026. February and March are complete here; a later unfinished month must not be presented as a complete observation.
Create a list of month–user pairs from returns.csv, remove repeated pairs and join them back to cohort membership. Count the distinct returning users for each month. Divide both counts by four, the original eligible cohort. SQLite window functions can retain partition-level context, but an explicit cohort-size table joined to the monthly counts is equally inspectable. Show months with zero recorded returns rather than allowing missing result rows to vanish.
Sources[1]
Check the worked result
U1 and U2 return in February, so February retention is 2 ÷ 4 = 50%. Only U1 returns in March, so March retention is 1 ÷ 4 = 25%. March is not 1 ÷ 2 = 50%: that would answer a different question about the previous month's returners.
Publish the numerator and denominator beside each percentage. A reader should be able to reconstruct the result without guessing who was eligible. These four invented users cannot support a conclusion about a real product's retention.
Make the failure visible
Duplicate U1's February activity. The number of events increases, but February's returning-user count remains two. Add a returning user who is absent from the January cohort; flag or exclude that event under the declared membership rule. Finally move the cutoff into the middle of March and label the observation incomplete instead of treating its rate as directly comparable.
Revise it and ask someone to reproduce it
Ask a peer to reproduce the table using only your definition, files and cutoff. For the next revision, add a second cohort with a different start month and ensure each group keeps its own denominator. Return to the first report and explain the difference between retention of starters and retention of last month's active users.
- Every rate displays unique returners, eligible starters and the month.
- Duplicate activity does not change returning-user counts.
- Incomplete observation windows have a visible status.
Describe the work accurately
After completing your own version, you could adapt this project bullet: “Built an independent cohort-retention exercise with fixed eligible populations, distinct monthly returners and an explicit observation cutoff.”
Use Projects or Independent practice as the heading. Keep the input files, result and revision together. Describe only your own completed work; the invented numbers are model outputs, not employer results or measured business impact.