use ptwiki_p;

​

select gr.gender as reverter_gender, grp.gender as reverted_gender, count(*) 

FROM revision r INNER JOIN revision rp ON  r.rev_parent_id = rp.rev_id  INNER JOIN revision rpp ON rp.rev_parent_id = rpp.rev_id  INNER JOIN page 

ON r.rev_page = page_id LEFT OUTER JOIN (select  user_id, if(up_value IS NULL ,"none",up_value) as gender from user left outer join (select * from user_properties where up_property = "gender") g on user_id = up_user) gr on gr.user_id = r.rev_user LEFT OUTER JOIN (select  user_id, if(up_value IS NULL ,"none",up_value) as gender  from user left outer join (select * from user_properties where up_property = "gender") g on user_id = up_user) grp on grp.user_id = rp.rev_user

WHERE r.rev_sha1  = rpp.rev_sha1

AND  page_namespace = 0

AND r.rev_user <> 0

AND rp.rev_user <> 0

group by reverter_gender, reverted_gender

order by 3;