BEIC files by usage and available article translations

Sorted by number of connected pages which lack the file, filtered to remove the files which are already available in all or most of the connected pages.

SQL

USE itwiki_p;
SELECT CONCAT('[[:File:', page_title, ']]') AS file, CONCAT('[[', g.gil_page_title, ']]') as article, COUNT(distinct(gil_wiki)) AS wikis, COUNT(distinct(ll_lang)) AS translations, COUNT(distinct(ll_lang))-COUNT(distinct(gil_wiki))+1 as difference
FROM commonswiki_p.categorylinks c
JOIN commonswiki_p.page p
ON c.cl_to = 'Media_from_BEIC'
AND c.cl_from = p.page_id
JOIN commonswiki_p.globalimagelinks g
ON g.gil_page_namespace = 0
AND p.page_title = g.gil_to
LEFT JOIN langlinks l
ON g.gil_page = l.ll_from
AND gil_wiki = 'itwiki'
GROUP BY file
NOT HAVING wikis > translations -- Minimum restriction
AND wikis > translations/2 -- Stricter
ORDER BY difference DESC;

SQL

Checking query status...