Fawiki revert ratio

SQL

-- MariaDB [fawiki_p]> select ctd_id, ctd_name from change_tag_def where ctd_name IN ('mw-rollback', 'mw-undo', 'mw-manual-revert', 'mw-reverted') ;
-- +--------+------------------+
-- | ctd_id | ctd_name         |
-- +--------+------------------+
-- |    276 | mw-manual-revert |
-- |    280 | mw-reverted      |
-- |      7 | mw-rollback      |
-- |     10 | mw-undo          |
-- +--------+------------------+

-- select min(rev_id) into @min_rev_id from revision where rev_timestamp > 20200000000000);

-- first rev_id of 20200000000000
SET @min_rev_id := 27914582;

SELECT
	floor(rev_timestamp/100000000) as yearmonth,
    count(rev_id) as total_ns0_edits, 
    sum(tags IS NOT NULL) as revert_ns0_edits,
    (sum(tags IS NOT NULL)/sum(1)) as ratio
FROM (
  SELECT
     rev_id,
     rev_timestamp,
     group_concat(ct_tag_id) as tags 
  FROM
     page,
     revision_compat
     LEFT JOIN change_tag ON rev_id=ct_rev_id AND ct_tag_id IN (276, 280, 7, 10)
     LEFT JOIN (
       SELECT
          user_id,
          user_name
       FROM
          user
          LEFT JOIN user_groups ON ug_user=user_id
          LEFT JOIN user_former_groups ON ufg_user=user_id
       WHERE
          ug_group='bot' OR ufg_user='bot' OR user_name REGEXP '[bB][oO0][tT]\b'
       GROUP BY user_id
     ) AS bots ON rev_user=bots.user_id
  WHERE
     rev_page=page_id
     AND page_namespace=0
     AND rev_id>@min_rev_id
     AND rev_timestamp>20201000000000
     AND bots.user_id IS NULL
  GROUP BY rev_id  
) as t group by yearmonth
SQL

Checking query status...
