Test - Quarry

SQL

USE ckbwiki_p;

WITH Usernames AS (
  SELECT
    SUBSTRING_INDEX(
      SUBSTRING_INDEX(rev_text.text, '[[User:', -1),
      '|', 1
    ) AS username
  FROM page
  JOIN revision ON page_id = rev_page
  JOIN text AS rev_text ON rev_text_id = rev_text_id
  WHERE page_title = 'بەکارھێنەر:Aram/تاقیکردنەوە'
)

SELECT
  ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC, SUM(rev_len) DESC) AS Rank,
  actor_name AS username,
  COUNT(*) AS NumberOfArticles,
  SUM(rev_len) AS TotalBytes
FROM revision
JOIN (
  SELECT DISTINCT rev_page, MIN(rev_id) AS first_revision_id
  FROM revision
  WHERE rev_parent_id = 0
    AND DATE(rev_timestamp) BETWEEN '2023-01-01' AND '2023-05-01'
  GROUP BY rev_page
) AS first_revisions
ON revision.rev_id = first_revisions.first_revision_id
JOIN actor ON actor.actor_id = revision.rev_actor
JOIN page ON page.page_id = revision.rev_page
WHERE page_namespace = 0
  AND page_is_redirect = 0
  AND rev_len >= 1000
  AND (
    SELECT COUNT(*)
    FROM pagelinks
    WHERE pl_from = page.page_id
    AND pl_namespace = 0
    ) > 1
  AND actor_name IN (SELECT username FROM Usernames)
GROUP BY actor_name
ORDER BY Rank;
SQL

Checking query status...
