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
)

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
SQL

Checking query status...
