BEIC (Paolo Monti) popular categories, top 1000 wanted categories

Fork of BEIC (Paolo Monti) popular categories, top 1000 by Nemo bis

This query is marked as a draft This query has been published by Nemo bis.

See request at https://commons.wikimedia.org/wiki/Commons_talk:GLAM_dashboard#More_than_100_categories

The query finds categories which are used by a page in the bucket category AND where the categories are not children of the bucket category. The default GLAMdashboard report is limited to the top 100 matches.

SQL

USE commonswiki_p;
SELECT c.cl_to AS category,
COUNT(DISTINCT file.page_id) AS total
FROM page AS file
INNER JOIN categorylinks AS c ON file.page_id=c.cl_from AND c.cl_to NOT IN ("Photographs_by_Paolo_Monti")
INNER JOIN categorylinks AS cc ON file.page_id=cc.cl_from AND cc.cl_to IN ("Photographs_by_Paolo_Monti")
LEFT JOIN page AS p ON p.page_namespace = 14 AND p.page_title = c.cl_to
WHERE c.cl_to NOT REGEXP "Media_from_BEIC|Files_from|CC-BY|Files_with|test_|Uploaded_with|Self-pub|Items_with_OTRS|GFDL|FAL|PD-|PD_Old|uploaded_by_|Flickr_images_reviewed|Flickr_images_uploaded"
AND p.page_id IS NULL
GROUP BY c.cl_to
HAVING total>1
ORDER BY COUNT(file.page_id) DESC
LIMIT 5000;

SQL

Checking query status...