Replicas browser and optimizer
This query is marked as a draft
This query has been published
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.
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;
By running queries you agree to the
and you irrevocably agree to release your SQL under
All SQL code is licensed under
Checking query status...
Report a bug!
Wikimedia Cloud VPS
, written in