Untitled query #88829

SQL

select
  */*,
  date_format(log_timestamp, "%Y-%m-%d %H:%i:%s") as timestamp*/


from logging_userindex
inner join comment_logging on comment_id = log_comment_id
inner join actor_logging on actor_id = log_actor

-- test
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

-- (attempt to) filter out currently-blocked users
where bl_id is null

-- filter down to the block log, and filter out unblock-log-events
and log_type = 'block'
and log_action regexp '^(re)?block$'

-- TRYING THIS: filter out 'softblocks' (blocks where autoblock is disabled/new account creation is 
-- explicitly allowed)
-- my reasoning behind trying this out is bc these softblocks are often for usernames, where the block
-- is bc the username contains (e.g.) an ORGNAME violation; rather than the user having created any
-- blockable content creation. these blocks can be removed immediately upon the user being renamed.
-- therefore, if I want to find commercial promoters that have 'reformed' (...or, at least, potentially
-- decrease the query's false positive rate), maybe filtering out softblocks will help do that.
-- having said that, though, i realise that filtering out softblocks may likely throw away some
-- of the results that *would* be the type of account i'm looking for; so i guess i've got to take
-- that into account, and it's a bit of a judgement call whether or not to filter them out at the end
-- of the day i guess.
and log_params not like '%noautoblock%' 

-- temporarily while i'm testing this, let's only grab blocks from the start of this year
and log_timestamp > '2024'


-- try and find users blocked for spam/advertising/promotion
and convert(comment_text using utf8) regexp 'spam|promo|advert'

-- temp for testing
-- and log_id between 104635823 and 106995001;

-- order by log_timestamp desc
-- limit 1000 ;
SQL

Checking query status...
