USE wikidatawiki_p;

​

#SELECT

#   SUBSTR(revision.rev_timestamp, 1, 8) AS bucket,

#    COUNT(revision.rev_id) AS cnt

#FROM

#   revision JOIN actor_revision ON actor_revision.actor_id=revision.rev_actor

#WHERE

#   revision.rev_page IN (SELECT page_id FROM page WHERE page_namespace=120)

#    AND actor_revision.actor_user IS NULL

#GROUP BY

#   bucket;

#SELECT

#   revision.rev_page,

#    COUNT(revision.rev_id) AS cnt

#FROM

#   revision JOIN actor_revision ON actor_revision.actor_id=revision.rev_actor

#WHERE

#   revision.rev_page IN (SELECT page_id FROM page WHERE page_namespace=120)

#    AND actor_revision.actor_user IS NULL

#GROUP BY

#   revision.rev_page;

#SELECT page_id, page_title FROM page WHERE page_namespace=120;

​

​

​

#SELECT * FROM change_tag_def;

​

​

### measure 1 ###

#SELECT

#    COUNT(revision.rev_id) AS cnt

#FROM 

#    revision JOIN change_tag ON revision.rev_id=change_tag.ct_rev_id

#WHERE 

#    revision.rev_parent_id IN (

#        SELECT

#            revision.rev_id

#        FROM

#           revision JOIN actor_revision ON actor_revision.actor_id=revision.rev_actor

#        WHERE

#           revision.rev_page IN (SELECT page_id FROM page WHERE page_namespace=120)

#            AND actor_revision.actor_user IS NULL)

#    AND change_tag.ct_tag_id IN (15, 18) # 15=mw-rollback, 18=mw-undo

#;

​

### measure 2: edit summaries ###

SELECT

    *

FROM 

    revision LEFT JOIN change_tag ON revision.rev_id=change_tag.ct_rev_id JOIN comment ON comment.comment_id=revision.rev_comment_id

WHERE 

    revision.rev_page IN (SELECT page_id FROM page WHERE page_namespace=120)

#    AND change_tag.ct_tag_id IN (15, 18) # 15=mw-rollback, 18=mw-undo

;