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.