Fork of Userspace pages of non-existent users (no talk, no subpages) by Uhai
This query is marked as a draft This query has been published by EqJjgOa8rVvsRmZL.

SQL

AخA
 
SELECT CONCAT('https://en.wikipedia.org/wiki/', IF(p.page_namespace = 2, 'User:', 'User_talk:'), p.page_title) AS URL
FROM page p WHERE REPLACE(SUBSTRING_INDEX(p.page_title, '/', 1), '_', ' ') not in (select user_name from user)
AND p.page_namespace IN (2, 3)
AND p.page_is_redirect = 0
AND NOT IS_IPV4(SUBSTRING_INDEX(p.page_title, '/', 1)) -- Exclude IPv4
AND NOT IS_IPV6(SUBSTRING_INDEX(p.page_title, '/', 1)) -- Exclude IPv6
AND p.page_title NOT RLIKE '\.(css|cs|js|charset)$'
AND SUBSTRING_INDEX(p.page_title, '/', 1) NOT LIKE '%.%.%.xxx'
AND SUBSTRING_INDEX(p.page_title, '/', 1) NOT IN ('Unknown_user', 'Global_rename_script',
                        'Sec\"\'27\\u00226\\x3ETest_(WMF)') -- Exclude technical user pages
AND p.page_id NOT IN (
  SELECT cl_from FROM categorylinks
  WHERE cl_to IN ('User_soft_redirects', 'Wikipedia_doppelganger_accounts',
                     'Wikipedia_alternative_accounts', 'Retired_Wikipedians') -- Exclude these categories
)
AND p.page_id NOT IN (
  SELECT tl_from FROM templatelinks WHERE tl_target_id IN (
    select lt_id from linktarget where lt_namespace = 10
    and lt_title in ("Sockpuppet", "Sockpuppeteer", "IPsock", "Checked_sockpuppet", "Sockpuppetry")
  ) -- Exclude pages tagged with sockpuppet template
)
ORDER BY p.page_latest DESC
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.

Checking query status...