Топ-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.film_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
),
ranked AS (
  SELECT film_rev.*,
         row_number() OVER (PARTITION BY category_id ORDER BY revenue DESC, film_id) AS rn
  FROM film_rev
)
SELECT cat.name AS category, ranked.title, ranked.revenue
FROM ranked
JOIN category cat USING (category_id)
WHERE ranked.rn <= 3
ORDER BY cat.name, ranked.rn;

Разбор

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

Считаем в два этапа через WITH. Первый CTE film_rev — обычная агрегация: собираем выручку каждого фильма (та же цепочка джойнов payment → … → film_category, что и в группировке по жанрам), группируем по фильму и категории. Второй CTE ranked навешивает окно: row_number() OVER (PARTITION BY category_id ORDER BY revenue DESC) внутри каждого жанра раздаёт фильмам номера 1, 2, 3, … по убыванию выручки. PARTITION BY — это как GROUP BY, только строки не схлопываются: у каждой остаётся её номер. Что вообще такое окно и чем оно отличается от группировки — в вопросе что такое оконные функции.

Дальше остаётся WHERE rn <= 3 — оставить первую тройку каждого жанра. Важно: фильтровать по rn в том же SELECT, где его посчитали, нельзя — окно вычисляется после WHERE, поэтому номер прячем в CTE и фильтруем уже снаружи.

Почему ROW_NUMBER, а не RANK? ROW_NUMBER всегда даёт ровно три строки, даже если у двух фильмов выручка совпала (их развёл tie-break film_id). RANK при равенстве выдал бы обоим одно место и мог вернуть больше трёх. Разницу разбираем в вопросе ROW_NUMBER, RANK и DENSE_RANK.

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

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

category | title | revenue
--- | --- | ---
Action | Fool Mockingbird | 162.79
Action | American Circus | 146.81
Action | Minds Truman | 144.81
Animation | Dogma Family | 168.72
Animation | Sunrise League | 155.78
Animation | Titanic Boondock | 149.78
Children | Backlash Undefeated | 140.84
Children | Heartbreakers Bright | 132.80
Children | Sabrina Midnight | 126.79
Classics | Steel Santa | 136.78
… всего строк: 48 (показаны первые 10)