SQLize Online / PHPize Online  /  SQLtest Online

WITH tmp AS ( SELECT category_id, film_id, COUNT(*) AS rentals_count, DENSE_RANK() OVER ( PARTITION BY category_id ORDER BY COUNT(*) DESC ) d_rank FROM inventory i JOIN rental USING(inventory_id) JOIN film_category fc USING(film_id) WHERE YEAR(rental_date) = '2005' GROUP BY category_id, film_id ) SELECT name AS category, GROUP_CONCAT(title ORDER BY title) AS most_rented_films, rentals_count FROM tmp JOIN film USING(film_id) JOIN category USING(category_id) WHERE d_rank = 1 GROUP BY name, rentals_count