Reverted contribution rate

Calculates how often the contribution of a group of users get reverted, and lists them most reverted first. Might be useful for evaluating campaigns.
Note that deleted edits are not counted (as they aren't publicly available).

To use, press "Fork" on the top right, then edit the list of names (in two places!).

SQL

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
	revision
	JOIN page ON rev_page = page_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
	actor_name IN ( 'Tgr', 'Samat' ) -- <=== replace with the list of names you want
	AND actor_user IS NOT NULL
	AND page_namespace = 0
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
	revision
	JOIN page ON rev_page = page_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
	actor_name IN ( 'Tgr', 'Samat' ) -- <=== replace with the list of names you want
	AND actor_user IS NOT NULL
	AND page_namespace = 0
;

SQL

Checking query status...