[enwiki] WikiProject takeover candidates

SQL

SELECT 
  SUBSTRING_INDEX(page_title, '/', 1) AS group_title,
  MAX(rev_timestamp) AS latest_edit_timestamp
FROM page
JOIN revision ON page.page_latest = revision.rev_id
JOIN user ON revision.rev_user = user.user_id
LEFT JOIN user_groups ON user.user_id = user_groups.ug_user AND user_groups.ug_group = 'bot'
WHERE page_namespace = 4 AND page_title LIKE 'WikiProject%' AND page_is_redirect = 0
  AND user_groups.ug_user IS NULL
GROUP BY group_title
HAVING NOT EXISTS (
  SELECT 1
  FROM page AS inner_page
  WHERE inner_page.page_namespace = 4
    AND inner_page.page_title = group_title
    AND inner_page.page_is_redirect = 1
) AND latest_edit_timestamp < DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 3 YEAR), '%Y%m%d%H%i%s')
ORDER BY latest_edit_timestamp;
SQL

Checking query status...
