Data envy workbook May 20 2016

Fork of Highly active editors who use the Visual Editor by Jmorgan (WMF)

This query is marked as a draft This query has been published by Jmorgan (WMF).

SQL

#get the list of all possible tags

#SELECT DISTINCT ct_tag FROM enwiki_p.change_tag; 

#find all mobile app edits to the WIkipedia article 'panama papers'
#tables
#page table - has the title and page_id of the article
#revision table - has data on which edits (rev_id) were made to this page_id
#change_tag table - has all the tags associated with a particular revision (rev_id)

#SELECT * FROM enwiki_p.revision AS r
#JOIN enwiki_p.page AS p
#ON p.page_id = r.rev_page 
#JOIN enwiki_p.change_tag AS ct ON r.rev_id = ct.ct_rev_id
#WHERE p.page_id = 50034356
#AND ct.ct_tag = "mobile app edit";

#SELECT tmp1.mobile_app_count, tmp2.mobile_web_count FROM
#(SELECT COUNT(r.rev_id) AS mobile_app_count FROM enwiki_p.revision AS r
#JOIN enwiki_p.page AS p
#ON p.page_id = r.rev_page 
#JOIN enwiki_p.change_tag AS ct ON r.rev_id = ct.ct_rev_id
#WHERE p.page_id = 50034356
#AND ct.ct_tag = "mobile app edit") AS tmp1,
#(SELECT COUNT(r.rev_id) AS mobile_web_count FROM enwiki_p.revision AS r
#JOIN enwiki_p.page AS p
#ON p.page_id = r.rev_page 
#JOIN enwiki_p.change_tag AS ct ON r.rev_id = ct.ct_rev_id
#WHERE p.page_id = 50034356
#AND ct.ct_tag = "mobile web edit") AS tmp2;

#SELECT COUNT(r.rev_id) FROM enwiki_p.revision AS r WHERE r.rev_page = 50034356;
/*
SELECT COUNT(r.rev_id) AS all_mobile_count FROM enwiki_p.revision AS r
JOIN enwiki_p.page AS p
ON p.page_id = r.rev_page 
JOIN enwiki_p.change_tag AS ct ON r.rev_id = ct.ct_rev_id
WHERE p.page_id = 50034356
AND ct.ct_tag = "mobile edit";
*/

SELECT SUM(tmp1.mobile_app_count) AS mac FROM
(SELECT r.rev_id AS mobile_app_count FROM enwiki_p.revision AS r
JOIN enwiki_p.page AS p
ON p.page_id = r.rev_page 
JOIN enwiki_p.change_tag AS ct ON r.rev_id = ct.ct_rev_id
WHERE p.page_id = 50034356
AND ct.ct_tag = "mobile app edit") AS tmp1
/*
(SELECT r.rev_id AS mobile_web_count FROM enwiki_p.revision AS r
JOIN enwiki_p.page AS p
ON p.page_id = r.rev_page 
JOIN enwiki_p.change_tag AS ct ON r.rev_id = ct.ct_rev_id
WHERE p.page_id = 50034356
AND ct.ct_tag = "mobile web edit") AS tmp2;
*/

########
#SELECT * FROM enwiki_p.revision AS r
#JOIN enwiki_p.page AS p
#ON p.page_id = r.rev_page 
#JOIN enwiki_p.change_tag AS ct ON r.rev_id = ct.ct_rev_id
#WHERE p.page_id = 50034356
#AND ct.ct_tag = "mobile web edit";

#select distinct r.rc_user userid, r.rc_user_text username, count(r.rc_id) ve_edits 
 #    	from recentchanges r join tag_summary t on r.rc_id = t.ts_rc_id 
  #   		where r.rc_user != 0 and r.rc_bot = 0 and r.rc_namespace = 0 
   #  		and r.rc_source in ("mw.edit", "mw.new") and t.ts_tags like "%visualeditor%" 
    # 	group by userid
    
#select * from enwiki_p.change_tag limit 100,10;

#select * from enwiki_p.change_tag AS ct JOIN enwiki_p.revision AS r 
#ON ct.ct_rev_id = r.rev_id WHERE ct_rev_id = 710408179;




#select * from enwiki_p.recentchanges where rc_id = 807266372;

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...