Vintage analysis просрочек кредитного портфеля

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

**Контекст:** Банк проводит vintage analysis кредитного портфеля. Для каждой когорты выдачи (месяц выдачи кредита) нужно отследить, какая доля кредитов выходит в просрочку > 90 дней (NPL — Non-Performing Loans) по мере «старения» когорты. Это позволяет сравнивать качество андеррайтинга в разные периоды.

**Данные:**

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

**Задание:**
1. Определите когорту как месяц выдачи кредита (issued_at)
2. Для каждого кредита в каждом месяце жизни (MOB — Months on Books: 1, 2, 3, ..., 12) определите максимальное количество дней просрочки (DPD — Days Past Due)
3. Постройте vintage-матрицу: для каждой когорты и MOB покажите долю кредитов с DPD > 90 (NPL rate)
4. Добавьте кумулятивный NPL rate (доля кредитов, которые хоть раз побывали в DPD > 90)

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

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

CREATE TABLE loans (
    id SERIAL PRIMARY KEY,
    borrower_id INT NOT NULL,
    loan_amount NUMERIC(12,2) NOT NULL,
    interest_rate NUMERIC(5,2) NOT NULL,
    term_months INT NOT NULL,
    issued_at DATE NOT NULL,
    product_type VARCHAR(50) NOT NULL       -- 'consumer', 'mortgage', 'auto', 'credit_card'
);

CREATE TABLE loan_payments (
    id SERIAL PRIMARY KEY,
    loan_id INT REFERENCES loans(id),
    due_date DATE NOT NULL,
    paid_date DATE,                          -- NULL если не оплачен
    amount_due NUMERIC(10,2) NOT NULL,
    amount_paid NUMERIC(10,2) DEFAULT 0
);

Темы

vintage analysis когорты CROSS JOIN кредиты NPL

Подсказки

• DPD = paid_date - due_date для оплаченных; CURRENT_DATE - due_date для неоплаченных (если due_date в прошлом)
• MOB = количество полных месяцев от issued_at до due_date
• generate_series(1, 12) для создания сетки MOB, если в каком-то месяце нет данных
• Кумулятивный NPL: если кредит хоть раз был DPD > 90, он остаётся NPL в последующих MOB

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

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

Какой уровень знаний нужен для задачи "Vintage analysis просрочек кредитного портфеля"?

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

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

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

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

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

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

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

← Все задания