Untitled query #88615

SQL

USE bnwiki_p;

SELECT 
  SUBSTR(rev_timestamp, 1, 7) AS Month,  -- Extracts year and month (YYYY-MM)
  COUNT(DISTINCT page_id) AS New_pages_this_month
FROM page, revision
WHERE page_id = rev_page
  AND page_namespace = 0  -- Main namespace
  AND rev_parent_id = 0  -- New page, not a revision of another page
  AND page_is_redirect = 0  -- Excludes redirects
  AND rev_timestamp >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)  -- Last 1 year
GROUP BY SUBSTR(rev_timestamp, 1, 7)  -- Group by Year-Month
ORDER BY SUBSTR(rev_timestamp, 1, 7);

-- Total count of new pages over the last year
SELECT 
  COUNT(DISTINCT page_id) AS Total_new_pages_last_year
FROM page, revision
WHERE page_id = rev_page
  AND page_namespace = 0  -- Main namespace
  AND rev_parent_id = 0  -- New page, not a revision of another page
  AND page_is_redirect = 0  -- Excludes redirects
  AND rev_timestamp >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR);  -- Last 1 year
SQL

Checking query status...
