Android #suggestededit-add-image edits and reverted by wiki

Fork of Android #suggestededit-add-image edits by wiki by SNowick (WMF)

This query is marked as a draft This query has been published by SNowick (WMF).

SQL

SELECT 
COUNT(DISTINCT r.rev_id) AS n_edits,
SUM(CASE WHEN array_contains(ctd_name, 'mw-reverted' THEN 1 ELSE 0 END) AS n_reverted
FROM revision r
JOIN actor a 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 IN (SELECT ctd_id
-- FROM change_tag_def
WHERE ctd_name IN ('android app edit', 'mw-reverted')
AND comment_text LIKE '%#suggestededit-add-image%'
  AND r.rev_timestamp > 20231001000000

SQL

Checking query status...