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

SELECT
	page_title,
    rev_timestamp,
    GROUP_CONCAT(REPLACE(cl_to,'_',' ') SEPARATOR ' | ') AS categories,
    (SELECT GROUP_CONCAT(pl_title SEPARATOR ' | ') FROM pagelinks WHERE pl_from=page_id AND pl_namespace=14) AS catlinks,
    pp_value AS defaultsort,
    (SELECT pp_value FROM page_props WHERE pp_page=page_id AND pp_propname='wikibase_item') AS Wikidata
FROM page 
	INNER JOIN (SELECT DISTINCT rc_cur_id FROM recentchanges WHERE rc_type IN (0,1) AND rc_namespace=0) rc ON rc_cur_id=page_id
	INNER JOIN revision ON (
		rev_parent_id=0 AND page_id=rev_page AND UNIX_TIMESTAMP()-UNIX_TIMESTAMP(STR_TO_DATE(rev_timestamp,'%Y%m%d%H%i%s'))>86400)
    LEFT JOIN categorylinks ON page_id=cl_from
    LEFT JOIN page_props ON page_id=pp_page AND pp_propname='defaultsort'
WHERE 
  	page_namespace=0 AND 
  	page_is_redirect=0 AND
  	NOT EXISTS (SELECT * FROM page_props WHERE page_id=pp_page AND pp_propname='disambiguation') AND
  	NOT EXISTS (SELECT * FROM categorylinks 
      	WHERE page_id=cl_from AND cl_to REGEXP 
                		'^Wikipedia:(Verwijderbaar/[0-9]{2}$|Nog_niet_gereed$|Nog_te_categoriseren_sinds_|Nuweg$|Werk_in_uitvoering$)'
    ) AND
  	NOT EXISTS (SELECT * FROM (
		# niet-beheer categorylinks
		SELECT cl_from, cl_to FROM categorylinks WHERE 
      		cl_to NOT REGEXP 
      			'^Diersoort_beschreven_in_|'
      			'^Boom_\\(plant\\)|'
      			'^Actuele_(sport)?gebeurtenis$|'
      			'^Wikipedia:|'
      			'^IUCN-status_|'
      			'^Dier_uit_het_[ANOP][a-zë]+_gebied$|'
      			'^Schimmel(klasse|orde|familie|geslacht|soort)$|'
      			'^Korstmos(parasieten|soort)$'
      			'^Uitgestorven_vissen$' 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)
GROUP BY page_title
ORDER BY rev_timestamp

SQL

Checking query status...