Most-edited pages containing the name of a WikiProject

Fork of Most-edited pages containing the name of a WikiProject by Jmorgan (WMF)

This query is marked as a draft This query has been published by PDas (WMF).

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

SQL

Checking query status...