Условие

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

Для каждого пользователя нужно вывести последние три взятых им произведения (не экземпляра!) и для каждого такого произведения дополнительно показать, сколько всего раз его брали (по всем пользователям, за всё время).

Решение

Идея

Из Лог_операций через Экземпляр → Издание дотянуть id_произведения.
Для каждого (user, произведение) — взять последнюю дату выдачи.
Внутри пользователя ранжировать произведения по этой дате DESC и оставить top-3.
Параллельно посчитать общий счётчик «сколько раз произведение брали за всё время» — оконной агрегацией или отдельной CTE.

«Последние 3 произведения» подразумевает: даже если пользователь брал одно и то же произведение пять раз подряд — оно должно считаться один раз.

Код

WITH log_with_work AS (
  SELECT l.id_user,
         p.id   AS id_произведения,
         l.дата_взяли
  FROM Лог_операций l
  JOIN Экземпляр    e ON e.id = l.id_экземпляр
  JOIN Издание      i ON i.id = e.id_издание
  JOIN Произведение p ON p.id = i.id_произведения
),
work_total AS (
  -- сколько всего раз каждое произведение брали (по всем пользователям)
  SELECT id_произведения,
         COUNT(*) AS total_takings
  FROM log_with_work
  GROUP BY id_произведения
),
user_last AS (
  -- последняя дата взятия конкретного произведения конкретным пользователем
  SELECT id_user,
         id_произведения,
         MAX(дата_взяли) AS last_taken
  FROM log_with_work
  GROUP BY id_user, id_произведения
),
ranked AS (
  SELECT id_user,
         id_произведения,
         last_taken,
         ROW_NUMBER() OVER (PARTITION BY id_user ORDER BY last_taken DESC) AS rn
  FROM user_last
)
SELECT r.id_user,
       r.id_произведения,
       r.last_taken,
       wt.total_takings
FROM ranked r
JOIN work_total wt USING (id_произведения)
WHERE r.rn <= 3
ORDER BY r.id_user, r.rn;

Альтернатива без CTE

В Hive / BigQuery можно через QUALIFY:

SELECT id_user, id_произведения, last_taken, total_takings
FROM (
  SELECT id_user, id_произведения,
         MAX(дата_взяли) AS last_taken,
         COUNT(*) OVER (PARTITION BY id_произведения) AS total_takings
  FROM log_with_work
  GROUP BY id_user, id_произведения
)
QUALIFY ROW_NUMBER() OVER (PARTITION BY id_user ORDER BY last_taken DESC) <= 3;

Сложность

Один проход по логу с тремя JOIN — линейно.
Оконные функции с PARTITION BY id_user — O(N log N) на сортировку внутри партиции.

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

Экземпляр vs произведение. Лог ссылается на экземпляр, не на произведение. Без двух JOIN получим «последние 3 экземпляра», а вопрос про произведения — это другое.
Один и тот же тайтл, два разных издания. «Война и мир» в твёрдой обложке и в мягкой — два разных издания, но одно произведение. По условию считаем именно произведение.
RANK vs ROW_NUMBER. Если две выдачи в одну секунду — RANK оставит обе на одной позиции, и в top-3 окажется 4 строки. Лучше ROW_NUMBER с детерминированным tie-break (ORDER BY last_taken DESC, id_произведения).
«Брали» — это дата_взяли, а не вернули. Если человек ещё держит книгу, дата_вернули = NULL, но запись в логе уже есть.
total_takings для не топ-3. Общий счётчик считается по всему логу, а не по top-3 пользователей. Внимательно с CTE.
Производительность на гигантском логе. Оконные функции с PARTITION BY id_user могут дать перекос: предварительная агрегация в user_last снижает объём входа в ROW_NUMBER.

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

Цепочка: Лог → Экземпляр → Издание → Произведение, агрегация до (user, work, MAX(date)), ROW_NUMBER() по пользователю + JOIN с глобальным счётчиком взятий произведения. Возвращаем строки с rn ≤ 3.

SQL — последние 3 произведения на пользователя со счётчиком всех взятий