Recent activity by accounts with promo-flagged usernames

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

Finds users who have tripped abuse filter 54 ("promotional, group, organization, or company username (Oshwah)") within the past year who have less than 30 contributions overall and at least one contribution or other filter trip within the past three days.

To do:
-Flag users who have triggered autobiography, COI, or link containing username abuse filters

SQL

SELECT afl.afl_user_text, MAX(afl.afl_timestamp), COUNT(r.rev_id), MAX(r.rev_timestamp)
FROM abuse_filter_log afl
INNER JOIN user u ON afl.afl_user_text = u.user_name
INNER JOIN actor a ON u.user_id = a.actor_user
INNER JOIN revision_userindex r ON a.actor_id = r.rev_actor
WHERE afl.afl_filter_id = 54
AND afl.afl_timestamp > DATE_SUB(NOW(), INTERVAL 1 YEAR)
AND afl.afl_user = 0
GROUP BY afl.afl_user_text
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.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...