USE enwiki_p;

​

SELECT SUBSTRING_INDEX(page_title, '/', 1) AS project,

       SUM((

         SELECT COUNT(*)

         FROM revision

         WHERE page_id = rev_page

         AND DATEDIFF(NOW(), rev_timestamp) <= 365

       )) AS count,

       SUM((

         SELECT COUNT(*)

         FROM revision

         WHERE page_id = rev_page

         AND DATEDIFF(NOW(), rev_timestamp) <= 365

         AND rev_actor NOT IN

          (SELECT ug_user

          FROM user_groups

          WHERE ug_group = 'bot')

       )) AS no_bots_count,

       (SELECT page_is_redirect

       FROM page

       WHERE page_namespace = 4

       AND page_title = project) AS redirect

FROM page

WHERE (page_title LIKE 'WikiProject\_%'

  OR page_title LIKE 'WikiAfrica')

AND page_namespace BETWEEN 4 AND 5

AND page_is_redirect = 0

GROUP BY project

ORDER BY count DESC;