nlwiki Categories without content category

Category pages without any categories or with only maintenance categories.

Query breakdown:
1. root query = a list of category pages that are not nominated for deletion and older than 24 hours
2. hc = a list of hidden categories, meaning the titles of category pages with the __HIDDENCAT__ 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 3-6 minutes to execute on nlwiki_p.

SQL

USE nlwiki_p;
SELECT page_title, rev_timestamp
FROM page 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)
WHERE 
  	page_namespace=14 AND 
  	NOT EXISTS (
        SELECT * 
      	FROM categorylinks 
      	WHERE page_id=cl_from AND cl_to REGEXP '^Wikipedia:(Verwijderbaar/[0-9]{2}|Nuweg)$'
    ) AND
  	NOT EXISTS (SELECT * FROM (
		# niet-verborgen categorylinks
		SELECT cl_from, cl_to FROM categorylinks WHERE 
            NOT EXISTS (SELECT * FROM (
                # verborgen categorieën
                SELECT DISTINCT page_title
                FROM page, page_props
				WHERE 
              		page_namespace=14 AND 
              		page_id=pp_page AND 
              		(pp_propname='hiddencat' OR cl_to='Wikipedia:Verborgen_categorie')
            ) hc WHERE cl_to=hc.page_title)
		GROUP BY cl_from
		ORDER BY cl_from
	) nhcl WHERE nhcl.cl_from=page_id)
ORDER BY rev_timestamp

SQL

Checking query status...