SQL — топ-10 неблагонадёжных пользователей библиотеки

Условие

Схема библиотеки: Произведение, Издание, Экземпляр, Лог_операций(id, id_user, id_экземпляр, дата_взяли, дата_вернули).

Сделать рейтинг 10 самых неблагонадёжных пользователей по двум или более критериям. Критерии нужно предложить самостоятельно с точки зрения бизнеса.

Решение

Шаг 1. Что такое «неблагонадёжный»

С точки зрения библиотеки это тот, кто:

1. Не возвращает книги — есть записи с дата_вернули IS NULL и от выдачи прошло слишком много времени (> N дней, например 60).
2. Возвращает с большой просрочкой — дата_вернули - дата_взяли > N.
3. Систематически просрочивает — высокий процент его выдач возвращён с просрочкой.
4. Никогда не возвращает — общая доля невозвращённых книг.
5. (Опционально) Берёт редкие/ценные экземпляры, которые тяжело восстановить — но без таблицы стоимости/тиража это спекуляция.

Берём минимум два критерия: «открытых просрочек > 60 дней» и «доля просрочек среди возвращённых».

Шаг 2. Нормализация и агрегирование

Каждый критерий измеряется в своих единицах. Чтобы суммировать в общий «индекс неблагонадёжности», переводим каждый в z-score или ранг и суммируем.

Код

WITH per_user AS (
  SELECT
    l.id_user,
    COUNT(*)                                                       AS total_takings,
    SUM(CASE WHEN l.дата_вернули IS NULL
              AND CURRENT_DATE - l.дата_взяли > 60 THEN 1 ELSE 0 END)
                                                                   AS overdue_open,   -- открытые > 60 дней
    SUM(CASE WHEN l.дата_вернули IS NOT NULL
              AND l.дата_вернули - l.дата_взяли > 30 THEN 1 ELSE 0 END)
                                                                   AS overdue_returned, -- возвращены с просрочкой > 30
    SUM(CASE WHEN l.дата_вернули IS NULL THEN 1 ELSE 0 END)        AS not_returned_yet,
    AVG(CASE WHEN l.дата_вернули IS NOT NULL
              THEN l.дата_вернули - l.дата_взяли END)              AS avg_keep_days
  FROM Лог_операций l
  GROUP BY l.id_user
),
scored AS (
  SELECT
    id_user,
    total_takings,
    overdue_open,
    overdue_returned,
    not_returned_yet,
    avg_keep_days,
    -- Доли
    overdue_returned * 1.0 / NULLIF(total_takings - not_returned_yet, 0) AS share_late_returns,
    not_returned_yet * 1.0 / NULLIF(total_takings, 0)                    AS share_open,
    -- Ранги: чем больше плохого — тем выше ранг
    RANK() OVER (ORDER BY overdue_open DESC)        AS r_overdue_open,
    RANK() OVER (ORDER BY overdue_returned DESC)    AS r_overdue_returned
  FROM per_user
  WHERE total_takings >= 5  -- отсеиваем «случайных» с 1 взятием
)
SELECT
  id_user,
  total_takings,
  overdue_open,
  overdue_returned,
  share_late_returns,
  share_open,
  (r_overdue_open + r_overdue_returned) AS reliability_score   -- меньше = хуже
FROM scored
ORDER BY reliability_score ASC
LIMIT 10;

Шаг 3. Альтернатива — индекс через нормировку

Вместо суммы рангов — z-score или min-max нормировка по каждому критерию, потом взвешенная сумма с весами от бизнеса (например, «открытая просрочка» в 2 раза хуже «возвращённой с задержкой»).

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

1. Минимальный порог активности. Без total_takings ≥ 5 в топ-10 попадут случайные люди с одной невозвращённой книгой — это не «неблагонадёжность», а статистический шум.
2. Только один критерий. Условие явно требует «по двум или более». Просто ORDER BY overdue_open DESC не зачёт.
3. Деление на ноль. share_late_returns без NULLIF упадёт у пользователей, у которых все книги ещё не возвращены.
4. Сравнивать абсолюты. У активного читателя 3 просрочки из 100 — нормально. У редкого 3 из 5 — он реально проблемный. Поэтому нужно смешивать счётчики и доли.
5. CURRENT_DATE в Hive. В разных диалектах нужно CURRENT_DATE, CURDATE() или getdate(). Уточняйте.
6. Что значит «просрочка». Без таблицы политики библиотеки граница «30 дней» — допущение. На собеседовании надо явно проговорить, что критерии и пороги — это бизнес-договорённость.

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

Считаем по пользователю минимум 2 метрики (например, «открытых просрочек > 60 дней» и «возвращённых с просрочкой > 30 дней»), отсекаем малоактивных, ранжируем каждый критерий и берём сумму рангов как индекс неблагонадёжности. ТОП-10 — ORDER BY total_rank ASC LIMIT 10. Веса критериев и пороги — бизнес-решение, на собеседовании их нужно явно проговорить.