Untitled query #88829

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

rough stupid bad query

SQL

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


from logging_userindex as l1
inner join comment_logging as c1 on c1.comment_id = l1.log_comment_id   -- to be able to filter by the block summary
inner join actor_logging as a1 on a1.actor_id = l1.log_actor            -- just bc i fancy seeing at a glance who placed the block


-- trying to match `logging.log_title` to `block_target.bt_user_text` resulted in some...
-- shall we say, not quite *optimal* query performance (see https://quarry.wmcloud.org/query/88867).
-- it turns out that joining `user` on `user.user_name`, *then* joining `block_target` by comparing
-- user IDs, sped up the query quite a lot than just joining with `block_target` directly.
-- also, no idea if i've got to query `block` in addition to/instead of `block_target` if i'm trying
-- to figure out whether or not an account has got an active block -- i don't know whether MediaWiki
-- removes a user's row in `block_target` when a block expires/is removed or not. i guess i figure
-- that checking the actual `block` table itself is staying on the safe side at least

-- SIDE NOTE: the reason for the `left join`s is because i basically wanted the inverse of an inner
-- join - i.e., i wanted to *filter out/exclude* users who had/have an entry in the `block` table.
-- https://stackoverflow.com/a/4585241 provided the idea/solution of left joining and then filtering
-- away any rows where the joined columns from `block` were not null.
inner join user as u1 on u1.user_name = replace(l1.log_title, '_', ' ')
left join block_target as bt1 on bt1.bt_user = u1.user_id
left join block as b1 on b1.bl_target = bt1.bt_id

-- TEST TEST TEST TEST TEST TEST TEST TEST TEST
inner join logging as l2 on l2.log_id = (
  select l3.log_id from logging as l3
  inner join user as u2 on u2.user_name = replace(l3.log_title, '_', ' ')
  where l3.log_type = 'block' and l3.log_action = 'unblock'
  and u2.user_id = u1.user_id
  and l3.log_timestamp > l1.log_timestamp
  order by l3.log_timestamp asc
  limit 1
)



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



-- (attempt to) filter out currently-blocked users (c.f., the side note above)
and b1.bl_id is null

-- filter down to the block log, and filter out unblock-log-events
and l1.log_type = 'block'
and l1.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 l1.log_params not like '%noautoblock%' 


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



order by l1.log_timestamp desc
limit 5 ;

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