-- версия первая

SELECT ctg.name AS film_category,
       COUNT(fctg.film_id) as film_cnt
FROM category AS ctg
LEFT OUTER JOIN film_category AS fctg ON ctg.category_id = fctg.category_id
LEFT OUTER JOIN film_actor AS fac ON fctg.film_id = fac.film_id
LEFT OUTER JOIN actor AS actr ON fac.actor_id = actr.actor_id
WHERE actr.actor_id IN (SELECT actor_id
                        FROM (SELECT m.film_id, fc.category_id,ct.name, fa.actor_id
                              FROM movie AS m
                              LEFT OUTER JOIN film_category AS fc ON m.film_id = fc.film_id
                              LEFT OUTER JOIN category AS ct ON fc.category_id = ct.category_id
                              LEFT OUTER JOIN film_actor AS fa ON fc.film_id = fa.film_id
                              WHERE m.release_year > 2013) AS pz
                        GROUP BY actor_id
                        HAVING COUNT(actor_id) > 7
                        ORDER BY actor_id DESC)
GROUP BY film_category
ORDER BY film_cnt DESC, film_category;

-- версия вторая

SELECT
    name film_category,
    COUNT(film_id) AS film_cnt
FROM (
    SELECT ac.actor_id, fc.film_id, fc.category_id, ct.name
    FROM actor AS ac
    LEFT OUTER JOIN film_actor AS fa ON ac.actor_id = fa.actor_id
    LEFT OUTER JOIN film_category AS fc ON fc.film_id = fa.film_id
    LEFT OUTER JOIN category AS ct ON fc.category_id = ct.category_id
    WHERE ac.actor_id IN (SELECT actor_id
                            FROM (SELECT m.film_id, flc.category_id, ctg.name, fla.actor_id
                                  FROM movie AS m
                                  LEFT OUTER JOIN film_category AS flc ON m.film_id = flc.film_id
                                  LEFT OUTER JOIN category AS ctg ON flc.category_id = ctg.category_id
                                  LEFT OUTER JOIN film_actor AS fla ON flc.film_id = fla.film_id
                                  WHERE m.release_year > 2013) AS pz
                            GROUP BY actor_id
                            HAVING COUNT(actor_id) > 7
                            ORDER BY actor_id DESC)) AS px

GROUP BY film_category
ORDER BY film_cnt DESC, film_category DESC;