Research talk:Wikipedia article creation/Work log/Friday, December 13th

Add discussion
Active discussions

Friday, December 13thEdit

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)

Return to "Wikipedia article creation/Work log/Friday, December 13th" page.