Untitled query #69136

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 (
	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( '#',
			'WPWP',  -- <=== 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( '#',
			'WPWP',  -- <=== 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
;
SQL

Checking query status...
