Test - Quarry

SQL

USE ckbwiki_p;

SELECT
  Subquery.UserName,
  Subquery.Bytes,
  Subquery.ArticleTitle AS CreatedArticleTitle,
  DATE_FORMAT(Subquery.PublishedTimestamp, '%Y-%m-%d %H:%i') AS PublishedDateTime,
  IFNULL(CTD.ctd_name, '') AS TagName
FROM (
  SELECT
    actor_name AS UserName,
    SUM(rev_len) AS Bytes,
    r.rev_page AS ArticleID,
    p.page_title AS ArticleTitle,
    MIN(r.rev_timestamp) AS PublishedTimestamp
  FROM revision r
  JOIN (
    SELECT DISTINCT rev_page, MIN(rev_id) AS first_revision_id
    FROM revision
    WHERE rev_parent_id = 0
      AND rev_timestamp BETWEEN 20231201000000 AND 20240101000000
    GROUP BY rev_page
  ) AS first_revisions
  ON r.rev_id = first_revisions.first_revision_id
  JOIN actor ON actor.actor_id = r.rev_actor
  JOIN page p ON p.page_id = r.rev_page
  JOIN (
    SELECT pl_title
    FROM pagelinks
    WHERE pl_from = 243968
      AND pl_namespace = 2
  ) AS UserPage
  ON actor_name LIKE UserPage.pl_title
  WHERE p.page_namespace = 0
    AND p.page_is_redirect = 0
    AND r.rev_len >= 2500
    AND (
      SELECT COUNT(*)
      FROM pagelinks
      WHERE pl_from = p.page_id
      AND pl_namespace = 0
    ) >= 1
  GROUP BY actor_name, r.rev_page, p.page_title
) AS Subquery
LEFT JOIN change_tag_def AS CTD ON CTD.ctd_id IN (
  SELECT ct_tag_id
  FROM change_tag
  WHERE ct_rev_id = (
    SELECT rev_id
    FROM revision
    WHERE rev_page = Subquery.ArticleID
    ORDER BY rev_timestamp ASC
    LIMIT 1
  )
  AND ctd_name = 'contenttranslation-high-unmodified-mt-text'
)
ORDER BY UserName, CreatedArticleTitle;
SQL

Checking query status...
