Fork of 最近30日有编辑的退休者 by 打工人007
This query is marked as a draft This query has been published by SunAfterRain.

SQL

AخA
 
SELECT a.user_name, STR_TO_DATE(MAX(rev_timestamp), '%Y%m%d%H%i%s') last_edit_time
FROM revision_userindex r
         INNER JOIN (
    SELECT actor_id, u.user_name
    FROM actor a
             INNER JOIN (
        SELECT user_id, u.user_name
        FROM user u
                 INNER JOIN
             (SELECT REPLACE(page_title, '_', ' ') user_name
              FROM page p
                       INNER JOIN (SELECT tl_from pid
                                   FROM templatelinks
                                   LEFT JOIN linktarget
                                   ON linktarget.lt_id = templatelinks.tl_target_id
                                   WHERE lt_title IN ('退休', 'Retired', 'Retirement') AND lt_namespace = 10) user1 ON p.page_id = user1.pid
              WHERE p.page_namespace = 2
                AND p.page_title NOT LIKE '%/%') p ON u.user_name = p.user_name
    ) u ON a.actor_user = u.user_id
) a ON a.actor_id = r.rev_actor AND r.rev_timestamp >
                                    CONVERT(DATE_FORMAT(DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY),
                                                        '%Y%m%d%H%i%s'), UNSIGNED INTEGER) - 30 * 86400
GROUP BY a.user_name
ORDER BY last_edit_time 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
All SQL code is licensed under CC0 License.

Checking query status...