Blocked UPEs on enwiki in the last year

Fork of Blocked UPEs on enwiki in the last year by Novem Linguae

This query is marked as a draft This query has been published by Novem Linguae.

SQL

SELECT actor_name AS blocking_admin,
	log_title AS blocked_user,
    comment_text AS block_reason,
    log_timestamp
FROM logging
JOIN comment_logging ON log_comment_id = comment_id
JOIN actor ON log_actor = actor_id
WHERE log_action = 'block'
AND (
    comment_text LIKE '%undisclosed paid%'
    OR comment_text LIKE '%upe%'   # LIKE isn't case sensitive. careful of false positives such as "super"
)
AND log_actor != 198977042   # proxy blocking bot
AND log_timestamp > DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -1 YEAR), "%Y%m%d%H%i%s")
ORDER BY log_id ASC

SQL

Checking query status...