Retention D1, D7, D30 для когорты

Условие задачи

Таблицы users (id, registered_at TEXT) и activity (id, user_id, activity_date TEXT). Для каждой когорты (месяц регистрации) посчитайте retention D1, D7 и D30 в процентах. D1 — вернулся на 1-й день, D7 — на 7-й, D30 — на 30-й (точно в этот день). Выведите cohort_month, cohort_size, ret_d1, ret_d7, ret_d30.

Схема данных

CREATE TABLE users (id INTEGER PRIMARY KEY, registered_at TEXT);
CREATE TABLE activity (id INTEGER PRIMARY KEY, user_id INTEGER, activity_date TEXT);

Темы

JULIANDAY CASE WHEN COUNT DISTINCT LEFT JOIN RETENTION

Подсказки

• Используй CAST(julianday(d2) - julianday(d1) AS INTEGER) для целочисленной разницы дней
• Три отдельных CASE WHEN внутри COUNT(DISTINCT) для каждого дня retention

Что проверяет эта задача

Задача «Retention D1, D7, D30 для когорты» учит считать удержание (retention) когортным методом на чистом SQL. Такие задачи регулярно дают на собеседованиях аналитиков данных. Уровень сложный — задача проверяет глубину и умение собрать запрос из нескольких приёмов.

Как подступиться к решению

Сначала разберись со схемой данных и пойми, какой результат нужен на выходе. Определи, какие таблицы и поля задействованы, и собирай запрос пошагово: фильтрация → соединение или группировка → итоговый отбор и сортировка. Проверь результат на крайних случаях — NULL, дубликаты, пустые группы. Свой запрос сразу прогоняешь автопроверкой в интерактивной SQL-песочнице на реальной базе (SQLite и PostgreSQL 16 прямо в браузере).

Разбор темы с примерами — в гайде «Retention-анализ в SQL». Хочешь системно с нуля — пройди бесплатный курс «SQL с нуля».

Решить в интерактивной SQL-песочнице → ← Все SQL-задачи