TeWiki- Last six months users whose edits>500

Fork of TeWiki- Last six months users whose edits>500 by Nivas10798

This query is marked as a draft This query has been published by Gopala Krishna A.

SQL

WITH
rev_filter AS (
  SELECT rev_actor,  COUNT(DISTINCT(rev_timestamp)) as last_twoyear_edits
  FROM revision
  WHERE rev_timestamp > '20201001000000' AND rev_timestamp < '20221031235959'
  GROUP BY rev_actor
),
edits_filter AS(
  SELECT DISTINCT(actor_name) as username, last_twoyear_edits, user_editcount as alltime_edits
  FROM rev_filter
  JOIN actor ON actor_id = rev_actor AND rev_actor IS NOT NULL
  JOIN user on actor_user = user_id
  LEFT JOIN user_groups on ug_user = user_id
  WHERE (ug_group IS NULL) OR (ug_group NOT LIKE '%bot%')
)
SELECT *
FROM edits_filter
WHERE last_twoyear_edits >= 200 and alltime_edits >= 500
ORDER BY last_twoyear_edits DESC

#LIMIT 100

SQL

Checking query status...