This query is marked as a draft This query has been published by Certes.

SQL

AخA
 
SELECT SUBSTRING_INDEX(page_title, "_(", 1) AS basename,
 SUM(1 - page_is_redirect) pages, GROUP_CONCAT(page_title ORDER BY page_title SEPARATOR'|') AS titles
FROM page Pq
WHERE page_namespace = 0 AND page_title LIKE "F%" AND page_title NOT REGEXP "_\\((season|series|\\d{4}(.\\d{4})?\\))|\\)."
AND (NOT EXISTS (SELECT 1 FROM page Pb /* basename links to qualified name */
                 JOIN pagelinks ON pl_from = Pb.page_id AND pl_namespace = 0 AND pl_title = Pq.page_title
                 WHERE Pb.page_namespace = 0 AND Pb.page_title = SUBSTRING_INDEX(Pq.page_title, "_(", 1) AND Pb.page_title != Pq.page_title)
 AND NOT EXISTS (SELECT 1 FROM page Pb /* qualified name is a redirect, and basename links to its target */
                 JOIN redirect ON rd_from = Pq.page_id AND rd_namespace = 0
                 JOIN pagelinks ON pl_from = Pb.page_id AND pl_namespace = 0 AND pl_title = rd_title
                 WHERE Pb.page_namespace = 0 AND Pb.page_title = SUBSTRING_INDEX(Pq.page_title, "_(", 1) AND Pb.page_title != Pq.page_title
                 AND Pq.page_is_redirect = 1)
 AND NOT EXISTS (SELECT 1 FROM page Pb /* basename links to redirect to qualified name */
                 JOIN pagelinks ON pl_from = Pb.page_id AND pl_namespace = 0
                 JOIN page Pr ON Pr.page_namespace = 0 AND Pr.page_title = pl_title
                 JOIN redirect ON rd_from = Pr.page_id  AND rd_namespace = 0 AND rd_title = Pq.page_title
                 WHERE Pb.page_namespace = 0 AND Pb.page_title = SUBSTRING_INDEX(Pq.page_title, "_(", 1) AND Pb.page_title != Pq.page_title)
 AND NOT EXISTS (SELECT 1 FROM page Pb /* qualified name is a redirect, and basename links to another redirect to its target */
                 JOIN redirect Rq ON Rq.rd_from = Pq.page_id AND Rq.rd_namespace = 0
                 JOIN pagelinks ON pl_from = Pb.page_id AND pl_namespace = 0
                 JOIN page Pr ON Pr.page_namespace = 0 AND Pr.page_title = pl_title
                 JOIN redirect Rb ON Rb.rd_from = Pr.page_id AND Rb.rd_namespace = 0 AND Rb.rd_title = Rq.rd_title
                 WHERE Pb.page_namespace = 0 AND Pb.page_title = SUBSTRING_INDEX(Pq.page_title, "_(", 1) AND Pb.page_title != Pq.page_title
                 AND Pq.page_is_redirect = 1)
 AND NOT EXISTS (SELECT 1 FROM redirect /* qualified name redirects to basename */
                 JOIN page Pb ON Pb.page_namespace = 0 AND Pb.page_title = rd_title AND Pb.page_title = SUBSTRING_INDEX(Pq.page_title, "_(", 1)
                 WHERE rd_from = Pq.page_id AND rd_namespace = 0)
 OR page_title LIKE "%_(disambiguation)")
GROUP BY basename
HAVING pages >= 5
AND MAX(CASE WHEN page_title LIKE "%_(disambiguation)" THEN 1 ELSE 0 END) = 0
AND MAX(CASE WHEN page_title = basename AND page_is_redirect = 1 THEN 1 ELSE 0 END) = 0
ORDER BY basename
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.

Query status: complete

Executed in 71.42 seconds as of Sat, 11 Feb 2023 22:28:25 UTC.