Когортный retention в SQL
Вопрос
Опишите, как написать в SQL когортную таблицу retention по неделям для таблиц users(id, signup_date) и visits(user_id, visit_date).
Попробуйте сами
Ответьте вслух за 2 минуты, как на собесе. Потом сверьтесь с тезисами: что прозвучало, а что нет.
Показать ответ и разбор
Коротко
Что должно прозвучать в ответе
Когорта: неделя регистрации через date_trunc
Номер недели жизни: (visit_date − signup_date) / 7
Уникальные пользователи по когорте и неделе
Размер когорты отдельно, деление для доли
Ограничение по неполным неделям у свежих когорт
Разворот в таблицу в BI или через FILTER
Что могут спросить следом
- Почему делить на 7 по дате регистрации, а не по календарной неделе?
- Как не показывать неполные ячейки?
Разбор
Для каждого визита считаю когорту date_trunc('week', signup_date) и номер недели жизни (visit_date - signup_date) / 7, целочисленное деление тут как раз нужно. Группирую по когорте и номеру недели и считаю COUNT(DISTINCT user_id). Отдельно считаю размер когорты из users и делю, получая долю. Ячейки, для которых неделя ещё не закончилась, отсекаю условием, что последний день недели меньше текущей даты данных. Разворачиваю в таблицу либо в BI, либо через COUNT(DISTINCT user_id) FILTER (WHERE week = 1) и так далее для нужного числа недель.
Ответьте сами, а не прочитайте
В тренажёре вопрос попадёт в повторения и вернётся, когда начнёт забываться. Плюс билеты с зачётом: 20 вопросов, не больше 2 ошибок.
Ещё по теме «SQL для продуктовых метрик»
- Разность дат
- Первый заказ пользователя
- FILTER в агрегатах
- Дни без событий
- Целочисленное деление
- COUNT после JOIN
- LAG
- Потерянный знаменатель