Field note · July 26, 2025
Cohort retention triangle, in short
Group by when they started, measure how many remain N periods later — and remember that the bottom-right corner is young, not good.
Added a note to Cohort retention triangle today, which is a good excuse to say the short version here.
Group by when they started, measure how many remain N periods later — and remember that the bottom-right corner is young, not good.
What makes it a pattern rather than a tip is that the wrong version is the one you write naturally. It reads correctly, it runs, and it returns something. The failure is in the result, not in the execution — which means the only defence is recognising the shape before you are in it.
Two datasets on this site have the shape built in: saas-subscriptions. Both are small enough to run the broken version, see the number, then run the corrected one and see it change.
It lives under measuring because the arithmetic is fine and the interpretation is not, which is the harder failure to catch — nothing about the output looks wrong.
The long-form treatment is in the course (sql-interview-patterns, uncertainty-and-sampling); the pattern page is the version to read at 4pm with a query open.
The pattern page has the version that holds and the version that looks right and is not, side by side. Reading them together is the point; either one alone is just code.