Activity - Top FLC commenters (1y)

SQL

SELECT user_name as Editor, concat("Wikipedia:", page_title) as 'Latest FAC', rev_timestamp as "Latest edit", count(*) as "Unique FLCs edited"
from 
( SELECT user_name , page_title, rev_timestamp
    FROM page p
	JOIN revision ON rev_page = page_id
    JOIN actor ON actor_id = rev_actor
	JOIN user ON actor_user = user_id
	WHERE page_namespace = 4
    and user_name!="FACBot"
    AND page_title LIKE "Featured_article_candidates/%"
    and page_title NOT in ("Featured_article_candidates/Closure_log", "Featured_article_candidates/backlog/items")
    and user_name!=
    ( SELECT user_name
      FROM page
      JOIN revision ON rev_page = page_id
      JOIN actor ON actor_id = rev_actor
      JOIN user ON actor_user = user_id
      WHERE page_namespace = 4
      and rev_parent_id=0 -- first revision of the page
      AND page.page_title=p.page_title
    )
    and page_title in
    ( SELECT page_title
      FROM page
      JOIN revision ON rev_page = page_id
      WHERE page_namespace = 4
      and rev_parent_id=0 -- first revision of the page
      AND page.page_title=p.page_title
      and rev_timestamp > date_add(now(), interval -365 day)
     -- only consider pages that were created in the last year
    ) 
    AND rev_timestamp > date_add(now(), interval -450 day)
    group by rev_actor, page_title
    order by rev_timestamp desc
) as x
group by user_name
having count(*) > 1
order by count(*) desc, rev_timestamp desc;
SQL

Checking query status...
