Test - Quarry

SQL

USE ckbwiki_p;

SELECT
  Subquery.UserName,
  Subquery.Bytes,
  Subquery.ArticleID,
  Subquery.ArticleTitle,
  DATE_FORMAT(Subquery.PublishedTimestamp, '%Y-%m-%d %H:%i') AS PublishedDateTime,
  IF(Subquery.HasTag, 'Yes', 'No') AS HasDesiredTag
FROM (
  SELECT
    CONCAT('[[User:', actor_name, '|', actor_name, ']]') AS UserName,
    SUM(r.rev_len) AS Bytes,
    r.rev_page AS ArticleID,
    p.page_title AS ArticleTitle,
    MIN(r.rev_timestamp) AS PublishedTimestamp,
    EXISTS (
      SELECT 1
      FROM page
      JOIN pagelinks ON page.page_id = pagelinks.pl_from
      JOIN templatelinks ON page.page_id = templatelinks.tl_from
      WHERE pagelinks.pl_namespace = 0
        AND pagelinks.pl_title = p.page_title
        AND templatelinks.tl_namespace = 10
        AND templatelinks.tl_title = 'contenttranslation-high-unmodified-mt-text'
    ) AS HasTag
  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
  WHERE p.page_namespace = 0
    AND p.page_is_redirect = 0
    AND r.rev_len >= 2499
    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
ORDER BY UserName, ArticleTitle;
SQL

Checking query status...
