Most-edited pages containing the name of a WikiProject

Last run Jan 15, 2020

List of WikiProjects by number of changes to all its pages in the last 365 days. Based on: https://en.wikipedia.org/wiki/Wikipedia:Database_reports/WikiProjects_by_changes/Configuration

Note that these are edits to the WikiProject pages, subpages, and talkpages, NOT edits to articles claimed by the WikiProject.

SQL

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_user 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;

SQL

Checking query status...