Which user's talk pages are most edited by non-bots in fawiki?

Fork of Which user's talk pages are most edited by non-bots in fawiki? by Huji

This query is marked as a draft This query has been published by محک.

SQL

use fawiki_p;
SELECT
  user_name,
  SUM(freq) AS total_edits
FROM
(
  SELECT
    page_title,
    CASE
      WHEN LOCATE('/', page_title) > 0 THEN LEFT(page_title, LOCATE('/', page_title) - 1)
      ELSE page_title
    END AS user_name,
    freq
  FROM
  (
    SELECT
      page_title,
      COUNT(*) freq
    FROM page
    JOIN revision
      ON rev_page = page_id
    JOIN actor
      ON rev_actor = actor_id
    LEFT JOIN user_groups
      ON actor_user = ug_user
      AND ug_group = 'bot'
    WHERE
      page_namespace = 3
      AND rev_timestamp > '20190101000000'
      AND ug_user IS NULL
    GROUP BY page_title
    HAVING COUNT(*) > 50
  ) page_level
) user_level
GROUP BY user_name
ORDER BY total_edits DESC

SQL

Checking query status...