-- This query is orphaned images from anyone who has uploaded over 20 orphaned images:

​

USE enwiki_p;

​

SELECT CONCAT(REPLACE(REPLACE(p.page_title, '"', '**DOUBLEQUOTE**'), '_', ' '), ' |') 

FROM page p

INNER JOIN image ON p.page_title = img_name

LEFT JOIN imagelinks ON p.page_title = il_to

WHERE il_from IS NULL -- Orphaned

AND img_user IN 

(

SELECT user_id

FROM user

INNER JOIN image ON img_user = user_id

LEFT JOIN imagelinks i ON img_name = i.il_to 

WHERE i.il_from IS NULL

GROUP BY user_name

HAVING COUNT(*) > 20

)

​

-- This query is anyone who has uploaded over 20 orphaned images:

​

/*

​

USE enwiki_p;

SELECT CONCAT(CONCAT('*{{user17|', user_name), '}}'), COUNT(*)

FROM user

INNER JOIN image ON img_user = user_id

LEFT JOIN imagelinks i ON img_name = i.il_to 

WHERE i.il_from IS NULL

GROUP BY user_name

HAVING COUNT(*) > 20

ORDER BY 2 DESC

​

*/