use trwiki_p;

SELECT NOW();

SET @lang = "tr"; 

​

select rc_user as id, concat(@lang,".wikipedia.org/wiki/User:", rc_user_text ) as url, rc_user_text as kul

, ec as total, ec0 as NS0_1, ec2 as uNS2_3, ec4 as wNS4_5, ec6 as fNS6_7, ec10 as tNS10_11

, ec14 as cNS14_15, ec828 as mNS829_830, ec1 as other_talks

, ec - ec0 - ec2 -ec4 -ec6 -ec10- ec14 -ec828 -ec1 as rest 

from(SELECT hep6.kul as kul, rc4.rc_user_text as ad, hep6.ec as ec, hep6.ec0 as ec0, hep6.ec2 as ec2, hep6.ec1 as ec1

     , hep6.ec828 as ec828, hep6.ec14 as ec14, hep6.ec10 as ec10, hep6.ec6 as ec6

     ,count(*) - hep6.ec0 as ec4

     from (SELECT hep10.kul as kul, hep10.ec as ec, hep10.ec0 as ec0, hep10.ec2 as ec2, hep10.ec1 as ec1

           , hep10.ec828 as ec828, hep10.ec14 as ec14, hep10.ec10 as ec10

           ,count(*) - hep10.ec0 as ec6

           from (SELECT hep14.kul as kul, hep14.ec as ec, hep14.ec0 as ec0, hep14.ec2 as ec2, hep14.ec1 as ec1

                 , hep14.ec828 as ec828, hep14.ec14 as ec14

                 ,count(*) - hep14.ec0 as ec10

                 from (SELECT hep828.kul as kul, hep828.ec as ec, hep828.ec0 as ec0, hep828.ec2 as ec2, hep828.ec1 as ec1

                       , hep828.ec828 as ec828

                       ,count(*) - hep828.ec0 as ec14

                       from (SELECT hep1.kul as kul, hep1.ec as ec, hep1.ec0 as ec0, hep1.ec2 as ec2, hep1.ec1 as ec1

                             ,count(*) - hep1.ec0 as ec828

                             from (SELECT hep2.kul as kul, hep2.ec as ec, hep2.ec0 as ec0, hep2.ec2 as ec2, hep2.rc_bot as rc_bot

                                   , count(*) - hep2.ec0 as ec1

                                   from (SELECT hep0.kul as kul, hep0.ec as ec, hep0.ec0 as ec0, hep0.rc_bot as rc_bot

                                         , count(*) - hep0.ec0 as ec2

                                         from (SELECT hep.kul as kul, rc0.rc_user_text as kulad, hep.ec as ec, hep.rc_bot as rc_bot

                                               , count(*) as ec0

                                               from (SELECT rc.rc_user as kul, count(*) as ec, rc.rc_bot as rc_bot  from recentchanges_userindex rc

                                                     where rc.rc_user >0 and rc.rc_bot = 0 group by kul having count(*) > 3000) as hep

                                               join recentchanges_userindex rc0 on rc0.rc_user = hep.kul

                                               where rc0.rc_namespace IN (0,1)

                                               group by kul) as hep0

                                         left join recentchanges_userindex rc2 on rc2.rc_user = hep0.kul

                                         where rc2.rc_namespace IN (2,3,0,1)

                                         group by kul) as hep2

                                   left join recentchanges_userindex rc1 on rc1.rc_user = hep2.kul

                                   where rc1.rc_namespace IN (9,13,101,0,1)

                                   group by kul) as hep1

                             left join recentchanges_userindex rc828 on rc828.rc_user = hep1.kul

                             where rc828.rc_namespace IN ( 828, 829,0,1)

                             group by kul) as hep828

                       left join recentchanges_userindex rc14 on rc14.rc_user = hep828.kul

                       where rc14.rc_namespace IN ( 14, 15,0,1)

                       group by kul) as hep14

                 left join recentchanges_userindex rc10 on rc10.rc_user = hep14.kul

                 where rc10.rc_namespace IN ( 10, 11,0,1)

                 group by kul) as hep10

           left join recentchanges_userindex rc6 on rc6.rc_user = hep10.kul

           where rc6.rc_namespace IN ( 6, 7,0,1) 

           group by kul) as hep6

     left join recentchanges_userindex rc4 on rc4.rc_user = hep6.kul

     where rc4.rc_namespace IN ( 4, 5,0,1) 

     group by kul) as hep4

     left join recentchanges_userindex rrr on rrr.rc_user = hep4.kul

     group by rrr.rc_user

;

SELECT NOW();

​

SELECT user_id, user_name, user_editcount, count(*) as ec

FROM user u join revision_userindex on rev_user = user_id

join page on page_id = rev_page

where page_namespace >0 and rev_user >0 

group by 1

having ec>0

order by ec desc

;

​