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;