Fork of All WikiEd students by number of edits since program ended #2 by Soni
This query is marked as a draft This query has been published by Soni.

SQL

AخA
 
SELECT 
    count(distinct rv2.rev_id) as edits,
    pl.pl_title as student,
    CONCAT('https://en.wikipedia.org/wiki/User:',pl.pl_title) as student_url,
    p3.page_title AS program_name,
    lg.log_timestamp as program_start_date,
    DATE_FORMAT(DATE_ADD(lg.log_timestamp, interval 10 month),'%Y%m%d%s0000') as program_end_date,
    TIMESTAMPDIFF(DAY,lg.log_timestamp,rv1.rev_timestamp) AS program_no_days,
    #p3.page_id as program_pageid,
    #u.user_id as student_id, 
    u.user_editcount as student_total_edit_count
FROM logging lg
JOIN page p3 ON log_title = p3.page_title AND p3.page_namespace = 4
JOIN pagelinks pl ON pl.pl_from = p3.page_id AND pl.pl_namespace = 2
JOIN categorylinks cl ON p3.page_id = cl.cl_from AND cl.cl_to like "%Dashboard.wikiedu.org_course_pages%" #Student has Template:Dashboard.wikiedu.org_course_pages on their talk page
JOIN user u ON u.user_name = pl.pl_title #user/student
JOIN linktarget lt ON (lt.lt_title LIKE "Dashboard.wikiedu.org_talk_course_link") AND lt.lt_namespace = 10 #has Category:Dashboard.wikiedu.org_talk_course_link on course page
JOIN templatelinks tl ON lt.lt_id = tl.tl_target_id  
JOIN page p2 ON tl.tl_from  = p2.page_id AND p2.page_namespace = 3 AND p2.page_title NOT like "%/%" #user has student template
JOIN revision rv1 ON rv1.rev_id = p3.page_latest #Course name
JOIN actor ac ON ac.actor_name = u.user_name 
LEFT JOIN revision_userindex rv2 ON rv2.rev_actor = ac.actor_id #edits by student
#AND rv.rev_timestamp >= '20210308000000'
WHERE
lg.log_type = 'create'
and lg.log_namespace = 4
#and lg.log_timestamp<20200101000000 and lg.log_timestamp>20190101000000 #when the program was created
AND rv2.rev_timestamp > DATE_FORMAT(DATE_ADD(lg.log_timestamp, interval 10 month),'%Y%m%d%s0000')
AND u.user_name = p2.page_title
and u.user_registration is NOT NULL
GROUP BY rv2.rev_actor
ORDER BY edits desc
#LIMIT 100
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...