Research:Editor Behaviour Analysis & Graphs/Queries

Active Editor

SELECT 
  rev_user, 
  rev_user_text, 
  YEAR(rev_timestamp) as year,
  MONTH(rev_timestamp) as month, 
  rev_timestamp,  /* TODO: This column should be an aggregate function */
  COUNT(*) 
FROM revision 
INNER JOIN page ON revision.rev_page = page.page_id 
WHERE 
  LOWER(CONVERT (rev_user_text USING latin1)) NOT LIKE '%bot%' AND
  rev_user != 0 AND 
  page_namespace = 0 AND 
  page_is_redirect = 0 
GROUP BY rev_user, LEFT(rev_timestamp, 6)
HAVING COUNT(*) >= 5;

Editor Group/Cohort

select rev_user, rev_user_text, YEAR(min(rev_timestamp)), MONTH(min(rev_timestamp)) from revision where rev_user !=0 and lower(CONVERT (rev_user_text USING latin1)) not like '%bot%' group by rev_user having count(*)>4;

Active Article

select page_title,page_id, YEAR(min(rev_timestamp)), MONTH(min(rev_timestamp)), count(*) from page join revision on page.page_id = revision.rev_page where page_namespace=0 and page_is_redirect=0 group by rev_page having count(*)>4;

Article Group/Cohort

select page_title,page_id, YEAR(rev_timestamp),MONTH(rev_timestamp), count(*) from page join revision on page.page_id = revision.rev_page where page_namespace=0 and page_is_redirect=0 and rev_user !=0 and lower(CONVERT(rev_user_text USING latin1)) not like '%bot%' group by YEAR(rev_timestamp), MONTH(rev_timestamp), rev_page having count(*)>4;