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 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 < 20170101000000
and r2.rev_comment not like "%*/ new section"
and r2.rev_user in 

(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 < 20170101000000
group by r.rev_user)

group by r2.rev_user;#  rev_user is equivalent to user_id in `td_answerers_current_usernames`

SQL

Checking query status...