Fork of Attempted edits to user pages of blocked users by Uhai
This query is marked as a draft This query has been published by EqJjgOa8rVvsRmZL.

SQL

x
 
WITH sock_template AS (
  SELECT tl.tl_from FROM
  enwiki_p.templatelinks tl
  WHERE tl.tl_target_id IN (32197, 277052, 282063, 115252, 1968835)
)
SELECT
REPLACE(afl.afl_user_text, ' ', '_') AS 'Offending user',
afl.afl_title AS 'Blocked user',
CASE WHEN p.page_id IN (SELECT tl_from FROM sock_template) THEN 'Yes' ELSE 'No' END AS 'Blocked user tagged sock?',
CASE WHEN ipb2.ipb_expiry = 'infinity' THEN 'Indef' ELSE CONCAT(ipb2.ipb_timestamp, ' to ', ipb2.ipb_expiry) END AS 'Blocked user block time',
TIMESTAMP(MAX(afl.afl_timestamp)) AS 'Most recent interaction'
FROM abuse_filter_log afl
INNER JOIN user u ON u.user_name = REPLACE(afl.afl_title, '_', ' ')
LEFT JOIN ipblocks ipb ON ipb.ipb_user = afl.afl_user -- Attempt to join offending user and later check if ID is null to verify they're not blocked
INNER JOIN ipblocks ipb2 ON ipb2.ipb_user = u.user_id -- Attempt to join targeted user to ensure they're blocked
INNER JOIN page p ON p.page_title = afl.afl_title AND p.page_namespace = 2
WHERE afl.afl_namespace = 2
AND afl.afl_filter_id = 803
AND afl.afl_user != 0
AND ipb.ipb_user IS NULL
AND CASE WHEN ipb2.ipb_expiry != 'infinity' THEN afl.afl_timestamp BETWEEN ipb2.ipb_timestamp AND ipb2.ipb_expiry ELSE TRUE END
GROUP BY afl.afl_user_text, afl.afl_title
-- HAVING MAX(afl.afl_timestamp) > DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY MAX(afl.afl_timestamp) DESC
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.

Checking query status...