Untitled query #89073

SQL

select
  date_format(rc1.rc_timestamp, '%Y-%m-%d %H:%i:%s') as 'timestamp',
  concat("User talk:", replace(page_title, "_", " ")) as 'user talk page',
  actor_name as 'this person',
  case
    when rc1.rc_params like '%"added";b:0;%' then 'removed from'
    when rc1.rc_params like '%"added";b:1;%' then 'added to'
    else 'unknown action'
  end as 'cat action',
  concat("Category:", replace(rc1.rc_title, "_", " ")) as 'category',
  rc1.rc_params,
  rc1.rc_namespace,
  rc1.*,
  page.*,
  rc2.*
  
from recentchanges_userindex as rc1

inner join page on rc_cur_id = page_id
inner join actor_recentchanges on actor_id = rc_actor


-- if a user talk place has been added into awaiting-response, *any* edit to the page from the blocked user
-- will remove it from that cat.
-- so in this testing we're trying to include those that remove the UTP from awaiting-response; while not including those
-- where it's also been moved to one of the other cats at the same time.
left join recentchanges as rc2
  on rc2.rc_this_oldid = rc1.rc_this_oldid
  and rc2.rc_type = 6
  and rc2.rc_title regexp "^(Unblock_on_hold)$"
  and rc2.rc_params like '%"added";b:1;%'


where rc1.rc_type = 6
and page_namespace = 3
and rc1.rc_title regexp "^(Requests_for_unblock(_awaiting_response_from_the_blocked_user)?|Unblock_on_hold)$"


and replace(page_title, "_", " ") = actor_name
and rc1.rc_params like '%"added";b:0;%'

order by rc1.rc_timestamp desc;
SQL

Checking query status...
