Untitled query #37917

SQL

use fawiki_p;

select page_title, sum(revert_count)
from (
    select 
        page_id, page_title, count(rev_sha1) - 1 as revert_count
    from 
        revision 
        join page on page_id = rev_page
        join logging on log_page = rev_page
    where 
        rev_timestamp > date_format(date_sub(now(), interval 1 month), '%Y%m%d') 
        and page_namespace = 0
        and not exists (
            select *
            from logging
            where (
                (log_type = 'protect' and log_action = 'protect' or log_action = 'modify') 
                or (log_type = 'stable' and log_action = 'config' or log_action = 'modify'))
                and log_timestamp < date_format(date_sub(now(), interval 1 month), '%Y%m%d') 
    group by 
        rev_sha1 ,page_id
) as rv_table
where revert_count > 0
group by page_id
order by sum(revert_count) desc 
limit 30
;
SQL

Checking query status...
