4 - Quarry

SQL

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
    )
  )  
)

# ignoring hidden 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'
)

# not 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 LIKE "%مجموعه‌نمایه%"
  )
)

GROUP BY cl1.cl_to
ORDER BY COUNT(cl1.cl_to) DESC
LIMIT 100;
SQL

Checking query status...
