Revert rates by edit tags (article namespace) in trwiki

SQL

select *, reverted_edit_count/edit_count*100 as percent_reverted
from (

  select ctd_name, count(*) as edit_count, count(t.reverted_rev_id) as reverted_edit_count
  from change_tag
  left join change_tag_def on ct_tag_id=ctd_id
  left join revision on ct_rev_id=rev_id
  
  left join (
    select ct_rev_id as reverted_rev_id
    from change_tag
    left join change_tag_def on ct_tag_id=ctd_id
    where ctd_name='mw-reverted'
  ) t on ct_rev_id=reverted_rev_id

  where ct_rev_id is not null
  and rev_timestamp between '20211220' and '20211223'
  group by ctd_id
  order by edit_count desc
  limit 500

) y
order by percent_reverted desc, edit_count desc

SQL

Checking query status...