Article creations/deletions by month (4+ years)

Fork of Article creations by month by Cryptic

This query is marked as a draft This query has been published by Cryptic.

The number of pages created in the main namespace, excluding (still-existing) redirects/dabs, each month since the beginning of the creation log at 2018 June 27 23:10:06 UTC, and the number of those pages which have been deleted.

Pages created in the main namespace, moved out of it, and then deleted in a different mainspace are counted in deletions.  (Example: [[Chhatak Bazar Railway Station]] was created in mainspace on 10 May 2020, moved to [[Chhatak Bazar railway station]] on 9 November 2020, moved to [[Draft:Chhatak Bazar railway station]] on 1 October 2021, and deletion in draft on 1 April 2021; it's counted in both creations and deletions.)

Pages created in the main namespace, moved out of it, and still exist are counted as creations but *not* as deletions.  (Example: [[Frankie Quinn]] was created at  23:40, 24 August 2022‎; was moved to [[Draft:Frankie Quinn]] at 00:22, 25 August 2022‎.  Since the page still exists in draft, it's counted as a creation and not as a deletion, even though [[Frankie Quinn]] is a redlink now that I've deleted the redirect left by the move.)

Pages created outside of the main namespace and then moved into it (such as a draft, originally created as a draft, and then accepted by AFC) are not listed either as creations or deletions.

201806, obviously, will be low since only a little over two days of the month were tracked; 202208 will also be a bit low since the month's not over yet.

For [[WP:RAQ#For NPP]] circa 20 Aug 2022.

SQL

SELECT LEFT(creation.log_timestamp,6),
       COUNT(creation.log_id) AS `creations`,
       SUM(EXISTS(SELECT 1
                  FROM logging_logindex AS deletion
                  WHERE deletion.log_page = creation.log_page
                    AND deletion.log_action = 'delete'
                    AND deletion.log_type = 'delete')) AS `deletions`
FROM logging AS creation
LEFT JOIN page ON page_id = log_page
LEFT JOIN page_props ON pp_page = page_id AND pp_propname = 'disambiguation'
WHERE creation.log_type = 'create'
  AND creation.log_timestamp >= '20180627' -- First entry with log_type='create' was timestamp 20180627231006
  AND creation.log_namespace = 0
  AND COALESCE(page_is_redirect, 0) = 0
  AND pp_page IS NULL
GROUP BY LEFT(creation.log_timestamp,6)
ORDER BY creation.log_timestamp ASC;

AخA
 
SELECT LEFT(creation.log_timestamp,6),       COUNT(creation.log_id) AS `creations`,       SUM(EXISTS(SELECT 1                  FROM logging_logindex AS deletion                  WHERE deletion.log_page = creation.log_page                    AND deletion.log_action = 'delete'                    AND deletion.log_type = 'delete')) AS `deletions`FROM logging AS creationLEFT JOIN page ON page_id = log_pageLEFT JOIN page_props ON pp_page = page_id AND pp_propname = 'disambiguation'WHERE creation.log_type = 'create'  AND creation.log_timestamp >= '20180627' -- First entry with log_type='create' was timestamp 20180627231006  AND creation.log_namespace = 0  AND COALESCE(page_is_redirect, 0) = 0  AND pp_page IS NULLGROUP BY LEFT(creation.log_timestamp,6)ORDER BY creation.log_timestamp ASC;

By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...