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,
  IF(ctd.ctd_name IS NOT NULL, 1, 0) AS HasTag
FROM (
  SELECT
    CONCAT('[[User:', actor_name, '|', 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 to include articles without tags
LEFT JOIN (
  SELECT rev_page, 1 AS HasTag
  FROM change_tag
  JOIN change_tag_def ctd ON change_tag.ct_tag_id = ctd.ctd_id
  WHERE ctd.ctd_name = "contenttranslation-high-unmodified-mt-text"
) AS ctd ON Subquery.ArticleID = ctd.rev_page

ORDER BY UserName, CreatedArticleTitle;
SQL

Checking query status...
