BEIC popular cats based on GLAMdashboard

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 page_id) AS total
FROM page
INNER JOIN categorylinks AS c ON page_id=c.cl_from AND c.cl_to NOT IN ("Scans_by_the_Internet_Archive_selected_by_BEIC")
INNER JOIN categorylinks AS cc on page_id=cc.cl_from AND cc.cl_to IN ("Scans_by_the_Internet_Archive_selected_by_BEIC")
WHERE c.cl_to NOT REGEXP "Files_from|CC-BY|Files_with|test_|Uploaded_with|Self-pub|Items_with_OTRS|GFDL|FAL|PD-|uploaded_by_|Flickr_images_reviewed|Flickr_images_uploaded"
GROUP BY c.cl_to
HAVING total>1
ORDER BY COUNT(page_id) DESC
LIMIT 10;

SQL

Checking query status...