Calculate the h-index for fawiki editors

SQL

use fawiki_p;
select user_name, max(iwlinks) as h_index
from
(
  select t1.rev_user, t1.iwlinks, sum(t2.freq) total
  from
  (
    select rev_user, iwlinks, count(*) freq
    from
    (
      select rev_user, rev_page, count(ll_lang) iwlinks
      from revision
      join page
        on rev_page = page_id
        and page_namespace = 0
      join langlinks
        on ll_from = rev_page
      where rev_parent_id = 0
      and rev_user <> 0
      group by rev_page
    ) t
    group by rev_user, iwlinks
  ) t1
  join
  (
    select rev_user, iwlinks, count(*) freq
    from
    (
      select rev_user, rev_page, count(ll_lang) iwlinks
      from revision
      join page
        on rev_page = page_id
        and page_namespace = 0
      join langlinks
        on ll_from = rev_page
      where rev_parent_id = 0
      and rev_user <> 0
      group by rev_page
    ) t
    group by rev_user, iwlinks
  ) t2
    on t2.rev_user = t1.rev_user
    and t2.iwlinks >= t1.iwlinks
  group by t1.rev_user, t1.iwlinks
) h
join user
  on rev_user = user_id
left join user_groups
  on ug_user = rev_user
  and ug_group = 'bot'
where
  total >= iwlinks
  and iwlinks > 5
  andn ug_user is null
group by rev_user
SQL

Checking query status...
