USE trwiki_p;

​

SELECT  rc_user_text, count(*) as ec, concat("tr.wikipedia.org/wiki/Özel:Katkılar/",rc_user_text) as katk

, round(sum(abs(rc_new_len - rc_old_len))/count(*),2) as ort_abs_kat,round(sum((rc_new_len - rc_old_len))/count(*),2) as ort_kat

,round(sum(sign(rc_deleted))/count(*),2) as del, round(sum(rc_patrolled)/count(*),2) as pat

, round(sum(sign(rc_namespace))/count(*),4) as ns

, ddd.sayi

-- , ipb_expiry

from recentchanges_userindex rc 

left join (select pp.page_title as ad, count(*) as sayi

  from page pp join revision_userindex as rev on rev.rev_page= pp.page_id

           where pp.page_namespace=3 and rev.rev_user = 104002

           group by pp.page_title

  ) as ddd on ddd.ad = rc_user_text

left join (

  select ib.ipb_address as adr, max(ib.ipb_id) as sonid, min(ib.ipb_id) as ilkid, count(*) as engelsayi, ib.ipb_expiry

  from ipblocks_ipindex ib

  where ib.ipb_user = 0

  group by ib.ipb_address

  ) as ipb on ipb.adr = rc_user_text 

where rc_user = 0 and rc_bot = 0 and rc_type < 5

and ipb.adr is null

-- and rc_deleted > 0

and rc_timestamp > DATE_ADD(NOW(), INTERVAL -60 DAY)

group by rc_user_text

having count(*) > 10

order by ddd.sayi, count(*) desc

;