Untitled query #78868

SQL

WITH UserEdits AS (
    SELECT actor_name, user_id, COUNT(*) AS sl
    FROM revision
    JOIN actor ON rev_actor = actor_id
    JOIN `user` ON actor_user = user_id
    WHERE rev_timestamp > DATE_FORMAT(NOW() - INTERVAL 30 DAY, '%Y%m%d%H%i%s')
    GROUP BY actor_name
)

SELECT RANK() OVER (ORDER BY sl DESC) AS rank, actor_name, sl, first_edit_date
FROM (
    SELECT ue.actor_name, ue.user_id, ue.sl, MIN(rev_timestamp) AS first_edit_date
    FROM UserEdits ue
    JOIN revision_userindex rui ON ue.user_id = rui.rev_actor
    GROUP BY ue.actor_name, ue.user_id, ue.sl
) combined_data
WHERE NOT EXISTS (
    SELECT *
    FROM user_groups
    WHERE ug_user = combined_data.user_id AND ug_group = 'bot'
)
ORDER BY sl DESC;
SQL

Checking query status...
