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


where log_type = 'block'
and log_action regexp '^(re)?block$'
and log_timestamp > '20240101000000'


and comment_text regexp 'SpAm|PrOmO|aDvErT'


-- idea jotting for block comment filtering for commercial/promo/advertising/spam accounts
-- 'spam|promo

-- to do:
-- i'm trying to build a query to answer asilvering's comment about finding a commercial promoter that has 'reformed'
 -- idea noting: query block log for entry indicating promo, 
   -- then (presumably) filter for unblocked,
     -- then (also presumably) find some rough heuristic that can be used to find accounts to review to see if
     -- they've 'reformed' or not?  (edit count?? presumably itd need to be something querying their contribs??)

order by log_timestamp desc
limit 1;
SQL

Checking query status...
