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 = '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;

​x
 
SET @user = 'RoySmith';-- 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;

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...