Page MenuHomePhabricator
Paste P7357

Number of page creations by day and patrol status
ActivePublic
Actions

Authored by Catrope on Jul 10 2018, 11:46 PM.
Tags
None
Referenced Files
F23578583: Number of page creations by day and patrol status
Jul 10 2018, 11:46 PM
Subscribers
None
-- Main namespace
mysql:research@s3-analytics-slave [enwiki]> select substring(log_timestamp,1,8) as day, IF(rc_patrolled=2,'yes','no') AS autopatrolled, count(*) from logging join recentchanges on rc_namespace=log_namespace and rc_title=log_title and rc_type=1 join page on page_id=rc_cur_id where log_type='create' and page_is_redirect=0 and log_namespace=0 and log_timestamp > '20180600000000' group by day, autopatrolled;
+----------+---------------+----------+
| day | autopatrolled | count(*) |
+----------+---------------+----------+
| 20180627 | no | 18 |
| 20180628 | no | 479 |
| 20180628 | yes | 41 |
| 20180629 | no | 228 |
| 20180629 | yes | 228 |
| 20180630 | no | 235 |
| 20180630 | yes | 254 |
| 20180701 | no | 238 |
| 20180701 | yes | 258 |
| 20180702 | no | 238 |
| 20180702 | yes | 191 |
| 20180703 | no | 312 |
| 20180703 | yes | 208 |
| 20180704 | no | 250 |
| 20180704 | yes | 319 |
| 20180705 | no | 293 |
| 20180705 | yes | 277 |
| 20180706 | no | 260 |
| 20180706 | yes | 312 |
| 20180707 | no | 274 |
| 20180707 | yes | 283 |
| 20180708 | no | 307 |
| 20180708 | yes | 266 |
| 20180709 | no | 340 |
| 20180709 | yes | 213 |
| 20180710 | no | 320 |
| 20180710 | yes | 198 |
+----------+---------------+----------+
27 rows in set (1.47 sec)
-- Draft namespace
mysql:research@s3-analytics-slave [enwiki]> select substring(log_timestamp,1,8) as day, IF(rc_patrolled=2,'yes','no') AS autopatrolled, count(*) from logging join recentchanges on rc_namespace=log_namespace and rc_title=log_title and rc_type=1 join page on page_id=rc_cur_id where log_type='create' and page_is_redirect=0 and log_namespace=118 and log_timestamp > '20180600000000' group by day, autopatrolled;
+----------+---------------+----------+
| day | autopatrolled | count(*) |
+----------+---------------+----------+
| 20180627 | no | 6 |
| 20180628 | no | 201 |
| 20180628 | yes | 1 |
| 20180629 | no | 182 |
| 20180629 | yes | 4 |
| 20180630 | no | 135 |
| 20180630 | yes | 3 |
| 20180701 | no | 148 |
| 20180701 | yes | 2 |
| 20180702 | no | 204 |
| 20180702 | yes | 2 |
| 20180703 | no | 200 |
| 20180704 | no | 177 |
| 20180704 | yes | 2 |
| 20180705 | no | 191 |
| 20180705 | yes | 2 |
| 20180706 | no | 189 |
| 20180706 | yes | 4 |
| 20180707 | no | 153 |
| 20180708 | no | 114 |
| 20180709 | no | 196 |
| 20180709 | yes | 4 |
| 20180710 | no | 221 |
| 20180710 | yes | 3 |
+----------+---------------+----------+
24 rows in set (8.28 sec)
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL