enwiki: unblock request rolling success statistics (past 30 days)

This query is marked as a draft This query has been published by A smart kitten.

attempt to use recentchanges' category tracking to work out a rough percentage of closed unblock requests within the last 30 days (as the length of time recentchanges stores things for) that have been successful.

attempt to do this by taking the number of times a page was logged as having been removed from [[category:Requests for unblock]] (and not added to [[category:Requests for unblock awaiting response from the blocked user]] or [[category:Unblock on hold]]), and comparing it to the number of unblock actions logged within the same timeframe.

this query isn't perfect and will probably treat things like (e.g.) a user blanking their talk page containing an open unblock request as that request having been declined when calculating the figure, but hopefully it's roughly around the actual unblock request success %.
it also doesn't know if an unblock action actually happened as a result of an unblock request or not.

SQL

select
  number_of_unblock_actions as 'Number of logged unblock actions',
  estimated_number_of_reviewed_unblock_requests as 'Estimated number of reviewed unblock requests',
  concat(round(100 * number_of_unblock_actions / estimated_number_of_reviewed_unblock_requests, 2), "%")
    as 'Estimated percentage of reviewed unblock requests that have been successful'
from (
  select
    sum(unblock) as number_of_unblock_actions,
    sum(
        case
          when
            removed_from_category = 1
            and category_name = "Requests_for_unblock"
          then 1
          else 0
        end
      )
      -- since the above sum will include all of the times a page was removed from CAT:RFU just because it was
      -- moved to RFU-awaiting-response or RFU-on-hold, remove the sum of all additions to those categories
      -- from the final figure.
      - sum(
        case
          when
            removed_from_category = 0
            and category_name regexp "^(Requests_for_unblock_awaiting_response_from_the_blocked_user|Unblock_on_hold)$"
          then 1  -- match when a page is added to one of those two categories
          else 0
        end
      )
      -- this number should, to my knowledge, include times when a request has been removed from CAT:RFU
      -- because the unblock request has been accepted
      as estimated_number_of_reviewed_unblock_requests
  from (
    -- this is messy but it's in the interests of not having to query logging/recentchanges again separately
    select 
      (rc_type = 3) as unblock,  -- we've filtered all rc_type=3 rows to only return unblock events 
                                 -- using the where clause below
      rc_title as category_name,
      (rc_params like '%"added";b:0;%') as removed_from_category -- return 1 when a page is removed from a category
    from recentchanges
    left join page on rc_cur_id = page_id
    where (
      rc_type = 6  -- categorization change
      and page_namespace = 3  -- double-check that the unblock category is being used in the user talk namespace
      and rc_title regexp "[Uu]nblock"
    ) or (
      rc_type = 3  -- logged action
      and rc_log_type = 'block'
      and rc_log_action = 'unblock'
    )
  ) as subquery2
) as subquery1;

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