Сложная аналитика: LTV когорт с учётом churn и revenue expansion

Условие задания

**Контекст:** SaaS-платформа рассчитывает LTV (Lifetime Value) по когортам с учётом оттока, апгрейдов и даунгрейдов тарифов. Необходим полный многошаговый анализ.

**Данные:**

[см. код в задании]

**Задание:**

Для каждой когорты (месяц регистрации) за последние 12 месяцев рассчитайте:
1. Размер когорты (количество пользователей)
2. Для каждого прожитого месяца (0, 1, 2, ..., до 6):
- Количество активных подписчиков
- Суммарный MRR
- Кумулятивный revenue (суммарный LTV когорты)
3. Retention rate (% оставшихся) и revenue retention rate (% от начального MRR)
4. Разбивку MRR на: new, expansion (апгрейды), contraction (даунгрейды), churn

Пример данных

Структура для ориентира — реальные значения из эталонного решения.

CREATE TABLE subscriptions (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    plan VARCHAR(50) NOT NULL,            -- 'starter', 'pro', 'enterprise'
    mrr NUMERIC(10,2) NOT NULL,           -- Monthly Recurring Revenue
    started_at DATE NOT NULL,
    ended_at DATE,                        -- NULL = активная подписка
    end_reason VARCHAR(50)                -- 'churn', 'upgrade', 'downgrade', NULL
);

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    registered_at TIMESTAMP NOT NULL,
    acquisition_channel VARCHAR(50) NOT NULL  -- 'organic', 'paid', 'referral'
);

Темы

LTV когорты churn expansion revenue многошаговая аналитика

Подсказки

• Определите когорту каждого пользователя через DATE_TRUNC('month', registered_at)
• Для каждого месяца жизни когорты определите статус подписки: active / churned / upgraded / downgraded
• generate_series(0, 6) для месяцев жизни когорты
• Подписка активна в месяц M, если started_at <= M и (ended_at IS NULL или ended_at > M)

Все тестовые задания →

Частые вопросы

Какой уровень знаний нужен для задачи "Сложная аналитика: LTV когорт с учётом churn и revenue expansion"?

Это задание для уровня Senior. Senior-уровень — глубокое понимание темы, опыт решения нестандартных задач, обсуждение trade-off на собеседовании.

На каких собеседованиях встречается такая задача?

Подобные задания в категории «SQL» регулярно дают на собеседованиях аналитика данных в Яндекс, Сбер, Ozon, Авито, Тинькофф, Wildberries, T-Bank, X5, ВТБ и других крупных IT-компаниях. Тематика: LTV, когорты, churn, expansion revenue, многошаговая аналитика.

Сколько времени даётся на решение?

На реальном собеседовании на подобную задачу отводится 30-60 минут с обсуждением подходов, оптимизаций и trade-off. Для тренировки рекомендуем сначала решить самостоятельно, потом сверить с эталонным решением и подсказками.

Где ещё потренироваться по теме «SQL»?

На zasqlpython.ru есть 425 SQL задач в песочнице с автопроверкой кода, конспекты SQL для аналитика, AI мок-собеседование с разбором ваших ответов.

← Все задания