SQL
x
/*
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;
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.