Research:Editor Behaviour Analysis & Graphs/Queries

Active Editor

edit
  • 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

edit
  • 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

edit
  • 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

edit
  • 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;