Untitled query #84036

SQL

WITH cte_math (page_id, page_title, priority) AS (
  SELECT
  	main.page_id,
	main.page_title,
    REPLACE(cl.cl_to,'-priority_mathematics_articles','')
  FROM page as talk
  INNER JOIN page AS main ON main.page_title = talk.page_title
  INNER JOIN categorylinks AS cl ON cl.cl_from = talk.page_id 

  WHERE
    main.page_title NOT IN (
      SELECT page_title FROM page
      INNER JOIN categorylinks AS cl ON cl.cl_from = page.page_id
      WHERE cl.cl_to IN (
    	'FA-Class_biography_articles',
    	'FL-Class_biography_articles',
    	'FM-Class_biography_articles',
    	'GA-Class_biography_articles',
    	'B-Class_biography_articles',
    	'C-Class_biography_articles',
    	'Start-Class_biography_articles'
    	'Stub-Class_biography_articles',
    	'List-Class_biography_articles',
    	'Disambig-Class_biography_articles',
    	'Redirect-Class_biography_articles',
    	'Unassessed_biography_articles'))
    AND main.page_namespace = 0
    AND	main.page_is_redirect = 0
  	AND cl.cl_to IN (
	  'Top-priority_mathematics_articles',
	  'High-priority_mathematics_articles',
	  'Mid-priority_mathematics_articles',
	  'Low-priority_mathematics_articles',
	  'NA-priority_mathematics_articles',
      'Unknown-priority_mathematics_articles')
)

SELECT
    cte_math.page_title AS from_page,
    # cte_math.priority as priority,
    lt.lt_title AS to_page
    #SUM(CASE WHEN cte_math.priority = 'Top' THEN 1 ELSE 0 END) AS Top,
    #SUM(CASE WHEN cte_math.priority = 'High' THEN 1 ELSE 0 END) AS High,
    #SUM(CASE WHEN cte_math.priority = 'Mid' THEN 1 ELSE 0 END) AS Mid,
    #SUM(CASE WHEN cte_math.priority = 'Low' THEN 1 ELSE 0 END) AS Low,
    #SUM(CASE WHEN cte_math.priority = 'NA' OR cte_math.priority = 'Unknown' THEN 1 ELSE 0 END) AS NA
  FROM pagelinks AS pl
  INNER JOIN cte_math ON pl.pl_from = cte_math.page_id
  INNER JOIN linktarget AS lt ON pl.pl_target_id = lt.lt_id
  WHERE
    lt.lt_title IN (SELECT page_title FROM cte_math) # = 'Mathematics' 
    AND lt.lt_namespace = 0
    AND pl.pl_from_namespace = 0
  #GROUP BY lt.lt_title
	# mismatch when lt.lt_title = 'Mathematics'??
    # 8073 using DISTINCT vs 8095 (131+437+1687+4237+1603) using SUM(CASE cte_math.priority)
SQL

Checking query status...
