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(
    case
      when
        category_name regexp "^(Requests_for_unblock_awaiting_response_from_the_blocked_user|Unblock_on_hold)$"
        and removed_from_category = 0
      then 1
      else 0
    end
  )
        

from (
  select
    rc_title as category_name,
    (rc_params like '%"added";b:0;%') as removed_from_category
  from recentchanges
  inner join page on rc_cur_id = page_id
  where rc_type = 6  -- categorization change
  and page_namespace = 3
) as subquery;


/*
sum(
  case
    when
      rc_title = "Requests_for_unblock"
      and rc_params like '%"added";b:0;%'  -- match when a page is removed from that category
    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
      rc_title regexp "^(Requests_for_unblock_awaiting_response_from_the_blocked_user|Unblock_on_hold)$"
      and rc_params like '%"added";b:1;%'  -- match when a page is added to one of those two categories
    then 1
    else 0
  end
)

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
*/

SQL

Checking query status...