Untitled query #45865

SQL

use wikidatawiki_p;


select 
    count(distinct(p2.page_id)) as p2_c,
    count(distinct(r1.rev_id)) as r1_c,
	p2.page_title, 
    p2.page_id, 
    r1.rev_user_text,
    r1.rev_comment,
    COALESCE(max(r1.rev_timestamp),0) as r1_max_rev_timestamp,     
    datediff(now(), str_to_date(r1.rev_timestamp, "%Y%m%d%H%i%S")) as datediff,
    r3.rev_id as revert
from 
	page as p1, 
    pagelinks as pl1, 
    page as p2,
    revision_compat as r1 
    LEFT JOIN (select ug_user as is_bot FROM user_groups WHERE ug_group="bot") as ug ON ug.is_bot=r1.rev_user 
    LEFT JOIN (select max(r2.rev_id) as max_rv_rev_id, min(r3.rev_id) as min_rv_rev_id from revision_compat as r2, revision_compat as r3 
               WHERE 
               		r2.rev_id>r3.rev_id 
	                AND r2.rev_page=r3.rev_page
               		AND datediff(now(), str_to_date(r2.rev_timestamp, "%Y%m%d%H%i%S")) < 90
                    AND r2.rev_sha1=r3.rev_sha1
              ) as revert ON r2.rev_id>r1.rev_id AND r3.rev_id<r1.rev_id
               		
where 
	p1.page_title="Zache/pori" 
    and p1.page_namespace=2 
    and pl1.pl_from=p1.page_id 
    and pl1.pl_from_namespace = 2 
    and pl1.pl_namespace=0 
    and p2.page_title=pl1.pl_title 
    and p2.page_namespace=0 
    and r1.rev_page=p2.page_id 
    and datediff(now(), str_to_date(r1.rev_timestamp, "%Y%m%d%H%i%S")) < 90
    and r1.rev_user_text NOT IN ("Antasa", "SannaOikkis")
    and ug.is_bot IS NULL
group by r1.rev_page
SQL

Checking query status...
