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

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.

Checking query status...