SELECT

    actor_name `name`,

    COUNT(*) `edits`,

    SUM( ct_tag_id IS NULL ) `edits_good`,

    SUM( ct_tag_id IS NOT NULL ) `edits_reverted`,

    SUM( ct_tag_id IS NOT NULL ) / NULLIF( COUNT(*), 0 ) `reverted_ratio`

FROM (

    SELECT

        *

    FROM

        revision

        JOIN page ON rev_page = page_id

        JOIN `comment` ON rev_comment_id = comment_id

        JOIN actor ON rev_actor = actor_id

        LEFT JOIN change_tag ON rev_id = ct_rev_id

            AND ct_tag_id = ( SELECT ctd_id FROM change_tag_def WHERE ctd_name = 'mw-reverted' )

    WHERE

        comment_text RLIKE CONCAT( '#',

            'CfACP',  -- <=== replace with the hashtag you want (case-sensitive!)

            '\\b' )

        AND actor_user IS NOT NULL

        AND rev_timestamp > DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 30 day ), "%Y%m%d%H%i%s" )

        AND page_namespace = 0

    ORDER BY

        rev_timestamp DESC

    LIMIT 1000

) latest_edits_with_hashtag

GROUP BY

    actor_name

ORDER BY

    reverted_ratio DESC

;

​

SELECT

    COUNT(*) `group_edits`,

    SUM( ct_tag_id IS NULL ) `group_edits_good`,

    SUM( ct_tag_id IS NOT NULL ) `group_edits_reverted`,

    SUM( ct_tag_id IS NOT NULL ) / NULLIF( COUNT(*), 0 ) `group_reverted_ratio`

FROM (

    SELECT

        *

    FROM

        revision

        JOIN page ON rev_page = page_id

        JOIN `comment` ON rev_comment_id = comment_id

        JOIN actor ON rev_actor = actor_id

        LEFT JOIN change_tag ON rev_id = ct_rev_id

            AND ct_tag_id = ( SELECT ctd_id FROM change_tag_def WHERE ctd_name = 'mw-reverted' )

    WHERE

        comment_text RLIKE CONCAT( '#',

            'CfACP',  -- <=== replace with the hashtag you want (case-sensitive!)

            '\\b' )

        AND actor_user IS NOT NULL

        AND rev_timestamp > DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 30 day ), "%Y%m%d%H%i%s" )

        AND page_namespace = 0

    ORDER BY

        rev_timestamp DESC

    LIMIT 1000

) latest_edits_with_hashtag

;

​