Untitled query #44787

SQL

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

Checking query status...
