WITH pages_sans_talk_pages AS (

​

SELECT DISTINCT 

  p.page_title as page_title,

  p.page_namespace as page_namespace,

  p.page_is_redirect as page_is_redirect,

  p.page_id as 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

  3

), wikiprojects AS (

SELECT 

  p.page_title as 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_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

​

) SELECT 

  CONCAT("[[", page_title, "]]") AS page_title,

  CONCAT("[[Talk:", page_title, "]]") AS talk_title,

  CONCAT("{{WikiProject banner shell|{{", group_concat( DISTINCT lt_title SEPARATOR '}} {{' LIMIT 15) , "}} }}") AS suggestion

FROM  wikiprojects

GROUP BY page_title