SQLize Online / PHPize Online  /  SQLtest Online

WITH category_data AS ( SELECT c.name AS category, COUNT(f.film_id) AS total_films, SUM(p.amount) AS total_revenue FROM category c JOIN film_category fc ON c.category_id = fc.category_id JOIN film f ON fc.film_id = f.film_id LEFT JOIN inventory i ON f.film_id = i.film_id LEFT JOIN rental r ON i.inventory_id = r.inventory_id LEFT JOIN payment p ON r.rental_id = p.rental_id GROUP BY c.name -- Изменено с c.category_id на c.name ), overall_counts AS ( SELECT SUM(total_films) AS grand_total_films, SUM(total_revenue) AS grand_total_revenue FROM category_data ) SELECT cd.category, ROUND(cd.total_revenue / oc.grand_total_revenue, 6) AS relative_rental_amount, ROUND(cd.total_films / oc.grand_total_films, 6) AS relative_rentals_count FROM category_data cd CROSS JOIN overall_counts oc ORDER BY relative_rental_amount DESC;