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

SET @course = 63; #use any course id number
#SET @articles = arwiki_p.ep_articles;

SELECT  article_user_id,
		article_page_id, 
        article_page_title,
        rev.rev_len as new_bytes, 
        COALESCE(parent.rev_len, 0) as prev_bytes, #transform null values to 0s, please
        CAST(rev.rev_len as signed) - CAST(ISNULL(parent.rev_len) as signed) AS edit_size #diff size
	FROM arwiki_p.revision rev
    	INNER JOIN arwiki_p.ep_articles
        ON rev.rev_page = article_page_id
        LEFT OUTER JOIN arwiki_p.revision parent #self join for parent diff size
        ON rev.rev_parent_id = parent.rev_id
    WHERE article_course_id = @course

SQL

Checking query status...