Untitled query #74032

SQL

WITH conventions AS (SELECT DISTINCT(CASE WHEN page_is_redirect = 1 THEN pl_title ELSE page_title END) AS convention
  FROM page
  JOIN categorylinks ON page_id = cl_from AND cl_to = "Wikipedia_naming_conventions"
  JOIN pagelinks ON pl_from = page_id AND pl_namespace = 4
  WHERE page_namespace = 4
  AND page_title NOT IN ("Protection_policy", 
                         "Redirect", 
                         "Categorizing_redirects", 
                         "Subpages", 
                         "Requested_moves", 
                         "Namespace", 
                         "Project_namespace", 
                         "Requested_moves/Technical_requests")
  AND pl_title NOT IN ("Protection_policy", 
                       "Redirect", 
                       "Categorizing_redirects", 
                       "Subpages", 
                       "Requested_moves", 
                       "Namespace", 
                       "Project_namespace", 
                       "Requested_moves/Technical_requests")
                    
),

direct AS (SELECT *
           FROM pagelinks AS direct
           LEFT JOIN page on direct.pl_from = page_id AND page_is_redirect = 1 AND page_namespace = 4
           WHERE direct.pl_title IN (SELECT convention FROM conventions)
           AND direct.pl_namespace = 4
           LIMIT 1000
)

#SELECT pl_title, COUNT(*)
#FROM (SELECT direct.pl_title
#      FROM direct
#      LEFT JOIN pagelinks AS indirect ON direct.page_title = indirect.pl_title
#      WHERE indirect.pl_namespace = 4
#      AND direct.page_id IS NOT NULL
#      UNION ALL
#      SELECT pl_title
#      FROM direct
#      WHERE direct.page_id IS NULL
#) AS all_links
#GROUP BY pl_title

SELECT *
FROM direct
SQL

Checking query status...
