SQLize | PHPize | SQLtest

WITH ranked_films AS ( SELECT c.name AS category, f.title AS film_title, COUNT(r.rental_id) AS rentals_count, RANK() OVER (PARTITION BY c.category_id ORDER BY COUNT(r.rental_id) DESC) AS rn FROM category c JOIN film_category fc ON c.category_id = fc.category_id JOIN film f ON fc.film_id = f.film_id JOIN inventory i ON f.film_id = i.film_id JOIN rental r ON i.inventory_id = r.inventory_id WHERE YEAR(r.rental_date) = 2005 GROUP BY c.category_id, c.name, f.film_id, f.title ) SELECT category, GROUP_CONCAT(film_title ORDER BY film_title SEPARATOR ',') AS most_rented_films, MAX(rentals_count) AS rentals_count FROM ranked_films WHERE rn = 1 GROUP BY category ORDER BY category; ; show status like 'Last_query_cost';