nlwiki Articles without content category

Pages without any categories or with only maintenance categories.

Query breakdown:
1. outer query = a list of article pages that are not nominated for deletion and older than 24 hours
2. hc = a list of maintenance categories, meaning the titles of category pages with the __HIDDENCAT__ or __EXPECTUNUSEDCATEGORY__ property
    note: could filter out empty categories using the `category` table
3. nhcl = take `categorylinks`, filter out hc and collapse to a single row per page
4. final result = get the titles of article pages not present in nhcl, and the creation timestamp

Takes about 30s to execute on nlwiki.

SQL

use nlwiki_p;

SELECT page_title, rev_timestamp
FROM page, revision
WHERE 
	page_namespace=0 AND page_is_redirect=0 AND
	NOT EXISTS (SELECT * FROM (
		# niet-beheer categorylinks
		SELECT cl_from, cl_to FROM categorylinks WHERE 
      		cl_to NOT REGEXP '^(Diersoort_beschreven_in_|Actuele_(sport)?gebeurtenis$|Wikipedia:(Verwijderbaar/[0-9]{2}|Nog_niet_gereed|Nuweg)$)' AND
      	NOT EXISTS (SELECT * FROM (
			# beheercategorieën
			SELECT DISTINCT page_title
			FROM page, page_props
			WHERE page_namespace=14 AND page_id=pp_page AND (pp_propname='hiddencat' OR pp_propname='expectunusedcategory')
		) hc WHERE cl_to=hc.page_title)
		GROUP BY cl_from
		ORDER BY cl_from
	) nhcl WHERE nhcl.cl_from=page_id) AND
    page_id=rev_page AND rev_parent_id=0
ORDER BY rev_timestamp

SQL

Checking query status...