SQL
x
WITH promo_users AS (
SELECT u.user_id, afl.afl_user_text AS user_name, MAX(afl.afl_timestamp) AS registered
FROM abuse_filter_log afl
INNER JOIN user u ON afl.afl_user_text = u.user_name
LEFT JOIN ipblocks ipb ON u.user_id = ipb.ipb_user
WHERE afl.afl_filter_id = 54
AND afl.afl_timestamp > DATE_SUB(NOW(), INTERVAL 1 YEAR)
AND afl_user = 0
AND ipb.ipb_id IS NULL
GROUP BY afl.afl_user_text
),
edit_count AS (
SELECT pu.user_id, COUNT(r.rev_id) AS edit_count, MAX(r.rev_timestamp) AS last_edit
FROM promo_users pu
LEFT JOIN actor_user a ON pu.user_id = a.actor_user
LEFT JOIN revision_userindex r ON a.actor_id = r.rev_actor
GROUP BY pu.user_id
),
filter_count AS (
SELECT pu.user_id, COUNT(afl.afl_id) AS filter_count, MAX(afl.afl_timestamp) AS last_filter
FROM promo_users pu
LEFT JOIN abuse_filter_log afl ON pu.user_id = afl.afl_user
GROUP BY pu.user_id
)
SELECT
CONCAT('https://en.wikipedia.org/wiki/Special:Contributions/', pu.user_name) AS 'User',
TIMESTAMP(pu.registered) AS 'Registered',
ec.edit_count AS 'Edit count',
TIMESTAMP(ec.last_edit) AS 'Last edit',
fc.filter_count AS 'Filter trip count',
TIMESTAMP(fc.last_filter) AS 'Last filter trip'
FROM promo_users pu
INNER JOIN edit_count ec ON pu.user_id = ec.user_id
INNER JOIN filter_count fc ON pu.user_id = fc.user_id
WHERE ec.edit_count <= 30
AND (ec.last_edit > DATE_SUB(NOW(), INTERVAL 3 DAY) OR fc.last_filter > DATE_SUB(NOW(), INTERVAL 3 DAY))
ORDER BY pu.registered 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.