Cohorts that are not lying to you

Somebody runs a retention query, sees the last five cohorts sliding downhill, and posts it with the words "retention is falling off a cliff." The query is syntactically fine. The join is correct. The numbers are arithmetically right. And nothing has happened.

This is the single most common analytics bug, it survives code review because there is nothing wrong with the code, and it has a name.

The query that reports a collapse

Twelve daily cohorts. Retention defined as "active at least once in the 7 days after signup." True retention is a flat 70% in every cohort — the seed data is constructed so that nothing changes.

-- A retention chart, and the bug that lives in almost every first draft of one.
-- Definition: "retained" = active at least once in the 7 days after signup.
-- Twelve daily signup cohorts. Today is 2026-03-08.
CREATE TABLE signups (
  user_id     INTEGER PRIMARY KEY,
  signup_date TEXT NOT NULL
);
CREATE TABLE visits (
  user_id     INTEGER NOT NULL,
  visit_date  TEXT NOT NULL
);

-- 50 users per daily cohort. In every cohort exactly 35 of them (70%) come back
-- once, five on each of days 1 through 7. Nothing changes across cohorts: the
-- true 7-day retention is a flat 70%, and any trend below is manufactured.
WITH RECURSIVE days(d, n) AS (
  SELECT '2026-02-25', 0
  UNION ALL SELECT date(d, '+1 day'), n + 1 FROM days WHERE n < 11
),
seq(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM seq WHERE i < 50)
INSERT INTO signups (user_id, signup_date)
SELECT c.n * 100 + s.i, c.d FROM days c, seq s;

INSERT INTO visits (user_id, visit_date)
SELECT user_id, signup_date FROM signups;

-- User k of each cohort returns on day ((k-1)/5)+1 for k <= 35. A visit that
-- would fall after today is not logged, because it has not happened yet.
INSERT INTO visits (user_id, visit_date)
SELECT user_id,
       date(signup_date, '+' || ((user_id % 100 - 1) / 5 + 1) || ' days')
FROM signups
WHERE user_id % 100 BETWEEN 1 AND 35
  AND date(signup_date, '+' || ((user_id % 100 - 1) / 5 + 1) || ' days')
      <= '2026-03-08';

-- ── the first draft ────────────────────────────────────────────────────────
-- Group by cohort, count who came back inside the window. This is the chart
-- that gets pasted into a channel under the words "retention is falling off a
-- cliff", and it is entirely an artefact of when the query was run.
SELECT
  u.signup_date                                   AS cohort,
  COUNT(DISTINCT u.user_id)                       AS cohort_size,
  COUNT(DISTINCT v.user_id)                       AS retained,
  ROUND(100.0 * COUNT(DISTINCT v.user_id)
        / COUNT(DISTINCT u.user_id), 1)           AS retained_pct,
  CAST(julianday('2026-03-08') - julianday(u.signup_date) AS INT)
                                                  AS days_observed
FROM signups u
LEFT JOIN visits v
  ON v.user_id = u.user_id
 AND v.visit_date >  u.signup_date
 AND v.visit_date <= date(u.signup_date, '+7 days')
GROUP BY u.signup_date
ORDER BY u.signup_date;

-- ── the same question, asked only of cohorts that can answer it ────────────
-- One predicate: a cohort is eligible once its whole 7-day window has elapsed.
-- Retention is flat at 70%, which is the truth, and the chart is now shorter --
-- that shortness is the honest cost of the metric's definition.
SELECT
  u.signup_date                                   AS cohort,
  COUNT(DISTINCT u.user_id)                       AS cohort_size,
  COUNT(DISTINCT v.user_id)                       AS retained,
  ROUND(100.0 * COUNT(DISTINCT v.user_id)
        / COUNT(DISTINCT u.user_id), 1)           AS retained_pct
FROM signups u
LEFT JOIN visits v
  ON v.user_id = u.user_id
 AND v.visit_date >  u.signup_date
 AND v.visit_date <= date(u.signup_date, '+7 days')
WHERE date(u.signup_date, '+7 days') <= '2026-03-08'
GROUP BY u.signup_date
ORDER BY u.signup_date;

-- result 1
cohort      cohort_size  retained  retained_pct  days_observed
----------  -----------  --------  ------------  -------------
2026-02-25  50           35        70            11
2026-02-26  50           35        70            10
2026-02-27  50           35        70            9
2026-02-28  50           35        70            8
2026-03-01  50           35        70            7
2026-03-02  50           30        60            6
2026-03-03  50           25        50            5
2026-03-04  50           20        40            4
2026-03-05  50           15        30            3
2026-03-06  50           10        20            2
2026-03-07  50           5         10            1
2026-03-08  50           0         0             0
(12 rows)

-- result 2
cohort      cohort_size  retained  retained_pct
----------  -----------  --------  ------------
2026-02-25  50           35        70
2026-02-26  50           35        70
2026-02-27  50           35        70
2026-02-28  50           35        70
2026-03-01  50           35        70
(5 rows)

Look at the shape of the first result: 70, 70, 70, 70, 70, 60, 50, 40, 30, 20, 10, 0. It is a clean downward slope over exactly the last seven days, and it is a picture of nothing happening. Each partially-observed cohort loses 10 percentage points per missing day because five of its fifty users were going to return on each day, and the days after today have not occurred.

The days_observed column is what gives it away, which is the practical lesson: put the observation window in the output. A cohort table without it cannot be audited by the person reading it, and the person reading it is usually the one who will publish the chart.

WatchSophia Yang · 13:06

Retention as a product-sense problem rather than a query — which segment, which definition, and what you would do with the answer. Useful straight after the query above, because it is the layer the SQL cannot give you.

A framework for user retention analysis Play “A framework for user retention analysis” on YouTube

Say which retention you mean

"Retention" is four different metrics, and the numbers differ substantially on the same data. An interviewer asking you to define retention is checking whether you know that.

Two rules follow. First, the numbers are not comparable across definitions, so a retention figure without its definition is not a figure. Second, rolling retention is the one to be careful with in an interview — it is genuinely useful and it is also unbounded above, so any cohort's number can still go up tomorrow. Saying that out loud is a strong signal.

Acquisition cohorts versus behavioural cohorts

The other definitional fork. An acquisition cohort groups users by when they arrived — signup week, install date, first purchase. A behavioural cohort groups them by something they did — used a feature, hit a paywall, invited someone.

They answer different questions and get confused constantly:

• "Did the product get better for new users over the last six months?" is an acquisition question. Compare cohort to cohort at the same age.
• "Do users who try the sharing feature stick around longer?" looks like a behavioural question and is actually a selection problem. Users who try a feature are not a random sample of users; they are more engaged already, which is why they tried it. The retention gap you measure includes that.

A third failure worth knowing: cohort mix shifts. Overall retention can fall while every cohort's retention is flat, if the mix of acquisition channels moved toward a channel that retains worse. The aggregate is a weighted average, and the weights move — which is why the decomposition into within-cohort change and mix change is the standard next step, and why "is this everywhere or is it one segment" is the first question to ask about any metric movement.

What this module gave you

Four things, and they are all the same skill applied to different objects:

• Which round you're in, because "data scientist" is four jobs and preparing for the average of them prepares you for none.
• What α and β are, and a sample size you can derive from four stated numbers rather than a calculator — including the σ-unknown iteration that a published handbook does and most candidates skip.
• Why ROC-AUC cannot see prevalence, demonstrated by holding a model fixed and watching precision collapse from 86% to 0.1% with the AUC unmoved.
• Why a flat retention curve can render as a collapse, and the one predicate that fixes it.

The common thread — the thing that is actually being tested in every one of these rounds — is whether you can say what a number could not have told you. Getting the calculation right is table stakes. Naming the assumption that would make it meaningless is the signal.

What comes next in this track

The rounds themselves, in depth: SQL as a skill rather than a syntax quiz (window functions, funnel and cohort shapes, the query patterns that recur); probability and the estimator questions; the diagnosis case worked end to end; ML fundamentals and the evaluation traps beyond imbalance; and ML system design, where a recommender or a fraud pipeline gets designed under the same rules as any other system-design round.