Untitled query #88920

SQL

select count(*)

from (
  select * from logging_userindex
  inner join actor_logging on actor_id = log_actor
  inner join comment_logging on comment_id = log_comment_id
  where log_timestamp > '2024'
  and log_type = 'block'
  and log_action regexp '^(un)?block$'
) as query1

right join (
  select * from (
    select *,
    row_number() over (
      partition by user_id order by log_timestamp desc
    ) as row_number
    from logging_userindex
    inner join actor_logging on actor_id = log_actor
    inner join comment_logging on comment_id = log_comment_id
    inner join user on user_name = replace(log_title, '_', ' ')
    left join block_target on bt_user = user_id
    left join block on bl_target = bt_id
    
    where log_timestamp > '2024'
    and bl_id is null
    and log_type = 'block'
    and log_action regexp '^(re)?block$'
    and log_params not like '%noautoblock%'
    and convert(comment_text using utf8) regexp 'spam|promo|advert'
  ) as query2_sub
  
  where query2_sub.row_number = 1
) as query2
  on query2.user_name = replace(query1.log_title, '_', ' ')
  and query1.log_timestamp > query2.log_timestamp

limit 1;
SQL

Checking query status...
