Toggle navigation
Home
New Query
Recent Queries
Discuss
Database tables
Database names
MediaWiki
Wikibase
Replicas browser and optimizer
Login
History
Fork
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
.
Assuming first edit on Program page was "start of course", edits by student since 10 months after 'start of course'
Toggle Highlighting
SQL
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,DATE_FORMAT(DATE_ADD(lg.log_timestamp, interval 10 month),'%Y%m%d%s0000')) 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
.
Submit Query
Stop Query
All SQL code is licensed under
CC0 License
.
Checking query status...