Martin AJ Martin AJ - 2 months ago 7
MySQL Question

How can I count the number of posts that have either zero or positive vote score?

I have this table:

// qanda
+----+----------------------------------------+---------+-----------+------+
| Id | body | related | user_id | free |
+----+----------------------------------------+---------+-----------+------+
| 1 | content of question1 | null | 2 | null |
| 2 | content of first answer for question1 | 1 | 2 | null |
| 3 | content of question2 | null | 6 | 300 |
| 4 | content of second answer for question1 | 1 | 4 | null |
| 5 | content of first answer for question2 | 3 | 2 | null |
| 6 | content of question3 | NULL | 8 | null |
| 7 | content of first answer for question3 | 6 | 4 | null |
| 8 | content of second answer for question3 | 6 | 2 | null |
+----+----------------------------------------+---------+-----------+------+


And I have this query:

SELECT count(1)
FROM qanda question
JOIN qanda answer ON question.Id = answer.related
WHERE answer.related IS NOT NULL
AND answer.user_id = 2
AND question.free IS NULL;


As you know, query above returns the number of answers that belongs to free questions (in this case, it returns
2
)
. Ok all fine. I also have this table:

// votes
+----+---------+-------+
| id | post_id | value |
+----+---------+-------+
| 1 | 2 | 1 |
| 2 | 3 | -1 |
| 3 | 2 | 1 |
| 4 | 8 | -1 |
| 5 | 1 | 1 |
| 6 | 4 | 1 |
| 7 | 2 | -1 |
| 8 | 8 | 1 |
| 9 | 8 | -1 |
| 10 | 8 | -1 |
+----+---------+-------+


And I need to eliminate the answers that have negative total score. So it should return
1
in this case. Because where
qanda.id = 8
, that answer has
-2
total vote score, So I don't want to count it. How can I do that?

Answer

The simplest way to exclude posts whose total votes are less than zero is like this:

SELECT count(1)
FROM qanda question
JOIN qanda answer ON question.Id = answer.related
WHERE answer.related IS NOT NULL
AND answer.user_id = 2
AND question.free IS NULL
AND question.id not in (
  select post_id
  from votes
  group by post_id
  having sum(value) < 0)

The key part here is the having sum(value) < 0 which select posts with net negative votes.


From comments...

To find users who have too many "bad" answers, you should probably return how many "good" answers they made and decide if overall they're a "bad" user. For example, a user that has 5 answers that are all bad is very different from a user with 1000 answers of which only 5 are bad, even though they both have 5 bad answers.

Try this:

select
    sum(score < 0) bad,
    count(*) total,
    sum(score < 0) / sum(.01) percent_bad
from (
    SELECT coalesce(sum(value), 0) score
    FROM qanda question
    JOIN qanda answer ON question.Id = answer.related
    LEFT JOIN votes ON votes.post_id = answer.id
    WHERE answer.related IS NOT NULL
    AND answer.user_id = 2
    AND question.free IS NULL
    AND answer.timestamp > subdate(now(), 365)
    GROUP BY answer.id
) scores

Disclaimer: Code may not compile or work as it was thumbed in on my phone (but there's a reasonable chance it will work)