برنامج التعليم:جامعة القاهرة - كلية الآداب/ترجمة من الإسبانية والإنجليزية ومناهج بحث (خريف 2016)

SQL

USE arwiki_p; #set the language/project database
SET @course = 109; #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;

SQL

Checking query status...