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 = 'Xeno';
-- SET @user = 'RHaworth';

SET @user_id = (
  SELECT actor_id
  FROM actor_logging
  WHERE actor_name = @user);

SET @deleted = (
  SELECT count(distinct(ll.log_title))
  FROM logging_userindex lu
  JOIN logging_logindex ll ON lu.log_title = ll.log_title
  WHERE lu.log_actor = @user_id
  AND lu.log_namespace = 0
  AND ll.log_namespace = 0
  AND lu.log_type = 'delete'
  AND lu.log_action = 'delete');

SET @restored = (
  SELECT count(distinct(ll.log_title))
  FROM logging_userindex lu
  JOIN logging_logindex ll ON lu.log_title = ll.log_title
  WHERE lu.log_actor = @user_id
  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);

SELECT @deleted, @restored, 100 * @restored / @deleted as percentage;

SQL

Checking query status...