# 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;

​