gidificer

Если на вашей базе данных есть функционал CTE, то можно так попробовать

WITH articlesCount(articleId,count) as 
SELECT articleId, COUNT(tagId) as count from article_tags
GROUP BY articleId

SELECT what your need from article as a
INNER JOIN articlesCount as ac on a.id = ac.articleId
order by ac.count

Если нету - подобное можно провернуть через временные таблицы.

SELECT *
  FROM (
    SELECT `at`.`id_article` AS `id_article`, COUNT(*) AS `count`
      FROM `article_tags` AS `at`
      JOIN `tags` AS `t` ON `t`.`id` = `at`.`id_tag` 
        AND `t`.`name` IN ('Тег_10', 'Тег_11', 'Тег_12')
      GROUP BY `id_article`
  ) AS `c` 
  JOIN `articles` AS `a` ON `a`.`id` = `c`.`id_article`
  ORDER BY `c`.`count` DESC