I am trying to build a query that displays the ID's of questions with 4 close votes within a particular tag. I do not care about the date the question was posted all I want is just questions with 4 closed votes so I can do some moderation in my free time and go through them and possibly get some of them put on hold or closed.
SO data explorer has a menu on the right where you can see the available columns.
My specific problem is that I can't seem to find the correct table & column that stores the current close votes count.
I tried something like this but I am failing to get any results
select COUNT(PostId) from Votes where PostId = '19577105' and VoteTypeId = '6'
Any pointer in the right direction is appreciated.
SELECT
Posts.ID
FROM
Posts
INNER JOIN Votes ON Posts.ID = Votes.PostID
WHERE
Posts.PostTypeID = 1
AND Posts.Tags LIKE '%C#%'
AND Votes.VoteTypeID = 6
GROUP BY
Posts.ID
HAVING
COUNT(DISTINCT Votes.ID) > 4
This takes a while to run so could probably be optimized. The Tag filter could probably use full text search which may be faster if it is enabled.