Category talk:Corrected articles

From Wikinews, the free news source you can write!
Jump to navigation Jump to search

In case anyone cares, here is the by month break down of corrected articles (as of 21:40, 7 April 2011 (UTC)) (Using cl_timestamp field, which is same as DPL uses, so if anyone removed and re-added the cat, it'd be off):

Per year
mysql> select  left(cl_timestamp, 4) as year, count(*) as "Number of articles" from categorylinks where cl_to='Corrected_articles' group by left(cl_timestamp, 4);
+------+--------------------+
| year | Number of articles |
+------+--------------------+
| 2007 |                 20 |
| 2008 |                 10 |
| 2009 |                  7 |
| 2010 |                 19 |
| 2011 |                  5 |
+------+--------------------+
5 rows in set (0.01 sec)

Breakdown per month
mysql> select left(cl_timestamp, 4) as Year, substring(cl_timestamp, 6, 2) as month, count(*) "Number of articles"  from categorylinks where cl_to='Corrected_articles' group by left(cl_timestamp, 7);
+------+-------+--------------------+
| Year | month | Number of articles |
+------+-------+--------------------+
| 2007 | 02    |                 16 |
| 2007 | 04    |                  1 |
| 2007 | 05    |                  1 |
| 2007 | 06    |                  1 |
| 2007 | 07    |                  1 |
| 2008 | 01    |                  3 |
| 2008 | 02    |                  2 |
| 2008 | 03    |                  1 |
| 2008 | 05    |                  2 |
| 2008 | 12    |                  2 |
| 2009 | 01    |                  3 |
| 2009 | 06    |                  2 |
| 2009 | 11    |                  2 |
| 2010 | 04    |                  1 |
| 2010 | 05    |                  1 |
| 2010 | 07    |                  1 |
| 2010 | 08    |                 11 |
| 2010 | 09    |                  1 |
| 2010 | 10    |                  2 |
| 2010 | 11    |                  1 |
| 2010 | 12    |                  1 |
| 2011 | 01    |                  1 |
| 2011 | 03    |                  4 |
+------+-------+--------------------+
23 rows in set (0.00 sec)

Bawolff 21:40, 7 April 2011 (UTC)

I take it this is based on when articles were added to the category, rather than when they were published. E.g., of the five articles that have been added to the category in 2011, three of them were published in 2011, one in 2007, and one in 2005. --Pi zero (talk) 21:53, 7 April 2011 (UTC)
Good point - yes it is. Here's an attempt that should use the first revision to each article as the date:

better query[edit]

mysql> select  year, count(article) as "Number of articles" from  (select cl_from as article, left(min(rev.rev_timestamp), 4) as year from categorylinks c1, revision rev where c1.cl_from = rev.rev_page and c1.cl_to='Corrected_articles' group  by c1.cl_from) foo group by year ;
+------+--------------------+
| year | Number of articles |
+------+--------------------+
| 2005 |                  7 |
| 2006 |                 13 |
| 2007 |                 11 |
| 2008 |                  6 |
| 2009 |                  6 |
| 2010 |                 15 |
| 2011 |                  3 |
+------+--------------------+
7 rows in set (0.00 sec)

by month/year:

mysql> select  left(year, 4) as Yearr, substring(year, 5, 2) as Month,  count(article) as "Number of articles" from  (select cl_from as article, left(min(rev.rev_timestamp), 6) as year from categorylinks c1, revision rev where c1.cl_from = rev.rev_page and c1.cl_to='Corrected_articles' group  by c1.cl_from) foo group by year ;
+-------+-------+--------------------+
| Yearr | Month | Number of articles |
+-------+-------+--------------------+
| 2005  | 03    |                  1 |
| 2005  | 04    |                  2 |
| 2005  | 05    |                  1 |
| 2005  | 07    |                  1 |
| 2005  | 08    |                  1 |
| 2005  | 12    |                  1 |
| 2006  | 01    |                  2 |
| 2006  | 05    |                  2 |
| 2006  | 07    |                  1 |
| 2006  | 08    |                  3 |
| 2006  | 09    |                  3 |
| 2006  | 11    |                  2 |
| 2007  | 01    |                  3 |
| 2007  | 02    |                  1 |
| 2007  | 04    |                  1 |
| 2007  | 07    |                  1 |
| 2007  | 09    |                  2 |
| 2007  | 10    |                  1 |
| 2007  | 12    |                  2 |
| 2008  | 02    |                  1 |
| 2008  | 03    |                  1 |
| 2008  | 04    |                  1 |
| 2008  | 10    |                  1 |
| 2008  | 12    |                  2 |
| 2009  | 01    |                  2 |
| 2009  | 03    |                  1 |
| 2009  | 06    |                  1 |
| 2009  | 10    |                  1 |
| 2009  | 11    |                  1 |
| 2010  | 01    |                  1 |
| 2010  | 03    |                  1 |
| 2010  | 05    |                  1 |
| 2010  | 07    |                  3 |
| 2010  | 08    |                  5 |
| 2010  | 09    |                  1 |
| 2010  | 10    |                  2 |
| 2010  | 12    |                  1 |
| 2011  | 02    |                  1 |
| 2011  | 03    |                  2 |
+-------+-------+--------------------+
39 rows in set (0.00 sec)

Bawolff 00:31, 8 April 2011 (UTC)