This query is marked as a draft This query has been published by Tflanagan (WMF).

SQL

x
 
USE arwiki_p; #set the language/project database
SET @course = 64; #set the course id based on Special:Courses
SELECT  c.course_id,u.user_name,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_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 article_page_title, user_name
ORDER BY course_id, bytes_added DESC;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.

Checking query status...