USE enwiki_p;

​

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

       COUNT(*) AS n_nobot_edits

FROM page

JOIN revision

ON page_id=rev_page

LEFT JOIN redirect

ON page_id=rd_from

WHERE (page_title LIKE 'WikiProject\_%'

      OR page_title='WikiAfrica')

AND page_namespace IN (4,5)

AND rev_timestamp >= DATE_FORMAT(NOW() - INTERVAL 180 DAY, '%Y%m%d%h%i%s')

AND rev_user NOT IN

          (SELECT ug_user

          FROM user_groups

          WHERE ug_group = 'bot')

AND rd_from IS NULL

GROUP BY project

ORDER BY n_nobot_edits DESC;