/*

Trying to calculate the total contributions for users listed on a course in the education extension.

Goal is to output a list of users, total edit size and total edit count, within the parameters of the course:

- limit to articles listed on course page

- limit to dates listed on course page

*/

SET @course = 63;

​

#List of users and articles in specified course

#SELECT student_first_course, user_name, article_user_id, article_page_id, article_page_title, rev.rev_id, rev.rev_len, rev.rev_parent_id, parent.rev_len

FROM arwiki_p.ep_students

    JOIN arwiki_p.user

    ON student_user_id = user_id

    LEFT OUTER JOIN arwiki_p.ep_articles

    ON student_user_id = article_user_id

    JOIN arwiki_p.ep_courses # join table for course_start and course_end timestamps

    ON student_first_course = course_id

    INNER JOIN arwiki_p.revision rev

    ON article_page_id = rev.rev_page

    INNER JOIN arwiki_p.revision parent #self join to calculate edit diff size

    ON rev.rev_parent_id = parent.rev_id

WHERE (student_first_course = @course  OR article_course_id = @course)

AND rev.rev_timestamp >= course_start AND rev.rev_timestamp < course_end

#AND rev_user = student_id AND rev_timestamp > course_start AND rev_timestamp < course_end#;

#GROUP BY user_name;

#GROUP BY article_page_id;