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

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
  sum(
    case
      when
        category_name = "Requests_for_unblock"
        and removed_from_category = 1
      then 1
      else 0
    end
  ) - sum(
    -- 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 sum.
    case
      when
        category_name regexp "^(Requests_for_unblock_awaiting_response_from_the_blocked_user|Unblock_on_hold)$"
        and removed_from_category = 0  -- match when a page is added to one of those two categories
      then 1
      else 0
    end
  )
from (
  select
    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
  inner 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 like "%unblock%"
) as subquery;



-- temp, testing
select rc_id from recentchanges inner join page on rc_cur_id = page_id where rc_type = 6 and page_namespace = 3
except select rc_id from recentchanges inner join page on rc_cur_id = page_id where rc_type = 6 and page_namespace = 3 and rc_title like "%unblock%";

SQL

Checking query status...