FlaggedRevs bug T164049: reviews per day change

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

https://phabricator.wikimedia.org/T164049

SQL

SELECT fi.d,  als.u AS als, ar.u AS ar, be.u AS be, bn.u AS bn, ce.u AS ce, de.u AS de, en.u AS en, eo.u AS eo, fa.u AS fa, fi.u AS fi, hi.u AS hi, hu.u AS hu, ia.u AS ia, id.u AS id, ka.u AS ka, pl.u AS pl, pt.u AS pt, ru.u AS ru, sq.u AS sq, tr.u AS tr, uk.u AS uk, vec.u AS vec FROM  (select floor(log_timestamp/1000000) as d, sum(1) as c, count(distinct(log_user)) as u, log_action, 'alswiki_p' as wiki from alswiki_p.logging where log_action IN ('approve', 'approve2', 'approve2', 'approve2-i') and log_timestamp > 20170415000000 group by d) as als 
, (select floor(log_timestamp/1000000) as d, sum(1) as c, count(distinct(log_user)) as u, log_action, 'arwiki_p' as wiki from arwiki_p.logging where log_action IN ('approve', 'approve2', 'approve2', 'approve2-i') and log_timestamp > 20170415000000 group by d) as ar 
, (select floor(log_timestamp/1000000) as d, sum(1) as c, count(distinct(log_user)) as u, log_action, 'bewiki_p' as wiki from bewiki_p.logging where log_action IN ('approve', 'approve2', 'approve2', 'approve2-i') and log_timestamp > 20170415000000 group by d) as be 
, (select floor(log_timestamp/1000000) as d, sum(1) as c, count(distinct(log_user)) as u, log_action, 'bnwiki_p' as wiki from bnwiki_p.logging where log_action IN ('approve', 'approve2', 'approve2', 'approve2-i') and log_timestamp > 20170415000000 group by d) as bn 
, (select floor(log_timestamp/1000000) as d, sum(1) as c, count(distinct(log_user)) as u, log_action, 'cewiki_p' as wiki from cewiki_p.logging where log_action IN ('approve', 'approve2', 'approve2', 'approve2-i') and log_timestamp > 20170415000000 group by d) as ce 
, (select floor(log_timestamp/1000000) as d, sum(1) as c, count(distinct(log_user)) as u, log_action, 'dewiki_p' as wiki from dewiki_p.logging where log_action IN ('approve', 'approve2', 'approve2', 'approve2-i') and log_timestamp > 20170415000000 group by d) as de 
, (select floor(log_timestamp/1000000) as d, sum(1) as c, count(distinct(log_user)) as u, log_action, 'enwiki_p' as wiki from enwiki_p.logging where log_action IN ('approve', 'approve2', 'approve2', 'approve2-i') and log_timestamp > 20170415000000 group by d) as en 
, (select floor(log_timestamp/1000000) as d, sum(1) as c, count(distinct(log_user)) as u, log_action, 'eowiki_p' as wiki from eowiki_p.logging where log_action IN ('approve', 'approve2', 'approve2', 'approve2-i') and log_timestamp > 20170415000000 group by d) as eo 
, (select floor(log_timestamp/1000000) as d, sum(1) as c, count(distinct(log_user)) as u, log_action, 'fawiki_p' as wiki from fawiki_p.logging where log_action IN ('approve', 'approve2', 'approve2', 'approve2-i') and log_timestamp > 20170415000000 group by d) as fa 
, (select floor(log_timestamp/1000000) as d, sum(1) as c, count(distinct(log_user)) as u, log_action, 'fiwiki_p' as wiki from fiwiki_p.logging where log_action IN ('approve', 'approve2', 'approve2', 'approve2-i') and log_timestamp > 20170415000000 group by d) as fi 
, (select floor(log_timestamp/1000000) as d, sum(1) as c, count(distinct(log_user)) as u, log_action, 'hiwiki_p' as wiki from hiwiki_p.logging where log_action IN ('approve', 'approve2', 'approve2', 'approve2-i') and log_timestamp > 20170415000000 group by d) as hi 
, (select floor(log_timestamp/1000000) as d, sum(1) as c, count(distinct(log_user)) as u, log_action, 'huwiki_p' as wiki from huwiki_p.logging where log_action IN ('approve', 'approve2', 'approve2', 'approve2-i') and log_timestamp > 20170415000000 group by d) as hu 
, (select floor(log_timestamp/1000000) as d, sum(1) as c, count(distinct(log_user)) as u, log_action, 'iawiki_p' as wiki from iawiki_p.logging where log_action IN ('approve', 'approve2', 'approve2', 'approve2-i') and log_timestamp > 20170415000000 group by d) as ia 
, (select floor(log_timestamp/1000000) as d, sum(1) as c, count(distinct(log_user)) as u, log_action, 'idwiki_p' as wiki from idwiki_p.logging where log_action IN ('approve', 'approve2', 'approve2', 'approve2-i') and log_timestamp > 20170415000000 group by d) as id 
, (select floor(log_timestamp/1000000) as d, sum(1) as c, count(distinct(log_user)) as u, log_action, 'kawiki_p' as wiki from kawiki_p.logging where log_action IN ('approve', 'approve2', 'approve2', 'approve2-i') and log_timestamp > 20170415000000 group by d) as ka 
, (select floor(log_timestamp/1000000) as d, sum(1) as c, count(distinct(log_user)) as u, log_action, 'plwiki_p' as wiki from plwiki_p.logging where log_action IN ('approve', 'approve2', 'approve2', 'approve2-i') and log_timestamp > 20170415000000 group by d) as pl 
, (select floor(log_timestamp/1000000) as d, sum(1) as c, count(distinct(log_user)) as u, log_action, 'ptwiki_p' as wiki from ptwiki_p.logging where log_action IN ('approve', 'approve2', 'approve2', 'approve2-i') and log_timestamp > 20170415000000 group by d) as pt 
, (select floor(log_timestamp/1000000) as d, sum(1) as c, count(distinct(log_user)) as u, log_action, 'ruwiki_p' as wiki from ruwiki_p.logging where log_action IN ('approve', 'approve2', 'approve2', 'approve2-i') and log_timestamp > 20170415000000 group by d) as ru 
, (select floor(log_timestamp/1000000) as d, sum(1) as c, count(distinct(log_user)) as u, log_action, 'sqwiki_p' as wiki from sqwiki_p.logging where log_action IN ('approve', 'approve2', 'approve2', 'approve2-i') and log_timestamp > 20170415000000 group by d) as sq 
, (select floor(log_timestamp/1000000) as d, sum(1) as c, count(distinct(log_user)) as u, log_action, 'trwiki_p' as wiki from trwiki_p.logging where log_action IN ('approve', 'approve2', 'approve2', 'approve2-i') and log_timestamp > 20170415000000 group by d) as tr 
, (select floor(log_timestamp/1000000) as d, sum(1) as c, count(distinct(log_user)) as u, log_action, 'ukwiki_p' as wiki from ukwiki_p.logging where log_action IN ('approve', 'approve2', 'approve2', 'approve2-i') and log_timestamp > 20170415000000 group by d) as uk 
, (select floor(log_timestamp/1000000) as d, sum(1) as c, count(distinct(log_user)) as u, log_action, 'vecwiki_p' as wiki from vecwiki_p.logging where log_action IN ('approve', 'approve2', 'approve2', 'approve2-i') and log_timestamp > 20170415000000 group by d) as vec 
 WHERE  fi.d = als.d  AND  fi.d = ar.d  AND  fi.d = be.d  AND  fi.d = bn.d  AND  fi.d = ce.d  AND  fi.d = de.d  AND  fi.d = en.d  AND  fi.d = eo.d  AND  fi.d = fa.d  AND  fi.d = hi.d  AND  fi.d = hu.d  AND  fi.d = ia.d  AND  fi.d = id.d  AND  fi.d = ka.d  AND  fi.d = pl.d  AND  fi.d = pt.d  AND  fi.d = ru.d  AND  fi.d = sq.d  AND  fi.d = tr.d  AND  fi.d = uk.d  AND  fi.d = vec.d  GROUP by d

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