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,
        COUNT(DISTINCT CASE WHEN cl.cl_from IS NOT NULL THEN cl.cl_from END) AS warned
    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
    LEFT JOIN
        actor_user a ON u.user_id = a.actor_user
    LEFT JOIN
        revision_userindex r ON a.actor_id = r.rev_actor
    LEFT JOIN
        abuse_filter_log afl2 ON u.user_id = afl2.afl_user AND afl2.afl_filter_id = 54
    LEFT JOIN
        categorylinks cl ON cl.cl_from = r.rev_page
                           AND (cl.cl_to LIKE 'Wikipedia_usernames_with_possible_policy_issues%'
                                OR cl.cl_to = 'User_talk_pages_with_conflict_of_interest_notices')
    WHERE
        afl.afl_timestamp > DATE_SUB(NOW(), INTERVAL 1 YEAR)
        AND afl.afl_user = 0
        AND ipb.ipb_id IS NULL
    GROUP BY
        afl.afl_user_text
)
SELECT
    CONCAT('https://en.wikipedia.org/wiki/Special:Contributions/', pu.user_name) AS 'User',
    TIMESTAMP(pu.registered) AS 'Registered',
    CASE WHEN pu.warned > 0 THEN 'Yes' ELSE 'No' END AS 'Warned',
    COUNT(DISTINCT r.rev_id) AS 'Edit count',
    MAX(r.rev_timestamp) AS 'Last edit',
    COUNT(DISTINCT afl2.afl_id) AS 'Filter trip count',
    MAX(afl2.afl_timestamp) AS 'Last filter trip'
FROM
    promo_users pu
LEFT JOIN
    revision_userindex r ON pu.user_id = r.rev_actor
LEFT JOIN
    abuse_filter_log afl2 ON pu.user_id = afl2.afl_user AND afl2.afl_filter_id = 54
GROUP BY
    pu.user_name
HAVING
    MAX(r.rev_timestamp) > DATE_SUB(NOW(), INTERVAL 3 DAY)
    OR MAX(afl2.afl_timestamp) > DATE_SUB(NOW(), INTERVAL 3 DAY)
ORDER BY
    MAX(r.rev_timestamp) DESC;
SQL

Checking query status...
