Fork of Untitled query #81446 by Certes
This query is marked as a draft This query has been published by Cryptic.

SQL

AخA
 
WITH names AS (
  WITH RECURSIVE deepcat (subcat, depth) AS
  (
    SELECT 'This category does not exist but makes the database column long enough to hold all the real category names', 99
    UNION
    SELECT 'Feminine_given_names', 0
    UNION
    SELECT page_title, depth + 1
    FROM deepcat
    JOIN categorylinks ON cl_type = "subcat" AND cl_to = subcat
    JOIN page ON page_id = cl_from AND page_namespace = 14 AND page_title LIKE "%eminine%"
    WHERE depth < 4
  )
SELECT DISTINCT SUBSTRING_INDEX(page_title, "_", 1) AS name
  FROM deepcat
  JOIN categorylinks ON cl_type = "page" AND cl_to = subcat
  JOIN page ON page_id = cl_from AND page_namespace = 0 AND page_title BETWEEN "Cn" AND "D" /* If changed, needs a matching change 7 lines below */
)
SELECT pl_title Target, SUM(Pt.page_title IS NULL) LinkCount, MIN(Pf.page_title) ExampleLink1, MAX(Pf.page_title) ExampleLink2
FROM pagelinks
LEFT JOIN page Pt ON Pt.page_namespace=0 AND Pt.page_title=pl_title
JOIN names ON name=SUBSTRING_INDEX(pl_title, "_", 1)
STRAIGHT_JOIN page Pf ON Pf.page_id=pl_from AND NOT (Pf.page_namespace=0 AND Pf.page_title REGEXP "^(.*\\D)?(19{{!}}20)\\d\\d(?!\\d)")
WHERE pl_from_namespace IN (0, 10) AND pl_namespace=0 AND pl_title BETWEEN "Cn" AND "D" /* If changed, needs a matching change 7 lines above */
AND pl_title REGEXP "^\\p{Lu}\\p{Ll}+_(\\p{Lu}(\\p{Ll}+_{{!}}\\._?))*\\p{Lu}\\p{Ll}+(_\\(.*\\))?$"
GROUP BY Target
HAVING MAX(pl_from_namespace) = 0 AND LinkCount>=5
ORDER BY LinkCount DESC, Target
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...