select
	message,
	coalesce(sum(case when ts='20160619' then num end), 0) as `20160619`,
	coalesce(sum(case when ts='20160620' then num end), 0) as `20160620`,
	coalesce(sum(case when ts='20160621' then num end), 0) as `20160621`,
	coalesce(sum(case when ts='20160622' then num end), 0) as `20160622`,
	coalesce(sum(case when ts='20160623' then num end), 0) as `20160623`,
	coalesce(sum(case when ts='20160624' then num end), 0) as `20160624`,
	coalesce(sum(case when ts='20160625' then num end), 0) as `20160625`,
	coalesce(sum(case when ts='20160626' then num end), 0) as `20160626`,
	coalesce(sum(case when ts='20160627' then num end), 0) as `20160627`,
	coalesce(sum(case when ts='20160628' then num end), 0) as `20160628`,
	coalesce(sum(case when ts='20160629' then num end), 0) as `20160629`,
	coalesce(sum(case when ts='20160630' then num end), 0) as `20160630`,
	coalesce(sum(case when ts='20160701' then num end), 0) as `20160701`,
	coalesce(sum(case when ts='20160702' then num end), 0) as `20160702`,
	coalesce(sum(case when ts='20160703' then num end), 0) as `20160703`,
	coalesce(sum(case when ts='20160704' then num end), 0) as `20160704`,
	coalesce(sum(case when ts='20160705' then num end), 0) as `20160705`,
	coalesce(sum(case when ts='20160706' then num end), 0) as `20160706`,
	coalesce(sum(case when ts='20160707' then num end), 0) as `20160707`,
	coalesce(sum(case when ts='20160708' then num end), 0) as `20160708`,
	coalesce(sum(case when ts='20160709' then num end), 0) as `20160709`,
	coalesce(sum(case when ts='20160710' then num end), 0) as `20160710`,
	coalesce(sum(case when ts='20160711' then num end), 0) as `20160711`,
	coalesce(sum(case when ts='20160712' then num end), 0) as `20160712`,
	coalesce(sum(case when ts='20160713' then num end), 0) as `20160713`,
	coalesce(sum(case when ts='20160714' then num end), 0) as `20160714`,
	coalesce(sum(case when ts='20160715' then num end), 0) as `20160715`,
	coalesce(sum(case when ts='20160716' then num end), 0) as `20160716`,
	coalesce(sum(case when ts='20160717' then num end), 0) as `20160717`,
	coalesce(sum(case when ts='20160718' then num end), 0) as `20160718`,
	coalesce(sum(case when ts='20160719' then num end), 0) as `20160719`,
	coalesce(sum(case when ts='20160720' then num end), 0) as `20160720`,
	coalesce(sum(case when ts='20160721' then num end), 0) as `20160721`,
	coalesce(sum(case when ts='20160722' then num end), 0) as `20160722`,
	coalesce(sum(case when ts='20160723' then num end), 0) as `20160723`,
	coalesce(sum(case when ts='20160724' then num end), 0) as `20160724`,
	coalesce(sum(case when ts='20160725' then num end), 0) as `20160725`
from (
	select
		replace(
			replace(
				trim(trailing '\r\n' from event_message),
				'[JavaScript Error: "info.video is undefined" {file: "resource://firefogg/encoder.jsm" line: 356}]''[JavaScript Error: "info.video is undefined" {fil',
				''
			),
			' result=null error=null',
			''
		) as message,
		ts,
		num
	from (
		select event_message, left(timestamp,8) as ts, count(*) as num
		from UploadWizardExceptionFlowEvent_11772722
		where timestamp > '20160619' and event_url like 'https://commons.wikimedia.org%'
		and event_message in (
			select event_message 
			from UploadWizardExceptionFlowEvent_11772722
			where timestamp > '20160619' and event_url like 'https://commons.wikimedia.org%'
			group by event_message
			having count(*)>10
		)
		group by event_message, left(timestamp,8)
		union
		select null, left(timestamp,8) as ts, count(*) as num
		from UploadWizardExceptionFlowEvent_11772722
		where timestamp > '20160619' and event_url like 'https://commons.wikimedia.org%'
		and event_message not in (
			select event_message 
			from UploadWizardExceptionFlowEvent_11772722
			where timestamp > '20160619' and event_url like 'https://commons.wikimedia.org%'
			group by event_message
			having count(*)>10
		)
		group by left(timestamp,8)
	) u
	where event_message is null
	or (
		event_message not like '%resolve%'
		and event_message not like '%toLowerCase%'
		and event_message not like '%extension is null%'
	)
	order by ts, num, message
) v
group by message;