SQL
x
SELECT user_name AS 'the rest of the top 10 overall deleters', deleted, restored, 100 * restored / deleted AS percentage
FROM (SELECT
user_name,
(SELECT COUNT(DISTINCT lu.log_title)
FROM logging_userindex lu
JOIN actor_logging ON lu.log_actor = actor_id
WHERE actor_name = user_name
AND lu.log_namespace = 0 AND lu.log_type = 'delete' AND lu.log_action = 'delete') AS deleted,
(SELECT COUNT(DISTINCT ll.log_title)
FROM logging_userindex lu
JOIN logging_logindex ll ON lu.log_title = ll.log_title
JOIN actor_logging ON lu.log_actor = actor_id
WHERE actor_name = user_name
AND lu.log_namespace = 0 AND lu.log_type = 'delete' AND lu.log_action = 'delete'
AND ll.log_namespace = 0 AND ll.log_type = 'delete' AND ll.log_action = 'restore'
AND ll.log_timestamp > lu.log_timestamp) AS restored
FROM user
WHERE user_name IN ('Explicit', 'Fastily', 'NawlinWiki', 'East718', 'Misza13', 'Magog the Ogre', 'Skier Dude', 'Plastikspork', 'Sphilbrick')) sq;
SELECT user_name AS 'arbitrators', deleted, restored, 100 * restored / deleted AS percentage
FROM (SELECT
user_name,
(SELECT COUNT(DISTINCT lu.log_title)
FROM logging_userindex lu
JOIN actor_logging ON lu.log_actor = actor_id
WHERE actor_name = user_name
AND lu.log_namespace = 0 AND lu.log_type = 'delete' AND lu.log_action = 'delete') AS deleted,
(SELECT COUNT(DISTINCT ll.log_title)
FROM logging_userindex lu
JOIN logging_logindex ll ON lu.log_title = ll.log_title
JOIN actor_logging ON lu.log_actor = actor_id
WHERE actor_name = user_name
AND lu.log_namespace = 0 AND lu.log_type = 'delete' AND lu.log_action = 'delete'
AND ll.log_namespace = 0 AND ll.log_type = 'delete' AND ll.log_action = 'restore'
AND ll.log_timestamp > lu.log_timestamp) AS restored
FROM user
WHERE user_name IN ('AGK', 'Beeblebrox', 'Bradv', 'Casliber', 'David Fuchs', 'DGG', 'GorillaWarfare', 'Joe Roe', 'KrakatoaKatie', 'Maxim', 'Mkdw', 'Newyorkbrad', 'SoWhy', 'Worm That Turned', 'Xeno')) sq;
SELECT user_name AS 'other admins who edited evidence or evidence talk', deleted, restored, 100 * restored / deleted AS percentage
FROM (SELECT
user_name,
(SELECT COUNT(DISTINCT lu.log_title)
FROM logging_userindex lu
JOIN actor_logging ON lu.log_actor = actor_id
WHERE actor_name = user_name
AND lu.log_namespace = 0 AND lu.log_type = 'delete' AND lu.log_action = 'delete') AS deleted,
(SELECT COUNT(DISTINCT ll.log_title)
FROM logging_userindex lu
JOIN logging_logindex ll ON lu.log_title = ll.log_title
JOIN actor_logging ON lu.log_actor = actor_id
WHERE actor_name = user_name
AND lu.log_namespace = 0 AND lu.log_type = 'delete' AND lu.log_action = 'delete'
AND ll.log_namespace = 0 AND ll.log_type = 'delete' AND ll.log_action = 'restore'
AND ll.log_timestamp > lu.log_timestamp) AS restored
FROM user
WHERE user_name IN ('Barkeep49', 'Black Kite', 'Cryptic', 'Ivanvector', 'Ritchie333', 'Thryduulf', 'Vanamonde93')) sq;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.