Mainspaces pages a user has deleted, and have been restored

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
;

SQL

Checking query status...