SQL
AخA
SELECT CONCAT('https://en.wikipedia.org/wiki/', CASE talk.page_namespace
WHEN -2 THEN 'Media:' WHEN -1 THEN 'Special:'
WHEN 0 THEN '' WHEN 1 THEN 'Talk:'
WHEN 2 THEN 'User:' WHEN 3 THEN 'User talk:'
WHEN 4 THEN 'Wikipedia:' WHEN 5 THEN 'Wikipedia talk:'
WHEN 6 THEN ':File:' WHEN 7 THEN 'File talk:'
WHEN 8 THEN 'MediaWiki:' WHEN 9 THEN 'MediaWiki talk:'
WHEN 10 THEN 'Template:' WHEN 11 THEN 'Template talk:'
WHEN 12 THEN 'Help:' WHEN 13 THEN 'Help talk:'
WHEN 14 THEN ':Category:' WHEN 15 THEN 'Category talk:'
WHEN 100 THEN 'Portal:' WHEN 101 THEN 'Portal talk:'
WHEN 108 THEN 'Book:' WHEN 109 THEN 'Book talk:'
WHEN 118 THEN 'Draft:' WHEN 119 THEN 'Draft talk:'
WHEN 446 THEN 'Education Program:' WHEN 447 THEN 'Education Program talk:'
WHEN 710 THEN 'TimedText:' WHEN 711 THEN 'TimedText talk:'
WHEN 828 THEN 'Module:' WHEN 829 THEN 'Module talk:'
WHEN 2300 THEN 'Gadget:' WHEN 2301 THEN 'Gadget talk:'
WHEN 2302 THEN 'Gadget definition:' WHEN 2303 THEN 'Gadget definition talk:'
ELSE CONCAT('{{ns:', talk.page_namespace, '}}:')
END,
talk.page_title) AS url,
CASE WHEN talk.page_id IN (SELECT cl_from FROM categorylinks WHERE cl_to = 'Candidates_for_speedy_deletion_as_dependent_on_a_non-existent_page')
THEN 'csd_talk'
WHEN talk.page_id IN (SELECT cl_from FROM categorylinks WHERE cl_to = 'Candidates_for_speedy_deletion')
THEN 'csd'
ELSE NULL
END AS reason
FROM page talk
WHERE talk.page_namespace % 2 = 1 AND talk.page_namespace NOT IN (3, 7, 9) AND talk.page_title not like '%/%'
AND talk.page_title NOT IN (SELECT page_title from page where page_namespace = talk.page_namespace-1)
AND talk.page_id NOT IN (
SELECT cl_from FROM categorylinks
WHERE cl_to IN ('Wikipedia_orphaned_talk_pages_that_should_not_be_speedily_deleted', 'Non-orphaned_pages_tagged_with_G8-exempt')
)
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.