Flagged revs manual review counts

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

- log_action IN ('approve', 'approve2')
- log_timestamp > 2017

SQL

/*
This is too slow for Quarry so this is just for storing it.

Result 15.4.2017
+------+---------+-----------+---------+
| year | wiki    | reviewers | reviews |
+------+---------+-----------+---------+
| 2017 | dewiki  |      4388 |  188574 |
| 2017 | ruwiki  |      1143 |  132167 |
| 2017 | plwiki  |      1061 |   65660 |
| 2017 | ukwiki  |       263 |   37643 |
| 2017 | trwiki  |       187 |   28941 |
| 2017 | fiwiki  |       204 |   23178 |
| 2017 | arwiki  |       274 |   17038 |
| 2017 | huwiki  |       128 |   16569 |
| 2017 | enwiki  |       706 |   13490 |
| 2017 | idwiki  |       100 |   11109 |
| 2017 | bewiki  |        19 |    4426 |
| 2017 | kawiki  |        19 |    2396 |
| 2017 | eowiki  |        63 |    2143 |
| 2017 | sqwiki  |        33 |    1247 |
| 2017 | fawiki  |        59 |     580 |
| 2017 | alswiki |        12 |     423 |
| 2017 | ptwiki  |        46 |     243 |
| 2017 | bnwiki  |        12 |     171 |
| 2017 | vecwiki |         5 |     111 |
| 2017 | cewiki  |         3 |     104 |
| 2017 | hiwiki  |         5 |      33 |
| 2017 | iawiki  |         1 |       1 |
+------+---------+-----------+---------+
22 rows in set (12 min 42.05 sec)

 SET @alswiki_p_reviewedPagesNSN0=(select a.frs_stat_val from alswiki_p.flaggedrevs_statistics as a, (select max(frs_timestamp) as max_timestamp, frs_stat_key from alswiki_p.flaggedrevs_statistics where frs_stat_key='reviewedPages-NS:0' group by frs_stat_key) AS b WHERE a.frs_timestamp=b.max_timestamp AND a.frs_stat_key=b.frs_stat_key);
SET @ruwiki_p_reviewedPagesNSN0=(select a.frs_stat_val from ruwiki_p.flaggedrevs_statistics as a, (select max(frs_timestamp) as max_timestamp, frs_stat_key from ruwiki_p.flaggedrevs_statistics where frs_stat_key='reviewedPages-NS:0' group by frs_stat_key) AS b WHERE a.frs_timestamp=b.max_timestamp AND a.frs_stat_key=b.frs_stat_key);
SET @dewiki_p_reviewedPagesNSN0=(select a.frs_stat_val from dewiki_p.flaggedrevs_statistics as a, (select max(frs_timestamp) as max_timestamp, frs_stat_key from dewiki_p.flaggedrevs_statistics where frs_stat_key='reviewedPages-NS:0' group by frs_stat_key) AS b WHERE a.frs_timestamp=b.max_timestamp AND a.frs_stat_key=b.frs_stat_key);
SET @plwiki_p_reviewedPagesNSN0=(select a.frs_stat_val from plwiki_p.flaggedrevs_statistics as a, (select max(frs_timestamp) as max_timestamp, frs_stat_key from plwiki_p.flaggedrevs_statistics where frs_stat_key='reviewedPages-NS:0' group by frs_stat_key) AS b WHERE a.frs_timestamp=b.max_timestamp AND a.frs_stat_key=b.frs_stat_key);
SET @eowiki_p_reviewedPagesNSN0=(select a.frs_stat_val from eowiki_p.flaggedrevs_statistics as a, (select max(frs_timestamp) as max_timestamp, frs_stat_key from eowiki_p.flaggedrevs_statistics where frs_stat_key='reviewedPages-NS:0' group by frs_stat_key) AS b WHERE a.frs_timestamp=b.max_timestamp AND a.frs_stat_key=b.frs_stat_key);
SET @huwiki_p_reviewedPagesNSN0=(select a.frs_stat_val from huwiki_p.flaggedrevs_statistics as a, (select max(frs_timestamp) as max_timestamp, frs_stat_key from huwiki_p.flaggedrevs_statistics where frs_stat_key='reviewedPages-NS:0' group by frs_stat_key) AS b WHERE a.frs_timestamp=b.max_timestamp AND a.frs_stat_key=b.frs_stat_key);
SET @arwiki_p_reviewedPagesNSN0=(select a.frs_stat_val from arwiki_p.flaggedrevs_statistics as a, (select max(frs_timestamp) as max_timestamp, frs_stat_key from arwiki_p.flaggedrevs_statistics where frs_stat_key='reviewedPages-NS:0' group by frs_stat_key) AS b WHERE a.frs_timestamp=b.max_timestamp AND a.frs_stat_key=b.frs_stat_key);
SET @iawiki_p_reviewedPagesNSN0=(select a.frs_stat_val from iawiki_p.flaggedrevs_statistics as a, (select max(frs_timestamp) as max_timestamp, frs_stat_key from iawiki_p.flaggedrevs_statistics where frs_stat_key='reviewedPages-NS:0' group by frs_stat_key) AS b WHERE a.frs_timestamp=b.max_timestamp AND a.frs_stat_key=b.frs_stat_key);
SET @hiwiki_p_reviewedPagesNSN0=(select a.frs_stat_val from hiwiki_p.flaggedrevs_statistics as a, (select max(frs_timestamp) as max_timestamp, frs_stat_key from hiwiki_p.flaggedrevs_statistics where frs_stat_key='reviewedPages-NS:0' group by frs_stat_key) AS b WHERE a.frs_timestamp=b.max_timestamp AND a.frs_stat_key=b.frs_stat_key);
SET @sqwiki_p_reviewedPagesNSN0=(select a.frs_stat_val from sqwiki_p.flaggedrevs_statistics as a, (select max(frs_timestamp) as max_timestamp, frs_stat_key from sqwiki_p.flaggedrevs_statistics where frs_stat_key='reviewedPages-NS:0' group by frs_stat_key) AS b WHERE a.frs_timestamp=b.max_timestamp AND a.frs_stat_key=b.frs_stat_key);
SET @idwiki_p_reviewedPagesNSN0=(select a.frs_stat_val from idwiki_p.flaggedrevs_statistics as a, (select max(frs_timestamp) as max_timestamp, frs_stat_key from idwiki_p.flaggedrevs_statistics where frs_stat_key='reviewedPages-NS:0' group by frs_stat_key) AS b WHERE a.frs_timestamp=b.max_timestamp AND a.frs_stat_key=b.frs_stat_key);
SET @kawiki_p_reviewedPagesNSN0=(select a.frs_stat_val from kawiki_p.flaggedrevs_statistics as a, (select max(frs_timestamp) as max_timestamp, frs_stat_key from kawiki_p.flaggedrevs_statistics where frs_stat_key='reviewedPages-NS:0' group by frs_stat_key) AS b WHERE a.frs_timestamp=b.max_timestamp AND a.frs_stat_key=b.frs_stat_key);
SET @fiwiki_p_reviewedPagesNSN0=(select a.frs_stat_val from fiwiki_p.flaggedrevs_statistics as a, (select max(frs_timestamp) as max_timestamp, frs_stat_key from fiwiki_p.flaggedrevs_statistics where frs_stat_key='reviewedPages-NS:0' group by frs_stat_key) AS b WHERE a.frs_timestamp=b.max_timestamp AND a.frs_stat_key=b.frs_stat_key);
SET @bnwiki_p_reviewedPagesNSN0=(select a.frs_stat_val from bnwiki_p.flaggedrevs_statistics as a, (select max(frs_timestamp) as max_timestamp, frs_stat_key from bnwiki_p.flaggedrevs_statistics where frs_stat_key='reviewedPages-NS:0' group by frs_stat_key) AS b WHERE a.frs_timestamp=b.max_timestamp AND a.frs_stat_key=b.frs_stat_key);
SET @trwiki_p_reviewedPagesNSN0=(select a.frs_stat_val from trwiki_p.flaggedrevs_statistics as a, (select max(frs_timestamp) as max_timestamp, frs_stat_key from trwiki_p.flaggedrevs_statistics where frs_stat_key='reviewedPages-NS:0' group by frs_stat_key) AS b WHERE a.frs_timestamp=b.max_timestamp AND a.frs_stat_key=b.frs_stat_key);
SET @ukwiki_p_reviewedPagesNSN0=(select a.frs_stat_val from ukwiki_p.flaggedrevs_statistics as a, (select max(frs_timestamp) as max_timestamp, frs_stat_key from ukwiki_p.flaggedrevs_statistics where frs_stat_key='reviewedPages-NS:0' group by frs_stat_key) AS b WHERE a.frs_timestamp=b.max_timestamp AND a.frs_stat_key=b.frs_stat_key);
SET @vecwiki_p_reviewedPagesNSN0=(select a.frs_stat_val from vecwiki_p.flaggedrevs_statistics as a, (select max(frs_timestamp) as max_timestamp, frs_stat_key from vecwiki_p.flaggedrevs_statistics where frs_stat_key='reviewedPages-NS:0' group by frs_stat_key) AS b WHERE a.frs_timestamp=b.max_timestamp AND a.frs_stat_key=b.frs_stat_key);
SET @bewiki_p_reviewedPagesNSN0=(select a.frs_stat_val from bewiki_p.flaggedrevs_statistics as a, (select max(frs_timestamp) as max_timestamp, frs_stat_key from bewiki_p.flaggedrevs_statistics where frs_stat_key='reviewedPages-NS:0' group by frs_stat_key) AS b WHERE a.frs_timestamp=b.max_timestamp AND a.frs_stat_key=b.frs_stat_key);
SET @enwiki_p_reviewedPagesNSN0=(select a.frs_stat_val from enwiki_p.flaggedrevs_statistics as a, (select max(frs_timestamp) as max_timestamp, frs_stat_key from enwiki_p.flaggedrevs_statistics where frs_stat_key='reviewedPages-NS:0' group by frs_stat_key) AS b WHERE a.frs_timestamp=b.max_timestamp AND a.frs_stat_key=b.frs_stat_key);
SET @ptwiki_p_reviewedPagesNSN0=(select a.frs_stat_val from ptwiki_p.flaggedrevs_statistics as a, (select max(frs_timestamp) as max_timestamp, frs_stat_key from ptwiki_p.flaggedrevs_statistics where frs_stat_key='reviewedPages-NS:0' group by frs_stat_key) AS b WHERE a.frs_timestamp=b.max_timestamp AND a.frs_stat_key=b.frs_stat_key);
SET @fawiki_p_reviewedPagesNSN0=(select a.frs_stat_val from fawiki_p.flaggedrevs_statistics as a, (select max(frs_timestamp) as max_timestamp, frs_stat_key from fawiki_p.flaggedrevs_statistics where frs_stat_key='reviewedPages-NS:0' group by frs_stat_key) AS b WHERE a.frs_timestamp=b.max_timestamp AND a.frs_stat_key=b.frs_stat_key);
SET @cewiki_p_reviewedPagesNSN0=(select a.frs_stat_val from cewiki_p.flaggedrevs_statistics as a, (select max(frs_timestamp) as max_timestamp, frs_stat_key from cewiki_p.flaggedrevs_statistics where frs_stat_key='reviewedPages-NS:0' group by frs_stat_key) AS b WHERE a.frs_timestamp=b.max_timestamp AND a.frs_stat_key=b.frs_stat_key);

SELECT year, wiki, reviewers, reviews FROM (SELECT 'alswiki' as wiki,  floor(log_timestamp/10000000000) as year, count(distinct(log_user)) as reviewers, sum(1) as reviews, @alswiki_p_reviewedPagesNSN0 as reviewedPagesNS0 FROM alswiki_p.logging WHERE log_action IN ('approve', 'approve2') and log_timestamp>20170000000000 group by year
UNION
SELECT 'ruwiki' as wiki,  floor(log_timestamp/10000000000) as year, count(distinct(log_user)) as reviewers, sum(1) as reviews, @ruwiki_p_reviewedPagesNSN0 as reviewedPagesNS0 FROM ruwiki_p.logging WHERE log_action IN ('approve', 'approve2') and log_timestamp>20170000000000 group by year
UNION
SELECT 'dewiki' as wiki,  floor(log_timestamp/10000000000) as year, count(distinct(log_user)) as reviewers, sum(1) as reviews, @dewiki_p_reviewedPagesNSN0 as reviewedPagesNS0 FROM dewiki_p.logging WHERE log_action IN ('approve', 'approve2') and log_timestamp>20170000000000 group by year
UNION
SELECT 'plwiki' as wiki,  floor(log_timestamp/10000000000) as year, count(distinct(log_user)) as reviewers, sum(1) as reviews, @plwiki_p_reviewedPagesNSN0 as reviewedPagesNS0 FROM plwiki_p.logging WHERE log_action IN ('approve', 'approve2') and log_timestamp>20170000000000 group by year
UNION
SELECT 'eowiki' as wiki,  floor(log_timestamp/10000000000) as year, count(distinct(log_user)) as reviewers, sum(1) as reviews, @eowiki_p_reviewedPagesNSN0 as reviewedPagesNS0 FROM eowiki_p.logging WHERE log_action IN ('approve', 'approve2') and log_timestamp>20170000000000 group by year
UNION
SELECT 'huwiki' as wiki,  floor(log_timestamp/10000000000) as year, count(distinct(log_user)) as reviewers, sum(1) as reviews, @huwiki_p_reviewedPagesNSN0 as reviewedPagesNS0 FROM huwiki_p.logging WHERE log_action IN ('approve', 'approve2') and log_timestamp>20170000000000 group by year
UNION
SELECT 'arwiki' as wiki,  floor(log_timestamp/10000000000) as year, count(distinct(log_user)) as reviewers, sum(1) as reviews, @arwiki_p_reviewedPagesNSN0 as reviewedPagesNS0 FROM arwiki_p.logging WHERE log_action IN ('approve', 'approve2') and log_timestamp>20170000000000 group by year
UNION
SELECT 'iawiki' as wiki,  floor(log_timestamp/10000000000) as year, count(distinct(log_user)) as reviewers, sum(1) as reviews, @iawiki_p_reviewedPagesNSN0 as reviewedPagesNS0 FROM iawiki_p.logging WHERE log_action IN ('approve', 'approve2') and log_timestamp>20170000000000 group by year
UNION
SELECT 'hiwiki' as wiki,  floor(log_timestamp/10000000000) as year, count(distinct(log_user)) as reviewers, sum(1) as reviews, @hiwiki_p_reviewedPagesNSN0 as reviewedPagesNS0 FROM hiwiki_p.logging WHERE log_action IN ('approve', 'approve2') and log_timestamp>20170000000000 group by year
UNION
SELECT 'sqwiki' as wiki,  floor(log_timestamp/10000000000) as year, count(distinct(log_user)) as reviewers, sum(1) as reviews, @sqwiki_p_reviewedPagesNSN0 as reviewedPagesNS0 FROM sqwiki_p.logging WHERE log_action IN ('approve', 'approve2') and log_timestamp>20170000000000 group by year
UNION
SELECT 'idwiki' as wiki,  floor(log_timestamp/10000000000) as year, count(distinct(log_user)) as reviewers, sum(1) as reviews, @idwiki_p_reviewedPagesNSN0 as reviewedPagesNS0 FROM idwiki_p.logging WHERE log_action IN ('approve', 'approve2') and log_timestamp>20170000000000 group by year
UNION
SELECT 'kawiki' as wiki,  floor(log_timestamp/10000000000) as year, count(distinct(log_user)) as reviewers, sum(1) as reviews, @kawiki_p_reviewedPagesNSN0 as reviewedPagesNS0 FROM kawiki_p.logging WHERE log_action IN ('approve', 'approve2') and log_timestamp>20170000000000 group by year
UNION
SELECT 'fiwiki' as wiki,  floor(log_timestamp/10000000000) as year, count(distinct(log_user)) as reviewers, sum(1) as reviews, @fiwiki_p_reviewedPagesNSN0 as reviewedPagesNS0 FROM fiwiki_p.logging WHERE log_action IN ('approve', 'approve2') and log_timestamp>20170000000000 group by year
UNION
SELECT 'bnwiki' as wiki,  floor(log_timestamp/10000000000) as year, count(distinct(log_user)) as reviewers, sum(1) as reviews, @bnwiki_p_reviewedPagesNSN0 as reviewedPagesNS0 FROM bnwiki_p.logging WHERE log_action IN ('approve', 'approve2') and log_timestamp>20170000000000 group by year
UNION
SELECT 'trwiki' as wiki,  floor(log_timestamp/10000000000) as year, count(distinct(log_user)) as reviewers, sum(1) as reviews, @trwiki_p_reviewedPagesNSN0 as reviewedPagesNS0 FROM trwiki_p.logging WHERE log_action IN ('approve', 'approve2') and log_timestamp>20170000000000 group by year
UNION
SELECT 'ukwiki' as wiki,  floor(log_timestamp/10000000000) as year, count(distinct(log_user)) as reviewers, sum(1) as reviews, @ukwiki_p_reviewedPagesNSN0 as reviewedPagesNS0 FROM ukwiki_p.logging WHERE log_action IN ('approve', 'approve2') and log_timestamp>20170000000000 group by year
UNION
SELECT 'vecwiki' as wiki,  floor(log_timestamp/10000000000) as year, count(distinct(log_user)) as reviewers, sum(1) as reviews, @vecwiki_p_reviewedPagesNSN0 as reviewedPagesNS0 FROM vecwiki_p.logging WHERE log_action IN ('approve', 'approve2') and log_timestamp>20170000000000 group by year
UNION
SELECT 'bewiki' as wiki,  floor(log_timestamp/10000000000) as year, count(distinct(log_user)) as reviewers, sum(1) as reviews, @bewiki_p_reviewedPagesNSN0 as reviewedPagesNS0 FROM bewiki_p.logging WHERE log_action IN ('approve', 'approve2') and log_timestamp>20170000000000 group by year
UNION
SELECT 'enwiki' as wiki,  floor(log_timestamp/10000000000) as year, count(distinct(log_user)) as reviewers, sum(1) as reviews, @enwiki_p_reviewedPagesNSN0 as reviewedPagesNS0 FROM enwiki_p.logging WHERE log_action IN ('approve', 'approve2') and log_timestamp>20170000000000 group by year
UNION
SELECT 'ptwiki' as wiki,  floor(log_timestamp/10000000000) as year, count(distinct(log_user)) as reviewers, sum(1) as reviews, @ptwiki_p_reviewedPagesNSN0 as reviewedPagesNS0 FROM ptwiki_p.logging WHERE log_action IN ('approve', 'approve2') and log_timestamp>20170000000000 group by year
UNION
SELECT 'fawiki' as wiki,  floor(log_timestamp/10000000000) as year, count(distinct(log_user)) as reviewers, sum(1) as reviews, @fawiki_p_reviewedPagesNSN0 as reviewedPagesNS0 FROM fawiki_p.logging WHERE log_action IN ('approve', 'approve2') and log_timestamp>20170000000000 group by year
UNION
SELECT 'cewiki' as wiki,  floor(log_timestamp/10000000000) as year, count(distinct(log_user)) as reviewers, sum(1) as reviews, @cewiki_p_reviewedPagesNSN0 as reviewedPagesNS0 FROM cewiki_p.logging WHERE log_action IN ('approve', 'approve2') and log_timestamp>20170000000000 group by year
) AS t ORDER BY reviews;
*/

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...