Research:Editor Behaviour Analysis & Graphs/Queries
Appearance
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;