Toggle navigation
Home
New Query
Recent Queries
Discuss
Database tables
Database names
MediaWiki
Wikibase
Replicas browser and optimizer
Login
History
Fork
This query is marked as a draft
This query has been published
by
MRaish (WMF)
.
Toggle Highlighting
SQL
ip_reverted_query = ''' WITH reverted_edits AS ( SELECT wiki_db, revision_id, -- revision ID of the _reverted_ edit revision_first_identity_reverting_revision_id -- to join with the reverting edit FROM wmf.mediawiki_history WHERE snapshot = "2021-08" AND wiki_db IN ("jawiki", "arwiki", "eswiki", "fawiki", "trwiki", "bnwiki") AND event_entity = "revision" AND event_type = "create" AND event_user_is_anonymous = true -- unregistered edit AND revision_is_identity_reverted = true -- was reverted AND revision_seconds_to_identity_revert < 60*60*48 -- less than 48 hours AND event_timestamp BETWEEN "2020-01-01" AND "2021-01-01" ), reverting_edits AS ( SELECT wiki_db, event_user_id, event_user_text, revision_id -- revision ID of the _reverting_ edit FROM wmf.mediawiki_history WHERE snapshot = "2021-08" AND wiki_db IN ("jawiki", "arwiki", "eswiki", "fawiki", "trwiki", "bnwiki") AND event_entity = "revision" AND event_type = "create" AND event_user_is_anonymous = false -- has to be reverted by a registered user AND size(event_user_is_bot_by_historical) = 0 -- can't be a bot AND revision_is_identity_revert = true -- is a reverting edit AND event_timestamp BETWEEN "2020-01-01" AND "2021-01-03" ) SELECT reverting_edits.wiki_db, reverting_edits.event_user_id, FIRST_VALUE(reverting_edits.event_user_text), count(1) AS num_reverted_ip_edits FROM reverted_edits JOIN reverting_edits ON reverted_edits.wiki_db = reverting_edits.wiki_db AND reverted_edits.revision_first_identity_reverting_revision_id = reverting_edits.revision_id GROUP BY reverting_edits.wiki_db, reverting_edits.event_user_id '''
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...