Untitled query #63237

SQL

USE enwiki_p;

SELECT SUBSTRING_INDEX(page_title, '/', 1) AS project,
       SUM((
         SELECT COUNT(*)
         FROM revision
         WHERE page_id = rev_page
         AND DATEDIFF('2021/12/31', rev_timestamp) <= 365
       )) AS count,
       SUM((
         SELECT COUNT(*)
         FROM revision
         WHERE page_id = rev_page
         AND DATEDIFF('2021/12/31', 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...
