User:EpochFail/Journal/2012-01-26

Hide
mysql> SELECT sample, sum(hide) as hide, count(*) as n, sum(hide)/count(*) as prop
    -> FROM
    -> (
    -> SELECT feedback_id, sample, bit_or(hide) AS hide
    -> FROM hide
    -> INNER JOIN item USING (feedback_id, chunk_id)
    -> INNER JOIN chunk USING (chunk_id)
    -> INNER JOIN sampled_feedback USING (feedback_id)
    -> WHERE assignee NOT IN ("test_user", "Fabrice")
    -> GROUP BY feedback_id, sample
    -> ) AS whocares
    -> GROUP BY sample;
+-------------+------+-----+--------+
| sample      | hide | n   | prop   |
+-------------+------+-----+--------+
| hand-picked |   56 | 137 | 0.4088 |
| post_01-11  |   24 |  89 | 0.2697 |
+-------------+------+-----+--------+
2 rows in set (0.44 sec)

Hide significance
        2-sample test for equality of proportions with continuity correction

data:  c(56, 24) out of c(137, 89) 
X-squared = 3.9766, df = 1, p-value = 0.04614
alternative hypothesis: two.sided 
95 percent confidence interval:
 0.006227799 0.271964607 
sample estimates:
   prop 1    prop 2 
0.4087591 0.2696629 


Useful (everyone, minus "unsure")
mysql> SELECT sample, sum(useful) as useful, count(*) as n, sum(useful)/count(*) as prop
    -> FROM
    -> (
    -> SELECT feedback_id, sample, bit_and(utility="yes" AND NOT unsure) AS useful
    -> FROM item
    -> INNER JOIN chunk USING (chunk_id)
    -> INNER JOIN sampled_feedback USING (feedback_id)
    -> WHERE assignee NOT IN ("test_user", "Fabrice")
    -> AND utility IS NOT NULL
    -> GROUP BY feedback_id, sample
    -> ) AS whocares
    -> GROUP BY sample;
+-------------+--------+-----+--------+
| sample      | useful | n   | prop   |
+-------------+--------+-----+--------+
| 01-09       |    155 | 414 | 0.3744 |
| 12-27       |     96 | 229 | 0.4192 |
| hand-picked |     37 | 180 | 0.2056 |
| post_01-11  |     68 | 128 | 0.5313 |
+-------------+--------+-----+--------+
4 rows in set (0.02 sec)

Useful (everyone, minus "unsure") significance
01-09 vs. 12-27: 
diff=(-0.127,  0.038) 
p=0.3025
01-09 vs. hand-picked: 
diff=(0.090, 0.248) 
p<0.001
01-09 vs. post_01-11: 
diff=(-0.260, -0.054) 
p=0.002
12-27 vs. hand-picked: 
diff=(0.122, 0.306) 
p<0.001
12-27 vs. post_01-11: 
diff=(-0.226,  0.002) 
p=0.054
hand-picked vs. post_01-11: 
diff=(-0.4370620 -0.2143268) 
p<0.001


Specifying the link
Hide
+-------------+------------+------+----+--------+
| sample      | af_link_id | hide | n  | prop   |
+-------------+------------+------+----+--------+
| hand-picked |          0 |   36 | 96 | 0.3750 |
| hand-picked |          4 |   20 | 41 | 0.4878 |
| post_01-11  |          0 |   14 | 53 | 0.2642 |
| post_01-11  |          4 |   10 | 36 | 0.2778 |
+-------------+------------+------+----+--------+
4 rows in set (0.01 sec)

Useful
+-------------+------------+--------+-----+--------+
| sample      | af_link_id | useful | n   | prop   |
+-------------+------------+--------+-----+--------+
| 01-09       |          0 |    155 | 414 | 0.3744 |
| 12-27       |          0 |     96 | 229 | 0.4192 |
| hand-picked |          0 |     21 | 121 | 0.1736 |
| hand-picked |          4 |     16 |  59 | 0.2712 |
| post_01-11  |          0 |     42 |  76 | 0.5526 |
| post_01-11  |          4 |     26 |  52 | 0.5000 |
+-------------+------------+--------+-----+--------+
6 rows in set (0.03 sec)