Retention by First-Purchase Cohort

Write a query using fp_signups and fp_orders to report retention by the calendar month of each user’s first order, rather than by signup month. Find the first order by ordered_on, not by order_id.

Return cohort_month as a month-start DATE and cohort_size as the number of users whose first order fell in that month. Users with no orders belong to no cohort and are excluded from all counts.

m1_retained counts users with at least one order in the calendar month immediately after their first-order month. Additional orders in month 0 do not qualify. m1_pct is that count divided by cohort size, multiplied by 100 and rounded to one decimal place.

Ordering: Sort by cohort_month ascending.

Returncohort_monthcohort_sizem1_retainedm1_pct

Columns

2 columns

columntype

user_idVARCHAR

signup_dateDATE

Sample data & expected output

6 rows · 2 columns

user_idsignup_date

p012026-01-10

p022026-01-15

p032026-01-20

p042026-02-02

p052026-02-18

p062026-03-05

If you get stuck

Show hint · 3 hints Walkthrough Reveal answer

About this problem

You practise the choice the lesson keeps insisting on: a cohort is defined by an event, and which event you pick changes what the analysis means. Cohorting the same six users by first purchase rather than by signup moves one of them two months later, removes another from the analysis entirely, and changes every cohort size. The SQL is a familiar shape; the work is in reading the question and picking the right starting event.

The two halves of that are worth separating. Moving p03 from the January cohort to the March one is a change of grouping – the user is still in the analysis, measured from a different point, and measured from the right point for a question about purchase behaviour. Removing p05 is a change of population, and it is the one people miss. A user who signed up and never bought has no first-purchase cohort, and a left join from the signups table that lets them through produces a row with a NULL cohort month holding every non-purchaser in the dataset. That row looks like a bug to be filtered later, which is how non-purchasers end up quietly inflating a denominator for a month or two before anyone checks. The third thing in here is smaller and more universal: min(order_id) is not min(ordered_on). p01’s ids disagree with their dates on purpose, because in real order tables they often do – backfills, migrations and retries all insert rows out of order – and a query that trusts the id to be chronological is wrong in a way that no amount of staring at the result will reveal.

Read the lesson: Cohorts & Retention (P23) →

Your query · runs in your browser⌘ Enter to run

Loading editor…

Output · run to see rows