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
/*
SELECT a.article_course_id, a.article_user_id, a.article_page_title, 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_articles a
    	JOIN revision r
        ON r.rev_page = a.article_page_id
        LEFT OUTER JOIN revision p
        ON p.rev_id = r.rev_parent_id
WHERE article_course_id IN (63)
GROUP BY article_page_title
ORDER BY bytes_added DESC;
*/
SELECT c.course_id,c.course_start,c.course_end,u.user_id,u.user_name,a.article_page_title, r.rev_len, p.rev_len
	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 (63)
        LEFT JOIN revision r
        	ON r.rev_page = a.article_page_id
        LEFT OUTER JOIN revision p
        	ON p.rev_id = r_rev_parent_id
WHERE c.course_id IN (63)
#GROUP BY article_page_title
ORDER BY course_id;

SQL

Checking query status...