Моделирование LTV игрока с учётом сессий и churn probability

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

**Контекст:** Игровая студия моделирует Lifetime Value (LTV) игроков. LTV = сумма всех покупок × вероятность остаться активным. Вероятность churn зависит от частоты сессий: если игрок не заходил > 7 дней, его churn probability = 80%; 3–7 дней = 40%; < 3 дней = 10%. Нужно рассчитать когортный LTV с учётом churn.

**Данные:**

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

**Задание:**
1. Определите когорту каждого игрока по неделе регистрации
2. Для каждого игрока рассчитайте: суммарные покупки, количество сессий, дней с момента последней сессии (inactivity_days)
3. Определите churn_probability по правилам выше
4. Рассчитайте expected_ltv = total_purchases × (1 - churn_probability) + predicted_future (средние покупки когорты × оставшийся срок)
5. Агрегируйте по когортам: средний LTV, медианный LTV, retention rate

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

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

CREATE TABLE players (
    id SERIAL PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    platform VARCHAR(20) NOT NULL,          -- 'ios', 'android', 'pc'
    registered_at TIMESTAMP NOT NULL
);

CREATE TABLE game_sessions (
    id SERIAL PRIMARY KEY,
    player_id INT REFERENCES players(id),
    started_at TIMESTAMP NOT NULL,
    ended_at TIMESTAMP NOT NULL,
    session_duration_sec INT NOT NULL
);

CREATE TABLE in_app_purchases (
    id SERIAL PRIMARY KEY,
    player_id INT REFERENCES players(id),
    item_type VARCHAR(50) NOT NULL,         -- 'gems', 'skin', 'battle_pass', 'lootbox'
    amount NUMERIC(10,2) NOT NULL,
    purchased_at TIMESTAMP NOT NULL
);

Темы

LTV когорты churn оконные функции CTE

Подсказки

• Когорта = DATE_TRUNC('week', registered_at)
• Дни неактивности = CURRENT_DATE - MAX(started_at)::DATE
• churn_probability через CASE WHEN по inactivity_days
• Для predicted_future используйте AVG покупок когорты за прошлые недели × (1 - churn_prob)

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

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

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

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

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

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

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

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

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

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

← Все задания