USE arwiki_p;

​

WITH low_edit_users AS (

    SELECT actor_id, actor_name, actor_user, COUNT(*) as total_edits

    FROM revision

    JOIN actor ON revision.rev_actor = actor.actor_id

    WHERE actor.actor_user IS NOT NULL  

    GROUP BY actor_id, actor_name, actor_user

    HAVING COUNT(*) < 100

)

​

SELECT a.actor_name AS username, u.user_registration AS registration_date, leu.total_edits AS total_edits,

       GROUP_CONCAT(DISTINCT p.page_title SEPARATOR ', ') AS articles, COUNT(r.rev_id) AS daily_edits

FROM revision r

JOIN actor a ON r.rev_actor = a.actor_id

JOIN user u ON a.actor_user = u.user_id

JOIN page p ON r.rev_page = p.page_id

JOIN low_edit_users leu ON r.rev_actor = leu.actor_id

WHERE r.rev_timestamp BETWEEN '20240523000000' AND '20240523235959'

AND p.page_namespace = 0

GROUP BY a.actor_name, u.user_registration, leu.total_edits

ORDER BY daily_edits DESC, username

LIMIT 500;

​