Retention triangle (D1/D7/D30)

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

Таблицы `users(id, signup_date)` и `events(user_id, event_date)`. Для каждой недели регистрации (cohort) посчитай retention в трёх точках: ровно через 1 день (D1), 7-й день (D7), 30-й день (D30) — события в эти конкретные дни. Колонки: `cohort_week`, `cohort_size`, `d1`, `d7`, `d30`. Каждое retention — доля от cohort_size (round 2). Сортировка по `cohort_week`.

Схема данных

CREATE TABLE users (id INTEGER PRIMARY KEY, signup_date TEXT);
CREATE TABLE events (user_id INTEGER, event_date TEXT);

Темы

cohort retention conditional-aggregation

Подсказки

• Считаем дни от signup через `julianday(event_date) - julianday(signup_date)`.
• Для каждого дня (1, 7, 30) — `COUNT(DISTINCT CASE WHEN day = X THEN user_id END)`.
• LEFT JOIN — чтобы пустые cohort показали 0% retention.

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

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

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

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

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

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