Data envy workbook May 20 2016

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 COUNT(r.rev_id) FROM enwiki_p.revision AS r WHERE r.rev_page = 50034356;

SELECT COUNT(distinct tmp1.mobile_app_count) AS mac, COUNT(tmp2.mobile_web_count) AS mwc 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 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;
SQL

Checking query status...
