MeritKeep

Monthly cohort retention from a raw event log

From a single `events(user_id, event_name, occurred_at)` table, build a monthly cohort retention table. Rows are signup month, columns are months since signup, values are the percentage of that cohort still active. Two things separate a correct answer from a plausible one: months with no activity must appear as 0 rather than disappear, and months that have not happened yet must be blank rather than 0.

Part of the SQL & Data Analysis course~120 minutesEvidence: a write-upWe mark it against a checklist you can read first
01 · What counts as done

What you have to hand in

  • Every user belongs to exactly one cohort.
  • Month 0 is the signup month and shows 100% for every cohort.
  • Inactive months inside a cohort's lifetime show 0, not blank.
  • Future months show blank, not 0.
  • Cohort size is visible alongside the percentages.
02 · How it will be marked

The checklist, published before you start

We score your work against each line below, and every score has to quote the part of your work it is based on. You read all of it first, so you can argue with any score on the specifics.
  • Cohorts are assigned correctly25% of the grade

    Each entity belongs to exactly one cohort, derived from its first qualifying event.

    1. AbsentEntities appear in several cohorts, or cohorts are assigned from an arbitrary event.
    2. DevelopingFirst-event logic is present but mis-handles ties or timezone boundaries.
    3. Competent— this is the pass markEach entity is in exactly one cohort, based on its first event.
    4. StrongCorrect, and the tie/boundary rule is stated explicitly.
  • Period offsets are correct25% of the grade

    Month 0 means the signup period; offsets count from the cohort's own start, not from a fixed date.

    1. AbsentOffsets are calendar periods rather than periods since signup.
    2. DevelopingOffsets are relative but off by one, or inconsistent across cohorts.
    3. Competent— this is the pass markOffsets are correct and consistent.
    4. StrongCorrect, with the month-0 convention documented.
  • No missing periods20% of the grade

    Periods with zero activity appear as zero rather than vanishing from the output.

    1. AbsentZero-activity periods are missing, making retention look better than it is.
    2. DevelopingSome periods filled, others missing.
    3. Competent— this is the pass markAll periods present within each cohort's lifetime.
    4. StrongAll periods present, and periods after the data ends are correctly excluded rather than shown as zero.
  • The denominator is right20% of the grade

    Retention is measured against the cohort's own size, not the total population.

    1. AbsentDivides by the wrong population.
    2. DevelopingCorrect denominator but not held constant across periods.
    3. Competent— this is the pass markCohort size used consistently as the denominator.
    4. StrongCorrect, with the cohort size shown alongside the percentages so a small cohort is visible.
  • A colleague could maintain it10% of the grade

    The query is decomposed into named steps that map onto the analysis.

    1. AbsentA single opaque query.
    2. DevelopingPartially decomposed.
    3. Competent— this is the pass markNamed CTEs matching the analytical steps.
    4. StrongStructure makes the method obvious without the write-up.
03 · What this proves

The skills a pass would prove

Pass, and these skills count as proven, with this piece of work attached as the evidence.

This brief is part of the SQL & Data Analysis course

Starting it starts the course: every skill above, in the order they depend on each other, with this brief at the end as the thing you hand in — marked against the checklist you have just read and nothing else.

About three minutes to set up: what you want to do with SQL & Data Analysis, where you are starting from, and how many hours a week you actually have. The subject we already have from this page.Start the SQL & Data Analysis course
See the whole SQL & Data Analysis course, or every graded brief we run.

One cookie, so we can see which subjects people finish and where the site loses them — plus a replay of where you click. Nothing you type is recorded, and once you’re signed in the words on screen are blanked out too. No advertising, nothing sold. The detail.