Untitled query #88909

This query is marked as a draft This query has been published by A smart kitten.

aaaaaaaaaaaa (pt.3)

SQL

select *  /*
  user.user_name                                   as 'username',
  user.user_editcount                              as 'edits',
  
  date_format(
    logging1.log_timestamp, '%Y-%m-%d %H:%i:%s'
  )                                                as 'block_timestamp',
  actor1.actor_name                                as 'block_admin',
  (logging1.log_action = 'reblock')                as 'reblock?',
  (logging1.log_params like '%infinity%')          as 'indef?',
  (logging1.log_params like '%"sitewide";b:1%')    as 'sitewide?',
  (logging1.log_params like '%nocreate%')          as 'ACB?',
  (logging1.log_params like '%nousertalk%')        as 'TPD?',
  (logging1.log_params like '%noemail%')           as 'EMD?',
  comment1.comment_text                            as 'block_comment',
  logging1.log_params                              as 'block_params',
  
  if(
    logging_subquery.log_action = 'unblock',
    date_format(
      logging_subquery.log_timestamp,
      '%Y-%m-%d %H:%i:%s'
    ), null
  )                                                as 'unblock_timestamp',
  if(
    logging_subquery.log_action = 'unblock',
    logging_subquery.actor_name, null
  )                                                as 'unblock_admin',
  if(
    logging_subquery.log_action = 'unblock',
    logging_subquery.comment_text, null
  )                                                as 'unblock_comment',
  if(
    logging_subquery.log_action = 'unblock',
    logging_subquery.log_params, null
  )                                                as 'unblock_params'
*/

from (
  select
    *,
    row_number() over (partition by user_id order by logging1.log_timestamp desc) as row_number
    
    
  -- logging1 fetches the most recent [re]block event
  -- that matches the given filters
  from logging_userindex as logging1
  inner join actor_logging as actor1
    on actor1.actor_id = logging1.log_actor
  inner join comment_logging as comment1
    on comment1.comment_id = logging1.log_comment_id
  inner join user
    on user.user_name = replace(logging1.log_title, '_', ' ')
  
  
  -- left join on the block table
  -- so we can filter out users that are currently blocked
  left join block_target
    on block_target.bt_user = user.user_id
  left join block
    on block.bl_target = block_target.bt_id
  
  
  -- logging2 fetches the [un]block event that comes soonest
  -- after the event fetched by logging1
  left join (
    select
      logging2.log_timestamp, logging2.log_title, logging2.log_action,
      logging2.log_params, actor2.actor_name, comment2.comment_text
    
    from logging_userindex as logging2 
    
    inner join actor_logging as actor2
      on actor2.actor_id = logging2.log_actor
    inner join comment_logging as comment2
      on comment2.comment_id = logging2.log_comment_id
    
    where logging2.log_timestamp > '2024'
    and logging2.log_type = 'block'
    and logging2.log_action regexp '^(un)?block'
    
  ) as logging_subquery  
    on user.user_name = replace(logging_subquery.log_title, '_', ' ')
    and logging_subquery.log_timestamp > logging1.log_timestamp
  
  
  -- filtering the [re]block event
  where logging1.log_timestamp > '2024'
  and block.bl_id is null
  and logging1.log_type = 'block'
  and logging1.log_action regexp '^(re)?block$'
  and logging1.log_params not like '%noautoblock%'
  and convert(comment1.comment_text using utf8) regexp 'spam|promo|advert'
) as subquery

where subquery.row_number = 1

limit 3;

By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...