N-дневный vs rolling retention — напишите оба и покажите, где числа расходятся
Таблицы users(user_id, signup_date) и activity(user_id, activity_date). Напишите оба определения retention дня 7: N-дневный (bracket) считает активных ровно в день 7; rolling (unbounded) считает активных в день 7 или любой позже. Верните каждое как процент от когорты.
-- d7 bracket: активность ровно в день 7; d7 rolling: в день 7 или позжеНапишите оба запроса.
Bracket сопоставляет activity_date = signup_date + 7; rolling — activity_date >= signup_date + 7. Оба делят уникальных совпавших на когорту. Rolling всегда не меньше bracket: активный в день 10, но не в день 7, проходит rolling, но не точный день.
- ✗Путать, какое определение берёт точный день, а какое день-и-позже
- ✗Считать, что оба определения дают одно число retention
- ✗Читать rolling как активность до дня 7, а не с дня 7 и далее
- →Почему rolling retention всегда не меньше bracket retention?
- →Какое определение лучше для продукта, которым пользуются пару раз в месяц?
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.
