I was trying to construct a query in the DataExplorer to find the posts with the most upvotes without any downvotes, but found that (by including the wrong ORDER BY clause) SO lists a bunch of questions at score -1, yet it doesn't record any downvotes (or votes for that matter) for them.

I can't log into the DataExplorer for some reason, but I'm using the following query (to show all posts of score < 1 without any downvotes):

WITH DownVotes AS (
SELECT v.Id AS Id, v.PostId AS PostId
FROM Votes v INNER JOIN VoteTypes vt ON vt.Id = v.VoteTypeId
WHERE vt.Name = 'DownMod'

SELECT p.Id AS [Post Link], p.PostTypeId AS [Post Type], p.Score,
(SELECT COUNT(*) FROM Votes v WHERE v.PostId = p.Id) AS [Num Votes]
FROM Posts p
WHERE p.CommunityOwnedDate IS NULL AND
(SELECT COUNT(*) FROM DownVotes v WHERE v.PostId = p.Id) = 0
AND p.Score < 0

I included the Score column to show that the votes didn't simply happen after the data dump.

Are these votes by deleted users or what may I have found?

  • spam/offensive flags also decrease the score by one
    – balpha StaffMod
    Oct 23, 2010 at 18:09
  • @balpha: Wouldn't those at least show in the "Total Votes" column I've specified?
    – palswim
    Oct 23, 2010 at 18:18
  • AFAIK, flags aren't included in the data dump, at least not those that have aged away. I could be wrong though.
    – balpha StaffMod
    Oct 23, 2010 at 19:04

1 Answer 1


Flags are definitely included in the data dump, all the way back to site inception (or flag type inception, which was probably later):

SELECT * FROM Votes WHERE VoteTypeId IN (4, 12)

but right now in the latest dump, I'm having a hard time finding a lot of posts where the calculated score is out of sync with the denormalized score. This query returns a meagre 2 results in the Stack Overflow data set:

    PostId AS [Post Link],
    UpDnVoteScore + FlagScore AS VoteScore
            p.Id AS PostId,
            p.Score AS PostScore,
            SUM(CASE v.VoteTypeId WHEN 2 THEN 1 WHEN 3 THEN -1 END) AS UpDnVoteScore,
            SUM(CASE v.VoteTypeId WHEN 4 THEN -1 WHEN 12 THEN -1 END) AS FlagScore
            FROM Posts p
            INNER JOIN Votes v ON p.Id = v.PostId
            WHERE v.VoteTypeId IN (2, 3, 4, 12)
            GROUP BY p.Id, p.Score
    ) s
    WHERE PostScore != UpDnVoteScore

Note that in your query, you need to filter out the different vote types -- you're going to get weird results from a query like SELECT COUNT(*) FROM Votes v WHERE v.PostId = p.Id because the Votes table includes voting types such as close votes, delete votes, bounty, etc. See here for a reference of all the vote types.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .