USE fawiki_p;

SELECT p1.page_title Article, cl1.cl_to Category, COUNT(cl1.cl_to)

FROM page AS p1 JOIN categorylinks AS cl1 ON cl_from = p1.page_id

WHERE 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 p2 ON p2.page_id = rd_from JOIN 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

    )

  )  

)

​

# excluding particular categories

AND NOT EXISTS (

  SELECT null

  FROM page AS p3 JOIN page_props ON pp_page =p3.page_id

  WHERE cl1.cl_to = p3.page_title

  AND p3.page_is_redirect = 0

  AND p3.page_namespace = 14

  AND (

    pp_propname = 'hiddencat'

    OR cl1.cl_to = "افراد_زنده" 

    OR cl1.cl_to = "سیاهه_قرمز_IUCN_از_گونه•های_با_کمترین_نگرانی"

  )

)

​

# excluding articles having particular categories

AND p1.page_id NOT IN (

  SELECT cl2.cl_from

  FROM categorylinks AS cl2

  WHERE (

    cl2.cl_to = "نام•های_خانوادگی"

    OR cl2.cl_to = "نام•های_کوچک"

    OR cl2.cl_to = "صفحه•های_حذف_زمان•دار" 

    OR cl2.cl_to LIKE "%مجموعه•نمایه%"

  )

)

GROUP BY cl1.cl_to

ORDER BY COUNT(cl1.cl_to) DESC

LIMIT 100

;