Reverted contribution rate

This query is marked as a draft This query has been published by Tgr.

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
;

​x
 
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 = 0GROUP BY    actor_nameORDER 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;​

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.

SQL

Checking query status...