SELECT DISTINCT concat('# [[:en:template:',REPLACE(tmpl.page_title,'_',' '),']]') template, COUNT(tmpl.page_title) cnt

#SELECT DISTINCT concat('# [[:en:template:',REPLACE(tmpl.page_title,'_',' '),']]') template, pl_title en_article, ll_title fa_article

FROM enwiki_p.page AS tmpl JOIN enwiki_p.pagelinks ON pl_from=tmpl.page_id JOIN enwiki_p.page AS artl ON artl.page_title=pl_title JOIN enwiki_p.langlinks AS artlll ON artl.page_id=artlll.ll_from

WHERE tmpl.page_namespace = 10

AND tmpl.page_is_redirect = 0

AND tmpl.page_is_redirect = 0

AND artl.page_namespace = 0

AND artl.page_is_redirect = 0

AND pl_from_namespace = 10

AND pl_namespace = 0

AND artlll.ll_lang = "fa"

​

AND tmpl.page_id NOT IN (

  SELECT tmplll.ll_from

  FROM langlinks AS tmplll

  WHERE tmplll.ll_lang = "fa"

)

​

AND REPLACE(ll_title,' ','_') IN (

SELECT p1.page_title

FROM fawiki_p.page AS p1

WHERE p1.page_is_redirect = 0

AND p1.page_namespace = 0

​

# fa article is not a DAB page

AND p1.page_id NOT IN (

  SELECT pp_page

  FROM fawiki_p.page_props

  WHERE pp_propname = 'disambiguation'

)

​

# fa article receives no direct link 

AND NOT (

  p1.page_title IN (

    SELECT pl1.pl_title

    FROM fawiki_p.pagelinks AS pl1

    WHERE pl1.pl_namespace = 0

    AND pl1.pl_from_namespace = 0

    AND pl1.pl_from NOT IN (

    SELECT rd_from

    FROM fawiki_p.redirect

    WHERE rd_namespace = 0

    AND rd_title = p1.page_title

  )

  )

# fa article receives no redirect link 

  OR (

    p1.page_title IN (

      SELECT rd_title

      FROM fawiki_p.redirect JOIN fawiki_p.page AS p2 ON p2.page_id = rd_from JOIN fawiki_p.pagelinks AS pl2 ON p2.page_title = pl2.pl_title

      WHERE rd_namespace = 0

      AND p2.page_is_redirect = 1

      AND p2.page_namespace = 0

      AND pl2.pl_namespace = 0

      AND pl2.pl_from_namespace = 0

    )

  )  

)

)

# exclude Some tempaltes

AND (

  tmpl.page_title NOT LIKE "Taxonomy%"

  AND tmpl.page_title NOT LIKE "%/doc%"

  AND tmpl.page_title NOT LIKE "POTD/%"

)

​

GROUP BY tmpl.page_title HAVING COUNT(tmpl.page_title) > 1

ORDER BY COUNT(tmpl.page_title) DESC;