Untitled query #87679

SQL

USE bnwiki_p;

SELECT
  CONCAT("# {{#target:", actor_name, "}}") AS rev_user_text,
  COUNT(rev_actor) AS recent_user_editcount,
  total_edits.user_editcount AS lifetime_editcount
FROM revision
JOIN actor ON rev_actor = actor_id
JOIN page ON rev_page = page_id
/* Subquery to calculate lifetime edits */
JOIN (
  SELECT actor_id, COUNT(*) AS user_editcount
  FROM revision
  GROUP BY actor_id
) AS total_edits ON actor.actor_id = total_edits.actor_id
WHERE actor_user IS NOT NULL
  AND page_namespace IN ('0', '1', '2', '3', '4')
  /* Edit count between 4 October 2024 and 4 November 2024 */
  AND rev_timestamp BETWEEN '20241004000000' AND '20241104235959'
GROUP BY actor_id
HAVING recent_user_editcount >= 10
   AND lifetime_editcount >= 1000
ORDER BY recent_user_editcount DESC
LIMIT 800;
SQL

Checking query status...
