Search code examples
databaset-sqldataexplorer

Query the SO data explorer for questions with 4 close votes within a specific tag


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.


Solution

  • 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.