Fork of
Debugging WIR query, solved
by Cryptic
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 lt_title Target, SUM(Pt.page_title IS NULL) LinkCount, MIN(Pf.page_title) ExampleLink1, MAX(Pf.page_title) ExampleLink2
FROM pagelinks
JOIN linktarget ON lt_id = pl_target_id
LEFT JOIN page Pt ON Pt.page_namespace=0 AND Pt.page_title=lt_title
JOIN names ON name=SUBSTRING_INDEX(lt_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 lt_namespace=0 AND lt_title BETWEEN "Cn" AND "D" /* If changed, needs a matching change 7 lines above */
AND lt_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.