Given names lacking an article

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.

This query attempts to list given names lacking an article.  It's not foolproof but should be useful.  The Chinese bit filters out people such as Xu Chen, whose first name has no given-name article because it is a surname.  (Sadly, it also filters out names such as Lee which are both a given name and a Chinese surname.)

SQL

SELECT COUNT(Cb.cl_to) AS people, SUBSTRING_INDEX(page_title, "_", 1) AS first_word, COUNT(Cg.cl_to)
FROM page
LEFT JOIN categorylinks Cb ON Cb.cl_from = page_id AND Cb.cl_type = 'page' AND Cb.cl_to REGEXP "^\\d+_births$"
LEFT JOIN categorylinks Cg ON Cg.cl_from = page_id AND Cg.cl_type = 'page' AND Cg.cl_to IN ("Given_names", "Chinese-language_surnames")
WHERE page_is_redirect = 0 AND page_namespace = 0
GROUP BY first_word
HAVING people >= 50 AND COUNT(Cg.cl_to) = 0
ORDER BY people DESC, first_word;

By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...