Выручка по категориям фильмов

Задача

Руководитель контента решает, какие жанры продвигать: «Дай выручку по категориям фильмов — сколько денег принёс каждый жанр за всё время. Отсортируй от самого прибыльного».

Верни название категории и суммарную выручку по ней.

Схема данных

Учебная база проката dvdrental. Деньги и жанр лежат в разных концах базы, между ними — цепочка связей:

• payment(payment_id, rental_id, amount, ...) — платежи; amount — сумма.
• rental(rental_id, inventory_id, ...) — аренды; платёж ссылается на аренду.
• inventory(inventory_id, film_id, ...) — физические копии дисков; аренда ссылается на копию.
• film_category(film_id, category_id) — какой фильм к какой категории относится.
• category(category_id, name) — справочник жанров.

Решение

SELECT cat.name AS category,
       sum(p.amount) AS revenue
FROM payment p
JOIN rental r USING (rental_id)
JOIN inventory i USING (inventory_id)
JOIN film_category fc USING (film_id)
JOIN category cat USING (category_id)
GROUP BY cat.name
ORDER BY revenue DESC;

Разбор

Деньги (payment.amount) и жанр (category.name) в этой базе разнесены далеко, и связать их напрямую нельзя — приходится строить цепочку из четырёх JOIN. Двигаемся по ссылкам: платёж знает свою аренду (rental_id), аренда — какую копию диска выдали (inventory_id), копия — какой это фильм (film_id), фильм через film_category получает category_id, а тот — имя жанра. Каждое звено соединяем по общему ключу; USING (rental_id) — это короткая запись для ON p.rental_id = r.rental_id, когда колонка называется одинаково в обеих таблицах. Разные типы соединений и когда какой брать — в вопросе про виды JOIN.

Когда цепочка собрана, GROUP BY cat.name раскладывает все платежи по жанрам, а SUM(p.amount) складывает деньги внутри каждой группы. Важно: суммируем именно после того, как размножили строки джойнами — каждая строка результата соединения несёт один платёж и его жанр, так что двойного счёта здесь нет (один платёж привязан к одной аренде, одной копии, одному фильму, одной категории).

Типичная ловушка новичка — сгруппировать слишком рано или забыть, что в SELECT вне SUM может стоять только то, что в GROUP BY. Здесь это cat.name — и он в группировке есть. ORDER BY revenue DESC ставит самый прибыльный жанр наверх — ровно то, что просил руководитель.

Если из этого списка нужно оставить только крупные жанры (например, с выручкой выше порога), фильтровать группы нужно через HAVING — смотри задачу «крупные категории: фильтр через HAVING».

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

category | revenue
--- | ---
Sports | 4892.19
Sci-Fi | 4336.01
Animation | 4245.31
Drama | 4118.46
Comedy | 4002.48
New | 3966.38
Action | 3951.84
Foreign | 3934.47
Games | 3922.18
Family | 3830.15
… всего строк: 16 (показаны первые 10)