MI/CV deletion discussions, summary

Counts of individual deletion discussions edited by Mark Ironie, CorbieVreccan, and both.

Granularity is per-target-page for Articles for deletion and Miscellany for deletion, and per-nomination-date for Categories/Templates/Redirects for deletion/discussion.

See query 76565 for individual edits.

For [[WP:AN#Long-term meatpuppetry by two admins]] circa 12 Sep 2023.

SQL

SELECT SUBSTRING_INDEX(page_title, '_', 1) AS type, actor_name, COUNT(DISTINCT page_title)
FROM revision_userindex
JOIN actor_revision ON actor_id = rev_actor
JOIN comment_revision ON comment_id = rev_comment_id
JOIN page ON page_id = rev_page
WHERE actor_name IN ('Mark Ironie', 'CorbieVreccan')
  AND page_namespace = 4
  AND ((page_title LIKE 'Articles_for_deletion/%' AND page_title NOT LIKE 'Articles_for_deletion/Log/%')
       OR page_title LIKE 'Categories_for_discussion/%'
       OR page_title LIKE 'Categories_for_deletion/%'
       OR page_title LIKE 'Templates_for_discussion/%'
       OR page_title LIKE 'Templates_for_deletion/%'
       OR page_title LIKE 'Redirects_for_discussion/%'
       OR page_title LIKE 'Redirects_for_deletion/%'
       OR page_title LIKE 'Miscellany_for_deletion/%')
GROUP BY SUBSTRING_INDEX(page_title, '_', 1), actor_name

UNION

SELECT SUBSTRING_INDEX(page_title, '_', 1), 'Both', COUNT(DISTINCT page_title)
FROM revision_userindex AS rmi
JOIN actor_revision AS ami ON ami.actor_id = rmi.rev_actor
JOIN revision_userindex AS rcv
JOIN actor_revision AS acv ON acv.actor_id = rcv.rev_actor
JOIN page ON page_id = rmi.rev_page
WHERE ami.actor_name = 'Mark Ironie'
  AND acv.actor_name = 'CorbieVreccan'
  AND rmi.rev_page = rcv.rev_page
  AND page_namespace = 4
  AND ((page_title LIKE 'Articles_for_deletion/%' AND page_title NOT LIKE 'Articles_for_deletion/Log/%')
       OR page_title LIKE 'Categories_for_discussion/%'
       OR page_title LIKE 'Categories_for_deletion/%'
       OR page_title LIKE 'Templates_for_discussion/%'
       OR page_title LIKE 'Templates_for_deletion/%'
       OR page_title LIKE 'Redirects_for_discussion/%'
       OR page_title LIKE 'Redirects_for_deletion/%'
       OR page_title LIKE 'Miscellany_for_deletion/%')
GROUP BY SUBSTRING_INDEX(page_title, '_', 1)
ORDER BY type, actor_name;

SQL

Checking query status...