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

SQL

use commonswiki_p;
select
	concat(major_mime, '/', minor_mime) as mime,
	case
		when width*height/1000000 < 10 then floor(width*height/200000)/5
		when width*height/1000000 < 30 then floor(width*height/1000000)
		else floor(width*height/10000000)*10
	end as mpx,
	sum(alive) as alive,
	count(*) - sum(alive) as dead
from (
	select
		-- log_id, log_title, log_user,
		(img_name is not null) as alive,
		-- substring(log_comment, 24) as source,
		-- coalesce(img_size,   fa_size  ) as size,
		coalesce(img_width,  fa_width ) as width,
		coalesce(img_height, fa_height) as height,
		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, case
	when width*height/1000000 < 10 then floor(width*height/200000)*200000
	when width*height/1000000 < 30 then floor(width*height/1000000)*1000000
	else floor(width*height/10000000)*10000000
end
order by mime, mpx;
SQL

Checking query status...
