Number of total HelpDesk posts by Teahouse users as of June 30 2016

Log in and fork this query to start playing around.

Fun exercise: get the list of all answerer userids from the table `td_answerers_current_usernames` (https://quarry.wmflabs.org/query/16273) in a subquery, and return a list with user_ids and first_post dates for all users in that table for whom td_answerers_current_usernames.user_id is not null.

Database schema reference: https://www.mediawiki.org/wiki/Manual:Database_layout

SQL

select rev_user, count(rev_timestamp), rev_user_text 
from enwiki_p.revision_userindex as r2
join 
	(select u.user_id
		from enwiki_p.revision as r 
		join 
			(
		      select user_id from enwiki_p.user where user_id is not NULL
		 	)  as u 
		on r.rev_user = u.user_id
		join 
			(
			  select page_id from enwiki_p.page where page_namespace = 4 and page_title="Teahouse" 
		      #Wikipedia namespace. See https://en.wikipedia.org/wiki/Wikipedia:Namespace 
		      #Wikipedia:Teahouse/Questions was recently moved to Wikipedia:Teahouse, so this is the Q&A board
			)  as p 
		on r.rev_page = p.page_id 
		where r.rev_timestamp < 20160701000000
		group by r.rev_user) as u2
on r2.rev_user=u2.user_id
join ( select page_id from enwiki_p.page where page_namespace = 4 and page_title="Help_desk") as p2
on r2.rev_page = p2.page_id
where r2.rev_id > 156275280
and r2.rev_timestamp < 20160701000000
and r2.rev_comment not like "%*/ new section"
group by r2.rev_user;#  rev_user is equivalent to user_id in `td_answerers_current_usernames`

SQL

Checking query status...