Userspace pages of non-existent users (no redirects)

SQL

SELECT CONCAT('https://zh.wikipedia.org/wiki/User:', p.page_title) AS URL
FROM (SELECT p.*, SUBSTRING_INDEX(p.page_title, '/', 1) AS page_name FROM zhwiki_p.page p) p
LEFT JOIN zhwiki_p.user u ON REPLACE(p.page_name, '_', ' ') = u.user_name
WHERE p.page_namespace = 2
AND u.user_id IS NULL
AND p.page_is_redirect = 0
AND NOT IS_IPV4(p.page_name) -- Exclude IPv4
AND NOT IS_IPV6(p.page_name) -- Exclude IPv6
AND p.page_title NOT LIKE '%.css'
AND p.page_title NOT LIKE '%.cs'
AND p.page_title NOT LIKE '%.js'
AND p.page_title NOT LIKE '%.charset'
AND p.page_name NOT LIKE '%.%.%.xxx'
AND p.page_name NOT IN ('Unknown_user', 'Global_rename_script', 'Sec\"\'27\\u00226\\x3ETest_(WMF)') -- Exclude technical user pages
AND p.page_id NOT IN (
  SELECT cl.cl_from FROM
  zhwiki_p.categorylinks cl
  WHERE cl.cl_to IN ('User_soft_redirects', 'Wikipedia_doppelganger_accounts', 'Wikipedia_alternative_accounts') -- Exclude these categories
)
AND p.page_id NOT IN (
  SELECT tl.tl_from FROM
  zhwiki_p.templatelinks tl
  WHERE tl.tl_target_id IN (29213,9035,121346,34911,15218,14482,114218,21619,21627,21630,1110888,26158,6153,11345,9036,9034,13160,9073,1086483,34907,1030092,106908) -- Exclude pages tagged with sockpuppet template
)
AND p.page_title NOT LIKE '%/%'
SQL

Checking query status...
