Test - Quarry

SQL

USE ckbwiki_p;

-- First Subquery: Retrieve the earliest revisions for all articles
WITH earliest_revisions AS (
  SELECT
    rev_page AS page_id,
    MIN(rev_timestamp) AS earliest_timestamp
  FROM revision
  WHERE rev_parent_id = 0
    AND DATE(rev_timestamp) BETWEEN '2023-01-01' AND '2023-05-01'
  GROUP BY rev_page
),

-- Second Subquery: Join to get the usernames and original sizes
first_edits AS (
  SELECT
    user_name AS username,
    rev_len AS original_size,
    page_id
  FROM revision
  JOIN page ON page_id = rev_page
  JOIN actor ON actor_id = rev_actor
  JOIN user ON user_id = actor_user
  WHERE page_namespace = 0
  AND rev_timestamp = earliest_revisions.earliest_timestamp
)

-- Final Query: Retrieve the summation of sizes for all articles
SELECT
  first_edits.username AS Username,
  COUNT(*) AS NumberOfArticles,
  SUM(page.page_len) AS TotalBytes,
  SUM(LENGTH(page.page_title)) AS TotalWords
FROM first_edits
JOIN page ON page.page_id = first_edits.page_id
GROUP BY first_edits.username;
SQL

Checking query status...
