Mainspaces pages a user has deleted, and have been restored

Fork of Mainspaces pages I've deleted, and which have been restored by RoySmith

This query is marked as a draft This query has been published by RoySmith.

This gives a (very) rough idea of what percentage of deletions performed by an admin end up getting restored.

@deleted is the number of pages a user has deleted.

@restored is the number of those which eventually got restored.  The restoration can be done by any user.  The only restriction is that the restore has to be after your deletion.  This makes no attempt to sort out intervening events.  For example, if User1 deletes a page, User2 creates a new page at the same title, User3 deletes that, and User4 restores it, that counts as a restore against User1.

Multiple deletions or restores of a given title count as a single event.

This only looks at mainspace deletions.  Partly because they're the most important, and partly because other namespaces have oddities that might skew the statistics.  Draft has G13, Talk has a lot of G8.

SQL

SET @user = 'RoySmith';

SET @user_id = (select actor_id from actor_logging);

SELECT
(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
AND lu.log_namespace = 0
AND ll.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
AND lu.log_namespace = 0
AND ll.log_namespace = 0
AND lu.log_type = 'delete'
AND lu.log_action = 'delete'
AND ll.log_type = 'delete'
AND ll.log_action = 'restore'
AND ll.log_timestamp > lu.log_timestamp) as restored
;

​x
 
SET @user = 'RoySmith';​SET @user_id = (select actor_id from actor_logging);​SELECT(SELECT count(distinct(ll.log_title))FROM logging_userindex luJOIN logging_logindex ll ON lu.log_title = ll.log_titleJOIN actor_logging ON lu.log_actor = actor_idWHERE actor_name = @userAND lu.log_namespace = 0AND ll.log_namespace = 0AND lu.log_type = 'delete'AND lu.log_action = 'delete') as deleted,(SELECT count(distinct(ll.log_title))FROM logging_userindex luJOIN logging_logindex ll ON lu.log_title = ll.log_titleJOIN actor_logging ON lu.log_actor = actor_idWHERE actor_name = @userAND lu.log_namespace = 0AND ll.log_namespace = 0AND lu.log_type = 'delete'AND lu.log_action = 'delete'AND ll.log_type = 'delete'AND ll.log_action = 'restore'AND ll.log_timestamp > lu.log_timestamp) as restored;

By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...