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(unblock) as number_of_unblock_actions,
  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
  ) as estimated_number_of_declined_unblocks
from (
  select
    -- this is messy but it's in the interests of not having to query logging/recentchanges again separately
    (rc_type = 3) as unblock,
    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 subquery;

SQL

Checking query status...