User:Staeiou/Journal/BRD tools
< User:Staeiou | Journal
mysql> select rvtg_tool, avg(recip_rev_id is not null AND recip_timestamp > post_timestamp AND DATEDIFF(recip_timestamp,post_timestamp) < 7) as response_rate, count(*) from staeiou.reverted_brd_20110115 where post_timestamp > timestamp group by 1 having count(*) > 500; +-------------+---------------+----------+ | rvtg_tool | response_rate | count(*) | +-------------+---------------+----------+ | NULL | 0.4323 | 934461 | | bot-other | 0.0019 | 16523 | | cluebot | 0.0005 | 12828 | | huggle | 0.0500 | 31883 | | mwt | 0.0034 | 594 | | rollback | 0.1736 | 84548 | | twinkle | 0.3575 | 60807 | | undo | 0.4316 | 34941 | | vandalproof | 0.0427 | 4336 | +-------------+---------------+----------+ mysql> select rvtg_tool, avg(TIMESTAMPDIFF(MINUTE,post_timestamp,recip_timestamp)) as avg_response_time, stddev(TIMESTAMPDIFF(MINUTE,post_timestamp,recip_timestamp)) as stddev_response_time, count(*) from staeiou.reverted_brd_20110115 where post_timestamp > timestamp AND DATEDIFF(recip_timestamp,post_timestamp) < 7 group by 1 having count(*) > 50; +-------------+-------------------+----------------------+----------+ | rvtg_tool | avg_response_time | stddev_response_time | count(*) | +-------------+-------------------+----------------------+----------+ | NULL | 1164.2525 | 1967.1558 | 403953 | | huggle | 158.1731 | 842.1298 | 1594 | | rollback | 974.2586 | 1896.7845 | 14678 | | twinkle | 878.1785 | 1740.6369 | 21739 | | undo | 1044.7456 | 1862.3165 | 15079 | | vandalproof | 553.3351 | 1229.7141 | 185 | +-------------+-------------------+----------------------+----------+ mysql> select substring(post_timestamp,1,4) as year, avg(recip_rev_id is not null AND recip_timestamp > post_timestamp AND DATEDIFF(recip_timestamp,post_timestamp) < 7) as response_rate, count(*) from staeiou.reverted_brd_20110115 where post_timestamp > timestamp group by 1; +------+---------------+----------+ | year | response_rate | count(*) | +------+---------------+----------+ | 2001 | 0.5952 | 42 | | 2002 | 0.5419 | 668 | | 2003 | 0.5292 | 3156 | | 2004 | 0.5772 | 16588 | | 2005 | 0.5500 | 56742 | | 2006 | 0.4999 | 179367 | | 2007 | 0.4347 | 222080 | | 2008 | 0.4110 | 176831 | | 2009 | 0.4077 | 151519 | | 2010 | 0.3605 | 102300 | | 2011 | 0.1477 | 23288 | | 2012 | 0.0713 | 1880 | +------+---------------+----------+ 12 rows in set (1.91 sec) mysql> select substring(post_timestamp,1,4) as year, avg(recip_rev_id is not null AND recip_timestamp > post_timestamp AND DATEDIFF(recip_timestamp,post_timestamp) < 7) as response_rate, count(*) from staeiou.reverted_brd_20110115 where post_timestamp > timestamp AND rvtg_tool is NULL group by 1; +------+---------------+----------+ | year | response_rate | count(*) | +------+---------------+----------+ | 2001 | 0.5952 | 42 | | 2002 | 0.5419 | 668 | | 2003 | 0.5292 | 3156 | | 2004 | 0.5772 | 16588 | | 2005 | 0.5500 | 56742 | | 2006 | 0.4999 | 179367 | | 2007 | 0.4347 | 222080 | | 2008 | 0.4110 | 176831 | | 2009 | 0.4077 | 151519 | | 2010 | 0.3605 | 102300 | | 2011 | 0.1477 | 23288 | | 2012 | 0.0713 | 1880 | +------+---------------+----------+ 12 rows in set (3.41 sec) mysql> select substring(post_timestamp,1,4) as year, avg(recip_rev_id is not null AND recip_timestamp > post_timestamp AND DATEDIFF(recip_timestamp,post_timestamp) < 7) as response_rate, count(*) from staeiou.reverted_brd_20110115 where post_timestamp > timestamp AND rvtg_tool is NOT NULL group by 1; +------+---------------+----------+ | year | response_rate | count(*) | +------+---------------+----------+ | 2002 | 0.0000 | 1 | | 2004 | 0.4391 | 690 | | 2005 | 0.3295 | 8734 | | 2006 | 0.1731 | 25364 | | 2007 | 0.1626 | 35900 | | 2008 | 0.1691 | 51338 | | 2009 | 0.1913 | 46134 | | 2010 | 0.3290 | 61919 | | 2011 | 0.1258 | 15785 | | 2012 | 0.0501 | 1097 | +------+---------------+----------+ 10 rows in set (1.10 sec) mysql> select substring(post_timestamp,1,4) as year, avg(recip_rev_id is not null AND recip_timestamp > post_timestamp AND DATEDIFF(recip_timestamp,post_timestamp) < 7) as response_rate, count(*) from staeiou.reverted_brd_20110115 where post_timestamp > timestamp AND rvtg_tool = 'huggle' group by 1; +------+---------------+----------+ | year | response_rate | count(*) | +------+---------------+----------+ | 2008 | 0.0695 | 7134 | | 2009 | 0.0474 | 12668 | | 2010 | 0.0535 | 9055 | | 2011 | 0.0050 | 2782 | | 2012 | 0.0000 | 244 | +------+---------------+----------+ 5 rows in set (0.21 sec) mysql> select substring(post_timestamp,1,4) as year, avg(recip_rev_id is not null AND recip_timestamp > post_timestamp AND DATEDIFF(recip_timestamp,post_timestamp) < 7) as response_rate, count(*) from staeiou.reverted_brd_20110115 where post_timestamp > timestamp AND rvtg_tool = 'twinkle' group by 1; +------+---------------+----------+ | year | response_rate | count(*) | +------+---------------+----------+ | 2007 | 0.2909 | 9511 | | 2008 | 0.3590 | 13750 | | 2009 | 0.4131 | 17059 | | 2010 | 0.3820 | 16931 | | 2011 | 0.1507 | 3304 | | 2012 | 0.0913 | 252 | +------+---------------+----------+ 6 rows in set (0.45 sec) mysql> select substring(post_timestamp,1,4) as year, avg(recip_rev_id is not null AND recip_timestamp > post_timestamp AND DATEDIFF(recip_timestamp,post_timestamp) < 7) as response_rate, count(*) from staeiou.reverted_brd_20110115 where post_timestamp > timestamp AND rvtg_tool = 'rollback' group by 1; +------+---------------+----------+ | year | response_rate | count(*) | +------+---------------+----------+ | 2004 | 0.4390 | 688 | | 2005 | 0.3298 | 8714 | | 2006 | 0.2125 | 19958 | | 2007 | 0.1454 | 19085 | | 2008 | 0.1393 | 22846 | | 2009 | 0.1227 | 9057 | | 2010 | 0.0536 | 2964 | | 2011 | 0.0269 | 1116 | | 2012 | 0.0167 | 120 | +------+---------------+----------+ 9 rows in set (0.41 sec) mysql> select substring(post_timestamp,1,4) as year, avg(recip_rev_id is not null AND recip_timestamp > post_timestamp AND DATEDIFF(recip_timestamp,post_timestamp) < 7) as response_rate, count(*) from staeiou.reverted_brd_20110115 where post_timestamp > timestamp AND rvtg_tool = 'undo' group by 1; +------+---------------+----------+ | year | response_rate | count(*) | +------+---------------+----------+ | 2007 | 0.4625 | 534 | | 2008 | 0.3776 | 143 | | 2009 | 0.4508 | 122 | | 2010 | 0.4805 | 27581 | | 2011 | 0.2304 | 6253 | | 2012 | 0.0974 | 308 | +------+---------------+----------+ 6 rows in set (0.19 sec)