4 - Quarry

This query is marked as a draft This query has been published by Dalba.
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
join (
  
  
  
  
SELECT cl1.cl_to Category, COUNT(cl1.cl_to) cat_count
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
) as innter_table on cl1.cl_to =  innter_table.Category




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 "%مجموعه‌نمایه%"
  )
)
ORDER BY cat_count DESC
LIMIT 100;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query Stop Query
All SQL code is licensed under CC0 License.
SQL

Checking query status...
