Day-1 / Day-7 Retention — Solution & ExplanationProblems / Day-1 / Day-7 Retention / EditorialApproach

Normalize both sides to days. Cohort label = truncated signup time; activity = *distinct* (user, day) pairs. The dedup is load-bearing: it makes each retention join at-most-one-row, so the joins cannot fan out against each other.

Two shifted LEFT JOINs. d1.activity_day = signup_date + INTERVAL 1 DAY and d7.activity_day = signup_date + INTERVAL 7 DAY. Equality joins (not ranges) implement "exactly day N".

Count matches vs population. COUNT(DISTINCT dN.user_id) (NULLs from non-retained users drop out) over COUNT(DISTINCT c.user_id), times 100.0, rounded.

Key InsightBounded retention ("active on day N") is an equality join against a shifted key, which is both its power and its trap. Power: no window functions, no ranges — one deduped activity table serves every N. Trap: with the activity table *not* deduped, the two LEFT JOINs multiply (a user with 3 events on D1 and 2 on D7 yields 6 rows), silently corrupting COUNT(*)-style denominators. Dedupe-then-join is the discipline; DISTINCT counts everywhere are the seatbelt.Hints

Dedupe activity to distinct (user, day) before any joining — a user with three events on their D1 day must count once, and the dedup also guarantees each retention join matches at most one row.

D1 and D7 are two independent LEFT JOINs against the same daily set, with the join condition shifting the cohort date: activity_day = signup_date + 1 (or + 7).

COUNT(DISTINCT d1.user_id) counts D1 matches because unmatched LEFT JOIN rows carry NULL, and DISTINCT-count ignores NULLs. The cohort denominator needs DISTINCT too, since two LEFT JOINs can stack.

'Exactly day 1' means exactly: activity on day 6 or day 8 contributes nothing to d7_pct. The join equality (not a range) enforces it for free.

Solve Day-1 / Day-7 Retention yourself →