Cross-wiki uploads to Commons and their deletion rates by MIME type

https://commons.wikimedia.org/wiki/Commons:Administrators'_noticeboard?oldid=199115948#AbuseFilter_for_cross-wiki_uploads

SQL

use commonswiki_p;
select
	concat(major_mime, '/', minor_mime) as mime,
	sum(alive) as alive,
	count(*) - sum(alive) as dead
from (
	select
		(img_name is not null) as alive,
		coalesce(img_major_mime, fa_major_mime) as major_mime,
		coalesce(img_minor_mime, fa_minor_mime) as minor_mime
	from logging
	join change_tag on log_id=ct_log_id
	left join image on img_name=log_title
	left join filearchive on fa_name=log_title
	where log_type='upload' and log_action='upload'
	and ct_tag='cross-wiki-upload'
	and (img_name is not null or fa_name is not null)
) t
group by major_mime, minor_mime;

SQL

Checking query status...