N-day vs rolling retention — write both and show where the numbers diverge
Tables users(user_id, signup_date) and activity(user_id, activity_date). Write both day-7 retention definitions: N-day (bracket) retention counts users active exactly on day 7; rolling (unbounded) retention counts users active on day 7 or any later day. Return each as a percent of the cohort.
-- d7 bracket: active exactly on day 7; d7 rolling: active on day 7 or laterWrite both queries.
Bracket retention matches activity_date = signup_date + 7; rolling matches activity_date >= signup_date + 7. Both divide distinct matched users by the cohort. Rolling is always at least bracket — a user active on day 10 but not day 7 passes rolling yet fails the exact-day test.
- ✗Swapping which definition uses the exact-day vs the on-or-after predicate
- ✗Assuming both definitions yield the same retention number
- ✗Reading rolling as activity up to day 7 rather than day 7 onward
- →Why is rolling retention always greater than or equal to bracket retention?
- →Which definition better reflects a product used only a few times a month?
The two definitions differ by one operator, and that operator is the whole point. Bracket (N-day) retention asks "active on the anniversary day"; rolling (unbounded) retention asks "active on or after it — i.e. still around":
-- Day-7 bracket (N-day) retention: active exactly on day 7
SELECT ROUND(100.0 * COUNT(DISTINCT a.user_id) / COUNT(DISTINCT u.user_id), 2) AS d7_bracket_pct
FROM users u
LEFT JOIN activity a
ON a.user_id = u.user_id
AND a.activity_date = u.signup_date + 7;
-- Day-7 rolling (unbounded) retention: active on day 7 or any later day
SELECT ROUND(100.0 * COUNT(DISTINCT a.user_id) / COUNT(DISTINCT u.user_id), 2) AS d7_rolling_pct
FROM users u
LEFT JOIN activity a
ON a.user_id = u.user_id
AND a.activity_date >= u.signup_date + 7;Rolling is always >= bracket: the exact-day predicate is a strict subset of the on-or-after predicate. They diverge most for products with infrequent, bursty usage — a user who returns on day 12 but skipped day 7 is retained under rolling and lost under bracket. Bracket suits daily-habit products (was the habit alive on the day?); rolling suits low-frequency products where "hasn't churned yet" is the honest question. Both are anchored per signup cohort, which is why they are read off a cohort's signup_date.
