Центр-Инвест — выборка телефонов клиентов с положительной кредитной историей

Условие

Бизнес просит сделать рассылку клиентам банка с положительной кредитной историей.

Таблицы:

• loans(loan_id, debtor_id, status, ...) — кредитные договоры.
• debtors(debtor_id, phone, name, ...) — заёмщики.
• sap_choice(record_id, etalon_id) — связи: одна запись клиента может иметь несколько record_id, но etalon_id — актуальные данные. Отношение etalon : records = 1 : N.
• uslacc(loan_id, scode, ...) — счета, связанные с кредитным договором.
• spracc(scode, scode_desc) — справочник видов счетов.

Часть А (простая): выборка телефонов клиентов по закрытым договорам.

Часть Б (сложнее): расширить — данные клиентов актуальные (через etalon в sap_choice) и у договора нет просрочек (сигнал — наличие в uslacc счёта определённого scode).

Часть В (тестирование): для каждой задачи продумать ошибки и составить тест-кейсы (1 для А, 3 для Б).

Решение

Часть А

SELECT DISTINCT d.phone
FROM loans l
JOIN debtors d ON d.debtor_id = l.debtor_id
WHERE l.status = 'closed';

Часть Б

Шаги:

1. Получить актуальные debtor_id через sap_choice (resolve to etalon).
2. Исключить договоры с просрочками: договор не должен иметь записей в uslacc с overdue-scode.

WITH overdue_codes AS (
  SELECT scode FROM spracc WHERE scode_desc ILIKE '%просроч%'
),
loans_with_overdue AS (
  SELECT DISTINCT u.loan_id
  FROM uslacc u
  JOIN overdue_codes oc ON oc.scode = u.scode
),
etalon AS (
  SELECT record_id, etalon_id FROM sap_choice
)
SELECT DISTINCT d.phone
FROM loans l
JOIN etalon e ON e.record_id = l.debtor_id
JOIN debtors d ON d.debtor_id = e.etalon_id
WHERE l.status = 'closed'
  AND l.loan_id NOT IN (SELECT loan_id FROM loans_with_overdue);

Тест-кейсы

Часть А (1 тест-кейс):

• TC1: «Клиент с одним закрытым договором» — должен попасть в выборку с одним телефоном.

Часть Б (3 тест-кейса):

• TC1: Клиент с дубликатами в debtors, актуальная запись через sap_choice — должен возвращаться актуальный телефон.
• TC2: Клиент с просрочкой по другому договору, но без просрочки по analyzed — должен попасть.
• TC3: Клиент с просрочкой по analyzed договору — НЕ должен попасть.

Доп: edge cases:

• Клиент без sap_choice (одиночная запись).
• Договор с status = NULL.
• Просроченный счёт, который уже закрыт.

Подводные камни

1. sap_choice 1:N: один etalon на много records. Запрос должен резольвить через etalon, не наоборот.
2. Просрочка = «наличие счёта определённого scode». Какой именно scode — нужно понять из spracc.scode_desc. Все scode с описанием «просрочка», «просроченный», и т.д.
3. Закрытие договора без полной выплаты: статус closed может быть «списан». Тогда это не «положительная история». Уточняйте.
4. Дубли телефонов: один клиент имеет несколько договоров → SELECT DISTINCT обязательно.
5. Phones: формат может быть +7..., 8..., без префикса. Нормализуйте перед рассылкой.
6. Sentitive data: PII требует согласия на коммуникацию — фильтруйте по subscribed_to_promo = true.

Эталонный ответ

(А) Простой JOIN loans + debtors WHERE status = 'closed'. (Б) CTE с overdue codes, etalon resolution, NOT IN по loans_with_overdue. (В) Test-cases на normal flow, дубликаты-resolve, просрочка по другому договору, edge cases.