Untitled query #81788

SQL

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
),
tp_cats AS (
SELECT DISTINCT cl.cl_from
FROM categorylinks cl
WHERE cl.cl_to LIKE 'Wikipedia_usernames_with_possible_policy_issues%'
OR cl.cl_to = 'User_talk_pages_with_conflict_of_interest_notices'
)

SELECT 
CONCAT('https://en.wikipedia.org/wiki/Special:Contributions/', pu.user_name) AS 'User',
TIMESTAMP(pu.registered) AS 'Registered',
CASE WHEN p.page_id IN (SELECT cl_from FROM tp_cats) THEN 'Yes' ELSE 'No' END AS 'Warned',
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
LEFT JOIN page p ON pu.user_name = REPLACE(p.page_title, '_', ' ') AND p.page_namespace = 3
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 ec.last_edit DESC
SQL

Checking query status...
