Test - Quarry

SQL

USE ckbwiki_p;

SELECT
  RANK() OVER (ORDER BY 0.5 * ZScoreArticles + 0.5 * ZScoreBytes DESC) AS Rank,
  CONCAT('[[User:', UserName, '|', UserName, ']]') AS UserName,
  NumberOfArticles,
  TotalBytes,
  ZScoreArticles,
  ZScoreBytes,
  0.5 * ZScoreArticles + 0.5 * ZScoreBytes AS TotalScore
FROM (
  SELECT
    actor.actor_name AS UserName,
    COUNT(*) AS NumberOfArticles,
    SUM(rev_len) AS TotalBytes,
    MAX(COUNT(*)) OVER () AS MaxArticles,
    MAX(SUM(rev_len)) OVER () AS MaxBytes
  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 20090101000000 AND 20231021000000
    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.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.actor_name
) AS UserData
CROSS JOIN (
  SELECT AVG(NumberOfArticles) AS MeanArticles, STDDEV(NumberOfArticles) AS StdDevArticles, 
         AVG(TotalBytes) AS MeanBytes, STDDEV(TotalBytes) AS StdDevBytes
  FROM UserData
) AS Statistics
OUTER APPLY (
  SELECT (NumberOfArticles - MeanArticles) / StdDevArticles AS ZScoreArticles,
         (TotalBytes - MeanBytes) / StdDevBytes AS ZScoreBytes
  FROM UserData
) AS ZScores
ORDER BY Rank;
SQL

Checking query status...
