Instantly share code, notes, and snippets.

Embed
What would you like to do?
Recent Draft publishing rate
Examining the number of pages created in the Draft namespace between 2017-09-15 and 2018-02-15 that currently (2018-03-14) exists
as pages in the article (Main) namespace.
1: Get a dataset of creations in the Draft namespace between those dates from the `log` database:
SELECT page_id
FROM mediawiki_page_create_2
WHERE `database`='enwiki'
AND rev_timestamp >= '2017-09-15 00:00:00'
AND rev_timestamp < '2018-02-15 00:00:00'
AND page_namespace = 118
AND page_is_redirect = 0;
2: Loaded that data into a scratch table:
SELECT count(*) FROM nettrom_temp;
+----------+
| count(*) |
+----------+
| 34115 |
+----------+
1 row in set (0.02 sec)
3: Join with the live page table on enwiki:
SELECT count(*)
FROM nettrom_temp
JOIN enwiki.page p
USING (page_id)
WHERE p.page_namespace=0;
+----------+
| count(*) |
+----------+
| 3771 |
+----------+
1 row in set (9.35 sec)
4: Calculate proportion:
SELECT ROUND(100*3771/34115, 1) AS recent_prop_published;
+-----------------------+
| recent_prop_published |
+-----------------------+
| 11.1 |
+-----------------------+
1 row in set (0.00 sec)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment