unused user images on commons

unused user images on commons, credits to Bawolff and https://wikitech.wikimedia.org/wiki/Nova_Resource:Tools/Shared_Resources/MySQL_queries for most of the work.

SQL

use commonswiki_p;
SELECT CONCAT("[[:File:", img_name, "]]" ) as 'link', img_timestamp as 'upload date', img_user_text as 'uploader'
FROM image
JOIN page p1
ON p1.page_namespace = 6
AND p1.page_title = img_name
JOIN categorylinks
ON cl_from = page_id
AND cl_type = "file"
AND cl_to = "User_page_images"
LEFT JOIN globalimagelinks
ON gil_to = img_name
LEFT join imagelinks
ON il_to = img_name
left join page p2 on il_from = p2.page_id and p2.page_title not like 'OgreBot/%'
WHERE gil_to IS NULL and p2.page_id is NULL
GROUP BY img_name
HAVING count(cl_to)=1
ORDER BY img_user_text desc;

SQL

Checking query status...