This query is marked as a draft This query has been published by Tflanagan (WMF).

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.

Checking query status...