Research talk:Wikipedia article creation/Work log/Friday, December 13th
Friday, December 13th[edit]
First task today: regenerate dataset. That line at the end of the query above: "original_namespace != 0". Yeah, that was supposed to be "original_namespace == 0". I've kicked that off. While it's running, I'm hacking on the library that I'll use to process AfC stuff. And looking at Schema:GettingStartedOnRedirect. --Halfak (WMF) (talk) 21:00, 13 December 2013 (UTC)
Yay! Dataset ready.
select left(published, 4) AS year_created, original_namespace, COUNT(*) AS pages, SUM(DATEDIFF(unpublished, published) < 30) AS archived, 1-(SUM(DATEDIFF(unpublished, published) < 30)/COUNT(*)) AS survival FROM nov13_article_page GROUP BY 1,2; +--------------+--------------------+---------+----------+----------+ | year_created | original_namespace | pages | archived | survival | +--------------+--------------------+---------+----------+----------+ | 2001 | 0 | 20819 | 0 | 1.0000 | | 2002 | 0 | 107751 | 0 | 1.0000 | | 2003 | 0 | 149501 | 0 | 1.0000 | | 2004 | 0 | 489626 | 0 | 1.0000 | | 2005 | 0 | 884720 | 12 | 1.0000 | | 2005 | 1 | 4 | NULL | NULL | | 2005 | 2 | 33 | NULL | NULL | | 2005 | 3 | 6 | NULL | NULL | | 2005 | 4 | 32 | NULL | NULL | | 2005 | 10 | 1 | NULL | NULL | | 2005 | 108 | 1 | NULL | NULL | | 2006 | 0 | 1432971 | 3904 | 0.9973 | | 2006 | 1 | 593 | 27 | 0.9545 | | 2006 | 2 | 1531 | 109 | 0.9288 | | 2006 | 3 | 166 | 59 | 0.6446 | | 2006 | 4 | 103 | 35 | 0.6602 | | 2006 | 5 | 4 | 4 | 0.0000 | | 2006 | 6 | 1 | 1 | 0.0000 | | 2006 | 10 | 74 | 7 | 0.9054 | | 2006 | 11 | 1 | NULL | NULL | | 2006 | 15 | 1 | NULL | NULL | | 2006 | 100 | 13 | 1 | 0.9231 | | 2007 | 0 | 1671720 | 4006 | 0.9976 | | 2007 | 1 | 663 | 19 | 0.9713 | | 2007 | 2 | 2941 | 58 | 0.9803 | | 2007 | 3 | 311 | 38 | 0.8778 | | 2007 | 4 | 112 | 17 | 0.8482 | | 2007 | 5 | 7 | 3 | 0.5714 | | 2007 | 10 | 99 | 25 | 0.7475 | | 2007 | 11 | 6 | NULL | NULL | | 2007 | 12 | 3 | 1 | 0.6667 | | 2007 | 100 | 20 | 2 | 0.9000 | | 2008 | 0 | 1862823 | 5816 | 0.9969 | | 2008 | 1 | 568 | 16 | 0.9718 | | 2008 | 2 | 5958 | 65 | 0.9891 | | 2008 | 3 | 316 | 30 | 0.9051 | | 2008 | 4 | 287 | 11 | 0.9617 | | 2008 | 5 | 979 | 2 | 0.9980 | | 2008 | 9 | 1 | NULL | NULL | | 2008 | 10 | 151 | 21 | 0.8609 | | 2008 | 12 | 7 | NULL | NULL | | 2008 | 15 | 2 | NULL | NULL | | 2008 | 100 | 8 | 1 | 0.8750 | | 2009 | 0 | 1537248 | 5107 | 0.9967 | | 2009 | 1 | 763 | 30 | 0.9607 | | 2009 | 2 | 12391 | 122 | 0.9902 | | 2009 | 3 | 196 | 27 | 0.8622 | | 2009 | 4 | 693 | 34 | 0.9509 | | 2009 | 5 | 2634 | 6 | 0.9977 | | 2009 | 10 | 141 | 10 | 0.9291 | | 2009 | 12 | 7 | 1 | 0.8571 | | 2009 | 100 | 9 | NULL | NULL | | 2009 | 108 | 2 | NULL | NULL | | 2010 | 0 | 1405025 | 4988 | 0.9964 | | 2010 | 1 | 878 | 13 | 0.9852 | | 2010 | 2 | 20014 | 110 | 0.9945 | | 2010 | 3 | 193 | 21 | 0.8912 | | 2010 | 4 | 483 | 14 | 0.9710 | | 2010 | 5 | 3354 | 16 | 0.9952 | | 2010 | 7 | 2 | NULL | NULL | | 2010 | 9 | 36 | 1 | 0.9722 | | 2010 | 10 | 174 | 14 | 0.9195 | | 2010 | 15 | 2 | 1 | 0.5000 | | 2010 | 100 | 3 | 1 | 0.6667 | | 2010 | 108 | 2 | 1 | 0.5000 | | 2011 | 0 | 1070728 | 3689 | 0.9966 | | 2011 | 1 | 534 | 12 | 0.9775 | | 2011 | 2 | 23467 | 118 | 0.9950 | | 2011 | 3 | 214 | 13 | 0.9393 | | 2011 | 4 | 261 | 9 | 0.9655 | | 2011 | 5 | 5752 | 13 | 0.9977 | | 2011 | 7 | 1 | NULL | NULL | | 2011 | 8 | 1 | NULL | NULL | | 2011 | 9 | 10 | NULL | NULL | | 2011 | 10 | 94 | 11 | 0.8830 | | 2011 | 15 | 2 | NULL | NULL | | 2011 | 108 | 2 | 1 | 0.5000 | | 2012 | 0 | 1040999 | 455 | 0.9996 | | 2012 | 1 | 43 | 2 | 0.9535 | | 2012 | 2 | 2118 | 10 | 0.9953 | | 2012 | 3 | 29 | 3 | 0.8966 | | 2012 | 4 | 106 | 2 | 0.9811 | | 2012 | 5 | 2632 | 4 | 0.9985 | | 2012 | 9 | 2 | NULL | NULL | | 2012 | 10 | 15 | 1 | 0.9333 | | 2012 | 12 | 3 | NULL | NULL | | 2013 | 0 | 796431 | NULL | NULL | +--------------+--------------------+---------+----------+----------+ 87 rows in set (17.56 sec)
Now to join this with user to get the age of the user at time of page creation. :) --Halfak (WMF) (talk) 23:11, 13 December 2013 (UTC)