Untitled query #88920

SQL

select * from (
  select
    query2.user_name                                as 'username',
    query2.user_editcount                           as 'edits',
    
    date_format(
      query2.log_timestamp, '%Y-%m-%d %H:%i:%s'
    )                                               as 'block_timestamp',
    query2.actor_name                               as 'block_admin',
    (query2.log_action = 'reblock')                 as 'reblock?',
    (query2.log_params like '%infinity%')           as 'indef?',
    (query2.log_params like '%"sitewide";b:1%')     as 'sitewide?',
    (query2.log_params like '%nocreate%')           as 'ACB?',
    (query2.log_params like '%nousertalk%')         as 'TPD?',
    (query2.log_params like '%noemail%')            as 'EMD?',
    query2.comment_text                             as 'block_comment',
    query2.log_params                               as 'block_params',
    
    if(
      query1.log_action = 'unblock',
      date_format(
        query1.log_timestamp, '%Y-%m-%d %H:%i:%s'
      ), null
    )                                               as 'unblock_timestamp',
    if(
      query1.log_action = 'unblock',
      query1.actor_name, null
    )                                               as 'unblock_admin',
    if(
      query1.log_action = 'unblock',
      query1.comment_text, null
    )                                               as 'unblock_comment',
    if(
      query1.log_action = 'unblock',
      query1.log_params, null
    )                                               as 'unblock_params',
    row_number() over (
      partition by user_id order by query1.log_timestamp asc
    ) as row_number
  
  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
) as main_query

where main_query.row_number = 1;
SQL

Checking query status...
