Fork of First words of non-redirect article titles with more than 50 occurrances by Ahecht
This query is marked as a draft This query has been published by Certes.

SQL

AخA
 
SELECT COUNT(DISTINCT Pb.page_id) AS people, SUBSTRING_INDEX(Pb.page_title, "_", 1) AS given_name
FROM categorylinks Cb
JOIN page Pb ON Pb.page_id = Cb.cl_from AND Pb.page_is_redirect = 0 AND Pb.page_namespace = 0
WHERE Cb.cl_type = 'page' AND Cb.cl_to <= "9999" AND Cb.cl_to REGEXP "^\\d+_births$"
GROUP BY given_name
HAVING given_name NOT REGEXP "\\.$" AND people >= 50
AND NOT EXISTS (SELECT 1 FROM page Pg, categorylinks Cg
 WHERE Pg.page_namespace = 0 AND Pg.page_title = given_name
 AND Cg.cl_from = Pg.page_id AND Cg.cl_type = 'page' AND Cg.cl_to REGEXP ("[gG]iven_name|(Chinese|Japanese|Korean|Vietnamese)-language_surnames"))
AND NOT EXISTS (SELECT 1 FROM page Pf, redirect, page Pr, categorylinks Cr
 WHERE Pf.page_namespace = 0 AND Pf.page_title = given_name
 AND rd_from = Pf.page_id AND rd_namespace = 0
 AND Pr.page_namespace = 0 AND Pr.page_title = rd_title
 AND Cr.cl_from = Pr.page_id AND Cr.cl_type = 'page' AND Cr.cl_to REGEXP ("[gG]iven_name|(Chinese|Japanese|Korean|Vietnamese)-language_surnames"))
AND NOT EXISTS (SELECT 1 FROM page Pg, categorylinks Cg
 WHERE Pg.page_namespace = 0 AND Pg.page_title = CONCAT(given_name, "_(given_name)")
 AND Cg.cl_from = Pg.page_id AND Cg.cl_type = 'page' AND Cg.cl_to REGEXP ("[gG]iven_name"))
AND NOT EXISTS (SELECT 1 FROM page Pf, redirect, page Pr, categorylinks Cr
 WHERE Pf.page_namespace = 0 AND Pf.page_title = CONCAT(given_name, "_(given_name)")
 AND rd_from = Pf.page_id AND rd_namespace = 0
 AND Pr.page_namespace = 0 AND Pr.page_title = rd_title
 AND Cr.cl_from = Pr.page_id AND Cr.cl_type = 'page' AND Cr.cl_to REGEXP ("[gG]iven_name"))
AND NOT EXISTS (SELECT 1 FROM page Pg, categorylinks Cg
 WHERE Pg.page_namespace = 0 AND Pg.page_title = CONCAT(given_name, "_(name)")
 AND Cg.cl_from = Pg.page_id AND Cg.cl_type = 'page' AND Cg.cl_to REGEXP ("[gG]iven_name|(Chinese|Japanese|Korean|Vietnamese)-language_surnames"))
AND NOT EXISTS (SELECT 1 FROM page Pf, redirect, page Pr, categorylinks Cr
 WHERE Pf.page_namespace = 0 AND Pf.page_title = CONCAT(given_name, "_(name)")
 AND rd_from = Pf.page_id AND rd_namespace = 0
 AND Pr.page_namespace = 0 AND Pr.page_title = rd_title
 AND Cr.cl_from = Pr.page_id AND Cr.cl_type = 'page' AND Cr.cl_to REGEXP ("[gG]iven_name|(Chinese|Japanese|Korean|Vietnamese)-language_surnames"))
 AND NOT EXISTS (SELECT 1 FROM page Pg, categorylinks Cg
 WHERE Pg.page_namespace = 0 AND Pg.page_title = CONCAT(given_name, "_(surname)")
 AND Cg.cl_from = Pg.page_id AND Cg.cl_type = 'page' AND Cg.cl_to REGEXP ("(Chinese|Japanese|Korean|Vietnamese)-language_surnames"))
AND NOT EXISTS (SELECT 1 FROM page Pf, redirect, page Pr, categorylinks Cr
 WHERE Pf.page_namespace = 0 AND Pf.page_title = CONCAT(given_name, "_(surname)")
 AND rd_from = Pf.page_id AND rd_namespace = 0
 AND Pr.page_namespace = 0 AND Pr.page_title = rd_title
 AND Cr.cl_from = Pr.page_id AND Cr.cl_type = 'page' AND Cr.cl_to REGEXP ("(Chinese|Japanese|Korean|Vietnamese)-language_surnames"))
ORDER BY people DESC, given_name;
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...