SQL
x
# limit by course
# calculate diff sizes {{done}} , SUM this column {{done}}
# limit to course start and end dates {{done}}
# Associate and group by username {{done}}
# limit namespace(s) {{done}}
USE arwiki_p; #set the language/project
SET @course = 63; #use any course id number
SET @namespace = 0; #limit to article namespace 0
SELECT user_name,
# article_page_id,
# rev.rev_id,
# rev.rev_timestamp,
# course_start,
# course_end,
# rev.rev_len as new_bytes,
# COALESCE(parent.rev_len, 0) as prev_bytes, #transform null values to 0s, please
SUM(CAST(rev.rev_len as signed) - CAST(COALESCE(parent.rev_len,0) as signed)) AS edits_bytes, #diff size, transform null values to 0s, please
COUNT(rev.rev_id) AS edit_count
FROM arwiki_p.revision rev
INNER JOIN ep_articles
ON rev.rev_page = article_page_id
LEFT OUTER JOIN revision parent #self join for parent diff size
ON rev.rev_parent_id = parent.rev_id
JOIN ep_courses
ON article_course_id = course_id
JOIN ep_students
ON article_user_id = student_user_id
JOIN user
ON student_user_id = user_id
JOIN page
ON rev.rev_page = page_id
WHERE article_course_id = @course OR student_first_course = @course
AND rev.rev_timestamp BETWEEN course_start AND course_end
AND page_namespace = @namespace
GROUP BY user_name
ORDER BY edits_bytes DESC;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.