contribs for users in Education Extension course (bytes, edits)

This query looks at the users and articles associated with a specified course id. It limits the query to the dates set out on the course page and calculates the bytes added and edits for users for their specified articles. It excludes other contribs by default. It also includes students who have not made edits or made edits to their designated articles.

One quirk may be that students who list new articles before they are created do not have an article id, so they may not capture the contribs.

This can be run periodically and posted on wiki as a table, or used to publish final results for a course.

SQL

USE arwiki_p; #set the language/project database
SET @course = 71; #set the course id based on Special:Courses

SELECT  c.course_id,u.user_name,
		SUM(CAST(r.rev_len as signed) - CAST(COALESCE(p.rev_len,0) as signed)) AS bytes_added,
        COUNT(r.rev_id) AS edit_count
	FROM ep_courses c
    	LEFT JOIN user u
        	ON u.user_id = if(instr(c.course_students,concat(":",u.user_id,";"))>0,u.user_id,c.course_students)
			AND u.user_id > 1000 #to weed out the registration numbers
        LEFT JOIN ep_articles a
        	ON a.article_user_id = u.user_id
            AND a.article_course_id IN (@course)
        LEFT JOIN revision r
        	ON r.rev_page = a.article_page_id
            AND r.rev_timestamp >= c.course_start
            AND r.rev_timestamp <= c.course_end
        LEFT JOIN revision p
        	ON r.rev_parent_id = p.rev_id
WHERE c.course_id IN (@course)
GROUP BY user_name
ORDER BY course_id, bytes_added DESC;

SQL

Checking query status...