English Wikipedia portals by age and number of edits, editors

This query takes all pages in the Portal namespace and aggregates them by base page name (the name before the first slash), then it aggregates the timestamps and username for all the corresponding revisions. It helps find portals which, in aggregate, have very few edits and/or editors (either in total or relative to their age since creation), or where the average revision is very old.

In some cases the results are produced by subpages which were left lingering after the actual portal was deleted or redirected. Such subpages may need to be redirected or speedy deleted themselves.

SQL

USE enwiki_p;
SELECT SUBSTRING_INDEX(page_title, "/", 1) AS portal, SUBSTR(MIN(rev_timestamp), 1, 4) AS creation, SUBSTR(AVG(rev_timestamp), 1, 4) AS avgEditsYear, 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 ASC
LIMIT 1000;

SQL

Checking query status...