USE fawiki_p;

#SELECT p1.page_title Article, p2.page_title Template, COUNT(p2.page_title)

​

SELECT concat('# {{الگوب|',p2.page_title,'}}') Template, COUNT(p2.page_title) count

FROM page AS p1 JOIN pagelinks ON pl_title = p1.page_title JOIN page AS p2 ON pl_from = p2.page_id

WHERE pl_from_namespace = 10

AND pl_namespace = 0

AND p2.page_is_redirect = 0

AND p2.page_namespace = 10

AND p1.page_is_redirect = 0

AND p1.page_namespace = 0

# not a DAB page

AND p1.page_id NOT IN (

  SELECT pp_page

  FROM page_props

  WHERE pp_propname = 'disambiguation'

)

​

# not receiving direct link 

AND NOT (

  p1.page_title IN (

    SELECT pl1.pl_title

    FROM pagelinks AS pl1

    WHERE pl1.pl_namespace = 0

    AND pl1.pl_from_namespace = 0

    AND pl1.pl_from NOT IN (

    SELECT rd_from

    FROM redirect

    WHERE rd_namespace = 0

    AND rd_title = p1.page_title

  )

  )

# not receiving redirect link 

  OR (

    p1.page_title IN (

      SELECT rd_title

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

      WHERE rd_namespace = 0

      AND p3.page_is_redirect = 1

      AND p3.page_namespace = 0

      AND pl2.pl_namespace = 0

      AND pl2.pl_from_namespace = 0

    )

  )  

)

​

# not having particular templates

AND p2.page_title NOT LIKE "%Taxonomy%"

AND p2.page_title NOT LIKE "%نر_محافظت_شده%"

AND p2.page_title NOT LIKE "%نر/%"

AND p2.page_title NOT LIKE "%/توضیحات"

​

​

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

ORDER BY COUNT(p2.page_title) DESC;