Old articles that probably need talk pages, with suggested Wikiprojects

SQL

WITH pages_sans_talk_pages AS (

SELECT DISTINCT 
  p.page_title,
  p.page_namespace,
  p.page_is_redirect,
  p.page_id
FROM
  revision AS r,
  page AS p
  -- only incude pages without a talk page
  LEFT OUTER JOIN page AS p2 ON  p2.page_title = p.page_title AND p2.page_namespace = 1 AND p.page_namespace = 0
  -- only include pages without the 'disambiguation' page property
  left outer join page_props on pp_page = p.page_id and pp_propname = "disambiguation"
  -- exclude members of this category
  left outer join categorylinks as c1 on c1.cl_type = "page" and c1.cl_from = p.page_id and c1.cl_to = "All_set_index_articles"
  -- exclude members of this category
  left outer join categorylinks as c2 on c2.cl_type = "page" and c2.cl_from = p.page_id and c2.cl_to = "Redirects_to_Wiktionary"
where 
  c1.cl_from is null 
  AND c2.cl_from is null 
  AND pp_page is null
  AND p2.page_title IS NULL
  AND p.page_namespace = 0
  AND p.page_is_redirect = 0
  AND r.rev_id = p.page_latest
  -- restrict to old pages
  AND r.rev_timestamp < 20190000000000
  -- ORDER BY   r.rev_timestamp
  LIMIT
  5
), wikiprojects AS (
SELECT 
  p.page_title,
  lt.lt_title AS lt_title,
  COUNT(p2.page_id) AS cnt
FROM
  pages_sans_talk_pages AS p,
  page AS p2,
  pagelinks AS pl,
  templatelinks AS tl,
  linktarget AS lt
 where 
  p.page_title = 'OnGreen'
  AND p.page_namespace = 0
  AND p.page_is_redirect = 0
  
  AND p.page_id = pl.pl_from
  AND pl.pl_namespace = 0
  AND pl.pl_from_namespace = 0

  AND p2.page_title = pl.pl_title
  AND p2.page_id = tl_from
  AND tl.tl_target_id = lt.lt_id
  AND tl.tl_from_namespace = 1
  AND lt.lt_namespace = 10
  
  AND lt.lt_title LIKE 'WikiProject\_%'
  AND NOT lt.lt_title LIKE '%/class'
  AND NOT lt.lt_title LIKE '%/importance'
  AND NOT lt.lt_title LIKE '%/styles.css'
  AND NOT lt.lt_title LIKE '%/portalbox'
  AND NOT lt.lt_title LIKE 'WikiProject\__leanup\__ist'
  AND NOT lt.lt_title LIKE 'WikiProject\__anner\__hell'

  GROUP BY lt.lt_title
  ORDER BY cnt DESC

LIMIT  10
) SELECT 
  CONCAT("[[", page_title, "]]") AS page_title,
  CONCAT("[[Talk:", page_title, "]]") AS talk_title,
  CONCAT("{{", group_concat( DISTINCT lt_title SEPARATOR '}} {{' LIMIT 10) , "}}") wikiprojects
FROM wikiprojects
SQL

Checking query status...
