uploadwizard-stats-exceptions-byday.sql
matmarex (Bartosz Dziewoński)
Actions

Authored By

	• matmarex
	Sep 30 2016, 9:19 PM

Size

6 KB

Referenced Files

None

Subscribers

None

uploadwizard-stats-exceptions-byday.sql
View Options

	select
	message,
	coalesce(sum(case when ts=date_format(date_sub(now(), interval 35 day), '%Y%m%d') then num end), 0) as `-35`,
	coalesce(sum(case when ts=date_format(date_sub(now(), interval 34 day), '%Y%m%d') then num end), 0) as `-34`,
	coalesce(sum(case when ts=date_format(date_sub(now(), interval 33 day), '%Y%m%d') then num end), 0) as `-33`,
	coalesce(sum(case when ts=date_format(date_sub(now(), interval 32 day), '%Y%m%d') then num end), 0) as `-32`,
	coalesce(sum(case when ts=date_format(date_sub(now(), interval 31 day), '%Y%m%d') then num end), 0) as `-31`,
	coalesce(sum(case when ts=date_format(date_sub(now(), interval 30 day), '%Y%m%d') then num end), 0) as `-30`,
	coalesce(sum(case when ts=date_format(date_sub(now(), interval 29 day), '%Y%m%d') then num end), 0) as `-29`,
	coalesce(sum(case when ts=date_format(date_sub(now(), interval 28 day), '%Y%m%d') then num end), 0) as `-28`,
	coalesce(sum(case when ts=date_format(date_sub(now(), interval 27 day), '%Y%m%d') then num end), 0) as `-27`,
	coalesce(sum(case when ts=date_format(date_sub(now(), interval 26 day), '%Y%m%d') then num end), 0) as `-26`,
	coalesce(sum(case when ts=date_format(date_sub(now(), interval 25 day), '%Y%m%d') then num end), 0) as `-25`,
	coalesce(sum(case when ts=date_format(date_sub(now(), interval 24 day), '%Y%m%d') then num end), 0) as `-24`,
	coalesce(sum(case when ts=date_format(date_sub(now(), interval 23 day), '%Y%m%d') then num end), 0) as `-23`,
	coalesce(sum(case when ts=date_format(date_sub(now(), interval 22 day), '%Y%m%d') then num end), 0) as `-22`,
	coalesce(sum(case when ts=date_format(date_sub(now(), interval 21 day), '%Y%m%d') then num end), 0) as `-21`,
	coalesce(sum(case when ts=date_format(date_sub(now(), interval 20 day), '%Y%m%d') then num end), 0) as `-20`,
	coalesce(sum(case when ts=date_format(date_sub(now(), interval 19 day), '%Y%m%d') then num end), 0) as `-19`,
	coalesce(sum(case when ts=date_format(date_sub(now(), interval 18 day), '%Y%m%d') then num end), 0) as `-18`,
	coalesce(sum(case when ts=date_format(date_sub(now(), interval 17 day), '%Y%m%d') then num end), 0) as `-17`,
	coalesce(sum(case when ts=date_format(date_sub(now(), interval 16 day), '%Y%m%d') then num end), 0) as `-16`,
	coalesce(sum(case when ts=date_format(date_sub(now(), interval 15 day), '%Y%m%d') then num end), 0) as `-15`,
	coalesce(sum(case when ts=date_format(date_sub(now(), interval 14 day), '%Y%m%d') then num end), 0) as `-14`,
	coalesce(sum(case when ts=date_format(date_sub(now(), interval 13 day), '%Y%m%d') then num end), 0) as `-13`,
	coalesce(sum(case when ts=date_format(date_sub(now(), interval 12 day), '%Y%m%d') then num end), 0) as `-12`,
	coalesce(sum(case when ts=date_format(date_sub(now(), interval 11 day), '%Y%m%d') then num end), 0) as `-11`,
	coalesce(sum(case when ts=date_format(date_sub(now(), interval 10 day), '%Y%m%d') then num end), 0) as `-10`,
	coalesce(sum(case when ts=date_format(date_sub(now(), interval 9 day), '%Y%m%d') then num end), 0) as `-9`,
	coalesce(sum(case when ts=date_format(date_sub(now(), interval 8 day), '%Y%m%d') then num end), 0) as `-8`,
	coalesce(sum(case when ts=date_format(date_sub(now(), interval 7 day), '%Y%m%d') then num end), 0) as `-7`,
	coalesce(sum(case when ts=date_format(date_sub(now(), interval 6 day), '%Y%m%d') then num end), 0) as `-6`,
	coalesce(sum(case when ts=date_format(date_sub(now(), interval 5 day), '%Y%m%d') then num end), 0) as `-5`,
	coalesce(sum(case when ts=date_format(date_sub(now(), interval 4 day), '%Y%m%d') then num end), 0) as `-4`,
	coalesce(sum(case when ts=date_format(date_sub(now(), interval 3 day), '%Y%m%d') then num end), 0) as `-3`,
	coalesce(sum(case when ts=date_format(date_sub(now(), interval 2 day), '%Y%m%d') then num end), 0) as `-2`,
	coalesce(sum(case when ts=date_format(date_sub(now(), interval 1 day), '%Y%m%d') then num end), 0) as `-1`,
	coalesce(sum(case when ts=date_format( now() , '%Y%m%d') then num end), 0) as `0`
	from (
	select
	trim(trailing '\r\n' from event_message) as message,
	ts,
	num
	from (
	select event_message, left(timestamp,8) as ts, count(*) as num
	from UploadWizardExceptionFlowEvent_11772722
	where timestamp > date_format(date_sub(now(), interval 35 day), '%Y%m%d')
	and event_url like 'https://commons.wikimedia.org%'
	and event_message in ( select * from (
	select event_message
	from UploadWizardExceptionFlowEvent_11772722
	where timestamp > date_format(date_sub(now(), interval 35 day), '%Y%m%d')
	and event_url like 'https://commons.wikimedia.org%'
	and (
	-- Errors known to come from browser extensions etc.
	event_message not like '%resolve%'
	and event_message not like '%toLowerCase%'
	and event_message not like '%extension is null%'
	and event_message not like '%__gCrWeb.autofill.extractForms%'
	and event_message not like '%Permission denied to access property "toString"%'
	and event_message not like "%Cannot set property 'tgt' of null%"
	)
	group by event_message
	order by count(*) desc limit 23
	) temp )
	group by event_message, left(timestamp,8)
	union
	select null, left(timestamp,8) as ts, count(*) as num
	from UploadWizardExceptionFlowEvent_11772722
	where timestamp > date_format(date_sub(now(), interval 35 day), '%Y%m%d')
	and event_url like 'https://commons.wikimedia.org%'
	and event_message in ( select * from (
	select event_message
	from UploadWizardExceptionFlowEvent_11772722
	where timestamp > date_format(date_sub(now(), interval 35 day), '%Y%m%d')
	and event_url like 'https://commons.wikimedia.org%'
	and (
	-- Errors known to come from browser extensions etc.
	event_message not like '%resolve%'
	and event_message not like '%toLowerCase%'
	and event_message not like '%extension is null%'
	and event_message not like '%__gCrWeb.autofill.extractForms%'
	and event_message not like '%Permission denied to access property "toString"%'
	and event_message not like "%Cannot set property 'tgt' of null%"
	)
	group by event_message
	order by count(*) desc limit 999999 offset 23
	) temp )
	group by left(timestamp,8)
	) u
	order by ts, num, message
	) v
	group by message;

File Metadata

Mime Type: text/plain
Storage Engine: blob
Storage Format: Raw Data
Storage Handle: 4024238
Default Alt Text: uploadwizard-stats-exceptions-byday.sql (6 KB)

uploadwizard-stats-exceptions-byday.sqlmatmarex (Bartosz Dziewoński)Actions

uploadwizard-stats-exceptions-byday.sqlView Options

File Metadata

Event Timeline

uploadwizard-stats-exceptions-byday.sql
matmarex (Bartosz Dziewoński)
Actions

uploadwizard-stats-exceptions-byday.sql
View Options