SQL_mistake 2_1

-- весь запрос
SELECT cat.name AS name_category,
       COUNT(DISTINCT m.film_id) AS total_films
FROM movie AS m
LEFT JOIN film_category AS fc ON m.film_id=fc.film_id
LEFT JOIN category AS cat ON fc.category_id=cat.category_id
LEFT JOIN film_actor AS fa ON fc.film_id=fa.film_id
WHERE fa.actor_id IN (SELECT fa1.actor_id
                     FROM film_actor AS fa1
                     LEFT JOIN movie AS m1 ON m1.film_id=fa1.film_id
                     WHERE m1.release_year > 2013
                     GROUP BY fa1.actor_id
                     HAVING fa1.actor_id > 7)
GROUP BY name_category
ORDER BY total_films DESC;

-- отдельно подзапрос с WHERE с разными положениями таблиц в LEFT JOIN
-- подсчет числа актеров, которые снимались > 7 фильмах после 2013 (конструкция FROM movie LEFT JOIN actor)
SELECT COUNT(*)
FROM
  (SELECT a1.actor_id
  FROM movie AS m1
  LEFT JOIN film_actor AS fa1 ON m1.film_id=fa1.film_id
  LEFT JOIN actor AS a1 ON fa1.actor_id=a1.actor_id
  WHERE m1.release_year > 2013
  GROUP BY a1.actor_id
  HAVING a1.actor_id > 7) AS query1

-- подсчет числа актеров, которые снимались > 7 фильмах после 2013 (конструкция FROM actor LEFT JOIN movie)
SELECT COUNT(*)
FROM
  (SELECT a1.actor_id
  FROM actor AS a1
  LEFT JOIN film_actor AS fa1 ON a1.actor_id=fa1.actor_id
  LEFT JOIN movie AS m1 ON fa1.film_id=m1.film_id
  WHERE m1.release_year > 2013
  GROUP BY a1.actor_id
  HAVING a1.actor_id > 7) AS query2