This will resolve a number of underlying problems with our metrics infrastructure, most notably:
* we depend on Wikistats, which is moribund and extremely slow, for calculating active editors
* the new active editors number which I custom calculate is not comparable to the Wikistats active editor number because it includes edits from all namespaces rather than just content namespaces
* ever since we merged the MobileWebEditing logs into the Edit logs (T110272), they've become too massive to query for overall statistics about mobile edits (which, to be fair, isn't what they're designed for)
* the current editor-month table doesn't include anonymous edits, so it can't be used to calculated overall numbers of edits
The query I'll use to aggregate this data is:
```
CREATE TABLE `editor_month_upgraded` (
`wiki` varbinary(50) NOT NULL DEFAULT ''255) not null,
`month` date NOT NULL DEFAULT ''not null,
`local_user_id` int(11) NOT NULL DEFAULT '0' unsigned not null,
`user_name` varbinary(191) DEFAULT NULL255),
`edits` int(11) DEFAULT NULL unsigned not null,
`main_ns_edits` int(11) DEFAULT NULL unsigned not null,
`deleted_edits` int(11) DEFAULT NULL unsigned not null,
`mobile_edits` int(11) DEFAULT NULL unsigned not null,
`bot` boolean,
`user_registration` datetime DEFAULT NULL,,
PRIMARY KEY (`wiki`,`month`,`user_name`),
KEY `wiki_user` (`wiki`,`user_name`),
KEY `user_name` (`user_name`)
) ENGINE=TokuDB DEFAULT CHARSET=binary `compression`='tokudb_zlib';
```
```
SELECT
LEFT(rev_timestamp, 6) as `month`,
rev_user as `local_user_id`,
COUNT(*) as `edits`,
SUM(IF(SELECT
DATABASE() as wiki,
STR_TO_DATE(CONCAT(month, "01"), "%Y%m%d") as month,
local_user_id,
user_name,
SUM(edits) as edits,
SUM(main_ns_edits) as main_ns_edits,
SUM(edits * deleted) as deleted_edits,
SUM(mobile_edits) as mobile_edits,
IF((ug_group = "bot" OR ufg_group = "both"), 1, 0) as bot,
STR_TO_DATE(user_registration, "%Y%m%d%H%i%S") as user_registration
FROM
(
SELECT
LEFT(rev_timestamp, 6) as `month`,
rev_user as `local_user_id`,
COUNT(*) as `edits`,
SUM(IF(page_namespace = 0, 1, 0)) as main_ns_edits,
SUM(IF(ct_tag = "mobile edit", 1, 0)) as mobile_edits,
0 as `deleted`
FROM revision
LEFT JOIN page ON rev_page = page_id
LEFT JOIN change_tag ON rev_id = ct_rev_id AND ct_tag = "mobile edit"
GROUP BY LEFT(rev_timestamp, 6), rev_user
UNION ALL
SELECT
LEFT(ar_timestamp, 6) as `month`,
ar_user as `local_user_id`,
COUNT(*) as `edits`,
SUM(IF(ar_namespace = 0, 1, 0)) as main_ns_edits,
SUM(IF(ct_tag = "mobile", 1, 0)) as mobile_edits,
1 as `deleted`
FROM archive
LEFT JOIN change_tag ON ar_rev_id = ct_rev_id AND ct_tag = "mobile"
GROUP BY LEFT(ar_timestamp, 6), ar_user
) revs
LEFT JOIN user ON local_user_id = user_id
LEFT JOIN user_groups ON local_user_id = ug_user AND ug_group = "bot"
LEFT JOIN user_former_groups ON local_user_id = ufg_user AND ufg_group = "bot"
GROUP BY month, local_user_idpage_namespace = 0, 1, 0)) as main_ns_edits,
SUM(IF(ct_tag = "mobile edit", 1, 0)) as mobile_edits,
0 as `deleted`
FROM revision
LEFT JOIN page ON rev_page = page_id
LEFT JOIN change_tag ON rev_id = ct_rev_id AND ct_tag = "mobile edit"
GROUP BY LEFT(rev_timestamp, 6), rev_user
UNION ALL
SELECT
LEFT(ar_timestamp, 6) as `month`,
ar_user as `local_user_id`,
COUNT(*) as `edits`,
SUM(IF(ar_namespace = 0, 1, 0)) as main_ns_edits,
SUM(IF(ct_tag = "mobile edit", 1, 0)) as mobile_edits,
1 as `deleted`
FROM archive
LEFT JOIN change_tag ON ar_rev_id = ct_rev_id AND ct_tag = "mobile edit"
GROUP BY LEFT(ar_timestamp, 6), ar_user
) revs
LEFT JOIN user ON local_user_id = user_id
LEFT JOIN user_groups ON local_user_id = ug_user AND ug_group = "bot"
LEFT JOIN user_former_groups ON local_user_id = ufg_user AND ufg_group = "bot"
GROUP BY month, local_user_id;
```
This looks like an unholy monstrosity to me, but it actually seems like it runs without causing too many problems. It took 33 minutes to run for all of history on the ruwiki (one of the largest), and as far as I can tell from Tendril didn't cause any server disruption.