Собеседование на Middle-senior Аналитик Данных в Точка Банк

Сверка таблиц

Исходные данные 3 таблицы со следующими полями:

balances — данные об остатках баллов клиентов на начало каждого дня (CODE, BAL_DATE, VALUE)
benefit_points — данные о всех транзакциях начислений и трат баллов (CODE, DIRECTION [1 — начисление, 0 — трата], CREATED_AT, CUST_SUM)
calendar — календарь (START_DATE)

1. Задача: Написать SQL-запрос, который будет проверять, что таблицы benefit_balances и benefit_points согласованы между собой, т.е. для каждого клиента и всегда выполняется условие Остаток(t) = Остаток(t-1) + Начисления(t-1) — Траты(t-1)

WITH daily_ops AS (
    -- Агрегация оборотов за каждый день
    SELECT
        CODE,
        CREATED_AT::date AS op_date,
        SUM(CASE WHEN DIRECTION = 1 THEN CUST_SUM ELSE 0 END) AS accruals,
        SUM(CASE WHEN DIRECTION = 0 THEN CUST_SUM ELSE 0 END) AS expenses
    FROM benefit_points
    GROUP BY CODE, CREATED_AT::date
),
balance_check AS (
    SELECT
        b.CODE,
        b.BAL_DATE,
        b.VALUE AS current_balance,
        -- Получаем баланс предыдущего дня внутри партиции клиента
        LAG(b.VALUE) OVER (PARTITION BY b.CODE ORDER BY b.BAL_DATE) AS prev_balance,
        b.BAL_DATE - LAG(b.BAL_DATE) OVER (PARTITION BY b.CODE ORDER BY b.BAL_DATE) AS date_diff,
        COALESCE(op.accruals, 0) AS accruals,
        COALESCE(op.expenses, 0) AS expenses
    FROM balances b
    LEFT JOIN daily_ops op
        ON b.CODE = op.CODE
        AND op.op_date = b.BAL_DATE - INTERVAL '1 day'
)
SELECT
    *,
    (prev_balance + accruals - expenses) AS calculated_balance
FROM balance_check
WHERE prev_balance IS NOT NULL
  -- Проверяем только если предыдущая запись была ровно вчера (разница 1 день)
  AND date_diff = 1
  -- Условие расхождения
  AND current_balance != (prev_balance + accruals - expenses);

Переносы встреч

Исходные данные Таблица meeting — данные о назначенных встречах:

lidID — идентификатор будущего клиента
meetID — идентификатор назначенной встречи
meetdate — время встречи
changedate — время проставления встречи
status — статус встречи (null, ‘отмена’, ‘перенесена по инициативе банка’, ‘перенесена по инициативе клиента’, ‘проведена’)

Задача 2

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

Ответ

Bank Reschedule Rate (BRR) — доля встреч, перенесенных по инициативе банка. Алгоритм расчета:

Считаем количество событий со статусом «перенесена по инициативе банка».
Делим на общее число валидных встреч (все встречи минус отмены).

SELECT
    DATE_TRUNC('month', changedate)::date AS report_month,

    -- Число переносов банком
    COUNT(CASE WHEN status = 'перенесена по инициативе банка' THEN 1 END) AS bank_reschedules,

    -- Общее число валидных попыток встреч (исключаем отмены, так как по ним работа не велась)
    COUNT(CASE WHEN status IS DISTINCT FROM 'отмена' THEN 1 END) AS total_valid_meetings,

    -- Метрика в %
    ROUND(
        COUNT(CASE WHEN status = 'перенесена по инициативе банка' THEN 1 END) * 100.0
        / NULLIF(COUNT(CASE WHEN status IS DISTINCT FROM 'отмена' THEN 1 END), 0),
        2
    ) AS brr_percentage

FROM meeting
GROUP BY 1
ORDER BY 1;

Нагрузка на рекрутеров

Задача 3

Составить план действий, предложить методику оценки нагруженности рекрутеров и перераспределения задач между ними

Ответ

Методика оценки: Балльная система оценки, где каждому типу активности присваивается вес в часах:

Собеседование (проведение + фидбек) — 1.5 ч
Скрининг резюме (пакет 10 шт.) — 1.0 ч
Ведение внутреннего проекта — 2.0 — 5.0 ч (фикс в неделю)
Синки и командные встречи — по календарю

Алгоритм перераспределения:

Сбор факта: Выгрузка данных из ATS (число кандидатов) и календарей.
Расчет Utilization Rate: (Сумма часов / 40) * 100%.
Матрица решений:
- < 75% (Зеленая зона): Назначаем новые вакансии/проекты.
- 75-90% (Желтая зона): Оптимальная нагрузка, мониторинг.
- 90% (Красная зона): Стоп на новые задачи, перераспределение скрининга на менее загруженных коллег.

Дополнительно

Кандидат выполнил тестовое задание для Точка Банка, после чего был приглашен на техническое собеседование.