Самый прибыльный фильм каждой категории

Задача

Продакт хочет по одному «локомотиву» на жанр: «В каждой категории найди фильм, который принёс больше всех денег. Не топ-3, а именно чемпион жанра — один фильм на категорию».

Верни название категории, название фильма-чемпиона и его выручку.

Схема данных

Учебная база проката dvdrental. Деньги и жанр разнесены по цепочке:

• payment(payment_id, rental_id, amount, ...) — платежи.
• rental(rental_id, inventory_id, ...) и inventory(inventory_id, film_id, ...) — аренды и копии.
• film(film_id, title, ...) — фильмы.
• film_category(film_id, category_id) и category(category_id, name) — жанры.

Решение

WITH film_rev AS (
  SELECT fc.category_id, f.title,
         sum(p.amount) AS revenue
  FROM payment p
  JOIN rental r USING (rental_id)
  JOIN inventory i USING (inventory_id)
  JOIN film f USING (film_id)
  JOIN film_category fc USING (film_id)
  GROUP BY fc.category_id, f.film_id, f.title
)
SELECT cat.name AS category, fr.title, fr.revenue
FROM film_rev fr
JOIN category cat USING (category_id)
WHERE fr.revenue = (
  SELECT max(fr2.revenue)
  FROM film_rev fr2
  WHERE fr2.category_id = fr.category_id
)
ORDER BY cat.name, fr.revenue DESC, fr.title;

Разбор

Задача «максимум по группе, но с деталями строки». Обычный GROUP BY с MAX(revenue) дал бы максимальную выручку по жанру, но не сказал бы, какой это фильм — агрегат теряет название. Нужен приём, который и найдёт максимум внутри жанра, и вернёт строку, где он достигнут.

Считаем в два хода. CTE film_rev собирает выручку каждого фильма (та же цепочка джойнов, что и в других «денежных» задачах) — получается таблица «фильм, его жанр, его выручка». Дальше основной запрос оставляет из неё только те строки, где выручка фильма равна максимуму по его жанру. Максимум добывает коррелированный подзапрос: (SELECT max(fr2.revenue) FROM film_rev fr2 WHERE fr2.category_id = fr.category_id).

Слово «коррелированный» — ключевое. Подзапрос ссылается на внешнюю строку через fr.category_id: для каждого фильма он пересчитывает максимум заново, но только по фильмам того же жанра. То есть подзапрос выполняется как бы «для каждой строки» и знает, к какой категории она относится. Это отличает его от обычного подзапроса из задачи про средние траты, где подзапрос считался один раз и не зависел от внешней строки. Разница «CTE против подзапроса против временной таблицы» — в вопросе подзапрос, CTE или временная таблица.

Про равенство revenue = max(...): если в жанре два фильма набрали ровно одинаковую максимальную выручку, вернутся оба — это честно, «чемпионов» действительно двое. ORDER BY cat.name, fr.revenue DESC, fr.title держит порядок детерминированным даже в таком случае.

Тот же «топ в жанре», но сразу по три фильма и через оконную функцию, — в задаче топ-3 фильма по выручке в каждой категории. Полезно сравнить два подхода к одной задаче.

Ожидаемый результат

category | title | revenue
--- | --- | ---
Action | Fool Mockingbird | 162.79
Animation | Dogma Family | 168.72
Children | Backlash Undefeated | 140.84
Classics | Steel Santa | 136.78
Comedy | Zorro Ark | 199.72
Documentary | Wife Turn | 198.73
Drama | Harry Idaho | 177.73
Family | Apache Divine | 160.72
Foreign | Innocent Usual | 191.74
Games | Massacre Usual | 149.75
… всего строк: 16 (показаны первые 10)