T357580 - zhwiki transition for site/user scripts between Vector and Vector 2022 skins

This SQL retrieves the names of active users (in the user discussion namespace) who have made recent changes (since may 2023 ; generalization of Vector 2022) and have vector.js/vector.css pages. We can send them a MassMessage.

SQL

SELECT
    CONCAT('User talk:', a.actor_name) AS `target`,
    a.actor_name AS `user`,
    MAX(r.rev_timestamp) AS `last edit`
FROM revision_userindex AS r
LEFT JOIN actor AS a ON a.actor_id = r.rev_actor
WHERE a.actor_id IN (
    SELECT
        a2.actor_id AS actor_id
    FROM revision AS r2
    LEFT JOIN actor AS a2 ON a2.actor_id = r2.rev_actor
    LEFT JOIN page AS p2 ON p2.page_id = r2.rev_page
    WHERE p2.page_namespace = 2
        AND (p2.page_title LIKE "%/vector.js"
            OR p2.page_title LIKE "%/vector.css")
        AND p2.page_title LIKE CONCAT(REPLACE(a2.actor_name, ' ', '_'), "/%")
        AND p2.page_len != 0
        AND r2.rev_timestamp >= 20230501000000
    GROUP BY a2.actor_id
)
GROUP BY a.actor_id;

SQL

Checking query status...