Arwiki contribs by course for student user total edits (bytes, count)

Map the revisions of education course articles to students and aggregate contribtuions.

Copy code and run for more up to date data. Last updated here on Friday, 04-Sep-15 03:16:44 UTC.

Table is based on users from the ep_students table which doesn't seem to be accurate, especially for previous courses (like when users have been in multiple courses, it may not list all the courses for that user).

SQL

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

SQL

Checking query status...