Untitled query #12649

This query is marked as a draft This query has been published by Strainu.
SQL

use rowiki;
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;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query Stop Query
All SQL code is licensed under CC0 License.
SQL

Checking query status...
