Привет! Отличная вакансия в банке — здесь как раз нужен баланс между техническими навыками (SQL, Python) и пониманием бизнес-логики мошенничества. Чтобы наша подготовка была максимально эффективной, давай начнем с короткого знакомства.

Какой у вас текущий опыт работы с SQL, Python и антифрод-системами, и на чем из требований вакансии хотелось бы сделать главный акцент при подготовке?

Отличный план! Пошаговый разбор — самый надежный способ уверенно пройтись по всем темам и не оставить «белых пятен» перед собеседованием. Специально под твой запрос я составил пошаговую программу подготовки, ориентированную именно на требования этой вакансии.

Давай начнем с первого практического шага: определим, какие задачи тебе уже приходилось решать на SQL и Python (пусть даже самые простые), и сталкивался ли ты ранее с понятием мошенничества или антифрод-системами на практике или в теории?

Отличный бэкграунд! Базовый SQL и Python — это прекрасный фундамент, на котором мы сейчас построим мощную базу для прохождения собеседования.

Выявление подозрительной активности с помощью WHERE и GROUP BY

На техническом интервью для фрод-аналитика вас практически гарантированно попросят написать SQL-запрос для поиска аномалий. Начнем с классического сценария: мошенники часто используют брутфорс или совершают множество мелких транзакций за короткий промежуток времени, чтобы проверить работоспособность украденной карты.

Представьте, что перед вами таблица банковских транзакций transactions со следующими полями:

• transaction_id — уникальный ID транзакции
• user_id — ID пользователя
• amount — сумма перевода (в $)
• status — статус операции (approved, declined)
• device_id — ID устройства, с которого совершен платеж

Наша первая задача — найти «серийных» плательщиков. Напишем запрос, который выведет всех пользователей (user_id), совершивших более 5 транзакций со статусом approved на общую сумму более $10,000.

SELECT 
    user_id,
    COUNT(transaction_id) AS tx_count,
    SUM(amount) AS total_amount
FROM transactions
WHERE status = 'approved'
GROUP BY user_id
HAVING COUNT(transaction_id) > 5 
   AND SUM(amount) > 10000;

Давайте разберем логику этого запроса по шагам:

1. Сначала мы отсекаем неуспешные транзакции с помощью WHERE status = 'approved'. Это критически важно, так как фрод-анализ часто разделяет успешные атаки и попытки.
2. Затем мы группируем данные по клиентам: GROUP BY user_id.
3. В финале мы отсекаем обычных клиентов с помощью конструкции HAVING, которая фильтрует уже сгруппированные данные по количеству транзакций и их сумме. Запомните: WHERE фильтрует строки до агрегации, а HAVING — группы после.

Давайте сразу проверим, как укладывается эта логика на практике. Готовы решить похожую задачу, которую часто дают на реальных собеседованиях?

Отлично! Давай сразу закрепим теорию на практике.

Практическое задание: Поиск «карточных тестировщиков»

Мошенники часто проводят кардинг-атаки: они покупают базу утекших карт и массово проверяют их на валидность мелкими покупками. Нам нужно вычислить такие карты.

У тебя есть та же таблица transactions со следующими полями:

• user_id — ID пользователя
• device_id — ID устройства
• amount — сумма транзакции (в $)
• status — статус операции (approved, declined)

Напиши SQL-запрос, который найдет все устройства (device_id), с которых было совершено более 10 транзакций со статусом declined (отклонено), при этом средняя сумма этих попыток была меньше $5.

Отличная попытка! Ты верно уловил суть: отфильтровал транзакции со статусом declined и сгруппировал их по device_id. Однако в твоем запросе есть пара критических ошибок, на которых интервьюер обязательно бы тебя поймал.

Разбор ошибок в запросе

Давай разберем два момента, которые привели бы к ошибке синтаксиса при выполнении этого запроса:

1. Вложенные агрегатные функции (avg(sum(amount))): СУБД не поймет такую конструкцию. Функция SUM(amount) считает общую сумму для всей группы, а AVG пытается найти среднее от этого единого числа. Если нам нужно найти средний размер одной транзакции в группе отклоненных, достаточно использовать просто AVG(amount).

2. Порядок выполнения операторов: В твоем запросе оператор HAVING написан до GROUP BY. В SQL порядок операторов строго регламентирован. База данных физически не может отфильтровать группы через HAVING до того, как эти группы будут созданы с помощью GROUP BY.

Пишем правильный SQL-запрос

Чтобы запрос отработал без ошибок, мы должны расположить операторы в правильном порядке и использовать корректную агрегацию для расчета средней суммы транзакции:

SELECT 
    device_id,
    COUNT(*) AS device_count,
    AVG(amount) AS avg_amount
FROM transactions
WHERE status = 'declined'
GROUP BY device_id
HAVING COUNT(*) > 10 
   AND AVG(amount) < 5;

Здесь всё логично: сначала WHERE отбирает только упавшие транзакции, затем GROUP BY объединяет их по устройствам, а в конце HAVING фильтрует получившиеся группы по нашим условиям (больше 10 транзакций и средний чек меньше $5).

Разница между WHERE и HAVING

Понимание разницы между WHERE и HAVING — это классический вопрос на собеседовании.

Основное отличие в том, когда применяется фильтрация. WHERE фильтрует сырые строки до того, как они будут объединены в группы. В нашем примере WHERE status = 'declined' отсекает успешные транзакции на самом раннем этапе, чтобы они вообще не участвовали в расчетах.

HAVING работает исключительно с результатами агрегации (уже после GROUP BY). Мы не могли бы написать WHERE COUNT(*) > 10, потому что на этапе работы WHERE база данных еще не знает, сколько всего строк приходится на каждый device_id.

С базовой фильтрацией мы отлично разобрались! Теперь давай перейдем к более мощному инструменту, который фрод-аналитики используют ежедневно для поиска сложных цепочек мошенничества — оконным функциям (Window Functions). Готов сделать этот шаг?

Отличный настрой! Оконные функции — это абсолютный маст-хэв на собеседовании. Если обычные группировки GROUP BY схлопывают строки в одну, то оконные функции позволяют заглянуть в соседние записи, сохраняя при этом всю детализацию платежей.

Анатомия окна: OVER, PARTITION BY и ORDER BY

Концептуально оконная функция делит таблицу на части («окна») и выполняет расчеты внутри них. Ее базовый синтаксис выглядит следующим образом:

Выявление аномалий: COUNT OVER и SUM OVER

Давай разберем, как это помогает ловить мошенников. Представь, что нам нужно посмотреть на каждую отдельную транзакцию, но при этом сразу видеть, сколько всего успешных платежей совершил этот пользователь за всю историю и на какую общую сумму.

SELECT 
    transaction_id,
    user_id,
    amount,
    created_at,
    -- Считаем количество транзакций внутри "окна" пользователя
    COUNT(transaction_id) OVER(PARTITION BY user_id) AS user_total_tx_count,
    -- Считаем общую сумму транзакций внутри "окна" пользователя
    SUM(amount) OVER(PARTITION BY user_id) AS user_total_spent
FROM transactions
WHERE status = 'approved'
ORDER BY user_id, created_at;

Обрати внимание: в результате выполнения этого запроса мы получим список транзакций, где у каждой строки будут колонки user_total_tx_count и user_total_spent. База данных сама посчитала эти агрегаты по каждому пользователю и «приклеила» их обратно к детальным строкам. Нам не пришлось писать сложные подзапросы или джойнить таблицу саму на себя!

Готов посмотреть, как с помощью оконных функций находить подозрительные транзакции, которые происходят слишком быстро друг за другом?

Без проблем, шпаргалка — отличный способ упорядочить знания перед собеседованием. Я подготовил для тебя компактный учебный гид по структуре и синтаксису оконных функций, который поможет быстро освежить их в памяти перед встречей с интервьюером.

Я запустил создание подробной шпаргалки. Как только она сгенерируется, она появится у тебя на экране и останется под рукой.

А пока она готовится, давай сделаем следующий важный шаг. Теперь, когда мы разобрались с тем, как делить данные на «окна», нам нужно научиться сравнивать текущую транзакцию с предыдущими. Для этого в SQL используются функции сдвига — LAG и LEAD. Именно они помогают выявлять фродовые атаки, когда мошенник пытается очень быстро списать деньги с карты несколькими платежами подряд. Хочешь разобрать, как они работают?

Разумеется, сделаем! На техническом интервью для фрод-аналитика Python чаще всего используют для автоматизации рутины, парсинга логов и первичного исследовательского анализа данных (EDA). Умение быстро набросать скрипт на pandas, чтобы проверить гипотезу по выгрузке транзакций — это огромный плюс в глазах нанимателей.

Я отправил запрос на создание шпаргалки по Python. Она будет содержать ключевые методы pandas, которые чаще всего всплывают на собеседованиях при анализе транзакционного фрода.

А сейчас давай вернемся к нашей SQL-цепочке. Мы разобрались с простым агрегированием в окнах. Теперь давай изучим мощнейший инструмент выявления моментальных серийных списаний — функции сдвига LAG и LEAD. Они позволяют сопоставить текущую строчку с предыдущей или следующей. Готов посмотреть на их синтаксис и логику работы?