Cohort Retention and the Denominator Trap

Level 5: Level 5: Advanced & Company-Specific SQL for DE Interviewshard38 minproduct-analyticscohort analysisDay-N retentionLEFT JOIN denominatorCOUNT(DISTINCT)date arithmetic

Anchor cohorts on first activity, pin one definition of retained, and keep the FULL cohort as the denominator with a LEFT JOIN.

Retention is where candidates corrupt the denominator

Cohort retention answers "of the users who started in a given week, what fraction came back later". It is one of the highest-signal analytics questions on a DE screen, and it has a famous failure mode: fewer than a third of candidates get the denominator right. The mistake is always the same. They join the cohort to the return activity with an INNER JOIN (or filter on the return event in the WHERE), which silently drops every user who did NOT come back, so the denominator shrinks to exactly the retained users and the rate reads 100%. Retention is a full-cohort fraction, so the denominator must stay the whole cohort.

Building it correctly

Work on events(user_id, event_date).

Anchor each user's cohort on their first activity. Use MIN(event_date) per user, not a signup_date column, which can be stale or backfilled. Bucket that first date into a cohort, here the week of year (Monday-starting) with strftime('%Y-%W', first_date).
Define "retained" and pin it. There are three common readings: active exactly on day N, active within N days, or active during the day-N window. They give different numbers, so commit to one out loud. We use active exactly 7 days after the first event: event_date = date(first_date, '+7 days').
Keep the full cohort as the denominator. LEFT JOIN the cohort to the day-7 activity, never INNER. COUNT(*) over the cohort is the denominator, COUNT(matched_user) is the numerator, and their ratio is the rate. Cast to a float first with 1.0 * ... or SQLite does integer division and every rate collapses to 0.

The demo shows the trap directly: the same cohort, with the denominator computed both ways, so you watch the INNER-join version report 100% while the full-cohort version reports the real 67%.

Interview nuance: use COUNT(DISTINCT user_id), not COUNT(*), on both sides. A user with two events on day 7 would otherwise count twice in the numerator and push the rate above 100%, which is the tell that the grain slipped.

In the warehouse this differs. The date math is date(first_date, '+7 days') here versus DATE_ADD or first_date + INTERVAL '7 days' in Snowflake and BigQuery, and the week bucket is DATE_TRUNC('week', first_date) rather than strftime. The discipline being tested (anchor on first activity, pin one definition, keep the full-cohort denominator) is identical in every dialect.

Sample data for this example

CREATE TABLE events (user_id INTEGER, event_date TEXT);
INSERT INTO events (user_id, event_date) VALUES
  (1, '2026-03-02'), (1, '2026-03-09'),   -- retained on day 7
  (2, '2026-03-02'),                       -- not retained (no day-7 event)
  (3, '2026-03-03'), (3, '2026-03-10');    -- retained on day 7

Worked example (SQL)

-- Same cohort, two denominators. INNER JOIN keeps only retained users, so the rate is always 100%.
WITH firsts AS (SELECT user_id, MIN(event_date) AS first_date FROM events GROUP BY user_id),
cohort AS (SELECT user_id, date(first_date, '+7 days') AS day7 FROM firsts),
day7_activity AS (
  SELECT DISTINCT c.user_id FROM cohort c JOIN events e ON e.user_id = c.user_id AND e.event_date = c.day7
)
SELECT
  COUNT(*)                                            AS full_cohort,
  COUNT(a.user_id)                                    AS retained,
  ROUND(1.0 * COUNT(a.user_id) / COUNT(a.user_id), 4) AS wrong_rate_inner_denominator,
  ROUND(1.0 * COUNT(a.user_id) / COUNT(*), 4)         AS correct_rate_full_cohort
FROM cohort c
LEFT JOIN day7_activity a ON a.user_id = c.user_id;

Apply

Your turn

The task this lesson builds to.

Write a query that returns the Day-7 retention rate per weekly signup cohort as (cohort_week, cohort_size, retained_day7, retention_rate), over events(user_id, event_date), keeping the full cohort in the denominator.

Anchor each user's cohort on the week of year, Monday-starting, of their first event (strftime('%Y-%W', MIN(event_date))). Count a user as retained if they have an event exactly 7 days after their first event. Keep every cohort member in cohort_size with a LEFT JOIN, and compute retention_rate as retained_day7 / cohort_size rounded to 4 places. Alias the columns exactly as named.

3 hints and 1 automated check are waiting in the workspace.

Practice

Make it stick

A second problem on the same idea, so it survives past today.

Write a query that returns a cohort retention triangle as (cohort_week, weeks_since_signup, retained_users) for weeks 0 through 4, over the same events(user_id, event_date) table.

Bucket each user by the week of year (Monday-starting) of their first event, compute how many whole weeks after that first event each of their events falls, and count the distinct retained users per (cohort_week, weeks_since_signup). Keep only week offsets 0 through 4. Alias the columns exactly as named.

3 hints and 1 automated check are waiting in the workspace.