Untitled query #39049

SQL

use fawiki_p;
SELECT
  user_name,
  MAX(iwlinks) AS h_index
FROM
(
  SELECT
    t1.actor_user,
    t1.iwlinks,
    SUM(t2.freq) AS total
  FROM
  (
    SELECT
      actor_user,
      iwlinks,
      COUNT(*) AS freq
    FROM
    (
      SELECT
        actor_user,
        rev_page,
        COUNT(ll_lang) AS iwlinks
      FROM revision_userindex
      JOIN actor
        ON rev_actor = actor_id
      JOIN page
        ON rev_page = page_id
        AND page_namespace = 0
      JOIN langlinks
        ON ll_FROM = rev_page
      WHERE
        rev_parent_id = 0
        AND actor_user <> 0
      GROUP BY rev_page
    ) t
    GROUP BY
      actor_user,
      iwlinks
  ) t1
  JOIN
  (
    SELECT
      actor_user,
      iwlinks,
      COUNT(*) AS freq
    FROM
    (
      SELECT
        actor_user,
        rev_page,
        COUNT(ll_lang) AS iwlinks
      FROM revision_userindex
      JOIN actor
        ON rev_actor = actor_id
      JOIN page
        ON rev_page = page_id
        AND page_namespace = 0
      JOIN langlinks
        ON ll_FROM = rev_page
      WHERE
        rev_parent_id = 0
        AND actor_user <> 0
      GROUP BY rev_page
    ) t
    GROUP BY
      actor_user,
      iwlinks
  ) t2
    ON t2.actor_user = t1.actor_user
    AND t2.iwlinks >= t1.iwlinks
  GROUP BY
    t1.actor_user,
    t1.iwlinks
) h
LEFT JOIN user_groups
  ON ug_user = actor_user
  AND ug_group = "bot"
WHERE
  total >= iwlinks
  AND iwlinks > 5
  AND ug_user IS NULL
GROUP BY actor_user
ORDER BY h_index DESC
SQL

Checking query status...
