use commonswiki;
select
	date(date) as date,
	crosswikiupload_good,
	crosswikiupload_bad,
	uploadwizard_good,
	uploadwizard_bad,
	gwtoolset_good,
	gwtoolset_bad,
	vicuna_good,
	vicuna_bad,
	magnusoauth_good,
	magnusoauth_bad,
	androidapp_good,
	androidapp_bad,
	iosapp_good,
	iosapp_bad,
	total_good - crosswikiupload_good-uploadwizard_good-gwtoolset_good-vicuna_good-magnusoauth_good-androidapp_good-iosapp_good as other_good,
	total_bad - crosswikiupload_bad-uploadwizard_bad-gwtoolset_bad-vicuna_bad-magnusoauth_bad-androidapp_bad-iosapp_bad as other_bad
from (
	select
		left( log_timestamp, 8 ) as date,
		sum( exist = 1 and ts_tags like '%cross-wiki-upload%' ) as crosswikiupload_good,
		sum( exist = 0 and ts_tags like '%cross-wiki-upload%' ) as crosswikiupload_bad,
		sum( exist = 1 and log_comment = 'User created page with UploadWizard' ) as uploadwizard_good,
		sum( exist = 0 and log_comment = 'User created page with UploadWizard' ) as uploadwizard_bad,
		sum( exist = 1 and ts_tags like '%gwtoolset%' ) as gwtoolset_good,
		sum( exist = 0 and ts_tags like '%gwtoolset%' ) as gwtoolset_bad,
		sum( exist = 1 and log_comment like 'VicuñaUploader%' ) as vicuna_good,
		sum( exist = 0 and log_comment like 'VicuñaUploader%' ) as vicuna_bad,
		sum( exist = 1 and ts_tags like '%OAuth CID: 67%' ) as magnusoauth_good,
		sum( exist = 0 and ts_tags like '%OAuth CID: 67%' ) as magnusoauth_bad,
		sum( exist = 1 and log_comment = 'Uploaded using Android Commons app' ) as androidapp_good,
		sum( exist = 0 and log_comment = 'Uploaded using Android Commons app' ) as androidapp_bad,
		sum( exist = 1 and log_comment = 'Uploaded with Commons for iOS' ) as iosapp_good,
		sum( exist = 0 and log_comment = 'Uploaded with Commons for iOS' ) as iosapp_bad,
		sum( exist = 1 ) as total_good,
		sum( exist = 0 ) as total_bad
	from (
		-- List of all uploads with tags and whether the page has still existed a month later
		select
			logging.log_title, logging.log_user, logging.log_timestamp, logging.log_comment,
			cast(ts_tags as char) as ts_tags,
			-- (page_id is not null) as exist
			(logging_delete.log_id is null) as exist
		from logging
		left join tag_summary on ts_log_id=log_id
		-- left join page on page_namespace=6 and page_title=log_title
		left join logging as logging_delete on (
			-- Log entries for deletion of this page within a month
			logging_delete.log_namespace=6
			and logging_delete.log_title=logging.log_title
			and logging_delete.log_type='delete'
			and logging_delete.log_timestamp between logging.log_timestamp and logging.log_timestamp+00000100000000
		)
		where logging.log_type='upload' and logging.log_action='upload'
		and logging.log_timestamp between '20160701' and '20160820'
		and logging.log_timestamp = (
			-- First upload by this user
			select log_timestamp
			from logging as logging2
			where logging2.log_type='upload' and logging2.log_action='upload'
			and logging2.log_user=logging.log_user
			order by log_user, log_timestamp asc limit 1
		)
	) t
	group by left( log_timestamp, 8 )
) u;