Total and reverted revisions of pages with most revisions

SQL

SELECT
 page_title,
 COUNT(*) AS total,
 COUNT(DISTINCT rev_sha1) AS unreverted,
 COUNT(*) - COUNT(DISTINCT rev_sha1) AS reverted,
 100 * (1 - COUNT(DISTINCT rev_sha1) / COUNT(*)) AS percentReverted
FROM revision r
JOIN actor ON rev_actor = actor_id
JOIN page p ON r.rev_page = p.page_id
JOIN change_tag ct ON ct_rev_id = rev_id
JOIN comment ON comment_id = r.rev_comment_id
LEFT JOIN change_tag_def tdef ON ct.ct_tag_id = tdef.ctd_id
WHERE ct_tag_id = (SELECT ctd_id
FROM change_tag_def
WHERE ctd_name LIKE '%android app edit%')
AND comment_text LIKE '%#suggestededit-add-image%'
  AND r.rev_timestamp > 20231001000000
GROUP BY page_title
ORDER BY COUNT(*) DESC;

SQL

Checking query status...