Page MenuHomePhabricator
Paste P8210

Missing user names in mediawiki_history
ActivePublic

Authored by Neil_P._Quinn_WMF on Mar 16 2019, 4:35 AM.
select
event_entity,
year,
name_status,
count(*) as rows
from (
select
trunc(event_timestamp, "YEAR") as year,
case
when (event_user_text is null and event_user_text_historical is null) then "both names null"
when event_user_text is null then "current name null"
when event_user_text_historical is null then "historical name null"
else "neither name null"
end as name_status,
event_entity
from wmf.mediawiki_history
where
snapshot = "2019-02" and
(event_user_is_anonymous = false or event_user_is_anonymous is null)
) name_status
group by event_entity, year, name_status
order by event_entity, year, name_status
limit 1000
event_entity year name_status rows
page 2001-01-01 both names null 55700
page 2001-01-01 neither name null 14042
page 2002-01-01 both names null 49008
page 2002-01-01 neither name null 116840
page 2003-01-01 both names null 112867
page 2003-01-01 neither name null 358368
page 2004-01-01 both names null 392795
page 2004-01-01 neither name null 1519810
page 2005-01-01 both names null 878957
page 2005-01-01 neither name null 5325512
page 2006-01-01 both names null 975361
page 2006-01-01 neither name null 11400581
page 2007-01-01 both names null 852611
page 2007-01-01 neither name null 16059825
page 2008-01-01 both names null 762957
page 2008-01-01 neither name null 17307836
page 2009-01-01 both names null 689726
page 2009-01-01 neither name null 18055880
page 2010-01-01 both names null 627157
page 2010-01-01 neither name null 20687597
page 2011-01-01 both names null 774898
page 2011-01-01 neither name null 22519951
page 2012-01-01 both names null 614462
page 2012-01-01 neither name null 27897360
page 2013-01-01 both names null 498878
page 2013-01-01 neither name null 44304542
page 2014-01-01 both names null 476827
page 2014-01-01 neither name null 38140294
page 2015-01-01 both names null 474278
page 2015-01-01 neither name null 41118567
page 2016-01-01 both names null 450785
page 2016-01-01 neither name null 37236772
page 2017-01-01 both names null 501982
page 2017-01-01 neither name null 56916080
page 2018-01-01 both names null 464349
page 2018-01-01 neither name null 45789212
page 2019-01-01 both names null 75643
page 2019-01-01 neither name null 6290349
revision 2001-01-01 current name null 10466
revision 2001-01-01 neither name null 49495
revision 2002-01-01 current name null 23307
revision 2002-01-01 neither name null 589095
revision 2003-01-01 current name null 71625
revision 2003-01-01 neither name null 2734521
revision 2004-01-01 current name null 390589
revision 2004-01-01 neither name null 15048578
revision 2005-01-01 current name null 1116799
revision 2005-01-01 neither name null 44435071
revision 2006-01-01 current name null 1631774
revision 2006-01-01 neither name null 116525702
revision 2007-01-01 current name null 1890238
revision 2007-01-01 neither name null 164136377
revision 2008-01-01 current name null 1739469
revision 2008-01-01 neither name null 176086955
revision 2009-01-01 current name null 1347671
revision 2009-01-01 neither name null 191423130
revision 2010-01-01 current name null 1108433
revision 2010-01-01 neither name null 200647902
revision 2011-01-01 current name null 1238481
revision 2011-01-01 neither name null 207241682
revision 2012-01-01 current name null 773137
revision 2012-01-01 neither name null 227046993
revision 2013-01-01 current name null 481157
revision 2013-01-01 neither name null 316325993
revision 2014-01-01 current name null 354684
revision 2014-01-01 neither name null 291251390
revision 2015-01-01 current name null 163311
revision 2015-01-01 neither name null 329873099
revision 2016-01-01 current name null 79500
revision 2016-01-01 neither name null 377724451
revision 2017-01-01 current name null 52920
revision 2017-01-01 neither name null 437583331
revision 2018-01-01 current name null 27745
revision 2018-01-01 neither name null 454032899
revision 2019-01-01 current name null 446
revision 2019-01-01 neither name null 86161942
user 0005-01-01 both names null 1
user 0006-01-01 both names null 1
user 1943-01-01 both names null 1
user 1945-01-01 both names null 4
user 1947-01-01 both names null 1
user 1948-01-01 both names null 2
user 1949-01-01 both names null 1
user 1951-01-01 both names null 1
user 1952-01-01 both names null 2
user 1953-01-01 both names null 1
user 1957-01-01 both names null 1
user 1961-01-01 both names null 1
user 1962-01-01 both names null 2
user 1963-01-01 both names null 1
user 1966-01-01 both names null 1
user 1970-01-01 both names null 2
user 1971-01-01 both names null 1
user 1972-01-01 both names null 5
user 1973-01-01 both names null 6
user 1974-01-01 both names null 4
user 1975-01-01 both names null 4
user 1976-01-01 both names null 1
user 1977-01-01 both names null 5
user 1979-01-01 both names null 3
user 1980-01-01 both names null 5
user 1981-01-01 both names null 2
user 1982-01-01 both names null 2
user 1983-01-01 both names null 3
user 1986-01-01 both names null 2
user 1988-01-01 both names null 1
user 1990-01-01 both names null 1
user 1997-01-01 both names null 1
user 1999-01-01 both names null 1
user 2001-01-01 both names null 532
user 2002-01-01 both names null 2856
user 2003-01-01 both names null 18227
user 2004-01-01 both names null 103250
user 2004-01-01 neither name null 214
user 2005-01-01 both names null 330847
user 2005-01-01 neither name null 69326
user 2006-01-01 both names null 75023
user 2006-01-01 neither name null 4248971
user 2007-01-01 both names null 75928
user 2007-01-01 neither name null 5441023
user 2008-01-01 both names null 135950
user 2008-01-01 neither name null 6195367
user 2009-01-01 both names null 245579
user 2009-01-01 neither name null 8240903
user 2010-01-01 both names null 223498
user 2010-01-01 neither name null 7473671
user 2011-01-01 both names null 1059450
user 2011-01-01 neither name null 6722431
user 2012-01-01 both names null 183702
user 2012-01-01 neither name null 11054130
user 2013-01-01 both names null 102254
user 2013-01-01 neither name null 16310974
user 2014-01-01 both names null 55873
user 2014-01-01 neither name null 24808384
user 2015-01-01 both names null 62899
user 2015-01-01 neither name null 31786764
user 2016-01-01 both names null 57498
user 2016-01-01 neither name null 20777040
user 2017-01-01 both names null 36931
user 2017-01-01 neither name null 20534318
user 2018-01-01 both names null 36592
user 2018-01-01 neither name null 21540802
user 2019-01-01 both names null 252267
user 2019-01-01 neither name null 3754531