Test - Quarry

SQL

USE ckbwiki_p;

WITH RankedUsers AS (
  SELECT
    ROW_NUMBER() OVER (ORDER BY TotalScore DESC) AS Rank,
    UserName,
    NumberOfArticles,
    TotalBytes,
    ArticleScore,
    BytesScore,
    TotalScore
  FROM (
    SELECT
      CONCAT('[[User:', actor_name, '|', actor_name, ']]') AS UserName,
      COUNT(*) AS NumberOfArticles,
      SUM(rev_len) AS TotalBytes,
      -- Calculate Article Score
      COUNT(*) * 1.0 / SUM(COUNT(*)) OVER () AS ArticleScore,
      -- Calculate Bytes Score
      SUM(rev_len) * 1.0 / SUM(SUM(rev_len)) OVER () AS BytesScore,
      -- Calculate Total Score
      (COUNT(*) * 1.0 / SUM(COUNT(*)) OVER ()) + (SUM(rev_len) * 1.0 / SUM(SUM(rev_len)) OVER ()) AS TotalScore
    FROM revision
    JOIN (
      SELECT DISTINCT rev_page, MIN(rev_id) AS first_revision_id
      FROM revision
      WHERE rev_parent_id = 0
        AND rev_timestamp BETWEEN 20230101000000 AND 20231101000000
      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
    JOIN (
      SELECT pl_title
      FROM pagelinks
      WHERE pl_from = 157289
        AND pl_namespace = 2
    ) AS UserPage
    ON actor_name LIKE UserPage.pl_title
    WHERE page_namespace = 0
      AND page_is_redirect = 0
      AND rev_len >= 0
      AND (
        SELECT COUNT(*)
        FROM pagelinks
        WHERE pl_from = page.page_id
        AND pl_namespace = 0
      ) >= 0
    GROUP BY actor_name
  ) AS Subquery
)

SELECT
  R.Rank,
  R.UserName,
  R.NumberOfArticles,
  R.TotalBytes,
  R.ArticleScore,
  R.BytesScore,
  R.TotalScore,
  GROUP_CONCAT(DISTINCT P.page_title ORDER BY P.page_title ASC SEPARATOR ', ') AS ArticleTitles
FROM RankedUsers R
JOIN revision RE ON R.UserName LIKE CONCAT('[[User:', RE.actor_name, '|', RE.actor_name, ']]')
JOIN page P ON RE.rev_page = P.page_id
GROUP BY R.Rank, R.UserName, R.NumberOfArticles, R.TotalBytes, R.ArticleScore, R.BytesScore, R.TotalScore
ORDER BY R.Rank;
SQL

Checking query status...
