Untitled query #41685

SQL

USE trwiki_p;
SELECT SUBSTRING_INDEX(page_title, "/", 1) AS portal, SUBSTR(MIN(rev_timestamp), 1, 4) AS creation, COUNT(DISTINCT(rev_actor)) AS editors, COUNT(DISTINCT(rev_sha1)) AS revisions
FROM page
JOIN revision_userindex
ON rev_page = page_id
AND page_namespace = 100
AND page_is_redirect = 0
AND rev_minor_edit = 0
WHERE SUBSTRING_INDEX(page_title, "/", 1) NOT IN (
  SELECT page_title
  FROM categorylinks
  JOIN page
  ON cl_from = page_id
  AND ( cl_to = "Redirected_portals_with_existing_subpages"
       OR cl_to = "Portal_disambiguation_pages"
       OR cl_to = "Redirects from moves" )
  
)
GROUP BY portal
ORDER BY editors desc
LIMIT 1000;
SQL

Checking query status...
