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;