Attempted edits to user pages of blocked users

Returns edit attempts that hit abuse filter 803 ("prevent new users from editing other's user pages") on user pages of blocked users.  Only offending accounts (the ones that triggered the filter) that are not currently blocked are shown.

Accounts that show up in the "offending user" column have a high probability of being sockpuppets.  The probability is even higher if the blocked user they attempted to edit is tagged as a sock.

Known issues with this query:
-Attempted edits to temp blocked users' user pages will show in this query (only if they occurred after the block started) but only if the query is run while the temp block is still active.  Would need to look at logs table to check for attempted edits that occurred during now-expired blocks.  Probably not worth it.
-Attempted edits before an indef block began will show in this query.  This may be beneficial to retain.
-Filter 803 may be too restrictive in who it targets as it doesn't get triggered by users who are autoconfirmed (which is easy to game).
-Globally locked accounts show in results (not possible to fix on Quarry)

To do:
-Add datetime of last activity of offending user

SQL

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

SQL

Checking query status...